Databricks Data Engineer Professional Question 68
Select 3You are tasked with updating multiple records in a Delta table to reflect the latest changes in a Type 1 slowly changing dimension (SCD). Which of the following strategies can you use in Databricks to accomplish this efficiently?
- A
Use a merge operation with the 'WHEN MATCHED' clause to update existing records and 'WHEN NOT MATCHED' clause to insert new records.
- B
Use a SQL UPDATE statement directly on the Delta table to modify the necessary rows.
- C
Delete the existing table and insert the updated dataset as a new table to replace it.
- D
Perform an overwrite operation by writing the new dataset to the same Delta table path with the 'overwrite' mode.
- E
Use a DataFrame join to identify updated records and write the modified dataset back to the Delta table in 'overwrite' mode.
Show answer and explanation
Correct answers: A, B, E
Explanation
To update records in a Delta table for a Type 1 SCD, efficient strategies include using the Delta merge operation, SQL UPDATE statements, or a combination of DataFrame joins and overwrite mode. Each of these methods leverages the transactional capabilities of Delta Lake to ensure data consistency and reliability. Deleting and recreating the table or overwriting the entire dataset without identifying changes are not efficient or safe strategies for updating records.
- A. Correct.
This is a correct option. The merge operation in Delta Lake is specifically designed to handle updates and inserts in a single operation, making it an efficient and reliable choice for updating records.
- B. Correct.
This is a correct option. Delta Lake supports SQL UPDATE statements, making it possible to directly modify specific rows in the table.
- C. Incorrect.
This is not a recommended approach. Deleting and recreating the entire table is inefficient and can lead to data loss or schema misalignment, particularly in large datasets.
- D. Incorrect.
This is incorrect. While 'overwrite' mode can replace the entire table, it does not specifically handle updates, and it would overwrite the entire dataset, losing data not included in the new dataset.
- E. Correct.
This is a correct option. By using a DataFrame join to identify changes and writing the updated dataset back to the Delta table in 'overwrite' mode, you can achieve the desired updates while preserving schema consistency.