Data Engineering Slowly Changing Dimensions (SCDs) Questions and Answers 1 — 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?
- SCD Type 1
- SCD Type 2 (Correct answer)
- SCD Type 3
- SCD Type 4
Correct answer: SCD Type 2
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?
- SCD Type 1
- SCD Type 2
- SCD Type 0
- SCD Type 3 (Correct answer)
Correct answer: SCD Type 3
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?
- SCD Type 2
- SCD Type 1 (Correct answer)
- SCD Type 6
- SCD Type 0
Correct answer: SCD Type 1
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?
- Delete the old record and insert a new record with the current date as the `effective_start_date`.
- Update the `department` field in the existing record and set the `effective_start_date` to the current date.
- Update the `effective_end_date` of the current record to the day before the change and insert a new record for the new department. (Correct answer)
- Add a new column named `previous_department` and populate it with the old department name.
Correct 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.
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?
- SCD Type 1
- SCD Type 3
- SCD Type 0 (Correct answer)
- SCD Type 2
Correct answer: SCD Type 0
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.
Question 6: A data engineering team needs to support two types of analysis on their `Salesperson` dimension: 1) Fast, simple queries to find the current territory for each salesperson. 2) In-depth historical analysis of a salesperson's territory changes over time. Which hybrid SCD approach is specifically designed to satisfy both of these requirements efficiently?
- SCD Type 6 (Correct answer)
- SCD Type 4
- SCD Type 2
- SCD Type 1
Correct answer: SCD Type 6
SCD Type 6 is a hybrid approach that combines Type 1, Type 2, and Type 3 (1+2+3=6). It tracks full history by adding a new row (Type 2 behavior) but also overwrites a 'CurrentTerritory' column in all of that salesperson's rows with the latest territory (Type 1 behavior). This allows analysts to get the current value easily without complex date filtering, while still having the full history available for deeper analysis.
Data Engineering Slowly Changing Dimensions (SCDs) Questions and Answers 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?