Upserts and the MERGE pattern

At 04:32 on a Tuesday a Razorpay data engineer named Aditi watches a merchants dimension table accept the same overnight refresh for the third time in two hours. The first run finished. The second run, from a confused Airflow retry, finished. The third run, kicked off by a colleague who didn't know about the first two, just finished too. The row count is unchanged, the merchant names are correct, the updated_at column has moved forward by exactly the duration of the third run, and the created_at column on every row still shows the original date the merchant first onboarded. Three runs of the same load. Zero duplicates. Zero data corruption. The mechanism doing this work — silently, atomically, on every write — is a single SQL statement called MERGE, and the pattern it expresses is called the upsert: insert the row if it doesn't exist, update it if it does, in one shot.

The previous chapter showed how to make a load idempotent by computing a content hash and rejecting duplicate inserts. That pattern is correct when the destination table is append-only — every change is a new row, the hash captures full identity, and ON CONFLICT DO NOTHING is exactly right. This chapter is about the other half of the contract: when the destination is a current-state table that must always reflect the latest version of each logical entity. There, "duplicate" doesn't mean "drop"; it means "merge in the new values". The upsert is the operation that does it, and MERGE is the standardised SQL syntax that every modern engine implements.

Why an upsert is not just "INSERT or UPDATE"

The naive way to write an upsert is two statements: try to update, and if no rows were affected, insert. In application code this looks reasonable for a single row at a time, and it is exactly what every junior engineer reaches for the first time:

cur.execute("UPDATE merchants SET name=%s, tier=%s WHERE merchant_id=%s",
            (row["name"], row["tier"], row["merchant_id"]))
if cur.rowcount == 0:
    cur.execute("INSERT INTO merchants (merchant_id, name, tier) VALUES (%s, %s, %s)",
                (row["merchant_id"], row["name"], row["tier"]))

This works on an empty table with one writer. On a busy production table with two concurrent loaders racing on the same merchant_id, the sequence is broken: writer A's UPDATE returns 0 rows, writer B's UPDATE returns 0 rows, both fall through to INSERT, and the first one to commit succeeds while the second one fails on the unique constraint and aborts the entire transaction. If the transaction wrapped a thousand rows, every one of them rolls back. The retry retries everything. Throughput collapses.

A merge primitive solves this with one round trip and one atomic decision per row. The engine's storage layer takes the row lock, decides "I have this key" or "I do not", and either updates or inserts under that lock. There is no window between the UPDATE and the INSERT for a competing writer to slip in. There is no client-side if. There is one statement, and the engine's correctness story takes care of the rest.

Why the atomicity matters in practice: at Razorpay's settlement-load scale, two Airflow workers retrying the same task because of a queue glitch is not theoretical, it is Tuesday. With MERGE the two workers serialise on the row lock and the second one sees the row the first one wrote — the merge is a no-op or a benign update. With the naive try-update-then-insert pattern, both workers see "no row" before either has committed, both fall through to insert, and one of them aborts a transaction that may have covered ten thousand other rows. The atomicity is not a nicety; it is the property that makes upserts compose with retries.

The MERGE statement, dissected

MERGE was added to the SQL standard in 2003 (SQL:2003) and has been implemented, with minor variations, in nearly every serious database engine: Postgres 15+, Oracle, SQL Server, BigQuery, Snowflake, Redshift, Databricks Delta Lake, Apache Iceberg, Trino. The core syntax is the same everywhere:

MERGE INTO target_table t
USING source_data s
   ON t.match_key = s.match_key
WHEN MATCHED THEN
    UPDATE SET t.col_a = s.col_a,
               t.col_b = s.col_b,
               t.updated_at = now()
WHEN NOT MATCHED THEN
    INSERT (match_key, col_a, col_b, created_at, updated_at)
    VALUES (s.match_key, s.col_a, s.col_b, now(), now());

There are five load-bearing pieces, and each has a way to be wrong.

The target is the table you are merging into. It must have an index on the match key — without it, every row in the source triggers a full-table scan of the target, and a 1-lakh-row source against a 5-crore-row target takes hours instead of seconds. Make the index a unique index if the match key is a logical primary key; this lets the engine optimise the matched/not-matched branch.

