Upserts and Merges
Replacing rows by key instead of appending them — the write that makes re-running safe, and the assumptions it quietly depends on.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
What has to be true for running the same load twice to leave the table in exactly the state one run would have produced?
Every downstream reader benefits invisibly and never knows. The direct consumer is the engineer re-running a pipeline at 3 a.m., for whom the difference between a merge and an append is the difference between a repair and an incident.
A merge is defined at the grain of its key: one target row per key value. If the key is order_id the table holds one row per order; if the key is (order_id, line_no) it holds one row per line. Getting that wrong does not produce an error — it produces a table at a grain nobody declared (Grain: What Does One Row Represent?).
Append. It is the cheapest write any storage system offers, it never has to read the target, it parallelises perfectly, and for immutable events landing in a fresh partition it is exactly right. Almost every pipeline starts here and most should.
The load is re-run after a timeout that was actually a success, and the period is now present twice. This is the unsafe-backfill behaviour in src/de/sim/pipeline.ts: append a second copy rather than replace, and every additive measure over the period roughly doubles (What Backfills Break).
- The load is re-run after a timeout that was actually a success, and the period is now present twice. This is the
unsafe-backfillbehaviour insrc/de/sim/pipeline.ts: append a second copy rather than replace, and every additive measure over the period roughly doubles (What Backfills Break). - A record represents mutable state — an order whose status moves from
pendingtopaidtorefunded— and appending gives three rows where consumers expect one, so every count of orders is a count of state changes instead (What a CDC Event Contains). - The merge key is not unique in the staged data, so the merge either raises or updates the target row an unspecified number of times, and which payload wins depends on execution order.
- The merge matches on a key but the staged relation also contains keys from outside the intended range, so the
WHEN NOT MATCHEDbranch inserts them and the backfill silently widens (Validating a Backfill Before You Publish). - Last-wins is implemented ordering by arrival time, so an update that was committed earlier but arrived later overwrites the newer state, and the table settles on a value that was true two hours ago (CDC Ordering and Transaction Boundaries).
- Deletes at the source are never represented, so the merge keeps rows that no longer exist and the table monotonically accumulates entities that were deleted months ago (DELETE: What Does Gone Mean?).
What is actually happening
- A merge is a conditional write: for each incoming row, if a target row with this key exists, update it; otherwise insert. The property that matters is convergence — applying the same input any number of times leaves the target in the same state — and that is what makes a re-run a no-op instead of an event (Idempotent Data Pipelines).
- The convergence rests on two assumptions that the merge itself does not check. The key must be unique in the source of the merge, and the resolution between competing versions must be deterministic. Break either and the merge is a non-deterministic write wearing the vocabulary of a safe one (Surrogate Keys).
- Partition replacement is the other idempotent write and it is coarser: instead of matching keys, it swaps a whole partition for a newly computed one. It needs no key and no uniqueness assumption, and it requires the partition boundary to align with the unit you are correcting (Partitioning).
- Underneath, an analytical merge is usually a join between the target and the staged relation followed by a rewrite of the affected files, because columnar files are immutable. This is why a merge that touches one row per file can rewrite most of the table — the same amplification a copy-on-write update has in a database (UPDATE, DELETE and Dead Tuples covers the transactional analogue).
- Delete handling is a separate decision from update handling. A merge with only matched and not-matched branches cannot express "this entity no longer exists", so a source delete becomes either a soft-delete flag you carry or a divergence you do not notice (What a CDC Event Contains).
- Because a merge makes re-running invisible, it removes a signal as well as a risk. Something has to count loads, or the fact that a pipeline ran four times last night is knowable only from logs nobody reads (Pipeline Metrics).
Two writes, one of which survives being run twice
The difference is one line of SQL and it decides whether re-running is a repair or a duplication. Everything else in this module — safe backfills, safe retries, safe late-data handling — depends on this one property being present.
The comparison is deliberately narrow, because the temptation is to treat this as an optimisation question. It is not: an append and a merge produce different results when run twice, and "run twice" is not an exceptional condition. It is what happens after any timeout, any retry, any ambiguous failure and any correction.
What the better version costs is real and worth naming. It reads the target, it needs a key you can guarantee, and it makes a duplicated run invisible rather than harmless-and-obvious. All three are acceptable; none of them is free.
`INSERT INTO fct_orders SELECT ... FROM staging WHERE order_date BETWEEN :start AND :end`. Fast, parallel, no read of the target, no key required. Run it once and the period is correct. Run it twice and the period is present twice, with every task green and every additive measure roughly doubled.
`MERGE INTO fct_orders USING staging ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...`, preceded by an assertion that `order_id` is unique in `staging` and that `staging` contains no keys outside the range. Run it any number of times and the target holds one row per order.
The two statements differ in whether the target's current contents affect the outcome. An append is a function of its input alone, so its effects accumulate; a merge is a function of input and target together, so its effects converge. Every recovery operation in this module is a repeated application of the same work, which makes convergence the property that decides whether recovery is possible at all.
What a merge actually does, stage by stage
Written as a single statement, a merge looks atomic and simple. Underneath it is a sequence, and each step has a precondition that the statement itself does not enforce — which is why merges fail in ways that surprise people who read only the SQL.
Read the guarantees column: the merge statement provides atomicity and convergence, and it provides them conditionally. The conditions are supplied by the two stages before it, and those stages exist in your pipeline only if you put them there.
The last stage is the one that is genuinely optional and genuinely necessary. A merge leaves no trace in the data that it ran twice, so unless something counts loads, the operational history of the table exists only in logs with their own retention.
- 1Stage the computed rows
Writes the recomputed range into a relation no consumer reads.
guarantees The target is untouched no matter what happens here, so everything after this point is inspectable before it is committed to.
fails by Being skipped — merging directly from a subquery removes the only chance to assert anything about the input (Planning a Backfill).
- 2Assert the key is unique in the staged rows
Groups the staged relation by the merge key and looks for counts above one.
guarantees The merge's precondition holds for this run. Without it, convergence is a hope rather than a property.
fails by Passing on data whose key is unique today and will not be after a source consolidation, which is why it belongs on every run rather than in a design document (Data Tests).
- 3Assert the staged keys are inside the range
Checks that no staged row belongs to a partition outside the declared range.
guarantees The not-matched branch cannot silently widen the operation beyond what was planned.
fails by Being omitted, which is how a merge inserts rows the plan never mentioned and the control-period check in Validating a Backfill Before You Publish catches afterwards.
- 4Match on the key
Joins target and staged rows to classify each incoming row as matched or not matched.
guarantees Nothing about ordering between concurrent merges — two loads over overlapping keys resolve by commit order.
fails by Matching on a key that does not prune partitions, so the join reads the whole target rather than the affected range (Partition Pruning).
- 5Apply and rewrite
Updates matched rows, inserts unmatched ones, and rewrites the files those rows live in.
guarantees Atomicity of the statement where the engine or table format provides it, and convergence given the assertions above.
fails by Rewriting far more than expected, because affected rows are scattered across many files and the file is the unit of rewrite (File Compaction).
- 6Record the load
Writes a load id, timestamp and the engine's inserted/updated counts to a run log.
guarantees That a repeated run is observable, which the data itself will no longer show.
fails by Not existing, which is the default, and which makes "did this run twice" unanswerable a week later (Pipeline Observability).
Three of the six stages are assertions and bookkeeping. That ratio is what separates a merge you can rely on from a MERGE statement.
Deletes, and the divergence nobody watches
A merge with two branches can express "this is new" and "this changed". It cannot express "this is gone". So a table maintained by merge diverges upward from its source, one deleted entity at a time, and nothing in the pipeline notices because every check is about rows that are present.
The three options are a soft-delete flag driven by tombstone records from CDC, a periodic full comparison against the source key set, or an explicit decision that the analytical table is an append-only history in which deletion is not represented. All three are defensible; the failure is having none of them and believing the first.
The SQL below shows the soft-delete form, which is the usual answer for analytical data: the row stays, carrying the fact that it was deleted and when. That preserves history, which is what the analytical copy is for, and it puts an obligation on every consumer to filter — an obligation that belongs in the model rather than in each query (Model Layering).
1-- Precondition 1: the key is unique in the staged rows. Any row here2-- means the merge below is not deterministic. Fail the run.3SELECT order_id FROM staging.orders_delta4GROUP BY order_id HAVING COUNT(*) > 1;5 6-- Precondition 2: nothing staged belongs outside the declared range,7-- or the NOT MATCHED branch will widen the operation silently.8SELECT MIN(order_date), MAX(order_date) FROM staging.orders_delta;9 10MERGE INTO analytics.fct_orders AS t11USING (12 -- Resolve to one row per key, ordered by a SOURCE-side position.13 -- Arrival order is not commit order; using it settles on stale state.14 SELECT * FROM (15 SELECT s.*,16 ROW_NUMBER() OVER (PARTITION BY order_id17 ORDER BY source_lsn DESC) AS rn18 FROM staging.orders_delta s19 ) WHERE rn = 120) AS s21 ON t.order_id = s.order_id22 -- Give the optimiser something to prune on, or the match reads the23 -- whole target rather than the affected partitions.24 AND t.order_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-25'25 26WHEN MATCHED AND s.op = 'D' THEN UPDATE SET27 t.is_deleted = TRUE,28 t.deleted_at = s.source_ts,29 t.load_id = s.load_id30 31WHEN MATCHED AND s.op IN ('I','U') THEN UPDATE SET32 t.customer_id = s.customer_id,33 t.country = s.country,34 t.amount_minor = s.amount_minor,35 t.is_refunded = s.is_refunded,36 t.is_deleted = FALSE,37 t.load_id = s.load_id38 39-- A delete for a row we never saw is not an error: it means the insert40-- and the delete both fell inside one batch. Recording it as deleted41-- keeps the table consistent with the source.42WHEN NOT MATCHED AND s.op = 'D' THEN INSERT43 (order_id, order_date, is_deleted, deleted_at, load_id)44VALUES (s.order_id, s.order_date, TRUE, s.source_ts, s.load_id)45 46WHEN NOT MATCHED THEN INSERT47 (order_id, order_date, customer_id, country, amount_minor,48 is_refunded, is_deleted, load_id)49VALUES (s.order_id, s.order_date, s.customer_id, s.country,50 s.amount_minor, s.is_refunded, FALSE, s.load_id);Three things carry the lesson. The ordering inside the USING subquery makes last-wins deterministic and source-ordered. The range predicate in the ON clause is a performance decision that is also a safety one. And op = 'D' appearing in both a matched and a not-matched branch handles the entity whose entire life fits inside one batch — an edge case that is rare, real, and produces a permanently missing row when it is omitted (What a CDC Event Contains).
MERGE support, the availability of multiple WHEN MATCHED branches with conditions, and whether a merge can prune partitions from the ON clause all vary by engine and table format, and some engines raise on a source row that matches several target rows while others silently pick one. Verify the semantics for yours before relying on the branch structure above.
How to build it
Most important first.
- Choose the key from the business, not from the payload. It should identify the real-world thing, be stable across producer retries, and be unique by a rule someone can state — and then assert that uniqueness in a test rather than believing it (Data Tests).
- Assert uniqueness on the staged relation before the merge runs. It is the merge's precondition, it costs one aggregate query, and it converts a non-deterministic result into a loud failure (Validating a Backfill Before You Publish).
- Order resolution by a source-side monotonic value — a log sequence number, a commit position — and never by arrival time or by a timestamp the consumer assigned (CDC Ordering and Transaction Boundaries).
- Prefer partition replacement when the correction aligns with partitions and the format supports an atomic swap. It has fewer assumptions than a merge and rewrites a bounded amount (Atomic Publish).
- Constrain the merge to the range it is supposed to touch, either by adding the range predicate to the match condition or by asserting the staged relation contains no keys outside it. This is what stops a merge from silently widening.
- Decide deletes explicitly: carry a tombstone and soft-delete on merge, or accept that the table only grows and say so in the contract (Data Contracts).
- Record a load id and load timestamp on every merged row. It costs one column and it is what makes a bad load identifiable afterwards (Deduplication).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- A merge guarantees the target converges to one row per key under repeated application of the same input — a scoped claim about the output write, not about the pipeline as a whole, and it rests entirely on the key being unique in the input (Exactly-Once: Input Consumption, State Update, Output Write).
- It guarantees nothing about ordering between concurrent merges. Two loads merging overlapping keys at the same time resolve by whichever commits last, which is a property of the engine rather than of your logic (Transactions and ACID).
- Atomicity is per merge statement where the engine provides it. A pipeline that issues one merge per partition has as many observable intermediate states as it has partitions.
- It guarantees nothing about deletes. Entities removed at the source persist in the target unless the merge was explicitly written to remove them, and this divergence grows silently (Reconciliation).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Assert uniqueness of the merge key on the staged input *before* the merge and on the target *after* it. The first is the precondition; the second catches the case where the key was not as unique as the model claimed (Data Tests).
- It misses ordering errors entirely: a merge that converged on the wrong version of a record has exactly one row per key and is perfectly unique. Catching that requires comparing the resolved value against the source (Reconciliation).
- It also misses divergence from deletes, which shows up only as a slowly growing row count relative to the source — a reconciliation on counts for a closed period is the check that sees it (Volume Anomalies).
- A merge is a slower write than an append because it reads the target, so a pipeline that merges every micro-batch trades freshness for idempotency. At small batch sizes this is often the dominant cost in the load (File Size and the Small-Files Problem).
- Partition replacement is fast per partition and coarse: the whole partition is unavailable-or-old until the swap, then instantly new. That is usually a better consumer experience than a merge that trickles.
- A merge that rewrites files continuously leaves many small files behind, which degrades read performance for consumers until compaction catches up — a freshness cost paid by readers rather than by the writer (File Compaction).
- Adding a column changes the merge statement, and an explicit column list is what makes that a compile-time failure rather than a silent omission. A merge written with an explicit
UPDATE SETlist will quietly stop updating any column added later (Schema Evolution). - Changing the key is a rebuild. A merge keyed on the old key cannot deduplicate against rows written under the new one, so both exist and both are unique by their own definition (Surrogate Keys).
- A key that gains duplicates in the source — a vendor changes an id scheme, two systems are consolidated — silently converts the merge from idempotent to non-deterministic, and nothing announces it. That is the strongest argument for asserting uniqueness on every run rather than once at design time (Data Contracts).
- A merge is the recovery mechanism for most of this module: re-running a range with a merge converges regardless of how many times it has been run before, which is exactly the property Backfills needs.
- Recovering from a *bad* merge — right mechanics, wrong data — needs the previous version, so snapshot retention on the target is what makes the idempotent write reversible as well as repeatable (Open Table Formats).
- Where the engine has no merge, the recovery-equivalent is delete-then-insert inside one transaction, which has the same effect and a much worse failure mode if the transaction is not actually atomic (Atomic Publish).
What can go wrong
- A merge key that is not unique in the input, making the result depend on execution order.
- Staged data containing keys outside the intended range, so the not-matched branch widens the operation.
- Last-wins resolved by arrival order, settling on a stale version of a mutable record.
- Deletes never represented, so the target diverges upward from the source forever.
- A merge that rewrites most of the table because the affected rows are spread thinly across files, turning a small correction into a full rewrite (File Size and the Small-Files Problem).
- The merge working perfectly and hiding that the pipeline ran four times — the mitigation removing the signal along with the damage (Pipeline Observability).
- "A merge makes the pipeline idempotent." It makes the write idempotent. A transformation that reads mutable dimensions or the wall clock still produces different output on every run, and the merge faithfully stores each one (Idempotent Data Pipelines).
- "Upsert means the data is always current." It means the target reflects the most recently *applied* version, which is the most recently *committed* one only if the ordering column is a source-side position rather than an arrival time (CDC Ordering and Transaction Boundaries).
- "We merge, so duplicates are impossible." Duplicates are impossible *at the merge key*. Two records describing the same real order with different keys merge into two rows, and both are unique (Deduplication).
- "Delete-then-insert is the same thing." It is, when both run inside one transaction the engine actually honours. Where it does not, there is a window in which the period is empty, and a consumer reading it sees zero rather than old (Atomic Publish).
Operating it
- Rows inserted versus rows updated per merge, which most engines report. A load that expected updates and performed only inserts is a key mismatch, visible in one number (Pipeline Metrics).
- Load count per partition per day, because a merge makes repeated runs invisible in the data and they must be visible somewhere.
- Files rewritten per merge and the small-file count on the target, which is the read-side cost accumulating (File Size and the Small-Files Problem).
- Target row count versus source row count for a closed period, which is the only signal that deletes are diverging (Reconciliation).
- At 10x the merge's target read becomes the dominant cost of the load, and layout — clustering on the key, partition pruning in the match condition — is what keeps it viable (Partition Pruning).
- At 100x, merges are usually restricted to a recent window while older partitions are treated as immutable and corrected only by explicit backfill. Idempotency everywhere stops being affordable and becomes a policy about where it is needed.
- Higher key cardinality makes the match more selective and the rewrite more scattered — the two effects push in opposite directions, which is why merge cost at scale is a layout question rather than a volume question (Partition Cardinality).
- A merge reads the target as well as the staged input, so it costs a join plus a rewrite of the touched files. An append costs a write. The gap is the price of idempotency and it is charged on every load, not only the ones that are re-run (What Actually Drives Data Platform Cost).
- The rewrite is the term that surprises people: correcting a handful of rows scattered across a table can rewrite most of its files, because the file is the unit of rewrite in a columnar store (File Compaction).
- Clustering the target on the merge key concentrates matches into fewer files and reduces the rewrite, which is one of the few layout decisions made for the write path rather than the read path (Clustering and Sort Order).
- Partition replacement costs a full partition rewrite regardless of how many rows changed, which is cheaper than a scattered merge and more expensive than a targeted one.
- Idempotency is bought with a more expensive write on every load, forever, to protect against re-runs that happen occasionally. That is a good trade for anything with published consumers and a poor one for a high-rate append-only event landing zone.
- A merge hides that a pipeline ran twice. It removes the damage and the evidence together, so it must be paired with a load counter or the operational signal is lost.
- Partition replacement has fewer assumptions and a coarser blast radius. A merge is more surgical and depends on a key you must guarantee. Choosing between them is choosing which assumption you would rather defend.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALConvergence under repeated application is the property, and it is available in some form everywhere: a warehouse MERGE, a lakehouse table format's upsert, a partition swap, or delete-then-insert in a transaction. What differs is the cost and which assumptions each one leaves unchecked.
- FORMAT-SPECIFICIceberg, Delta and Hudi all provide row-level updates over immutable files, and they differ in how: copy-on-write rewrites the affected data files at merge time and makes reads fast, merge-on-read writes delete or change files and defers the cost to the reader until compaction. The correctness is the same and the cost lands on opposite sides.
- WAREHOUSE-SPECIFICWarehouses provide MERGE as a transactional statement, so the atomicity question is settled; object-store table formats provide it as a metadata commit with snapshot isolation, and a plain directory of Parquet files provides nothing, so there the only idempotent write is replacing a whole partition.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why convergence under repeated application is the property worth designing for: in a system where a sender cannot know whether its message arrived, an operation that can be applied twice safely is the only kind that composes.