Prompt and context
A team reads Parquet change files with DuckDB and maintains a customer dimension and price history locally. Explain when to use MERGE INTO, and how to handle duplicate keys, deletes, SCD Type 2, reruns, and recovery.
What the interviewer tests
They are checking your understanding of match predicates, action ordering, source uniqueness, and transaction boundaries, plus your ability to turn SQL convenience into a rerunnable pipeline with quality, audit, and rollback.
Questions to clarify
Ask for the business key, event-time ordering, and whether history is required. Clarify duplicate, late, and delete records and whether the target can be rebuilt after failure. Do not treat MERGE as automatic CDC exactly-once.
30-second answer
“I would deduplicate in staging by business key and event time, verify at most one applicable change per target key, and run MERGE in a transaction. A current-state table uses matched updates and unmatched inserts; an SCD Type 2 table closes the old version and inserts a new one. Deletes and reruns need explicit semantics. Each batch records its input snapshot and checks, and a failure rolls back and retries the same snapshot.”
Step-by-step deep dive
Define target semantics
Separate a current snapshot from history. The current table wants latest state; SCD Type 2 needs validfrom, validto, is_current, and version constraints.
Build auditable staging first
Keep source file, batch id, read time, and row hash. Deduplicate by business key, event time, and source priority; quarantine ties that cannot be resolved.
Design matches and actions
Use a stable business key in the match predicate, not mutable attributes. Specify matched updates, unmatched inserts, and any not-matched-by-source delete policy so late data is not accidentally deleted.
Handle SCD Type 2
Close the current row before inserting a changed version; identical content should not create a version. Enforce that each key has one current row with a constraint or quality query.
Guarantee reruns and transactions
Batch id and a frozen input snapshot make a run repeatable. Put MERGE, audit, and batch status in one transaction; retry the same staging data instead of rereading changing files.
Monitor and roll back
Compare source and target insert, update, and delete counts; check orphan keys, duplicate current rows, and reversed times. Keep a before snapshot or rebuild path and roll back by batch on anomalies.
Model answer
I would land the Parquet files in staging with batch metadata, deduplicate by business key and event time, and gate on row counts, nulls, and delete ratios. The current table uses a stable-key update/insert; the history table closes changed versions and inserts new ones so each key has one current row. MERGE, audit, and batch status share a transaction, and failures retry the same snapshot. I monitor per-batch deltas and duplicate versions and roll back or rebuild by batch when needed.
Common mistakes
Merging raw files directly
Duplicate source rows can make outcomes ambiguous or update twice; stage, deduplicate, and gate first.
Matching on mutable columns
A renamed customer can look like a new key. Match on a stable business identifier.
Inserting only for SCD Type 2
Old versions stay current and queries return multiple current rows. Maintain validity and uniqueness.
Rereading files on retry
Files may have changed or new files may appear. Freeze the input snapshot and batch id.
Follow-up questions
What if two different events for one key arrive together?
Choose one with an explicit event-time, version, or source-priority rule; otherwise quarantine instead of silently overwriting.
How do you handle a late delete?
Compare delete event time with the target version, record a tombstone, and prevent an old delete from overwriting a newer update.
How do you prove a failed MERGE did not partially commit?
Keep MERGE, audit, and batch status in one transaction; inspect target counts and status after failure, then retry the same staging snapshot.
When would you avoid MERGE?
For near-full replacements, complex matching, or cross-system transactions, build a new table and atomically swap it to reduce row-action uncertainty.