Databricks Data Engineer Professional Question 187
Select 3You are implementing a Slowly Changing Dimension (SCD) Type 2 table in Delta Lake to track historical changes in a customer dataset. The source data arrives in both batch and streaming modes. Which of the following steps are necessary to correctly implement this architecture?
- A
Use a Delta Lake MERGE operation to update existing records and insert new records.
- B
Implement watermarking for the streaming input to handle late-arriving data.
- C
Delete older historical records from the Delta Lake table to maintain table size.
- D
Include a surrogate key and effective date fields to track changes over time.
- E
Configure Delta Lake Change Data Feed (CDF) to automatically handle SCD logic.
Show answer and explanation
Correct answers: A, B, D
Explanation
To implement an SCD Type 2 table with Delta Lake for both streaming and batch workloads, you must use a Delta Lake MERGE operation to handle updates and inserts, implement watermarking to manage late-arriving data in streaming scenarios, and include fields like surrogate keys and effective dates to track historical changes. Deleting historical records is inappropriate for SCD Type 2, and while Delta Lake CDF helps track changes, it does not replace the need for explicit SCD logic.
- A. Correct.
Correct: The Delta Lake MERGE operation is essential for implementing SCD Type 2, as it allows for both updates to existing records and the insertion of new ones while maintaining historical data.
- B. Correct.
Correct: Watermarking is critical in streaming pipelines to handle late-arriving data and ensure accurate processing of changes.
- C. Incorrect.
Incorrect: Deleting historical records contradicts the purpose of an SCD Type 2 table, which is to retain historical data for tracking changes.
- D. Correct.
Correct: Including a surrogate key and effective date fields is a standard practice in SCD Type 2 implementations to uniquely identify records and track their validity over time.
- E. Incorrect.
Incorrect: While Delta Lake Change Data Feed (CDF) can track changes in Delta tables, it does not automatically implement the SCD logic. Manual implementation using operations like MERGE is still required.