Databricks Data Engineer Professional Question 191
Select 3You are tasked with implementing a Slowly Changing Dimension (SCD) Type 2 table to maintain historical records in Delta Lake. The data is ingested in near real-time using a structured streaming job, while batch jobs are periodically run to update historical data. Which of the following steps are required to implement this architecture correctly?
- A
Use Merge statements in Delta Lake to handle updates and inserts for SCD Type 2 logic.
- B
Configure the structured streaming job to overwrite the entire Delta table with each micro-batch.
- C
Include a 'valid_from' and 'valid_to' timestamp column in the schema to track the historical validity of records.
- D
Enable Delta Lake's Change Data Feed (CDF) to track changes and simplify downstream processing.
- E
Partition the Delta table by a surrogate key to improve query performance for historical lookups.
Show answer and explanation
Correct answers: A, C, D
Explanation
To implement an SCD Type 2 table using Delta Lake with streaming and batch workloads, you must use Delta Lake's Merge functionality to handle inserts and updates. Additionally, metadata columns like 'valid_from' and 'valid_to' are necessary to track historical record validity. Enabling Change Data Feed (CDF) further simplifies downstream data processing by exposing changes directly. Overwriting the table and partitioning by a surrogate key are not suitable approaches for this scenario.
- A. Correct.
Correct. Merge statements in Delta Lake are crucial for implementing SCD Type 2 tables as they allow you to perform inserts, updates, and deletes in a single operation.
- B. Incorrect.
Incorrect. Overwriting the entire table with each micro-batch would erase historical data, which defeats the purpose of maintaining a Slowly Changing Dimension table.
- C. Correct.
Correct. 'Valid_from' and 'valid_to' columns are essential to track the time range during which a record is valid, fulfilling the requirements of SCD Type 2.
- D. Correct.
Correct. Delta Lake's Change Data Feed (CDF) simplifies tracking changes, which is particularly useful for downstream systems needing to process historical data changes.
- E. Incorrect.
Incorrect. Partitioning by a surrogate key is not an efficient partitioning strategy for SCD Type 2 tables. Instead, partitioning by date or other relevant dimensions is more appropriate for query performance.