Databricks Data Engineer Associate Question 339
Single answerYou are working with a Delta Lake table named customer_data, which may contain duplicate records due to multiple data ingestion processes. You need to ensure the table contains only the latest version of each customer's record based on the customer_id column. Which of the following commands would you use to deduplicate the data while writing it to the Delta table?
- A
MERGE INTO customer_data USING source_data ON customer_data.customer_id = source_data.customer_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *
- B
INSERT INTO customer_data SELECT DISTINCT * FROM source_data
- C
UPDATE customer_data SET * FROM source_data WHERE customer_data.customer_id = source_data.customer_id
- D
DELETE FROM customer_data WHERE EXISTS (SELECT 1 FROM source_data WHERE customer_data.customer_id = source_data.customer_id)
Show answer and explanation
Correct answer: A
Explanation
The MERGE command is the most appropriate choice for deduplicating data in Delta Lake tables. It allows you to perform updates on existing rows and insert new rows based on a matching key, which in this scenario is customer_id. This ensures the table only contains the latest version of each customer's record, effectively deduplicating the data.
- A. Correct.
This option is correct because the MERGE command is explicitly designed to handle deduplication and upserts, ensuring the latest records are updated, and new records are inserted into the Delta table.
- B. Incorrect.
This option is incorrect because it uses DISTINCT, which can remove duplicate rows but does not perform updates or ensure deduplication based on a specific key like
customer_id. - C. Incorrect.
This option is incorrect because the UPDATE command alone does not handle deduplication or insert new records. It only modifies existing rows in the table.
- D. Incorrect.
This option is incorrect because the DELETE command removes rows but does not handle updates or insert missing records to deduplicate the data.