A data engineer receives a daily batch of customer records containing both brand-new customers and updates to existing ones, keyed by customer_id. They must insert the new customers and overwrite changed attributes for existing ones in a single atomic Delta operation. Which approach fits?
Answer & explanation
Correct answer: D
WHY D: MERGE INTO matches on customer_id, updating matched rows and inserting unmatched ones in a single atomic upsert. This is the standard CDC/SCD pattern.
WHY NOT A: Delta does not enforce primary-key uniqueness (the constraint is informational), so INSERT INTO would append duplicates rather than reject them, and existing customers would not be updated.
WHY NOT B: Delete-then-insert can produce the right rows, but it is two statements rather than one atomic operation, and the premise is false: MERGE combines conditional inserts and updates in one statement.
WHY NOT C: COPY INTO has no key-based upsert. It loads files (force = true simply reloads files already loaded) and does not update existing rows by key.
WHY NOT E: CREATE OR REPLACE TABLE overwrites the entire table with the batch, dropping historical rows that are not in today's delivery rather than preserving them.