Data Engineering Slowly Changing Dimensions (SCDs) Questions and Answers

Question 1

A data warehouse contains a customer dimension where, for compliance reasons, a complete and auditable history of every customer's address must be maintained. When a customer moves, the previous address record must be preserved, and a new record with the new address must become the active record. Which Slowly Changing Dimension (SCD) type is required to meet this specification?

Accepted Answer

SCD Type 2

Answer

SCD Type 2 is designed specifically for maintaining a full historical record of changes. It works by adding a new row for each change, while the old row is preserved and marked as no longer current, often using effective date ranges or a 'is_current' flag. This ensures that no historical information is lost, which is critical for trend analysis and compliance.

Question 2

A marketing team asks a data engineer to modify the `DimEmployee` table. They need to track a salesperson's current sales region but also want to know their immediately prior region for short-term campaign analysis. They want to avoid the storage growth and query complexity associated with adding new rows for each change. Which SCD type provides the most direct and efficient solution for this specific requirement?

Accepted Answer

SCD Type 3

Answer

SCD Type 3 is designed to track limited history by adding a new column to store a previous value. In this case, a `PreviousSalesRegion` column would be added. When a salesperson's region changes, the value from `CurrentSalesRegion` is moved to `PreviousSalesRegion`, and `CurrentSalesRegion` is updated. This meets the requirement without adding new rows.

Question 3

A data engineer is managing a product dimension table where minor corrections, such as fixing a typo in a product description, occur occasionally. The business requirement is to only store the most recent and accurate version of the product information, and there is no need to retain a history of the incorrect data. Which of the following SCD approaches is the most appropriate and simplest to implement?

Accepted Answer

SCD Type 1

Answer

SCD Type 1 involves overwriting the old attribute value with the new one. This approach does not preserve any history, making it ideal for situations like correcting errors or when historical context is irrelevant. It is the simplest SCD method to implement.

Question 4

When implementing an SCD Type 2 dimension table for employees, a data engineer uses `effective_start_date` and `effective_end_date` columns to track the time period for which each record is valid. When an employee's department changes, which of the following actions must be performed?

Accepted Answer

Update the `effective_end_date` of the current record to the day before the change and insert a new record for the new department.

Answer

The standard procedure for managing SCD Type 2 with effective dates is to 'close' the currently active record by updating its `effective_end_date`. A new record is then inserted with the updated information, and its `effective_start_date` is set to the date the change became effective. This maintains a continuous and non-overlapping history.

Question 5

In a dimensional model, certain attributes are not expected to change under any circumstances after they are created. An example would be a person's `DateOfBirth` in a customer dimension. Which SCD type is used to classify and handle such fixed attributes?

Accepted Answer

SCD Type 0

Answer

SCD Type 0, or 'Fixed Attribute', is used for attributes whose values are never expected to change. The ETL process is designed to disregard any updates to a Type 0 attribute, ensuring the original value is retained.

Data Engineering Practice Test

Data Engineering Practice Test

Data Engineering Slowly Changing Dimensions (SCDs) Questions and Answers