Databricks Data Engineer Professional Question 192
Select 4You are tasked with implementing a Slowly Changing Dimension (SCD) Type 2 table using Delta Lake to track historical changes in customer data. The system must handle both batch updates (e.g., daily data imports) and real-time streaming updates (e.g., customer profile changes from a web application). Which of the following steps are necessary to correctly design and implement this architecture?
- A
Use Delta Lake's MERGE INTO operation to handle both batch and streaming updates by matching records on a unique key.
- B
Configure a watermark for the streaming source to ensure late-arriving data is properly handled.
- C
Use Delta Lake's OPTIMIZE command to compact small files created by streaming writes.
- D
Set the Delta table's overwrite mode to overwrite existing data to ensure only the latest records are stored for each customer.
- E
Implement a column to track the start and end timestamps of each record's validity for historical tracking.
Show answer and explanation
Correct answers: A, B, C, E
Explanation
To implement an SCD Type 2 table using Delta Lake with both batch and streaming workloads, you must use Delta's MERGE INTO for handling updates/inserts, configure watermarks to manage late-arriving data in streaming, and optimize the table to maintain performance. Additionally, tracking the validity of records using start and end timestamps is critical for historical data consistency. Overwriting the table would break the SCD Type 2 schema, so it is not appropriate in this scenario.
- A. Correct.
Correct. MERGE INTO is a critical operation in Delta Lake for implementing SCD Type 2 as it allows for updating, inserting, or deleting records based on specific conditions, making it suitable for both batch and streaming updates.
- B. Correct.
Correct. Configuring a watermark ensures that streaming queries handle late-arriving data efficiently, which is important for ensuring the integrity of the SCD implementation.
- C. Correct.
Correct. The OPTIMIZE command is used to compact small files generated by frequent streaming writes, which improves query performance and overall table efficiency.
- D. Incorrect.
Incorrect. Overwrite mode would replace the entire table, which is not suitable for SCD Type 2 as it requires historical data to be preserved for tracking changes over time.
- E. Correct.
Correct. A start and end timestamp column is essential for tracking the validity of each record in SCD Type 2 implementations, enabling historical queries to access data as it existed at any point in time.