DELETE-INSERT and MERGE Patterns

There are exactly three idempotent write patterns. Know all three, know when each applies, and know which one to reach for first. The interviewer is testing whether you have a default pattern, not whether you can invent one on the spot. Pattern 1: Partition Overwrite (DELETE-INSERT) Pattern 2: MERGE (Upsert) MERGE is idempotent when the match key is stable and the update is deterministic. Running it twice: first run inserts new rows and updates changed rows. Second run finds all rows already match and makes zero changes. Pattern 3: Write-Audit-Publish Write output to a staging location. Run validation checks. If checks pass, atomically swap staging into production (e.g., Hive partition swap, Iceberg snapshot commit). If the job reruns, it overwrites staging and re-validates. Production onl