The source is whatever produces the rows: a staging table you bulk-loaded, a CTE assembled from a query, an inline VALUES clause for a handful of rows, or another physical table. In the warehouse pattern, the source is almost always a freshly-loaded staging table that holds the day's batch.

The match condition decides which target row a source row corresponds to. Get this wrong and you either match nothing (and insert duplicates of every row that should have been an update) or match too much (and update one source row's values into multiple target rows). The match condition is almost always equality on the business primary key — t.merchant_id = s.merchant_id — but in dimension-loading patterns it can include a soft-delete or active-row predicate: t.merchant_id = s.merchant_id AND t.is_current = true.

The WHEN MATCHED clause specifies the update behaviour. It is a partial column update: only the columns you list are touched. This is where teams accidentally overwrite history. Imagine the target has created_at set to the moment the row was first inserted; an over-eager UPDATE SET t.* = s.* (which some engines allow as shorthand) overwrites created_at with the source's created_at, which on a refreshed extract is the moment the source system last saw the row, which is the wrong value. The discipline is to enumerate the mutable columns explicitly and to never list immutable ones (created_at, first_seen_at, the surrogate key, any audit column) in the update target.

The WHEN NOT MATCHED clause specifies the insert. Here you usually do want to populate every column, including the ones the matched branch carefully leaves alone — created_at = now() on insert is correct, because this row is brand-new in the destination.

Why splitting columns by category matters: a Flipkart catalogue refresh that listed created_at in the update target meant every product's "first seen" date got rewritten to the date of the most recent refresh. Three months of lifecycle data — when each SKU first onboarded, which campaigns lined up with which onboardings — was silently overwritten before anyone noticed. The fix was to never list created_at in the WHEN MATCHED clause. Once the discipline became "audit columns belong in WHEN NOT MATCHED only", the bug never recurred.

A complete, runnable MERGE pipeline

The example below loads merchant master data from a JSON file into a Postgres merchants table, applies the upsert pattern, and demonstrates that running the same load three times in a row produces exactly the same destination state — same row count, same created_at per merchant, advancing updated_at on rows whose mutable columns changed, and untouched updated_at on rows whose mutable columns did not change.

# upsert_merchants.py — idempotent upsert with column-aware MERGE.
import json, datetime
import psycopg2
from psycopg2.extras import execute_values

DDL = """
CREATE TABLE IF NOT EXISTS merchants (
    merchant_id     TEXT PRIMARY KEY,
    name            TEXT NOT NULL,
    tier            TEXT NOT NULL,
    status          TEXT NOT NULL,
    amount_cap_paise BIGINT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_run_id     TEXT
);
"""

# Note: Postgres MERGE arrived in v15. For older Postgres, ON CONFLICT DO UPDATE
# is the equivalent primitive and is shown later in this chapter.
MERGE_SQL = """
MERGE INTO merchants AS t
USING (VALUES %s) AS s(merchant_id, name, tier, status, amount_cap_paise, run_id)
   ON t.merchant_id = s.merchant_id
WHEN MATCHED AND (t.name, t.tier, t.status, t.amount_cap_paise)
              IS DISTINCT FROM (s.name, s.tier, s.status, s.amount_cap_paise)
THEN UPDATE SET
       name = s.name,
       tier = s.tier,
       status = s.status,
       amount_cap_paise = s.amount_cap_paise,
       updated_at = now(),
       last_run_id = s.run_id
WHEN NOT MATCHED THEN INSERT
       (merchant_id, name, tier, status, amount_cap_paise, last_run_id)
   VALUES
       (s.merchant_id, s.name, s.tier, s.status, s.amount_cap_paise, s.run_id);
"""

def run_load(rows: list[dict], run_id: str, conn) -> None:
    tuples = [(r["merchant_id"], r["name"], r["tier"], r["status"],
               r["amount_cap_paise"], run_id) for r in rows]
    with conn.cursor() as cur:
        execute_values(cur, MERGE_SQL, tuples)
    conn.commit()

if __name__ == "__main__":
    with open("merchants.json") as f:
        rows = json.load(f)
    run_id = datetime.datetime.utcnow().strftime("run_%Y%m%d_%H%M%S")
    with psycopg2.connect("host=localhost dbname=razorpay user=etl") as conn:
        with conn.cursor() as cur:
            cur.execute(DDL)
        run_load(rows, run_id, conn)
        with conn.cursor() as cur:
            cur.execute("SELECT count(*), count(distinct merchant_id) FROM merchants")
            total, distinct = cur.fetchone()
        print(f"run_id={run_id} rows={total} distinct={distinct}")

A three-run sample, where the first two runs use the same input file and the third run uses a file with three merchants whose tier field has been bumped from bronze to silver:

$ python upsert_merchants.py merchants.json
run_id=run_20260425_043200 rows=4218 distinct=4218

$ python upsert_merchants.py merchants.json
run_id=run_20260425_043215 rows=4218 distinct=4218

$ python upsert_merchants.py merchants_with_tier_changes.json
run_id=run_20260425_043301 rows=4218 distinct=4218

$ psql -c "SELECT count(*) FROM merchants WHERE last_run_id = 'run_20260425_043215'"
  count
-------
      0
$ psql -c "SELECT count(*) FROM merchants WHERE last_run_id = 'run_20260425_043301'"
  count
-------
      3

Three runs, no growth in row count, and the last_run_id column tells you precisely how many rows each run actually touched. The second run touched zero rows because the IS DISTINCT FROM predicate suppressed the no-op update; the third run touched exactly three because three merchants had genuine changes. This is what an idempotent upsert is supposed to do.

Three lines deserve a careful walkthrough.

USING (VALUES %s) AS s(...). The source side of the MERGE is constructed inline from a list of tuples. psycopg2.extras.execute_values expands the %s placeholder into a single big VALUES (...), (...), ... clause, so the entire batch is one round trip. For larger loads — a few lakh rows — you bulk-load into a temp staging table first and write USING staging_table s instead. Either way, the MERGE itself is one statement.

WHEN MATCHED AND (t.name, ...) IS DISTINCT FROM (s.name, ...). The IS DISTINCT FROM predicate is the no-op suppressor. Without it, every row in the source rewrites the corresponding row in the target — including its updated_at — even when nothing has actually changed. With it, the engine compares the mutable columns and only fires the UPDATE branch when at least one differs. This converts your updated_at column from "the last time we ran the load" to "the last time this row's content actually changed", which is what downstream consumers want. IS DISTINCT FROM is null-safe (unlike <>) so it handles columns that may be null without subtle bugs.

updated_at = now(), last_run_id = s.run_id. The metadata columns get touched on every UPDATE branch, and they are populated by the destination (now()) rather than by the source. Carrying a source-supplied updated_at would tie the destination's freshness signal to the source's clock, which is a correctness landmine — the source's clock can jump backward, the timezone library can change, two source systems can disagree. The destination's own now() is the single trustable timestamp.

Why IS DISTINCT FROM and not <>: the <> operator returns null when either side is null, and null is treated as "not true" in a WHEN clause, so a row whose tier was previously null and is being updated to 'silver' would not fire the UPDATE. IS DISTINCT FROM is the SQL-standard null-aware comparison: null IS DISTINCT FROM 'silver' evaluates to true, exactly as a human would expect. This is one of the rare cases where the more verbose SQL keyword is the correct one.

Why a bulk-load-then-MERGE pattern beats row-by-row: every MERGE statement has a fixed cost — parsing, planning, lock acquisition, transaction overhead. On a row-by-row pattern that cost is paid once per row. On a batch pattern, it is paid once per batch. For the 4,218-row example above the row-by-row variant runs in about 12 seconds; the batched MERGE finishes in 180 milliseconds. The pattern matters more as the table grows: at 1 crore rows, the row-by-row variant runs for hours and the batched MERGE still finishes under a minute.

ON CONFLICT, REPLACE INTO, MERGE: the engine-specific spellings

Postgres added MERGE in v15 (October 2022). Before that, the canonical Postgres upsert was INSERT ... ON CONFLICT (key) DO UPDATE SET ..., which is functionally equivalent for the common case and uglier for complex match logic. The same pattern under different syntax exists almost everywhere:

Engine	Upsert syntax
Postgres ≥15	MERGE INTO (full standard) or INSERT ... ON CONFLICT (key) DO UPDATE
Postgres ≤14	INSERT ... ON CONFLICT (key) DO UPDATE
MySQL	INSERT ... ON DUPLICATE KEY UPDATE
SQLite	INSERT ... ON CONFLICT(key) DO UPDATE
SQL Server	MERGE INTO (with documented caveats — Microsoft recommends UPSERT-via-procedure for high-concurrency cases)
Oracle	MERGE INTO (introduced in 9i)
BigQuery	MERGE INTO
Snowflake	MERGE INTO
Redshift	MERGE INTO (added 2023)
Apache Iceberg	MERGE INTO
Delta Lake	MERGE INTO
Trino	MERGE INTO
ClickHouse	INSERT INTO ... SELECT with ReplacingMergeTree engine; no row-level MERGE

The differences across engines are usually about three things: support for multiple WHEN MATCHED clauses with different predicates, support for a WHEN NOT MATCHED BY SOURCE clause (which lets MERGE delete target rows missing from the source — useful for full-snapshot dimension refreshes), and support for DELETE actions inside WHEN MATCHED. Postgres's first MERGE release did not support WHEN NOT MATCHED BY SOURCE; Iceberg's does. Read the version-specific docs.

ClickHouse is the interesting outlier. It has no MERGE statement at all — the engine's storage model is append-only with background merging by sort key (ReplacingMergeTree), and the upsert pattern is simply "insert again with the same key, the engine will eventually deduplicate by sort key". This means ClickHouse upserts have eventual consistency for reads (a FINAL query forces the merge at read time, at substantial cost), and most production ClickHouse pipelines structure their data so that this is acceptable — append-only fact tables, dimension joins materialised separately. Build 14 returns to this distinction in detail.

Common confusions

• "An upsert is the same as INSERT ... ON CONFLICT DO NOTHING." No. DO NOTHING is the append-only pattern from the previous chapter — duplicates are dropped, the row stays at its original values. An upsert replaces those values with the source's. The two patterns are siblings, and which one you want depends on whether the destination is a current-state table (upsert) or an append-only event log (DO NOTHING). Mixing them is the most common cause of "why does the dashboard show a stale tier for this merchant?" tickets.

• "MERGE is just syntactic sugar over INSERT and UPDATE." Almost. The crucial extra is atomicity per row: the engine acquires the row lock, decides the branch, and writes — all under the same lock. The two-statement application-level equivalent has a window between the UPDATE and INSERT where another writer can slip in. On a busy table this manifests as occasional unique-constraint violations rather than never. MERGE removes the window.

• "I should use UPDATE SET t.* = s.* to avoid listing every column." Don't. Several engines allow this shorthand, and on a table with audit columns it overwrites them. The discipline is to list mutable columns explicitly in WHEN MATCHED and to leave audit columns out — code review catches the mistake when the column list is explicit but cannot when it is t.* = s.*.

• "The match condition can be any expression." It can syntactically; it should not be in practice. A match condition that is anything other than equality on an indexed column degrades to a scan, and a scan against a large target table makes every batch a full-table scan. If the match logic is genuinely complex — say, fuzzy matching on customer names — do the resolution upstream in the staging area, materialise the resolved match key as a column, and let MERGE match on equality.

• "MERGE is slower than two separate UPDATE and INSERT statements because it does both." It is faster, almost always. The engine handles the branch internally without the application paying for two round trips, and modern planners optimise the matched/not-matched paths separately. For batches over a few hundred rows the speedup over the application-level pattern is 5–10x, and it grows with batch size.

• "If two MERGE statements run concurrently on overlapping rows, one of them will fail." Not exactly — the row lock serialises them, so the second one sees the first one's writes and applies its update on top. This is correct for last-writer-wins semantics. If you need different semantics — for example, "discard the second writer's update if its version column is older" — you encode that as a predicate in WHEN MATCHED: WHEN MATCHED AND s.version > t.version THEN UPDATE ....

Going deeper

MERGE for slowly-changing dimension Type 2

The default upsert overwrites the previous version of a row with the new version. Sometimes you need to preserve the previous version as a historical record — what data warehousing literature calls a Slowly Changing Dimension Type 2 (SCD-2). The pattern: each row in the dimension has valid_from and valid_to timestamps and an is_current flag, and a "change" produces two MERGE statements — first close out the old row by setting valid_to = now() and is_current = false, then insert the new row with valid_from = now() and is_current = true.

MERGE supports both actions in one statement on engines that allow multiple WHEN MATCHED clauses with different THEN actions, including a WHEN MATCHED AND s.tier <> t.tier THEN UPDATE ... to close the old row and a separate companion INSERT for the new row. Postgres's MERGE does not yet support a THEN INSERT action inside a WHEN MATCHED branch, so on Postgres the SCD-2 pattern is two statements in one transaction; on Snowflake, BigQuery, and Iceberg you can express it as a single MERGE. The two-statement-in-one-transaction pattern is fine for production — the transaction guarantees atomicity even though the syntax is splittable.

The Zerodha brokerage account-master dimension uses SCD-2 to track tier changes — a customer moving from Basic to Premium produces a new row, and the old row is closed at the moment of the upgrade. This lets a query "what tier was this customer when they placed order X on date Y?" join on customer_id and valid_from <= Y < valid_to, which is the only correct way to answer historical attribution questions. The MERGE pattern is the implementation primitive; the SCD-2 schema is the design that uses it.

Batched MERGE versus per-row UPSERT in streaming pipelines

In a batch warehouse load, MERGE runs once per batch against a staging table holding the day's input. In a streaming pipeline with sub-second latency requirements, you cannot wait for a batch; each event must be applied to the target immediately, and the MERGE pattern is wrapped in a per-row INSERT ... ON CONFLICT DO UPDATE.

The throughput penalty is real. On a Postgres target with a B-tree index, per-row upserts achieve roughly 8k–15k operations per second per connection; batched MERGE on a 10k-row staging table achieves 60k–100k effective inserts/updates per second on the same hardware. The gap is the per-row overhead: connection round-trip, parse, plan, lock acquisition.

Production streaming pipelines that need both low latency and high throughput typically buffer events in a small window (say, 500 ms or 5,000 events, whichever comes first) and run a batched MERGE on each window. This is the micro-batch pattern, and it is the basis of Spark Structured Streaming's continuous-mode semantics and Flink's two-phase-commit sink. The latency-throughput trade-off is encoded in the buffer size: smaller buffers give lower latency and lower throughput, larger buffers the reverse. The Swiggy order-events pipeline runs with a 200 ms buffer and a 5,000-event maximum, which keeps p99 end-to-end latency under 500 ms and throughput well above the peak Friday-evening order rate.

MERGE and the read-write tension on lakehouse formats

On a row-store database, MERGE is cheap because the engine updates rows in place. On a columnar lakehouse format (Iceberg, Delta Lake, Apache Hudi), MERGE is structurally more expensive because the underlying data files are immutable Parquet — "updating" a row means rewriting the file containing that row. The lakehouse engines handle this with two strategies:

Copy-on-write (CoW): every MERGE rewrites the affected files entirely. Read performance is excellent (each query reads exactly one file per partition), but write performance degrades for small frequent updates because each merge of one row may rewrite a 100 MB file. Iceberg and Delta default to CoW. Best for low-frequency, large-batch upserts.

Merge-on-read (MoR): every MERGE writes a small "delete file" or "update log" alongside the unchanged data files; the read path reconciles the two. Write performance is excellent for small frequent updates; read performance degrades as the delete-file count grows, until a compaction job rewrites the base files. Apache Hudi defaults to MoR. Best for high-frequency, small-batch upserts. Iceberg added MoR support in v2 spec.

The Cred ledger team made this tradeoff explicitly: their high-volume rewards-event table runs MoR with hourly compaction, and their low-volume customer-master table runs CoW with daily MERGE. The choice is per-table, not per-cluster, and the wrong choice is the difference between sub-second queries and ten-minute queries. Build 12 unpacks the lakehouse mechanics in detail.

Why Postgres 15 took twenty years to ship MERGE

Postgres had INSERT ... ON CONFLICT DO UPDATE since 2016 (v9.5), which covered most production upsert needs. The reason MERGE took until 2022 (v15) to land is partly that the SQL standard's MERGE has a long-standing transient race: between the engine deciding "no matching row exists, take the NOT MATCHED branch" and the engine inserting the new row, another transaction can insert a row with the same key. The standard's specification of MERGE does not require the engine to retry the branch decision. Oracle's and SQL Server's implementations have documented quirks here; SQL Server's MERGE has been the subject of several Microsoft-published advisories recommending stored-procedure alternatives for high-concurrency workloads.

Postgres's implementation of MERGE shipped only after the maintainers were satisfied that the engine's row-locking semantics extended cleanly to MERGE without exposing the standard's race. The implementation acquires the row lock by the match key before evaluating the branch, which is stricter than the standard requires but matches the no-surprises guarantee Postgres has elsewhere. The takeaway for production: if you are on Postgres ≥15, MERGE is safe to use without additional ceremony; if you are on a different engine, read its concurrency documentation before assuming the standard's semantics.

Where this leads next

Chapter 10 widens the lens from "is this insert idempotent?" to "is the whole pipeline idempotent under partial failure?" — the at-least-once contract that this chapter quietly assumes. The MERGE pattern absorbs duplicates; the at-least-once contract is what the upstream pipeline promises about how often duplicates can arrive.

• Hash-based deduplication on load — chapter 8, the append-only sibling of this pattern
• What "idempotent" actually means for data (and why it's hard) — chapter 6, the conceptual frame
• Partial failures and the at-least-once contract — chapter 10
• State files and checkpoints: the poor man's job queue — chapter 7
• Crashing mid-run: which state are you in? — chapter 4

Build 6 returns to MERGE on lakehouse formats — Iceberg's MERGE INTO and Delta's MERGE are the same pattern but with very different cost models because of the immutable-file substrate. Build 11 returns again, this time as the destination-side primitive that absorbs change-data-capture streams: every CDC event is an upsert, and the MERGE pattern is the universal sink.

References

1. SQL:2003 MERGE specification (ISO/IEC 9075-2:2003) — the standard that defines the syntax adopted (with variations) by every major engine.
2. Postgres MERGE documentation — the reference for the v15+ implementation, including its concurrency guarantees and the comparison to INSERT ... ON CONFLICT.
3. Iceberg MERGE INTO — production-grade MERGE on a lakehouse format, with copy-on-write and merge-on-read variants.
4. Delta Lake MERGE INTO — the Databricks variant with column-level update predicates and merge-on-read support.
5. SQL Server MERGE caveats (Aaron Bertrand, Microsoft MVP) — the documented concurrency issues with SQL Server's MERGE that motivate the stored-procedure alternative for high-concurrency workloads.
6. Hash-based deduplication on load — chapter 8, the append-only-sibling pattern that this chapter contrasts with.
7. Designing Data-Intensive Applications, Chapter 7 — Transactions — Martin Kleppmann's treatment of write skew and serialisation, which underpins the per-row atomicity guarantee MERGE depends on.
8. Apache Hudi documentation: copy-on-write vs merge-on-read — the canonical reference for the two file-format strategies that lakehouse MERGE uses.

---

A practical exercise: take the MERGE_SQL statement above and remove the WHEN MATCHED AND ... IS DISTINCT FROM predicate. Run the same load three times in a row and check SELECT count(*) FROM merchants WHERE last_run_id = '<run_id_of_second_run>'. Without the predicate, the second run touches every row in the target — the updated_at advances on rows that did not actually change, downstream change-data-capture consumers light up, and the ledger's "this changed today" reports are full of false positives. The predicate is the single line that turns "we re-ran the load" into "we applied genuine changes only", and pinning it with a unit test that compares pre- and post-rerun updated_at timestamps will catch the regression the day someone "simplifies" the MERGE.

A second exercise, for senior engineers calibrating their own dimension loads: walk every WHEN MATCHED clause in your warehouse and check the column list against the schema. Any column that should be immutable history — created_at, first_seen_at, surrogate keys, audit columns — must not appear in the SET list. The most-common production accident in this area is the well-meaning addition of t.* = s.* shorthand, which silently overwrites every audit column with the source's values; once that pattern is in production, the only way to recover the original created_at values is to restore from a backup taken before the change was deployed.