Databricks Data Engineer Professional Question 189
Select 2You are implementing a Slowly Changing Dimension (SCD) Type 2 table using Delta Lake for a customer data warehouse. The system must handle both batch and streaming updates. Which of the following architectural and operational approaches ensure accurate processing and support for both workloads?
- A
Use Delta Lake MERGE INTO operation to combine incoming updates with the existing dimension table.
- B
Configure the Delta table with automatic schema evolution to accommodate new columns in the streaming workload.
- C
Implement a watermark on the streaming source to handle late-arriving data and prevent duplicate records in the dimension table.
- D
Use the Delta Lake OPTIMIZE command with ZORDER to reduce the storage size of the dimension table.
- E
Partition the Delta table by a surrogate key to improve the performance of MERGE operations.
Show answer and explanation
Correct answers: A, C
Explanation
To implement SCD Type 2 logic with Delta Lake, the MERGE INTO operation is the cornerstone for combining new updates with existing records based on matching keys. This ensures historical data is preserved while updates are applied correctly. In a streaming context, using a watermark helps manage late-arriving data, preventing duplicate or incorrect records in the dimension table. Other options, like schema evolution, ZORDER, and surrogate key partitioning, while useful in other scenarios, are not directly relevant or optimal for SCD Type 2 table implementation.
- A. Correct.
Correct: The MERGE INTO operation is essential for implementing SCD Type 2 tables as it allows updating, inserting, or deleting records in Delta Lake based on business logic.
- B. Incorrect.
Incorrect: While schema evolution is useful for handling new columns dynamically, it is not directly relevant to implementing SCD Type 2 logic for handling historical changes.
- C. Correct.
Correct: A watermark ensures that the system can handle late-arriving data gracefully, which is crucial for maintaining accuracy in streaming workloads when updating a Slowly Changing Dimension table.
- D. Incorrect.
Incorrect: While the OPTIMIZE command with ZORDER improves query performance, it does not directly relate to implementing SCD Type 2 tables for batch or streaming workloads.
- E. Incorrect.
Incorrect: Partitioning by a surrogate key is not a common practice for SCD Type 2 tables. Instead, partitioning is typically done by a date or range of dates for performance optimization.