Databricks Data Engineer Associate Question 342
Single answerA data engineering team is tasked with deduplicating data in a Delta Lake table during an ETL pipeline workflow. The source data contains duplicate rows, and the target table must retain only the most recent record for each unique key. Which of the following commands should the team use to achieve this deduplication?
- A
MERGE INTO target_table USING source_table ON condition WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...
- B
INSERT INTO target_table SELECT DISTINCT * FROM source_table
- C
UPDATE target_table SET column = value WHERE condition
- D
DELETE FROM target_table WHERE condition
Show answer and explanation
Correct answer: A
Explanation
The MERGE statement is specifically designed for use cases like deduplication, as it allows conditional updates and inserts based on a match condition. This ensures that duplicates are removed and only the most relevant data is retained in the target table.
- A. Correct.
This is the correct command to use because the MERGE statement allows the team to compare the source and target tables based on a condition, enabling deduplication by updating or inserting records conditionally.
- B. Incorrect.
Using INSERT with SELECT DISTINCT will not ensure deduplication in the target table if duplicates already exist. It only avoids inserting duplicate rows from the source.
- C. Incorrect.
UPDATE is used for modifying existing records, but it cannot deduplicate data across tables or consolidate unique records.
- D. Incorrect.
DELETE removes rows from a table but does not help in deduplicating or merging data between the source and target tables.