Databricks Data Engineer Professional Question 69
Select 3You are working on a Spark table named 'customer_data' that needs to be updated with new customer records. The table uses a Type 1 approach for handling updates, where changes are overwritten without keeping historical data. Which of the following strategies can be used to update one or more records in the 'customer_data' table?
- A
Use the Delta Lake
MERGEoperation to match and update existing records and insert new ones. - B
Rewrite the entire table after joining the existing data with the updated data and filtering the latest records.
- C
Use the
UPDATESQL command directly on a standard parquet-based Spark table. - D
Use the Spark SQL
INSERT OVERWRITEstatement along with a proper deduplication strategy. - E
Use the Delta Lake
DELETEcommand to remove outdated records and then append the updated records.
Show answer and explanation
Correct answers: A, B, D
Explanation
To perform Type 1 updates (overwrite without maintaining history) on Spark tables, strategies like using Delta Lake's MERGE operation, rewriting the entire table after joining and filtering, or leveraging the INSERT OVERWRITE statement with proper deduplication are recommended. While Delta Lake provides ACID capabilities for efficient updates, non-Delta approaches require careful handling to ensure data correctness. Unsupported commands like UPDATE on standard parquet tables or incomplete workflows like using DELETE without deduplication are not suitable for Type 1 updates.
- A. Correct.
This is correct. Delta Lake's
MERGEoperation is explicitly designed to handle updates and inserts (upserts) efficiently, making it a reliable option for Type 1 updates. - B. Correct.
This is correct. Rewriting the entire table by merging the existing and updated data ensures the table reflects the latest changes, especially if Delta Lake is not being used.
- C. Incorrect.
This is incorrect. The
UPDATEcommand is not natively supported on standard parquet-based Spark tables. It requires Delta Lake or another table format that supports ACID transactions. - D. Correct.
This is correct. The
INSERT OVERWRITEstatement can be used to replace the contents of a table with a new dataset, which can include deduplicated and updated records. - E. Incorrect.
This is incorrect. While the
DELETEcommand in Delta Lake can remove outdated records, appending updated records afterward does not ensure a proper Type 1 update as it won't handle overwrites or deduplication properly.