Databricks Data Engineer Professional Question 197
Single answerYou are tasked with implementing a data pipeline to manage a customer dimension table in Databricks. The requirements are as follows: historical data changes need to be tracked, and new records should be inserted without overwriting existing records. Additionally, you need to ensure that each record reflects the current state of the data at the time of any update. Which Slowly Changing Dimension (SCD) type is most appropriate for this use case?
- A
SCD Type 0
- B
SCD Type 1
- C
SCD Type 2
- D
SCD Type 3
Show answer and explanation
Correct answer: C
Explanation
The use case specifies the need to track historical changes and insert new records without overwriting existing records, which aligns with SCD Type 2. This approach ensures that the table captures the entire history of changes for each record by maintaining multiple rows for the same entity, with appropriate metadata such as effective start and end dates.
- A. Incorrect.
SCD Type 0 is not suitable because it involves no updates to the dimension table, and historical changes are not tracked.
- B. Incorrect.
SCD Type 1 overwrites existing records with new data and does not track historical changes, which does not meet the requirements.
- C. Correct.
SCD Type 2 is the correct choice as it tracks historical changes by maintaining multiple records for each entity, capturing changes over time while also retaining the current state.
- D. Incorrect.
SCD Type 3 only tracks limited historical changes by adding a new column for the previous value, which does not fully meet the requirement to track all historical changes.