Databricks Data Engineer Professional Question 188
Select 3You are implementing a Slowly Changing Dimension (SCD) Type 2 table using Delta Lake to track historical changes in customer data. The table receives updates from a batch job every night and also streams incremental updates from a real-time source during the day. Which of the following steps are necessary to correctly implement this architecture?
- A
Use Delta Lake MERGE to upsert records from both batch and streaming sources into the SCD Type 2 table.
- B
Ensure that all streaming updates are first written to a staging Delta table before processing them into the SCD Type 2 table.
- C
Use a DELETE operation on the SCD Type 2 table to remove old records before processing new updates.
- D
Generate surrogate keys for the SCD Type 2 table to uniquely identify each version of a record.
- E
Use the 'optimize.autoCompact' Delta Lake configuration to automatically compact small files generated by streaming workloads.
Show answer and explanation
Correct answers: A, B, D
Explanation
To implement an SCD Type 2 table with Delta Lake, you need to use Delta Lake's MERGE functionality to handle updates and inserts efficiently. Streaming updates should be staged to maintain data consistency, and surrogate keys should be generated for uniquely tracking historical versions. Deleting old records contradicts the purpose of SCD Type 2, and while file compaction is beneficial for performance, it is not a mandatory step for SCD Type 2 logic.
- A. Correct.
Correct: Delta Lake MERGE is essential for implementing SCD Type 2 as it allows you to handle both inserts and updates efficiently for batch and streaming sources.
- B. Correct.
Correct: Writing streaming updates to a staging Delta table ensures data consistency and avoids directly modifying the SCD table during incremental processing.
- C. Incorrect.
Incorrect: Deleting old records contradicts the principles of SCD Type 2, as historical data must be preserved for tracking changes over time.
- D. Correct.
Correct: Surrogate keys are necessary to uniquely identify historical versions of records in an SCD Type 2 table.
- E. Incorrect.
Incorrect: While 'optimize.autoCompact' can improve performance for streaming workloads, it is not directly required for implementing SCD Type 2 logic.