Databricks Data Engineer Professional Question 70
Select 2You are working on a data pipeline that processes customer records in Delta Lake. The table uses a Type 1 schema, meaning no history is maintained and existing records are overwritten when updated. When new customer data arrives, you need to update one or more records in the table efficiently. Which of the following strategies can be used to update the records in the Delta table?
- A
Use the Delta Lake
MERGE INTOstatement to match and update the records. - B
Perform a full table overwrite using the DataFrame
overwritemode. - C
Use the Delta Lake
UPDATEstatement with a condition to modify the records. - D
Delete the existing table and write the updated records as a new table.
- E
Use Spark SQL's
INSERT INTOwith theOVERWRITEoption to replace the table.
Show answer and explanation
Correct answers: A, C
Explanation
When working with Delta Lake tables, the most efficient strategies for updating one or more records in a Type 1 schema are using the MERGE INTO statement for upserts and the UPDATE statement for conditionally modifying records. These approaches are optimized for Delta Lake and avoid the unnecessary overhead of rewriting the entire table, making them ideal for managing updates in large datasets.
- A. Correct.
The Delta Lake
MERGE INTOstatement is specifically designed for upserts (insert and update operations). It allows you to efficiently match new data with existing data based on a condition and update the corresponding records. - B. Incorrect.
Performing a full table overwrite is not an efficient or recommended strategy for updating a subset of records in a large table. It would unnecessarily rewrite the entire table, which is costly and not scalable.
- C. Correct.
The Delta Lake
UPDATEstatement allows you to modify specific records in a table based on a given condition, making it a valid and efficient strategy for Type 1 updates. - D. Incorrect.
Deleting the table and creating a new one is not a recommended or efficient strategy for updating records, as it would lead to data loss and additional overhead in managing the table schema and metadata.
- E. Incorrect.
The
INSERT INTOwithOVERWRITEdoes not provide a mechanism for updating specific records, and it would replace the entire table, which is not aligned with the Type 1 update requirement.