Databricks Data Engineer Associate Question 340
Single answerYou are working with a Delta Lake table named 'customers' that contains duplicate data due to multiple ingestion jobs. You want to deduplicate the table by merging the incoming updates from a staging table called 'staging_customers' into the 'customers' table. Which of the following commands ensures that duplicates are handled correctly during the write operation?
- A
MERGE INTO customers USING staging_customers ON customers.id = staging_customers.id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *
- B
INSERT INTO customers SELECT DISTINCT * FROM staging_customers
- C
UPDATE customers SET * FROM staging_customers WHERE customers.id = staging_customers.id
- D
MERGE INTO customers USING staging_customers ON customers.id = staging_customers.id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT *
Show answer and explanation
Correct answer: A
Explanation
The MERGE command is specifically designed to deduplicate data by performing updates for matching records and insertions for non-matching records. This ensures that the data in the target table remains consistent and free of duplicates after the operation.
- A. Correct.
This is the correct command for deduplicating data upon writing. The MERGE statement ensures data is updated or inserted based on matching conditions, effectively handling duplicates.
- B. Incorrect.
Using INSERT with SELECT DISTINCT can remove duplicates, but it does not address updates for existing records, making it unsuitable for deduplication in Delta Lake.
- C. Incorrect.
UPDATE commands cannot handle insertions for non-matching records, so this does not achieve deduplication across the two tables.
- D. Incorrect.
This MERGE command deletes matching records instead of updating them, which does not fulfill the deduplication requirement.