Databricks Data Engineer Professional Question 195
Single answerYou are designing a data pipeline in Databricks to manage customer data changes. The requirements are as follows:
- Historical versions of customer records must be maintained for audit purposes.
- Updates to customer address information should create new records while preserving previous ones.
- Changes to non-address fields such as email should overwrite the existing record. Which of the following table implementations best meets these requirements?
- A
Implement an SCD Type 0 table for all fields.
- B
Implement an SCD Type 1 table for all fields.
- C
Implement an SCD Type 2 table for all fields.
- D
Implement an SCD Type 2 table for address changes and an SCD Type 1 table for non-address fields.
Show answer and explanation
Correct answer: D
Explanation
To meet the requirements, the implementation needs to use SCD Type 2 for address changes to preserve historical records and SCD Type 1 for non-address fields to allow overwrites without creating duplicates. This approach ensures both auditability and efficient data handling.
- A. Incorrect.
SCD Type 0 tables are immutable and do not allow updates or new records for changes, which does not meet the requirement to track changes or overwrite non-address fields.
- B. Incorrect.
SCD Type 1 tables overwrite existing data without preserving historical records, which violates the requirement to maintain historical versions.
- C. Incorrect.
SCD Type 2 tables create new rows for every change, which might meet the requirement for address changes but does not handle overwrites for non-address fields.
- D. Correct.
This option meets the requirements as SCD Type 2 maintains historical records for address changes, while SCD Type 1 overwrites non-address fields without creating unnecessary duplicate records.