MERGE Semantics: Upserts That Survive a Rerun

MERGE in one shape

Every warehouse spells it slightly differently, and every version is the same three-part sentence: match target rows to source rows on a key, update the ones that matched, insert the ones that did not.

MERGE INTO orders t
USING orders_batch s
   ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
  status = s.status, amount_usd = s.amount_usd, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, amount_usd, updated_at)
  VALUES (s.order_id, s.status, s.amount_usd, s.updated_at);

That is the real syntax, and it is what Snowflake, BigQuery, Redshift, Databricks, and Iceberg through Spark or Athena all accept. MERGE is also the standard form: it entered ISO SQL in SQL:2003, which is why every warehouse spells it the same way. SQLite has no MERGE keyword, so this level grades the identical semantics through SQLite's upsert clause, the Postgres-style INSERT ... ON CONFLICT DO UPDATE:

INSERT INTO orders (order_id, status, amount_usd, updated_at)
SELECT order_id, status, amount_usd, updated_at
FROM orders_batch
WHERE 1 = 1
ON CONFLICT(order_id) DO UPDATE SET
  status     = excluded.status,
  amount_usd = excluded.amount_usd,
  updated_at = excluded.updated_at;

Worth getting right if it comes up: ON CONFLICT DO UPDATE is a PostgreSQL extension that SQLite adopted, not standard SQL. It is the portable stand-in you reach for on Postgres and SQLite, and the standard upsert is the MERGE above. Saying it the other way round in an interview is the kind of confident inversion an interviewer remembers.

Read it as the same three parts. The conflict target ON CONFLICT(order_id) is the join key, and it only works because order_id carries a primary key or unique constraint. excluded is the pseudo-table holding the row that was about to be inserted, so excluded.status is the source value and a bare status is the target value. The WHERE 1 = 1 is a SQLite parsing requirement, not a filter: when the values come from a SELECT, the parser cannot tell whether ON is starting the upsert clause or a join, so the SELECT needs a WHERE before it.

Why an upsert is the load you want

Here is the failure that makes this the most-asked pipeline question there is. A nightly job appends its batch to a table, the run half-fails, an operator retries it, and now every order in the batch exists twice. Every downstream count, sum, and dashboard is wrong, and nothing errored.

An upsert sink cannot double count. Rerunning it re-matches the same keys and rewrites the same values, so the table after two runs is byte for byte the table after one. That property has a name and it is the spine of this whole course: idempotency. Level 6 introduced it as a pipeline habit. Here it becomes a property of the table itself, enforced by a key.

That is why the grader for this lesson runs your script twice and compares. A script that passes once and drifts on the second run has not solved the problem.

Table

Three sinks, one retry. Only the bottom two survive it, and only the upsert survives a partial batch.

sink	after run 1	after retry (run 2)	safe to retry
append only	N rows	2N rows	no
delete partition + insert	N rows	N rows	yes, per partition
upsert on a key	N rows	N rows	yes, per row

Dedupe before you merge

There is a trap sitting inside the source. Real batches arrive from an at-least-once system, so the same key can appear several times, out of order, sometimes as an exact duplicate redelivery. If you merge that batch directly, the row that wins is whichever one the engine happened to apply last, which is not a guarantee you can build on. Most warehouses will not even let you try: MERGE raises an error when more than one source row matches the same target row.

The fix is one window function, and it is the single most-cited SQL interview task in this space. You have written it before: Level 5's CDC changelog apply used exactly this window to collapse an insert, update, and delete stream to the latest version per key. Same window, same job, this time standing in front of a table-format MERGE instead of a warehouse table:

WITH latest AS (
  SELECT order_id, status, amount_usd, updated_at,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
  FROM orders_batch
)
SELECT order_id, status, amount_usd, updated_at FROM latest WHERE rn = 1

Partition by the natural key, order by the change timestamp descending, keep rank 1. That collapses the batch to one row per key, newest wins, and only then do you merge.

Copy-on-write vs merge-on-read

Object storage cannot edit a file in place, so "update three rows" has to become "rewrite something." Iceberg gives you two strategies and expects you to pick per table.

• Copy-on-write rewrites every data file that contains a changed row and commits the replacements. Writes are expensive, reads are as fast as a fresh table. Right for a table written once a night and read all day.
• Merge-on-read writes a small delete file (a deletion vector, in the v3 spec) beside the data instead of rewriting it, and readers apply the deletes on the fly. Writes are cheap, reads pay a growing tax until compaction folds the deletes in. Right for frequent small updates and streaming CDC.

The judgment sentence: copy-on-write when reads dominate, merge-on-read when writes dominate, and compaction is what stops merge-on-read from degrading. The next lesson is that compaction.

Common mistake: merging a batch that still contains duplicate keys. It passes on clean data, then produces a nondeterministic winner or a hard error the first night the source redelivers, which is exactly the night you are asleep.

Interview nuance: "how do you get exactly-once?" has one correct answer and it is not a technology. True exactly-once delivery is impossible; at-least-once delivery plus an idempotent sink equals effectively exactly-once. Say that sentence, then name the sink you would build (an upsert on the natural key), and the follow-up about retries answers itself.

On a real platform this differs. You would write MERGE INTO and the engine would decide how to rewrite files, then log the whole thing as one Iceberg snapshot you can roll back to. Two more things change at scale: the merge key is usually a surrogate key resolved from the natural key, and a production loader guards the update with a condition like WHEN MATCHED AND s.updated_at > t.updated_at so a late-arriving old row can never overwrite a newer one. That guard is what you build in the apply.