Databricks Certified Data Engineer - Associate

Explanation

The correct answer is MERGE.

Why MERGE is correct:

1. MERGE command (also known as UPSERT) is specifically designed for handling duplicate records in Delta tables
2. It allows you to perform conditional updates, inserts, and deletes based on matching criteria
3. You can use MERGE with conditions like WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT
4. This is the standard pattern for avoiding duplicate records in data pipelines

Why other options are incorrect:

• DROP: This command deletes tables or columns, not used for writing data while avoiding duplicates
• IGNORE: This is not a valid SQL command for handling duplicates in Delta tables. While there might be options like ignoreDuplicates in some APIs, it's not a SQL command

Example usage:

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

This pattern ensures that existing records are updated and new records are inserted, preventing duplicates based on the matching condition.