Gowtham Potureddi

Posted on Jul 12

SQL MERGE / UPSERT / ON CONFLICT: Dialect-Aware Idempotent Writes

#pythons #sql #interview #dataengineering

sql merge upsert is the primitive every incremental load pipeline, every CDC apply-loop, every API upsert endpoint, and every dbt incremental model eventually reaches for — and it is the primitive most engineers ship with a race condition on day one because the honest "insert-if-new-else-update" contract is deceptively subtle and the dialect story is genuinely fragmented across eight engines that all spell the same idea differently. Every backend intern types SELECT ... IF NOT EXISTS THEN INSERT ELSE UPDATE in week one and only discovers the race between the SELECT and the INSERT when two concurrent workers produce a primary-key violation at 3 a.m. This guide is the honest, dialect-aware tour of what actually happens when you ask an engine to atomically merge new rows into an existing table without losing writes and without producing duplicates.

The tour walks the eight engines you have to keep straight in 2026 — the ANSI SQL:2003 MERGE INTO ... USING ... ON ... WHEN MATCHED / WHEN NOT MATCHED / WHEN NOT MATCHED BY SOURCE grammar and its three row-level branches, the OLTP-flavoured INSERT ... ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col (Postgres, SQLite) and INSERT ... ON DUPLICATE KEY UPDATE ... = new.col (MySQL) native UPSERTs that guarantee atomicity via unique-index detection instead of a two-statement dance, the warehouse-flavoured MERGE INTO t USING s ON ... on Snowflake, BigQuery, and Databricks with their per-engine storage-rewrite models (micro-partition rewrites on Snowflake, partition rewrites on BigQuery, Delta file rewrites on Databricks), the notorious SQL Server MERGE cardinality bug and the safer alternatives senior engineers use in its place, and the anti-patterns that turn any of the above into a lost-update incident under concurrency. Every section ships a teaching block followed by a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works — so you leave with the two-line skeleton and the reason it wins.

When you want hands-on reps immediately after reading, drill the SQL optimization practice library → for merge-cost tuning, rehearse the SQL join drill room → since MERGE is a specialised join, and sharpen the broader SQL practice surface → that covers 450+ DE-focused problems on incremental loads, idempotency, and upsert patterns.

On this page

Why idempotent writes matter in 2026
SQL Standard MERGE anatomy
Postgres ON CONFLICT + MySQL ON DUPLICATE KEY + SQLite UPSERT
Snowflake + BigQuery + Databricks MERGE
Anti-patterns and dialect matrix
Cheat sheet — MERGE / UPSERT recipe list
Frequently asked questions
Practice on PipeCode

1. Why idempotent writes matter in 2026

The `sql merge upsert` mental model — idempotency as a contract, the two loading anti-patterns, and where MERGE shows up in every real DE pipeline

The one-sentence invariant: an idempotent write is a statement that, when replayed any number of times against the same target state and the same input, produces the same final state — no duplicates, no lost updates, no partial writes — and MERGE / UPSERT / ON CONFLICT are the SQL primitives that let you express this contract atomically in a single round-trip instead of a two-statement dance that races under concurrency. Every pipeline eventually retries; only pipelines built on idempotent primitives survive that retry without corruption.

Where MERGE / UPSERT actually shows up in production.

dbt incremental models. Every materialized='incremental' model with unique_key='id' compiles to a MERGE on Snowflake / BigQuery / Databricks and to an INSERT ... ON CONFLICT ... DO UPDATE on Postgres. The unique_key config is the ON condition; the entire dbt incremental pattern is one big MERGE.
CDC apply-loops. Debezium streams inserts, updates, and deletes from Postgres → Kafka → Snowflake. The apply job reads a batch of change events and MERGEs them into the target table. Without MERGE, you'd have to run separate INSERT / UPDATE / DELETE statements per row-op and risk partial application.
Event ingestion. IoT sensor readings, product analytics events, ML feature updates — all arrive as append-only streams that need to upsert by (entity_id, event_ts) when late-arriving events overwrite earlier snapshots.
API upsert endpoints. REST PUT /users/:id is semantically an upsert — create if new, update if existing. Backend code that does SELECT ... IF NOT EXISTS INSERT ELSE UPDATE has a race window; the correct implementation is a single INSERT ... ON CONFLICT ... DO UPDATE.
Dimension SCD updates. Slowly Changing Dimension Type 1 (overwrite) is a simple MERGE. SCD Type 2 (history-preserving) is a MERGE with an expiration-date trick — insert a new row, close the old one via WHEN MATCHED THEN UPDATE.
Fact-table backfills. Reload the last 24 h of clickstream from source. MERGE by (event_id) so re-runs are idempotent even when the same event flows through the pipeline twice.
Change data feeds. Delta Lake CHANGES feed, Snowflake Streams — both consume via MERGE downstream, since the produced feed contains inserts and updates.

The two loading anti-patterns.

Anti-pattern 1 — SELECT-then-INSERT / UPDATE. The junior instinct: SELECT ... IF NOT EXISTS INSERT ... ELSE UPDATE .... This has a race window between the SELECT and the write — two concurrent workers can both see the row as missing, both attempt the INSERT, and one gets a primary-key violation. On some engines, the race is even more subtle: one INSERT wins, the other returns "success" but silently drops the update semantics. Every senior review flags this the moment it appears.
Anti-pattern 2 — blind INSERT with error swallowing. INSERT ...; IF error THEN UPDATE. This works but leaves an audit-log stink (rejected INSERT logs on every duplicate), thrashes the write-ahead log, and fails on some engines that don't cleanly recover from constraint violations mid-transaction.
Anti-pattern 3 — DELETE + INSERT. The "just delete the row and re-insert" pattern. Simple, but breaks referential integrity (child tables with foreign keys), triggers on both DELETE and INSERT (double effects), and violates the "no gaps in AUTO_INCREMENT" invariant many downstream systems rely on.
Anti-pattern 4 — application-side transactions with pessimistic locks. BEGIN; SELECT FOR UPDATE; if row exists UPDATE else INSERT; COMMIT. Works, but takes an exclusive row-lock that serializes writers and creates a hotspot. Fine for low volume; disastrous at 10 k QPS.

Idempotency as a contract in five bullets.

Same input → same final state. Regardless of how many times you replay the same source batch, the target ends up in the same state. run 5 times = run 1 time.
No visible intermediate states. During the MERGE, the target is either fully-old or fully-new to any concurrent reader. Isolation-level dependent, but MERGE is atomic per-row on most engines.
No lost updates under retry. If a worker crashes mid-batch and the batch is re-driven, no rows are lost.
No duplicates under retry. No rows appear twice; the unique-index / MERGE ON condition guarantees this.
Deterministic under concurrency. Two concurrent MERGEs of the same source batch produce identical target states (though they may serialize / block each other during execution).

What senior interviewers actually probe when they open a MERGE question.

Do you know the difference between MERGE and UPSERT? MERGE is the ANSI SQL statement with three branches (MATCHED / NOT MATCHED [BY TARGET | BY SOURCE]). UPSERT is the informal term for insert-or-update; on Postgres / MySQL / SQLite it's typically spelled INSERT ... ON CONFLICT or ON DUPLICATE KEY UPDATE. MERGE is a superset; UPSERT is the common two-branch case.
Do you know the race condition in a naive UPSERT? SELECT then INSERT has a TOCTOU (time-of-check to time-of-use) race. Fix is atomic UPSERT.
Do you know the SQL Server MERGE bug? SQL Server's MERGE has a known cardinality bug where multiple source rows matching the same target row cause silent data loss. Microsoft's own docs recommend workaround patterns.
Do you know the storage cost per engine? Snowflake MERGE rewrites micro-partitions (cost per partition rewritten). BigQuery MERGE rewrites partitions (cost per partition rewritten + slot_ms). Delta MERGE rewrites Parquet files (cost per file rewritten + Photon acceleration if enabled).
Do you know how MERGE composes with SCD? SCD Type 1 = MERGE with WHEN MATCHED THEN UPDATE. SCD Type 2 = MERGE with WHEN MATCHED THEN UPDATE ... on the expiration row plus a fresh INSERT for the new version. Snowflake's MERGE ... WHEN MATCHED AND cond THEN INSERT is the modern spelling.
Do you know the race-safe API upsert pattern? REST PUT /users/:id handler that does a single INSERT ... ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name RETURNING id. Atomic, idempotent, no application-side check.

Worked example — the race condition in the naive SELECT-then-INSERT pattern

Detailed explanation. Two backend workers both receive a PUT /users/42 request at the same millisecond. Both check "does user 42 exist?" — both get NO, because the row doesn't exist yet. Both attempt the INSERT. Under READ COMMITTED isolation, one wins and the other gets a UNIQUE-constraint violation. Under snapshot isolation, both may commit and one silently loses.

Question. Given the endpoint below, describe the race window and show the fixed atomic version.

Input.

# The naive PUT handler
def put_user(user_id: int, name: str):
    with db.transaction():
        existing = db.query("SELECT id FROM users WHERE id = %s", (user_id,))
        if existing:
            db.execute("UPDATE users SET name = %s WHERE id = %s", (name, user_id))
        else:
            db.execute("INSERT INTO users (id, name) VALUES (%s, %s)", (user_id, name))

Code.

# The atomic fix — single-statement UPSERT
def put_user(user_id: int, name: str):
    db.execute(
        """
        INSERT INTO users (id, name) VALUES (%s, %s)
        ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
        """,
        (user_id, name),
    )

Step-by-step explanation.

Two concurrent workers both call put_user(42, 'alice') and put_user(42, 'bob') at the same time.
Naive path — both open a transaction. Both run SELECT id FROM users WHERE id = 42. Under READ COMMITTED, both see no row (before either INSERT commits). Both branches choose INSERT. Both attempt INSERT INTO users (id, name) VALUES (42, ...). The one that commits first wins; the other hits a UNIQUE-constraint violation on the primary key.
Result — one worker's request fails with a database error. The client sees a 500 and retries; the row is now visible; the retry does UPDATE. Semantically correct, but with a spurious error and log noise.
Atomic path — a single INSERT ... ON CONFLICT (id) DO UPDATE statement. Postgres detects the unique-index conflict inside the write, atomically routes to UPDATE, and commits. No race window, no error, no retry.
Rule — every UPSERT must be a single statement. Never two.

Output.

Worker	Naive (race)	Atomic (fix)
Worker A	INSERT (42, 'alice')	INSERT ... ON CONFLICT (42, 'alice') → INSERT wins
Worker B	INSERT (42, 'bob') → UNIQUE violation	INSERT ... ON CONFLICT (42, 'bob') → UPDATE branch
Client A sees	201 Created (alice wins first)	201 Created
Client B sees	500 Internal Server Error	200 OK (bob overrides alice)
Final row	(42, 'alice' or 'bob') non-deterministic	(42, 'alice' or 'bob') non-deterministic but idempotent

Rule of thumb. Never write SELECT then INSERT. Every backend UPSERT is one statement. Snapshot the source, ship a single INSERT ... ON CONFLICT (Postgres), ON DUPLICATE KEY UPDATE (MySQL), or MERGE INTO ... WHEN NOT MATCHED / MATCHED (warehouses).

Worked example — idempotent replay of a Kafka batch

Detailed explanation. A common CDC pattern: consume a Kafka topic of user-profile change events, apply to a target table. If the consumer crashes mid-batch and re-drives the same offsets, the same events flow through again. The apply logic must be idempotent — replaying the batch produces the same final state.

Question. Given the Kafka batch below with three user-profile events, show the idempotent MERGE statement that produces the same final state whether the batch runs once or five times.

Input.

[
  {"op": "u", "id": 42, "name": "alice", "updated_at": "2026-07-12T10:00:00Z"},
  {"op": "u", "id": 43, "name": "bob",   "updated_at": "2026-07-12T10:01:00Z"},
  {"op": "u", "id": 42, "name": "alice-updated", "updated_at": "2026-07-12T10:02:00Z"}
]

Code.

-- Stage the batch as a temp table
CREATE TEMP TABLE batch (id INT, name TEXT, updated_at TIMESTAMPTZ);
INSERT INTO batch VALUES
  (42, 'alice',         '2026-07-12 10:00:00Z'),
  (43, 'bob',           '2026-07-12 10:01:00Z'),
  (42, 'alice-updated', '2026-07-12 10:02:00Z');

-- Idempotent MERGE
MERGE INTO users AS u
USING (
  SELECT id, name, updated_at
  FROM (
    SELECT id, name, updated_at,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
    FROM batch
  ) x
  WHERE rn = 1
) AS b
ON u.id = b.id
WHEN MATCHED AND b.updated_at > u.updated_at
  THEN UPDATE SET name = b.name, updated_at = b.updated_at
WHEN NOT MATCHED
  THEN INSERT (id, name, updated_at) VALUES (b.id, b.name, b.updated_at);

Step-by-step explanation.

The Kafka batch has two events for id=42 — the later one (10:02) should win. Naive replay would apply both events in order; a re-drive after crash could apply them again but must not corrupt state.
The ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) deduplicates within the batch — for id=42, only the latest event survives. Same batch → same deduped source → same MERGE result.
WHEN MATCHED AND b.updated_at > u.updated_at — guards against out-of-order arrivals. If a stale event arrives after a fresh one, it's not applied.
WHEN NOT MATCHED THEN INSERT — new IDs are inserted.
Replay safety — running the MERGE twice on the same batch is a no-op the second time because b.updated_at > u.updated_at is now false (the first run already updated u.updated_at to the batch's max).

Output.

Run	id=42 name / updated_at	id=43 name / updated_at
Empty target	—	—
After run 1	alice-updated / 10:02	bob / 10:01
After run 2 (replay)	alice-updated / 10:02	bob / 10:01
After run 5 (replay)	alice-updated / 10:02	bob / 10:01

Rule of thumb. Every CDC / event-stream apply-loop needs three properties in the MERGE — batch-level dedup, timestamp guard on WHEN MATCHED, and monotonic updated_at in the source. Together they make replay a no-op.

Worked example — a dbt incremental model that compiles to a MERGE

Detailed explanation. dbt is the most common surface for MERGE in modern DE. materialized='incremental' + unique_key='id' config compiles to an engine-specific MERGE. The template is worth memorising — every incremental model looks like this.

Question. Write a dbt model that incrementally loads recent orders into the target table, using MERGE semantics with id as the merge key.

Input.

-- models/incremental_orders.sql (dbt)
{{
  config(
    materialized='incremental',
    unique_key='id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
  )
}}

SELECT id, customer_id, total, created_at, updated_at
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Code. (dbt compiles this to different SQL per engine.)

-- Postgres compile output
INSERT INTO analytics.incremental_orders (id, customer_id, total, created_at, updated_at)
SELECT id, customer_id, total, created_at, updated_at
FROM raw.orders
WHERE updated_at > (SELECT MAX(updated_at) FROM analytics.incremental_orders)
ON CONFLICT (id) DO UPDATE SET
  customer_id = EXCLUDED.customer_id,
  total = EXCLUDED.total,
  updated_at = EXCLUDED.updated_at;

-- Snowflake compile output
MERGE INTO analytics.incremental_orders AS t
USING (
  SELECT id, customer_id, total, created_at, updated_at
  FROM raw.orders
  WHERE updated_at > (SELECT MAX(updated_at) FROM analytics.incremental_orders)
) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET
  customer_id = s.customer_id,
  total = s.total,
  updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT
  (id, customer_id, total, created_at, updated_at)
  VALUES (s.id, s.customer_id, s.total, s.created_at, s.updated_at);

-- BigQuery compile output
MERGE INTO `project.analytics.incremental_orders` AS t
USING (...) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED BY TARGET THEN INSERT (...) VALUES (...);

Step-by-step explanation.

is_incremental() — dbt macro that returns true on subsequent runs (after the initial full-refresh). On first run, dbt does CREATE TABLE AS SELECT ....
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) — the incremental filter. Only pulls source rows newer than the highwater mark on the target.
unique_key='id' — the ON condition for MERGE. dbt generates the appropriate WHEN MATCHED / WHEN NOT MATCHED branches.
on_schema_change='append_new_columns' — dbt handles schema drift by appending new columns to the target when they appear in the source.
Every engine compiles to the same conceptual MERGE, but the syntax differs — Postgres uses INSERT ... ON CONFLICT, warehouses use MERGE INTO. dbt hides the difference behind the same model config.

Output.

Engine	Statement type	Notes
Postgres	`INSERT ... ON CONFLICT`	Atomic UPSERT
Snowflake	`MERGE INTO`	Micro-partition rewrite
BigQuery	`MERGE INTO ... WHEN NOT MATCHED BY TARGET`	Partition rewrite; `--dry_run` first
Databricks	`MERGE INTO` (Delta)	Parquet file rewrite; Photon-accelerated

Rule of thumb. dbt's incremental model is the canonical MERGE surface. Every DE codebase has hundreds of these; learn the compile output on your target engine so you can debug the compiled SQL when performance regresses.

Common beginner mistakes

Writing SELECT then INSERT instead of a single-statement UPSERT.
Forgetting the WHERE updated_at > highwater incremental filter — every run reads the full source.
Forgetting the WHEN MATCHED AND b.updated_at > u.updated_at timestamp guard — out-of-order events overwrite fresh ones.
Confusing MERGE with UPSERT — MERGE is ANSI three-branch; UPSERT is the two-branch native pattern on OLTP engines.
Assuming MERGE is atomic across concurrent writers — most engines still require row-level locking to serialize.
Using MERGE for high-QPS API upserts — MERGE is designed for batch, not for 10 k QPS. Use ON CONFLICT on OLTP engines instead.

`sql merge upsert` interview question on choosing between MERGE and native UPSERT

A senior interviewer often opens with: "You're building the API layer for a two-billion-row users table. Every profile update from the mobile app hits a PUT /users/:id endpoint. Do you use MERGE or INSERT ... ON CONFLICT, and why? Then design the equivalent daily-batch backfill from a Kafka topic into the analytics warehouse — same question there."

Solution Using dual-endpoint architecture — `INSERT ... ON CONFLICT` for OLTP, `MERGE INTO` for warehouse

-- ===================================================================
-- OLTP endpoint: high QPS, single-row UPSERT on Postgres 15
-- ===================================================================
-- API handler: PUT /users/:id
-- Expected QPS: 10,000+
-- Payload: {name, email, updated_at}
INSERT INTO users (id, name, email, updated_at)
VALUES (%s, %s, %s, %s)
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email,
  updated_at = EXCLUDED.updated_at
WHERE users.updated_at < EXCLUDED.updated_at   -- guard against stale writes
RETURNING id, name, email, updated_at;

-- ===================================================================
-- Warehouse backfill: daily batch, ~50M rows, Snowflake
-- ===================================================================
-- 1. Load source stage from S3 → Snowflake table s_users_delta
-- 2. Dedup within the batch, apply MERGE
MERGE INTO analytics.dim_users AS t
USING (
  SELECT id, name, email, updated_at
  FROM (
    SELECT id, name, email, updated_at,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
    FROM stage.s_users_delta
    WHERE _kafka_ts BETWEEN :start_ts AND :end_ts
  ) x
  WHERE rn = 1
) AS s
ON t.id = s.id
WHEN MATCHED AND s.updated_at > t.updated_at
  THEN UPDATE SET name = s.name, email = s.email, updated_at = s.updated_at
WHEN NOT MATCHED
  THEN INSERT (id, name, email, updated_at)
       VALUES (s.id, s.name, s.email, s.updated_at);

Step-by-step trace.

Step	OLTP `INSERT ... ON CONFLICT`	Warehouse `MERGE INTO`
1	client sends `PUT /users/42` with new name	daily batch: ~50M rows land in stage
2	single-row statement, atomic on unique index	dedup batch by (id, updated_at DESC) — one row per id
3	Postgres detects conflict → routes to UPDATE branch	Snowflake plans MERGE — scans stage, matches on t.id
4	RETURNING clause gives the client the persisted row	WHEN MATCHED AND updated_at > → UPDATE; WHEN NOT MATCHED → INSERT
5	commit: single-row lock held briefly	micro-partitions rewrite; wall clock ~2 min on Medium WH
6	client sees 200 in ~1-2 ms	orchestrator sees success; audit log has row_count
7	O(log n) per call (unique index seek)	O(source + target_matched) per batch

The OLTP endpoint uses ON CONFLICT because the workload is single-row, high-QPS, and needs atomic conflict detection at the unique index. The warehouse backfill uses MERGE INTO because the workload is batch, tens of millions of rows, and benefits from the source-scan-once execution the MERGE grammar guarantees. Two different primitives, same idempotency contract.

Output:

Endpoint	Cost model	Latency per call/batch	Idempotency guarantee
OLTP `ON CONFLICT`	~50 μs CPU + 1-2 ms wall clock	1-2 ms	Atomic on unique index
Warehouse `MERGE INTO`	~500 credits (Medium WH × 2 min)	~2 min	Deterministic on batch-dedup + timestamp guard

Why this works — concept by concept:

Dual primitive for dual workload — OLTP is high-QPS single-row; native ON CONFLICT is designed for exactly that shape (atomic conflict detection, minimal locking, fast). Warehouse batch is 50 M rows; MERGE INTO scans the source once, plans MATCHED / NOT MATCHED branches, and rewrites storage in a single pass.
WHERE users.updated_at < EXCLUDED.updated_at guard — protects against stale writes overtaking fresh writes when two concurrent clients update the same row with different timestamps. The write with the older updated_at becomes a no-op.
ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) batch dedup — the Kafka source may have 5 events for the same user in one batch. The dedup keeps only the latest per id; the MERGE never sees duplicates in the source, avoiding the SQL Server MERGE cardinality bug (see H2-5) and simplifying the WHEN MATCHED logic.
WHEN MATCHED AND s.updated_at > t.updated_at — out-of-order events across batches are guarded. If a stale batch arrives after a fresh one, MATCHED rows fail the condition and the branch is a no-op.
Cost — OLTP per-call is O(log n) (unique index seek) with a fixed ~1-2 ms overhead. Warehouse per-batch is O(source_scan + target_matched_partitions_rewrite); a 50 M-row batch on Snowflake Medium is roughly 2 minutes and ~500 credits. Total daily cost is bounded by batch cadence.

SQL
Topic — optimization
SQL optimization drills

Practice →

SQL Topic — indexing Unique index and constraint drills

Practice →

2. SQL Standard MERGE anatomy

`sql standard merge` — the ANSI SQL:2003 grammar, the three row-level branches, and how `WHEN NOT MATCHED BY SOURCE` extends the two-branch UPSERT into a three-way sync

The mental model in one line: the ANSI MERGE statement is a single-round-trip three-way sync operator that walks the source table once, joins each source row against the target via the ON condition, and applies exactly one of three actions per row — UPDATE (when a target match exists), INSERT (when the source row has no target match), or DELETE (when a target row has no source match) — turning the merge of two tables into a deterministic, atomic bulk operation. Master the grammar and you can express incremental loads, dimension updates, CDC apply-loops, and full three-way syncs in a single statement per pipeline.

Slot 1 — the six clauses of a canonical MERGE.

MERGE INTO target_table [AS t] — the write destination. Must be a base table (not a view on most engines, though some allow updatable views).
USING source_table [AS s] — the source. Can be a base table, subquery, CTE, or table function. Warehouses often materialise this as a temp table if complex.
ON t.pk = s.pk — the match condition. Determines which target rows are "matched" by each source row. Usually equality on the natural or synthetic primary key; can be composite.
WHEN MATCHED [AND condition] THEN UPDATE SET col = s.col, ... — the UPDATE branch. Optional AND condition scopes which matched rows this branch applies to.
WHEN NOT MATCHED [BY TARGET] [AND condition] THEN INSERT (cols) VALUES (s.cols) — the INSERT branch. Fires when a source row has no target match.
WHEN NOT MATCHED BY SOURCE [AND condition] THEN DELETE / UPDATE — the DELETE branch. Fires when a target row has no source match. Not universally supported; SQL Server and Snowflake support it, others do not.

Slot 2 — the three-way ANSI split.

INSERT branch (WHEN NOT MATCHED [BY TARGET]). New source rows land in the target. Guaranteed exactly once per source row that has no ON match.
UPDATE branch (WHEN MATCHED). Existing target rows are updated in place using values from the joined source row. Can conditionally UPDATE via AND condition or even DELETE the target row in some dialects.
DELETE branch (WHEN NOT MATCHED BY SOURCE). Target rows without a source match are removed. Used for full-refresh syncs where source is the authoritative snapshot.
The two-branch case (INSERT + UPDATE) is UPSERT. No DELETE branch → source can only add or update, never remove. Common for append-heavy pipelines.
The three-branch case is full sync. Source is the authoritative snapshot; target must mirror it. Common for dimension tables or config tables.

Slot 3 — deterministic evaluation.

Source is scanned once. The MERGE reads the source in a single pass. Each source row is joined against the target and routed to one branch. No per-row re-evaluation.
Target is scanned once (via join). For matched rows, the target is read (locked, in some dialects) once during the join.
Each source row triggers exactly one action. UPDATE, INSERT, or (with BY SOURCE) DELETE. The branches are mutually exclusive.
Row-level triggers fire in ANSI order. BEFORE INSERT / BEFORE UPDATE / BEFORE DELETE fire before the row-level action; AFTER equivalents fire after. Some engines have historically buggy trigger fire orders (SQL Server).
Statement-level triggers fire once. Total per-branch action counts are reported to the statement-level trigger — one AFTER MERGE trigger sees all inserts / updates / deletes.

Slot 4 — the ON condition rules.

Best case — equality on a unique key. ON t.id = s.id where t.id has a primary key or unique index. Deterministic, planner uses index seek, cost is O(source + log(target)).
Composite keys work. ON t.tenant_id = s.tenant_id AND t.entity_id = s.entity_id. Use composite unique index on (tenant_id, entity_id).
Non-unique ON conditions are dangerous. If a source row matches multiple target rows, some engines UPDATE all matched rows; others raise an error; SQL Server has the notorious cardinality bug.
Range conditions are allowed but rarely useful. ON t.start_ts <= s.event_ts AND t.end_ts > s.event_ts — a range join. Legal but expensive; often better to precompute the target row via a separate query.
Filter predicates in ON are pushed to the join. Adding AND s.tenant_id = 42 to the ON is equivalent to filtering source before MERGE. Cleaner to filter source in the USING subquery.

Slot 5 — the WHEN MATCHED variants.

WHEN MATCHED THEN UPDATE SET col = s.col — the classic. Updates all matched rows.
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET ... — conditional UPDATE. Only updates when the source is fresher. Common for CDC.
WHEN MATCHED AND s.is_deleted THEN DELETE — conditional DELETE via the MATCHED branch. Handy for tombstone events.
WHEN MATCHED THEN DO NOTHING — some dialects support explicit no-op. Rarely useful; equivalent to not writing the branch.
Multiple WHEN MATCHED clauses. Some dialects allow multiple; each row picks the first matching condition. Order matters.

Slot 6 — the WHEN NOT MATCHED variants.

WHEN NOT MATCHED [BY TARGET] THEN INSERT (...) VALUES (...) — the classic. Inserts new rows.
WHEN NOT MATCHED AND s.op = 'i' THEN INSERT — conditional INSERT. Only insert when the source-op is "insert" (CDC pattern).
WHEN NOT MATCHED THEN DO NOTHING — explicit no-op. Rare.
WHEN NOT MATCHED BY SOURCE THEN DELETE — the DELETE branch. Fires for target rows without source matches. Full-sync semantics.
WHEN NOT MATCHED BY SOURCE AND t.status = 'A' THEN UPDATE SET status = 'D' — soft-delete via UPDATE on the BY SOURCE branch. Preserves history.

Slot 7 — the row-level action decision tree.

For each source row s: Find target rows where t.pk = s.pk. Zero or more.
If exactly one target match — WHEN MATCHED branch fires. Optional condition filters. Action: UPDATE or DELETE.
If no target match — WHEN NOT MATCHED [BY TARGET] branch fires. Action: INSERT.
If multiple target matches — depends on dialect. Some UPDATE all (non-deterministic if source values differ per matched row). SQL Server crashes with cardinality error. Some engines allow it only if all matched target rows agree.
After all source rows processed — for each target row NOT matched by any source row, WHEN NOT MATCHED BY SOURCE branch fires (if written).

Slot 8 — the ANSI spec bites.

SQL Server MERGE bugs. Long list of known issues — cardinality errors, trigger fire order, plan-cache instability with parameter sniffing. Microsoft documents workarounds. Interview signal: knowing SQL Server MERGE is the buggy one and preferring the CTE-based UPDATE ... FROM ... for critical paths.
Oracle MERGE. ANSI-compliant but the WHEN NOT MATCHED BY SOURCE branch is missing. Full three-way sync requires a separate DELETE statement.
Postgres MERGE. Added in Postgres 15. Full ANSI compliance for the three branches. Postgres 17 added WHEN NOT MATCHED BY SOURCE. Most Postgres code still uses INSERT ... ON CONFLICT because it predates MERGE.
BigQuery MERGE. ANSI-compliant. WHEN NOT MATCHED BY TARGET is the explicit spelling; the implicit WHEN NOT MATCHED is also accepted. WHEN NOT MATCHED BY SOURCE supported.
Snowflake MERGE. ANSI-compliant with the full three-branch grammar plus optional AND cond on every branch.
Databricks MERGE. ANSI-compliant on Delta tables. Photon-accelerated. WHEN MATCHED AND cond THEN DELETE is idiomatic for tombstones.

Common beginner mistakes

Writing MERGE with a non-unique ON condition — cardinality bug or silent data loss.
Forgetting WHEN NOT MATCHED [BY TARGET] — some engines assume TARGET, some require the explicit spelling.
Writing INSERT ... VALUES (t.id, ...) — you meant s.id. Common typo.
Multiple WHEN MATCHED clauses without understanding the order — first matching condition wins per row.
Using MERGE for high-QPS API upserts — designed for batch. Use ON CONFLICT on OLTP.
Assuming DELETE branch (BY SOURCE) exists on all engines — Oracle and MySQL do not have it.

Worked example — the two-branch MERGE (UPSERT) on Snowflake

Detailed explanation. The most common MERGE pattern: dbt-style incremental UPSERT. Source is a batch of new + updated rows; target is the existing table; INSERT new IDs, UPDATE existing.

Question. Write a Snowflake MERGE that upserts new orders from the stage_orders table into analytics.orders, matching on order_id.

Input.

order_id	customer_id	total	updated_at
100	42	25.00	2026-07-12 09:00
101	43	40.00	2026-07-12 09:10
102	44	15.00	2026-07-12 09:15

Target (analytics.orders) currently has:

order_id	customer_id	total	updated_at
100	42	20.00	2026-07-12 08:00

Code.

MERGE INTO analytics.orders AS t
USING stage_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
  customer_id = s.customer_id,
  total = s.total,
  updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT
  (order_id, customer_id, total, updated_at)
  VALUES (s.order_id, s.customer_id, s.total, s.updated_at);

Step-by-step explanation.

Source scan — Snowflake reads stage_orders once (3 rows).
For each source row, join against target on order_id. 100 matches, 101 and 102 don't.
100 — WHEN MATCHED branch fires. UPDATE the target row's customer_id, total, updated_at to source values.
101 and 102 — WHEN NOT MATCHED branch fires. INSERT them with the source values.
Snowflake reports rows_inserted: 2, rows_updated: 1 in the query result summary.

Output.

order_id	customer_id	total	updated_at
100	42	25.00	2026-07-12 09:00
101	43	40.00	2026-07-12 09:10
102	44	15.00	2026-07-12 09:15

Rule of thumb. Two-branch MERGE (INSERT + UPDATE) covers 80% of dbt incremental use cases. Learn this template cold; the rest of the ANSI grammar layers on top.

Worked example — the three-branch MERGE with DELETE (full sync)

Detailed explanation. Full-sync MERGE: source is the authoritative snapshot; target must exactly mirror it. Rows in target that are no longer in source must be deleted.

Question. Write a Snowflake MERGE that fully syncs the target dim_products from the source stage_products. Insert new products, update changed ones, delete products no longer in source.

Input.

product_id	name	price
1	Widget	10.00
2	Gadget	20.00
4	Sprocket	30.00

Target (dim_products) currently:

product_id	name	price
1	Widget	8.00
2	Gadget	20.00
3	Doohickey	15.00

Code.

MERGE INTO dim_products AS t
USING stage_products AS s
ON t.product_id = s.product_id
WHEN MATCHED AND (t.name <> s.name OR t.price <> s.price) THEN UPDATE SET
  name = s.name,
  price = s.price
WHEN NOT MATCHED BY TARGET THEN INSERT
  (product_id, name, price) VALUES (s.product_id, s.name, s.price)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Step-by-step explanation.

Source scan — 3 rows: (1, Widget, 10), (2, Gadget, 20), (4, Sprocket, 30).
Target rows: (1, Widget, 8), (2, Gadget, 20), (3, Doohickey, 15).
1 — matched. UPDATE branch fires with condition. (t.name <> s.name OR t.price <> s.price) — name is same, price differs (8 vs 10) → UPDATE. Set price=10.
2 — matched. Condition — same name and same price → UPDATE skipped (no-op).
3 — NOT MATCHED BY SOURCE — DELETE branch fires. Row removed.
4 — NOT MATCHED BY TARGET — INSERT branch fires. Row added.

Output.

product_id	name	price
1	Widget	10.00
2	Gadget	20.00
4	Sprocket	30.00

Rule of thumb. Three-branch MERGE (with BY SOURCE DELETE) is the full-sync pattern for dimension tables and config snapshots. Confirm the DELETE branch is intentional — a bad source query can delete production dim rows.

Worked example — conditional UPDATE using `WHEN MATCHED AND cond`

Detailed explanation. CDC apply-loops often want to update only when the source is newer than the target. The AND clause on WHEN MATCHED expresses this cleanly.

Question. Write a Snowflake MERGE that upserts CDC events into users, only updating when the event's updated_at is newer than the target's updated_at.

Input.

-- CDC batch (source)
CREATE TEMP TABLE cdc_batch AS
SELECT * FROM VALUES
  (42, 'alice-new', TIMESTAMP '2026-07-12 10:00'),
  (43, 'bob-STALE', TIMESTAMP '2026-07-12 08:00')   -- older than target
AS t(id, name, updated_at);

-- Target
CREATE TEMP TABLE users AS
SELECT * FROM VALUES
  (42, 'alice-old', TIMESTAMP '2026-07-12 09:00'),
  (43, 'bob-fresh', TIMESTAMP '2026-07-12 09:30')
AS t(id, name, updated_at);

Code.

MERGE INTO users AS t
USING cdc_batch AS s
ON t.id = s.id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
  name = s.name,
  updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT
  (id, name, updated_at) VALUES (s.id, s.name, s.updated_at);

Step-by-step explanation.

Source scan — 2 rows: (42, alice-new, 10:00) and (43, bob-STALE, 08:00).
Target: (42, alice-old, 09:00) and (43, bob-fresh, 09:30).
42 — matched. Condition 10:00 > 09:00 → true. UPDATE fires. name → alice-new, updated_at → 10:00.
43 — matched. Condition 08:00 > 09:30 → false. UPDATE skipped — no-op.
Result — 42 updated, 43 unchanged. Stale event correctly ignored.

Output.

id	name	updated_at
42	alice-new	2026-07-12 10:00
43	bob-fresh	2026-07-12 09:30

Rule of thumb. Every CDC apply-loop needs WHEN MATCHED AND s.updated_at > t.updated_at. Without it, stale events replay corrupt fresh state.

`sql standard merge` interview question on writing a three-branch MERGE

A senior interviewer often asks: "Write a Snowflake MERGE that syncs the stg_customers staging table into analytics.dim_customers — insert new customers, update changed customers, and soft-delete customers no longer in source by setting is_active = FALSE and deactivated_at = CURRENT_TIMESTAMP(). Do not hard-delete — retain history."

Solution Using three-branch MERGE with soft-delete on WHEN NOT MATCHED BY SOURCE

MERGE INTO analytics.dim_customers AS t
USING (
  SELECT
    id,
    name,
    email,
    country,
    _snapshot_ts AS updated_at
  FROM stg_customers
  WHERE _batch_id = :batch_id
) AS s
ON t.id = s.id
WHEN MATCHED AND (
    t.name    IS DISTINCT FROM s.name
 OR t.email   IS DISTINCT FROM s.email
 OR t.country IS DISTINCT FROM s.country
) THEN UPDATE SET
  name = s.name,
  email = s.email,
  country = s.country,
  updated_at = s.updated_at,
  is_active = TRUE,
  deactivated_at = NULL
WHEN NOT MATCHED BY TARGET THEN INSERT
  (id, name, email, country, updated_at, is_active, deactivated_at)
  VALUES (s.id, s.name, s.email, s.country, s.updated_at, TRUE, NULL)
WHEN NOT MATCHED BY SOURCE AND t.is_active = TRUE THEN UPDATE SET
  is_active = FALSE,
  deactivated_at = CURRENT_TIMESTAMP();

Step-by-step trace.

Step	Action	Rows affected
1	Source scan of `stg_customers` filtered by batch_id	~500K rows
2	Join against `dim_customers` on `id`	matched + unmatched sets
3	WHEN MATCHED AND cols DIFFER → UPDATE (name, email, country, updated_at)	~50K changed
4	WHEN NOT MATCHED BY TARGET → INSERT	~5K new
5	WHEN NOT MATCHED BY SOURCE AND is_active → UPDATE (soft-delete)	~1K deactivated
6	Report row_count summary	50K+5K+1K = 56K

Output:

Metric	Value
Rows inserted	~5,000
Rows updated (data change)	~50,000
Rows soft-deleted	~1,000
Rows re-activated (previously inactive, back in source)	included in UPDATE branch
Total wall clock (Snowflake Medium WH, 500K source × 2M target)	~90 seconds

Why this works — concept by concept:

IS DISTINCT FROM — null-safe inequality. NULL <> NULL is NULL (falsy in boolean context); NULL IS DISTINCT FROM NULL is FALSE (correct). Guards against updating rows where a column is null on both sides.
Conditional WHEN MATCHED — only UPDATE if a column actually changed. Skips writes on rows that arrived unchanged, saving micro-partition rewrite cost.
Re-activation via WHEN MATCHED UPDATE — a previously soft-deleted customer that re-appears in source flips is_active = TRUE, deactivated_at = NULL. Handled inside the MATCHED branch alongside the data update.
Soft-delete via WHEN NOT MATCHED BY SOURCE — preserves history. Analysts can still query the customer's fields; downstream reports respect is_active.
AND t.is_active = TRUE guard on the soft-delete branch — prevents re-writing already-deactivated rows on subsequent runs. Deactivation is a one-time transition.
Cost — MERGE reads source once and target once via the join. On Snowflake, cost is dominated by micro-partition rewrites for the matched+changed set. On Medium WH, 500K source × 2M target with 50K UPDATEs + 5K INSERTs + 1K soft-DELETEs is roughly 90 seconds of wall clock and ~10 credits. Batch-level idempotency: replaying the same batch is a no-op because the IS DISTINCT FROM guard filters unchanged rows.

SQL
Topic — joins
SQL join and merge drills

Practice →

SQL Topic — SQL SQL practice library

Practice →

3. Postgres ON CONFLICT + MySQL ON DUPLICATE KEY + SQLite UPSERT

The OLTP-native `sql upsert` — `postgres on conflict`, `mysql on duplicate key update`, and `sqlite upsert` — three spellings of the same atomic conflict-resolution primitive

The mental model in one line: on OLTP engines (Postgres, MySQL, SQLite), the idiomatic UPSERT is not MERGE — it is a special extension of INSERT that says "if the INSERT would violate a unique constraint, resolve the conflict this way instead of raising an error", and the resolution runs atomically inside the write path with no application-side check and no race window. Each engine spells it differently but the semantics are identical: atomic, race-free, single-round-trip.

Slot 1 — Postgres INSERT ... ON CONFLICT ... DO UPDATE / DO NOTHING.

INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name — the canonical spelling. The (id) is the conflict target — a column list matching a unique index or primary key.
ON CONFLICT ON CONSTRAINT constraint_name DO UPDATE ... — alternate spelling that names the constraint explicitly. Useful when multiple unique indexes exist and you need to specify which one.
ON CONFLICT DO NOTHING — the "insert or skip" pattern. Silently drops rows that would conflict. Common for append-only tables where duplicate detection matters more than the actual duplicate values.
EXCLUDED.col — the pseudo-table representing the row that would have been inserted. Available inside the DO UPDATE clause. SET col = EXCLUDED.col copies the incoming value; SET col = t.col + EXCLUDED.col adds them (increment counter pattern).
WHERE clause on DO UPDATE. ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name WHERE t.updated_at < EXCLUDED.updated_at. Guards against stale writes overwriting fresh writes. Common for LWW (last-writer-wins) semantics with timestamp guard.
RETURNING — returns the persisted row(s). Distinguishes INSERT vs UPDATE via a RETURNING (xmax = 0) AS was_insert trick.
INSERT ... ON CONFLICT ... is atomic. Detects the conflict inside the write path via the unique index. No race window. No application-side check.

Slot 2 — MySQL INSERT ... ON DUPLICATE KEY UPDATE.

INSERT INTO t (id, name) VALUES (1, 'a') ON DUPLICATE KEY UPDATE name = 'a' — the canonical spelling. Note: no conflict target — any unique-index conflict triggers the DUPLICATE branch.
VALUES(col) pseudo-function — pre-MySQL 8.0.20 spelling. ON DUPLICATE KEY UPDATE name = VALUES(name) — the VALUES(name) returns the incoming value.
new.col row alias (MySQL 8.0.19+). INSERT INTO t (id, name) VALUES (1, 'a') AS new ON DUPLICATE KEY UPDATE name = new.name. Cleaner than VALUES(). Preferred spelling.
No explicit conflict target. MySQL treats any unique-index conflict as a duplicate. If multiple unique indexes exist and different rows conflict on each, behaviour is dialect-specific and often surprising.
ROW_COUNT() semantics. Returns 1 for INSERT, 2 for UPDATE. Handy for detecting which branch fired.
INSERT IGNORE ... — the MySQL equivalent of ON CONFLICT DO NOTHING. Silently drops rows that would conflict. Also drops rows that would fail any insert-time error, which is dangerous — avoid.
REPLACE INTO — do not use. MySQL's REPLACE INTO is a DELETE-then-INSERT. Breaks FK constraints, fires DELETE triggers, and re-numbers AUTO_INCREMENT. Historical wart; use ON DUPLICATE KEY UPDATE instead.

Slot 3 — SQLite INSERT ... ON CONFLICT ... DO UPDATE / DO NOTHING.

INSERT INTO t (id, name) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET name = excluded.name — the canonical spelling. Since SQLite 3.24 (2018). Grammar mirrors Postgres, with the excluded pseudo-table being lowercase (Postgres uses uppercase EXCLUDED; both accept either in practice).
Conflict target on unique column or unique index. ON CONFLICT (id) — targets the primary key or unique index on id.
WHERE clause on DO UPDATE. Same as Postgres.
DO NOTHING supported. Same semantics.
INSERT OR REPLACE INTO ... — the legacy SQLite spelling. Also DELETE-then-INSERT, same wart as MySQL's REPLACE.
INSERT OR IGNORE INTO ... — silently skip on conflict. Same as ON CONFLICT DO NOTHING but without a target.

Slot 4 — the EXCLUDED / VALUES / excluded pseudo-tables.

Postgres EXCLUDED — represents the row that would have been inserted. Read-only. Reference as EXCLUDED.col. Available inside DO UPDATE SET ... = EXCLUDED.col.
MySQL VALUES(col) — a function that returns the incoming value for col. Legacy; MySQL 8.0.20 deprecated it in favor of the new.col row alias.
MySQL new.col — the row alias set via VALUES (...) AS new. Preferred in 8.0.19+.
SQLite excluded — same as Postgres. Lowercase, but SQLite is case-insensitive.
Use case — increment counter. INSERT INTO counters (id, n) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET n = counters.n + EXCLUDED.n. Atomically increments if row exists, initializes to 1 if new.
Use case — LWW with timestamp. INSERT ... ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val WHERE EXCLUDED.updated_at > t.updated_at. Only updates when the incoming row is newer.

Slot 5 — constraint targeting.

Postgres — must name a target. ON CONFLICT (id) DO UPDATE. If you omit the target, the syntax is ON CONFLICT DO NOTHING only (no UPDATE). This is by design — DO UPDATE needs to know which columns' EXCLUDED values to reference.
Postgres — target must match a unique constraint. The column list must exactly match a unique or primary key constraint. Partial or expression-index conflicts require ON CONFLICT ON CONSTRAINT.
Postgres partial unique index. CREATE UNIQUE INDEX idx ON t (id) WHERE is_active. Conflict target must include the WHERE clause: ON CONFLICT (id) WHERE is_active DO UPDATE SET ....
MySQL — no target needed. Any unique-index conflict fires the DUPLICATE branch. This is simpler but harder to reason about with multiple unique indexes.
SQLite — target optional. Column list preferred but not required. Similar to MySQL in permissiveness.

Slot 6 — atomicity and locking.

Postgres — row-level lock during conflict resolution. The unique-index lookup and subsequent UPDATE happen under an exclusive row lock. Concurrent writers serialize on the same key; different keys go through in parallel.
MySQL — gap-locking implications. InnoDB uses gap locks and next-key locks during conflict detection. Occasionally causes surprise deadlocks under high concurrency; the default is fine for most workloads.
SQLite — full DB lock. SQLite serialises writes at the database level (single writer). Concurrency is limited but consistency is bulletproof.
All three engines — conflict detection inside the write. No two-statement race. The unique-index check happens as part of the INSERT, atomically.

Slot 7 — RETURNING and side effects.

Postgres INSERT ... ON CONFLICT ... RETURNING ... — returns the persisted row (post-INSERT or post-UPDATE). Handy for API layers that need to know the current server-side state.
Postgres RETURNING (xmax = 0) AS was_insert — distinguishes INSERT vs UPDATE. xmax = 0 when the row was inserted (no prior version); otherwise the row was updated.
MySQL — no RETURNING. MariaDB 10.5+ added RETURNING, but MySQL proper does not. Use SELECT ... FROM ... WHERE id = LAST_INSERT_ID() or SELECT ROW_COUNT().
SQLite INSERT ... ON CONFLICT ... RETURNING ... — supported since 3.35 (2021). Same semantics as Postgres.
Trigger fire order. All three engines fire triggers appropriately per branch (BEFORE INSERT + AFTER INSERT for the INSERT branch; BEFORE UPDATE + AFTER UPDATE for the UPDATE branch).

Slot 8 — interview probes on OLTP UPSERT.

"When would you use ON CONFLICT DO NOTHING?" — Append-only tables where dedup matters more than the values. Common for audit logs, event ledgers.
"How do you distinguish INSERT vs UPDATE in Postgres?" — RETURNING (xmax = 0) AS was_insert. xmax = 0 on the returned row means INSERT.
"Why is MySQL's REPLACE INTO bad?" — DELETE-then-INSERT breaks FK, fires DELETE triggers, and re-numbers AUTO_INCREMENT. Use ON DUPLICATE KEY UPDATE instead.
"What's the EXCLUDED pseudo-table?" — The row that would have been inserted, accessible in DO UPDATE. EXCLUDED.col gives you the incoming value.
"How do you write a race-safe counter increment?" — INSERT INTO counters (id, n) VALUES (1, 1) ON CONFLICT (id) DO UPDATE SET n = counters.n + 1 RETURNING n. Atomic.
"What happens if the INSERT would satisfy multiple unique indexes?" — Postgres requires you to name one target. MySQL uses whichever fires first (non-deterministic). SQLite similar to MySQL.

Common beginner mistakes

Writing ON DUPLICATE KEY UPDATE name = 'a' — hardcodes the value instead of name = new.name or name = VALUES(name).
Forgetting the WHERE clause guard on DO UPDATE — allows stale writes to overwrite fresh writes.
Using INSERT IGNORE on MySQL — silently drops errors beyond duplicate keys; masks real issues.
Using REPLACE INTO on MySQL — DELETE-then-INSERT wart.
Not naming the conflict target on Postgres DO UPDATE — Postgres requires it.
Assuming ON CONFLICT semantics work on views — they only work on base tables.

Worked example — the atomic counter increment on Postgres

Detailed explanation. A hit-counter pattern that increments a per-key counter atomically, initializing to 1 on first hit. The atomic ON CONFLICT DO UPDATE SET n = t.n + EXCLUDED.n is race-free even under thousands of concurrent writers.

Question. Write a Postgres UPSERT that increments a page-view counter for a given page_id, initializing to 1 if the row doesn't exist, returning the new count.

Input.

CREATE TABLE page_views (page_id INT PRIMARY KEY, n INT NOT NULL);

Code.

INSERT INTO page_views (page_id, n) VALUES (%s, 1)
ON CONFLICT (page_id) DO UPDATE SET n = page_views.n + EXCLUDED.n
RETURNING n;

Step-by-step explanation.

Client calls with page_id = 42. First call — no row exists. INSERT branch fires. Row (42, 1) inserted. RETURNING gives n = 1.
Second concurrent call with same page_id = 42. Row exists. ON CONFLICT branch fires. UPDATE sets n = page_views.n + 1 = 2. Under exclusive row lock — no race.
Third concurrent call — same. UPDATE sets n = 3.
Under 10,000 concurrent calls — Postgres serialises writers on the same page_id via row lock. Different pages go through in parallel.
No lost increments. Every call increments by exactly 1.

Output.

Call	page_id	Returned n
1	42	1
2	42	2
3	42	3
4	43	1
5	43	2

Rule of thumb. Atomic counter increments are the poster child for OLTP UPSERT. Never use SELECT n THEN UPDATE n = n + 1 — always INSERT ... ON CONFLICT DO UPDATE.

Worked example — LWW UPSERT with timestamp guard on Postgres

Detailed explanation. A common CDC / cache-invalidation pattern — update the cached row only if the incoming version is newer than the cached version. Guards against out-of-order arrivals.

Question. Write a Postgres UPSERT for a session cache where each row has an updated_at timestamp. Only update if the incoming version is newer.

Input.

CREATE TABLE session_cache (
  session_id UUID PRIMARY KEY,
  payload JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);

Code.

INSERT INTO session_cache (session_id, payload, updated_at)
VALUES (%s, %s, %s)
ON CONFLICT (session_id) DO UPDATE SET
  payload = EXCLUDED.payload,
  updated_at = EXCLUDED.updated_at
WHERE EXCLUDED.updated_at > session_cache.updated_at;

Step-by-step explanation.

Client calls with session_id = X, payload = P, updated_at = 10:00. Row doesn't exist. INSERT.
Second call with same session_id, payload = P2, updated_at = 09:00 (older — network delivered out of order). ON CONFLICT branch. WHERE clause: 09:00 > 10:00 → false. UPDATE skipped. Row unchanged.
Third call: payload = P3, updated_at = 11:00. ON CONFLICT branch. WHERE: 11:00 > 10:00 → true. UPDATE fires. Row now has P3, 11:00.
Fourth call: payload = P2, updated_at = 09:00 (very late duplicate). ON CONFLICT. WHERE: 09:00 > 11:00 → false. Skipped.
Idempotent — the same duplicate calls have no effect after the first application.

Output.

Call	payload arg	updated_at arg	Row state after
1	P	10:00	(P, 10:00)
2	P2	09:00 (stale)	(P, 10:00) unchanged
3	P3	11:00	(P3, 11:00)
4	P2	09:00 (dup)	(P3, 11:00) unchanged

Rule of thumb. LWW UPSERT with timestamp guard is the standard cache-pattern. Never write ordered assumptions into your API client — the pattern handles late/duplicate arrivals correctly at the write.

Worked example — MySQL UPSERT with the row alias

Detailed explanation. MySQL 8.0.19+ ships a cleaner UPSERT syntax with row-aliasing. Prefer this over VALUES() in new code.

Question. Write a MySQL 8 UPSERT for the page_views example, using the row alias.

Input.

CREATE TABLE page_views (page_id INT PRIMARY KEY, n INT NOT NULL);

Code.

INSERT INTO page_views (page_id, n) VALUES (%s, 1) AS new
ON DUPLICATE KEY UPDATE n = page_views.n + new.n;

Step-by-step explanation.

AS new — alias the incoming row. Subsequent references to new.n return the incoming value.
If page_id doesn't exist — INSERT branch. Row (42, 1) inserted.
If page_id exists — ON DUPLICATE KEY branch. UPDATE n = page_views.n + new.n. Row goes from (42, N) to (42, N+1).
Pre-MySQL-8.0.19 spelling: ... ON DUPLICATE KEY UPDATE n = page_views.n + VALUES(n). Both work; new is cleaner.
SELECT ROW_COUNT() — returns 1 for insert, 2 for update. Distinguish INSERT vs UPDATE this way.

Output.

Call	Row state after	ROW_COUNT()
First	(42, 1)	1 (INSERT)
Second	(42, 2)	2 (UPDATE)
Third	(42, 3)	2 (UPDATE)

Rule of thumb. MySQL 8+ ships two UPSERT spellings — VALUES() (legacy) and AS new (row alias). Prefer the row alias.

`postgres on conflict` interview question on multi-column conflict target

A senior interviewer asks: "You have a orders_by_customer_month table with a composite PK (customer_id, year_month) tracking monthly order totals. Write a Postgres UPSERT that atomically adds a new order's total to the monthly counter, initializing on first order per customer per month."

Solution Using composite conflict target + atomic increment via EXCLUDED

CREATE TABLE orders_by_customer_month (
  customer_id INT NOT NULL,
  year_month CHAR(7) NOT NULL,     -- e.g. '2026-07'
  total_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
  order_count INT NOT NULL DEFAULT 0,
  last_updated TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (customer_id, year_month)
);

-- The upsert
INSERT INTO orders_by_customer_month
  (customer_id, year_month, total_amount, order_count, last_updated)
VALUES
  (%s, TO_CHAR(%s::date, 'YYYY-MM'), %s, 1, NOW())
ON CONFLICT (customer_id, year_month) DO UPDATE SET
  total_amount = orders_by_customer_month.total_amount + EXCLUDED.total_amount,
  order_count  = orders_by_customer_month.order_count + 1,
  last_updated = NOW()
RETURNING total_amount, order_count;

Step-by-step trace.

Step	(cust=42, month=2026-07, amount=25)	State after
1	First order for cust 42 in July 2026	INSERT → (42, '2026-07', 25.00, 1)
2	Second order for cust 42 in July, amount 40	CONFLICT → UPDATE → total=65.00, count=2
3	First order for cust 43 in July, amount 30	INSERT → (43, '2026-07', 30.00, 1)
4	Third order for cust 42 in July, amount 15	CONFLICT → UPDATE → total=80.00, count=3
5	First order for cust 42 in August, amount 20	INSERT → (42, '2026-08', 20.00, 1)

Output:

customer_id	year_month	total_amount	order_count
42	2026-07	80.00	3
42	2026-08	20.00	1
43	2026-07	30.00	1

Why this works — concept by concept:

Composite conflict target — ON CONFLICT (customer_id, year_month) matches the composite PK. Row-level lock is scoped to the specific (customer, month) bucket, so different customers or different months proceed in parallel.
EXCLUDED.total_amount + running-total via reference to the target — the DO UPDATE clause reads orders_by_customer_month.total_amount (current stored value) and adds EXCLUDED.total_amount (incoming order's amount). Atomic under exclusive row lock; no read-modify-write race.
+ 1 for order_count — increment by exactly 1 per order. Race-free because the entire UPSERT runs under the row lock.
NOW() in both branches — timestamp always updated to the moment of the write, whether it was an INSERT or an UPDATE. Handy for observability.
RETURNING — the API can return {total_amount, order_count} after the write in the same round-trip. No follow-up SELECT needed.
Cost — per-call O(log n) on the composite unique index. Under 10,000 concurrent writers, throughput is bounded by lock contention on hot (customer, month) buckets; for cold buckets, throughput scales linearly with connections.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — indexing Composite unique index drills

Practice →

4. Snowflake + BigQuery + Databricks MERGE

Warehouse-native `snowflake merge`, `bigquery merge`, and Databricks MERGE — the same ANSI grammar, three different storage-rewrite models, three different cost surfaces

The mental model in one line: on modern warehouses, MERGE is the canonical bulk-idempotent-write primitive; Snowflake rewrites the affected micro-partitions, BigQuery rewrites the affected partitions, Databricks/Delta rewrites the affected Parquet files, and the cost surface for a given MERGE is roughly (rows_affected / rows_per_partition) × (partition_rewrite_cost) plus the source-scan cost. Understanding the storage-rewrite model per engine is the difference between a 30-second dbt incremental and a 30-minute one.

Slot 1 — Snowflake MERGE anatomy.

Grammar — full ANSI three-branch: MERGE INTO t USING s ON t.pk = s.pk WHEN MATCHED [AND cond] THEN UPDATE / DELETE WHEN NOT MATCHED [AND cond] THEN INSERT.
Storage rewrite — Snowflake rewrites the micro-partitions containing matched target rows. If your MERGE touches rows in 47 of 8,000 partitions, only those 47 are rewritten. Immutable, versioned; old micro-partitions retained per Time Travel retention.
Cost — warehouse tier × wall clock. A Medium warehouse for 2 minutes ≈ 4 credits (~$8 at $2/credit). Optimising MERGE means minimising rewritten partitions (via CLUSTER BY on the ON column) and minimising wall clock (via appropriate WH sizing).
Query Profile — MERGE shows as a plan with source scan, target scan (via join), and a Delete/Insert operator at the top. Read partition-scan and pruning % on both source and target.
Cluster keys — cluster the target on the merge key (t.pk) so target scan prunes. Cluster the source on the merge key for source-side pruning.
Concurrent MERGE handling — Snowflake serialises concurrent MERGEs on the same target table by default. Concurrent MERGEs against different partitions may parallelise.
STREAM ... ON TABLE — combines with MERGE for CDC-style incremental loads. The stream captures inserts/updates/deletes on the source; MERGE consumes.
WHEN MATCHED AND is_deleted THEN DELETE — supports tombstone-driven deletes cleanly.
WHEN NOT MATCHED BY SOURCE THEN DELETE — full-sync deletes supported.
Return value — Snowflake reports number of rows inserted / updated / deleted in the query result summary.

Slot 2 — BigQuery MERGE anatomy.

Grammar — full ANSI three-branch: MERGE INTO t USING s ON t.pk = s.pk WHEN MATCHED [AND cond] THEN UPDATE / DELETE WHEN NOT MATCHED BY TARGET [AND cond] THEN INSERT WHEN NOT MATCHED BY SOURCE [AND cond] THEN UPDATE / DELETE. The BY TARGET / BY SOURCE spellings are explicit.
Storage rewrite — BigQuery rewrites the affected partitions. Each partition is a set of columnar files; MERGE rewrites the entire partition, not individual rows. This is why partition alignment on the MERGE key is critical.
Cost — on-demand. $5/TB scanned + rewrite cost. Full-table MERGE on a 100 TB unpartitioned table scans 100 TB → $500 per MERGE.
Cost — flat-rate. slot_ms. MERGE consumes slots for source-scan + join + rewrite. A well-planned MERGE completes in tens of thousands of slot_ms; a badly-planned one in millions.
--dry_run — estimate bytes-processed before running. Free. Use in CI to gate expensive MERGEs.
Partition alignment — if the source's date range covers only July 2026, BigQuery can prune to partitions 20260701..20260731 on the target if the ON clause includes a partition column filter.
Cluster keys — cluster the target on the MERGE key for block-level pruning inside partitions.
Concurrent MERGE handling — BigQuery serialises writes to a table. Concurrent MERGEs queue.
WHEN NOT MATCHED BY SOURCE — supported. Requires a full target scan; expensive on large tables.
Return value — the job's numDmlAffectedRows metadata field reports total rows affected.

Slot 3 — Databricks / Delta Lake MERGE anatomy.

Grammar — full ANSI three-branch on Delta tables. Non-Delta tables (Parquet, CSV, etc.) do NOT support MERGE.
Storage rewrite — Delta MERGE rewrites the affected Parquet files (typically 100 MB – 1 GB per file). Reads the affected files, applies the branches, writes new files, updates the Delta transaction log to point at the new files.
Cost — DBUs. Databricks Units are the compute-time unit. MERGE cost = DBU/hr × wall clock. Photon acceleration reduces wall clock and DBU cost by 3-5× typical.
File pruning — Delta uses data-skipping via min/max per file for the MERGE key. Cluster / Z-order on the MERGE key for pruning.
Concurrent MERGE handling — Delta uses optimistic concurrency control. Two concurrent MERGEs to the same table may both start; one commits, the other detects the conflict on commit and retries (with the latest snapshot).
WHEN MATCHED AND cond THEN UPDATE / DELETE — full conditional branches.
WHEN NOT MATCHED AND cond THEN INSERT — full support.
WHEN NOT MATCHED BY SOURCE — supported since Databricks Runtime 12.
CHANGE DATA FEED — Delta's built-in CDC feed. Combine with downstream MERGE for incremental pipelines.
OPTIMIZE ... ZORDER BY — periodic maintenance to reduce fragmentation from many small MERGE writes. Rewrite small files into big ones, z-order by the MERGE key.

Slot 4 — the storage-rewrite math per engine.

Snowflake — per micro-partition. Micro-partition size is ~50-500 MB uncompressed. A MERGE touching 47 micro-partitions rewrites 47 × ~200 MB = ~9 GB of storage. Cost is proportional.
BigQuery — per partition. Partition size varies (daily partitions may be MB or TB). A MERGE touching 30 partitions rewrites all 30 in full — even if only 1 row per partition matched.
Delta — per Parquet file. File size default ~1 GB; can be tuned. A MERGE touching 100 files rewrites those 100 files = ~100 GB of storage. OPTIMIZE periodically compacts to keep files at target size.
Optimising rewrite cost. Cluster / partition / Z-order the target on the MERGE key so the affected files are localised, not spread across all files.
The "small MERGE, big cost" trap. A MERGE that updates 100 rows spread across 100 partitions rewrites 100 partitions. Same MERGE on a table clustered by the merge key rewrites 1 partition. Same cost model on all three engines.

Slot 5 — cost surface per engine.

Snowflake. Warehouse tier × wall clock. Optimise for shorter wall clock at given tier.
BigQuery on-demand. Bytes scanned × $5/TB + rewrite. Optimise for smaller bytes scanned via partition prune.
BigQuery flat-rate. Slot commitment × month. Optimise for slot efficiency.
Databricks. DBU/hr × wall clock. Photon acceleration is often the biggest lever.
Cluster / partition / z-order investments. Rewrite the target's physical layout once; benefit every subsequent MERGE.

Slot 6 — CDC apply-loops with STREAM / CHANGES.

Snowflake CREATE STREAM ON TABLE source_events — captures inserts/updates/deletes. Combined with MERGE, gives you an idempotent CDC apply-loop.
Snowflake TASK — schedule the MERGE from the stream, runs every N minutes.
BigQuery INFORMATION_SCHEMA.STREAMING_TABLES — not a native CDC feed; use Datastream or third-party.
Delta CHANGES feed — enable spark.databricks.delta.properties.defaults.enableChangeDataFeed = true. Downstream reads the CDC feed and applies via MERGE.
Common pattern — source stream → dedup by key → MERGE with timestamp guard → target dimension table.

Slot 7 — the 7 most common warehouse MERGE pathologies.

Unpartitioned target on BigQuery. MERGE scans the full table on every run. Fix — PARTITION BY DATE(created_at).
Unclustered target on Snowflake. MERGE rewrites micro-partitions across the whole table. Fix — CLUSTER BY (merge_key).
Delta table without OPTIMIZE. Many small files from frequent MERGEs. Fix — schedule OPTIMIZE t ZORDER BY (merge_key) weekly.
SELECT * in source. Reads more columns than needed. Fix — project only the merge key + updated columns.
Non-unique ON condition. Ambiguous WHEN MATCHED, silent data loss. Fix — dedup source before MERGE.
MERGE on the wrong join order. Fix — the smaller side goes on the right. Warehouses generally figure this out, but hints help.
No timestamp guard. Stale events overwrite fresh. Fix — WHEN MATCHED AND s.updated_at > t.updated_at.

Common beginner mistakes

Using MERGE on unpartitioned BigQuery tables — costs 100× more than needed.
Forgetting WHEN NOT MATCHED BY TARGET on BigQuery — the explicit spelling is required in some contexts.
Running Delta MERGE on non-Delta tables — silent failure (Parquet tables reject MERGE).
Not scheduling OPTIMIZE ZORDER BY on Delta — files fragment into thousands of tiny ones.
Assuming Snowflake serialises all MERGE writers — different partitions can parallelise.
Non-deterministic source rows — duplicate keys in source produce undefined behaviour on WHEN MATCHED across all three engines.

Worked example — dbt incremental on Snowflake, from raw batch to MERGE

Detailed explanation. The daily dbt incremental pattern on Snowflake. Loads yesterday's orders into a snapshot table, then MERGEs into the analytics dim table.

Question. Write the full Snowflake MERGE for the dbt incremental with unique_key='order_id', timestamp guard on updated_at, and a _dbt_updated_at tracker column on the target.

Input. stg_orders has ~1M rows (yesterday's batch), dim_orders has ~500M rows (all-time).

Code.

MERGE INTO analytics.dim_orders AS t
USING (
  SELECT
    order_id, customer_id, total, status, updated_at,
    CURRENT_TIMESTAMP() AS _dbt_updated_at
  FROM (
    SELECT
      order_id, customer_id, total, status, updated_at,
      ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
    FROM staging.stg_orders
    WHERE _batch_id = :batch_id
  ) x
  WHERE rn = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
  customer_id = s.customer_id,
  total = s.total,
  status = s.status,
  updated_at = s.updated_at,
  _dbt_updated_at = s._dbt_updated_at
WHEN NOT MATCHED THEN INSERT
  (order_id, customer_id, total, status, updated_at, _dbt_updated_at)
  VALUES
  (s.order_id, s.customer_id, s.total, s.status, s.updated_at, s._dbt_updated_at);

Step-by-step explanation.

Source subquery dedupes the batch by order_id, keeping the latest updated_at per order.
Target scan on dim_orders uses the cluster key on order_id for pruning. Only micro-partitions containing matching order_ids are touched.
WHEN MATCHED AND timestamp — updates only when source is fresher. Prevents stale replays from overwriting.
WHEN NOT MATCHED — inserts new order_ids into the target.
_dbt_updated_at — a target-side observability column tracking when dbt last touched the row. Common in dbt patterns.

Output.

Metric	Value
Source rows scanned	1,000,000
Source rows deduped	950,000 (some order_ids had multiple events)
Target rows scanned (matched)	800,000
Rows updated	750,000 (50K stale, guard skipped)
Rows inserted	150,000 (new orders)
Wall clock on Medium WH	45 seconds
Credits used	~1.5

Rule of thumb. dbt incremental on Snowflake is the canonical warehouse MERGE. Cluster on unique_key, dedup source in the USING subquery, use timestamp guard on WHEN MATCHED.

Worked example — BigQuery MERGE with partition prune predicate

Detailed explanation. BigQuery MERGE cost is dominated by target-scan bytes. Adding a partition-prune predicate to the ON or WHERE clause is the single biggest optimisation.

Question. Write a BigQuery MERGE that upserts July 2026 orders from stage_orders into dim_orders (partitioned by DATE(created_at), clustered by order_id), with a partition-prune predicate.

Input.

-- Target
CREATE TABLE `project.analytics.dim_orders` (
  order_id STRING,
  customer_id STRING,
  total NUMERIC,
  status STRING,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
)
PARTITION BY DATE(created_at)
CLUSTER BY order_id;

-- Source
CREATE TABLE `project.staging.stage_orders` AS (
  SELECT * FROM raw.orders WHERE _batch_id = 'BATCH-2026-07-12'
);

Code.

MERGE INTO `project.analytics.dim_orders` AS t
USING (
  SELECT DISTINCT
    order_id, customer_id, total, status, created_at, updated_at
  FROM `project.staging.stage_orders`
) AS s
ON t.order_id = s.order_id
   AND DATE(t.created_at) BETWEEN DATE '2026-07-01' AND DATE '2026-07-31'
   AND DATE(s.created_at) BETWEEN DATE '2026-07-01' AND DATE '2026-07-31'
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
  customer_id = s.customer_id,
  total = s.total,
  status = s.status,
  updated_at = s.updated_at
WHEN NOT MATCHED BY TARGET THEN INSERT
  (order_id, customer_id, total, status, created_at, updated_at)
  VALUES (s.order_id, s.customer_id, s.total, s.status, s.created_at, s.updated_at);

Step-by-step explanation.

DATE(t.created_at) BETWEEN ... AND ... in the ON — the partition-prune predicate. BigQuery uses this to skip all partitions outside July 2026.
Source subquery — SELECT DISTINCT to dedup. In production, prefer ROW_NUMBER() with a timestamp guard.
WHEN MATCHED — timestamp guard.
WHEN NOT MATCHED BY TARGET — INSERT the new order.
--dry_run — verify bytes-processed. Should be roughly 31 partitions × avg_partition_size + source_size. Full-table scan without the prune would be all_partitions × avg_partition_size.

Output.

Query	Bytes processed	Cost (on-demand @ $5/TB)
Without partition prune	12 TB	$60 per MERGE
With partition prune	400 GB	$2 per MERGE
Speedup	30×	30× cost saving

Rule of thumb. Every BigQuery MERGE against a partitioned target needs a partition-prune predicate. Verify with --dry_run before running.

Worked example — Databricks Delta MERGE with tombstone deletes

Detailed explanation. CDC events often carry a soft-delete flag or op='d' marker. The Delta MERGE handles inserts / updates / deletes in one statement using conditional branches.

Question. Write a Databricks MERGE that consumes a Kafka batch of CDC events (with op in {'i', 'u', 'd'}) and applies to a Delta target table.

Input. cdc_batch table with columns id, name, op, updated_at.

Code.

MERGE INTO analytics.users_delta AS t
USING (
  SELECT id, name, op, updated_at
  FROM (
    SELECT
      id, name, op, updated_at,
      ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
    FROM cdc_batch
  )
  WHERE rn = 1
) AS s
ON t.id = s.id
WHEN MATCHED AND s.op = 'd' THEN DELETE
WHEN MATCHED AND s.op IN ('i', 'u') AND s.updated_at > t.updated_at THEN UPDATE SET
  name = s.name,
  updated_at = s.updated_at
WHEN NOT MATCHED AND s.op IN ('i', 'u') THEN INSERT
  (id, name, updated_at) VALUES (s.id, s.name, s.updated_at);

Step-by-step explanation.

Source dedup — keep the latest per id (by updated_at).
WHEN MATCHED AND s.op = 'd' THEN DELETE — tombstones become target deletes.
WHEN MATCHED AND s.op IN ('i', 'u') AND fresher THEN UPDATE — updates with timestamp guard.
WHEN NOT MATCHED AND s.op IN ('i', 'u') THEN INSERT — inserts new rows.
If op = 'd' and the row doesn't exist in target — no-op (WHEN NOT MATCHED with op = 'd' is not written; the branch is silently skipped). Correct behaviour: delete of a nonexistent row is a no-op.

Output.

CDC event	Target action
`(42, 'alice', 'i', ...)` new	INSERT
`(42, 'alice-2', 'u', ...)` existing, fresher	UPDATE
`(42, '', 'd', ...)` existing	DELETE
`(99, '', 'd', ...)` not in target	no-op

Rule of thumb. Multi-branch conditional MERGE is the modern CDC apply pattern. Learn it once; ship it everywhere.

`bigquery merge` interview question on cost-optimised MERGE

A senior interviewer asks: "You're on-call for a daily dbt incremental that MERGEs 5M orders into a 2B-row BigQuery table, and it's costing $500/day. Walk me through the diagnosis and the top three optimisations."

Solution Using dry-run + partition prune + cluster on merge key

-- Step 1 — Confirm cost via --dry_run
bq query --dry_run --use_legacy_sql=false '
MERGE INTO `project.analytics.dim_orders` AS t
USING (SELECT * FROM staging.stg_orders WHERE _batch_id = "current") AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED BY TARGET THEN INSERT ...';
-- Output: This query will process 100 TB when run.

-- Step 2 — Repartition + cluster the target (one-time cost)
CREATE OR REPLACE TABLE `project.analytics.dim_orders`
PARTITION BY DATE(created_at)
CLUSTER BY order_id
AS SELECT * FROM `project.analytics.dim_orders`;

-- Step 3 — Add partition prune predicate to future MERGEs
MERGE INTO `project.analytics.dim_orders` AS t
USING (
  SELECT DISTINCT * FROM staging.stg_orders WHERE _batch_id = "current"
) AS s
ON t.order_id = s.order_id
  AND DATE(t.created_at) >= CURRENT_DATE() - 7
  AND DATE(s.created_at) >= CURRENT_DATE() - 7
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET ...
WHEN NOT MATCHED BY TARGET THEN INSERT ...;

Step-by-step trace.

Fix	Bytes processed	Cost	Ratio
Baseline (unpartitioned)	100 TB	$500	1×
Partition target	3 TB	$15	33× cheaper
+ Partition prune predicate	300 GB	$1.50	333× cheaper
+ Cluster on order_id	100 GB	$0.50	1000× cheaper
+ Materialised view for hot filter	~30 GB	$0.15	3333× cheaper

Output:

Change	Effect
Repartition target	Daily partitions align with source date range
Cluster on order_id	Blocks pruned inside partitions on the merge key
Partition prune predicate	Explicit filter in ON scoping affected partitions
Source dedup	Prevents multi-match cardinality issues
Timestamp guard	Idempotent replay

Why this works — concept by concept:

Partition target on DATE(created_at) — daily partitions. Source batches typically cover a small date window; the MERGE prunes to just those partitions. 300× cost reduction on typical dbt loads.
Cluster on order_id — the MERGE key. BigQuery uses block-level metadata to skip blocks that can't contain matching order_ids. 3-10× on top of partition prune.
Partition prune predicate in ON — explicit hint to the planner: "we only care about the last 7 days of data". Prevents accidental full-table scans when source has stale data.
--dry_run before every deploy — catches accidental full-table MERGEs in CI. Fail the PR if estimated bytes > 10 TB.
Materialised view (final step) — for dashboards that repeatedly query the MERGE output, an MV avoids re-scanning the target on read. Combined with BI Engine, dashboards are sub-second.
Cost — one-time re-partition + cluster cost is ~$500 for a 2 TB target rewrite. Ongoing per-MERGE cost drops from $500/day to <$1/day. Payback in one day.

SQL
Topic — optimization
SQL optimization drills

Practice →

SQL Topic — SQL SQL practice library

Practice →

5. Anti-patterns and dialect matrix

The `sql merge upsert` failure modes — SQL Server's cardinality bug, concurrent MERGE races, non-deterministic sources — and the 8-engine dialect matrix that ties it all together

The mental model in one line: MERGE and UPSERT are the correct primitives for idempotent writes, but they ship with a well-known set of failure modes — non-deterministic source rows, concurrent-writer lost updates, the SQL Server MERGE cardinality bug, and trigger fire-order weirdness — that every senior engineer knows to defend against with dedup, unique indexes, and (when necessary) explicit serialization. Master the failure modes and you can write MERGEs that survive concurrency, retries, and data-drift.

Slot 1 — the SQL Server MERGE cardinality bug.

The bug. When a MERGE has multiple source rows matching the same target row, SQL Server sometimes silently produces incorrect results — either running the UPDATE branch multiple times per target row (non-deterministic final value) or attempting both an UPDATE and an INSERT for the same target row (constraint violation).
Reproduction. Simple MERGE with a non-unique ON condition or a source with duplicate keys. Search for "SQL Server MERGE bug" — Microsoft's own docs acknowledge these issues.
Documented issues (Microsoft KB). Multiple Cannot insert duplicate key errors, race conditions with concurrent inserts, trigger fire-order surprises.
Workaround 1 — dedup source before MERGE. Use a CTE with ROW_NUMBER() to guarantee unique source keys.
Workaround 2 — use INSERT + UPDATE separately. Ship an INSERT ... WHERE NOT EXISTS + UPDATE ... FROM in a transaction. Verbose but bug-free.
Workaround 3 — use SERIALIZABLE isolation. Prevents the race but hurts throughput.
Community recommendation. Many senior SQL Server DBAs recommend avoiding MERGE for critical paths and using explicit INSERT / UPDATE via CTE instead. Aaron Bertrand's "Please stop using MERGE" is the canonical write-up.

Slot 2 — trigger fire order.

BEFORE INSERT vs BEFORE UPDATE per branch. MERGE fires the appropriate BEFORE trigger for each row's branch (INSERT branch → BEFORE INSERT; UPDATE branch → BEFORE UPDATE). Simple in principle.
AFTER triggers fire per statement, not per branch. After the MERGE completes, statement-level AFTER triggers fire once, seeing the union of INSERTs / UPDATEs / DELETEs.
SQL Server historical bugs. In some SQL Server versions, AFTER INSERT and AFTER UPDATE triggers on the same table both fire twice due to MERGE's compilation. Patched in later versions but worth verifying in your environment.
Postgres — clean. Row-level BEFORE triggers fire per row per branch. Statement-level AFTER triggers fire once for the entire MERGE.
Interview signal. Knowing that trigger behaviour on MERGE differs subtly across engines is a senior-level detail.

Slot 3 — concurrent MERGE races.

The setup. Two workers both start a MERGE on the same target key. Under READ COMMITTED, both see the pre-write target state. Both attempt to INSERT the same key.
What happens on Postgres. The unique index catches the second INSERT and raises a unique-violation error. If the second MERGE was inside an atomic UPSERT (INSERT ... ON CONFLICT), the error is caught internally and routed to UPDATE.
What happens on Snowflake. Snowflake serialises MERGE writes on the same table via a Cluster-Table Lock. The second MERGE blocks until the first commits, then runs on the fresh state. No race.
What happens on BigQuery. BigQuery queues concurrent DML. Second MERGE runs after first commits.
What happens on Delta. Optimistic concurrency — both start, first commits, second detects the conflict at commit and retries with the fresh snapshot.
The safe pattern. Use atomic UPSERT on OLTP (INSERT ... ON CONFLICT). Use MERGE with unique ON condition on warehouses. Rely on engine-level serialisation for the concurrent-write safety.

Slot 4 — non-deterministic source rows.

The problem. Source contains multiple rows with the same MERGE key. WHEN MATCHED fires once per source row per matched target row — the target's final state depends on the order in which source rows are processed. Order is not deterministic on parallel plans.
The signal. UPDATE runs multiple times on the same target row; final target value depends on which source row was processed last. Different runs of the same MERGE produce different final states.
Fix — dedup source before MERGE. ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) in the USING subquery.
Fix — aggregate source before MERGE. SELECT id, MAX(updated_at) AS updated_at, ARRAY_AGG(name) FROM source GROUP BY id. Preserves determinism.
SQL Server specific. With multiple source matches, SQL Server may attempt both UPDATE and INSERT on the same target — the notorious cardinality error.
Snowflake / BigQuery. Both accept multiple matches but final target state is undefined. Effectively equivalent to dedup being your responsibility.

Slot 5 — the safe MERGE checklist.

Unique source keys. Dedup source before MERGE using ROW_NUMBER() or GROUP BY.
Unique target keys. Ensure the ON condition uses a primary key or unique index. Composite is fine.
Timestamp guard. WHEN MATCHED AND s.updated_at > t.updated_at for CDC / event workloads.
IS DISTINCT FROM for null-safe compare. When conditional-updating on column difference.
Idempotency test. Run the MERGE twice on the same batch — output should be identical after both runs.
Cardinality assertion. For SQL Server, wrap MERGE in a check that raises if source cardinality doesn't match unique keys.
Cost dry-run. For BigQuery, run --dry_run first.
Cluster target on ON key. For warehouse MERGE, cluster the target for pruning.
Rollback plan. For Time-Travel-supported engines (Snowflake, Delta), you can rollback the target if a MERGE ships bad data. Know the retention.

Slot 6 — the 8-engine dialect matrix (one-line spellings).

Engine	Native UPSERT syntax	MERGE support
Postgres 15+	`INSERT ... ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col`	Full ANSI MERGE (PG 15+); BY SOURCE in PG 17+
MySQL 8	`INSERT ... AS new ON DUPLICATE KEY UPDATE col = new.col`	No native MERGE
SQLite 3.24+	`INSERT ... ON CONFLICT (col) DO UPDATE SET col = excluded.col`	No native MERGE
SQL Server 2008+	`MERGE INTO ... USING ... WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED THEN INSERT`	Full ANSI MERGE (with known bugs); prefer INSERT + UPDATE via CTE for critical paths
Oracle 9i+	`MERGE INTO ... USING ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT`	Full ANSI MERGE (no BY SOURCE)
Snowflake	`MERGE INTO ... USING ... WHEN MATCHED [AND cond] THEN UPDATE / WHEN NOT MATCHED THEN INSERT`	Full ANSI MERGE with BY SOURCE
BigQuery	`MERGE INTO ... USING ... WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED BY TARGET THEN INSERT / WHEN NOT MATCHED BY SOURCE THEN DELETE`	Full ANSI MERGE
Databricks (Delta)	`MERGE INTO delta_table USING ... WHEN MATCHED [AND cond] THEN UPDATE / DELETE / WHEN NOT MATCHED THEN INSERT / WHEN NOT MATCHED BY SOURCE THEN DELETE`	Full ANSI MERGE on Delta tables only

Slot 7 — the 5-step senior MERGE reading strategy.

Step 1 — inspect the ON condition. Is it unique on both sides? If not, dedup source.
Step 2 — inspect WHEN MATCHED guard. Is there a timestamp / condition guard? If not, replays are non-idempotent.
Step 3 — inspect source dedup. Does the USING subquery have ROW_NUMBER() or GROUP BY? If not, multiple-match risk.
Step 4 — inspect target index / cluster. Is the target clustered on the ON key? If not, MERGE reads more than needed.
Step 5 — inspect concurrency. Is this the only writer? If not, understand engine-level serialisation guarantees.

Slot 8 — when NOT to use MERGE.

High-QPS single-row API calls. Use OLTP UPSERT (INSERT ... ON CONFLICT). MERGE is batch-oriented; per-call cost is higher.
SQL Server critical paths. Use INSERT + UPDATE via CTE. Avoid the known bugs.
Append-only tables. Use INSERT ... ON CONFLICT DO NOTHING. MERGE overhead is unnecessary.
Full-refresh dimension loads. TRUNCATE + INSERT can be simpler than three-branch MERGE with WHEN NOT MATCHED BY SOURCE.
Cross-database MERGE. Not universally supported. Materialise the source into a local staging table first.

Common beginner mistakes

Using MERGE on SQL Server for critical paths — subject to known bugs.
Non-unique ON condition — non-deterministic UPDATE.
Assuming concurrent MERGEs are magically safe — engine-level serialisation varies.
Forgetting that MERGE is batch — using it for API single-row upserts.
Not deduping source — the #1 cause of MERGE bugs across all engines.
Ignoring trigger fire order — MERGE fires per-branch, not per-statement.

Worked example — SQL Server MERGE cardinality bug and the CTE workaround

Detailed explanation. Reproduce the SQL Server MERGE cardinality bug, then show the safe CTE-based rewrite.

Question. Show a SQL Server MERGE that triggers the cardinality bug and the safe rewrite using INSERT + UPDATE via CTE.

Input. Source has two rows with the same PK; target has one matching row.

Code — buggy MERGE.

-- Buggy: source has two rows with same PK
MERGE INTO dbo.users AS t
USING (VALUES (42, 'alice'), (42, 'alice-dup')) AS s(id, name)
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
-- Msg 8672: The MERGE statement attempted to UPDATE or DELETE the same row more than once.

Code — safe rewrite.

-- Safe: dedup source, then explicit INSERT + UPDATE via CTE
WITH deduped AS (
  SELECT id, name,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY name DESC) AS rn
  FROM (VALUES (42, 'alice'), (42, 'alice-dup')) AS raw(id, name)
),
source AS (
  SELECT id, name FROM deduped WHERE rn = 1
)
UPDATE t SET name = s.name
FROM dbo.users t
JOIN source s ON s.id = t.id;

INSERT INTO dbo.users (id, name)
SELECT id, name FROM source
WHERE id NOT IN (SELECT id FROM dbo.users);

Step-by-step explanation.

Buggy MERGE — source has two rows for id=42. Target has one. WHEN MATCHED fires twice per source row against the same target row. SQL Server raises 8672 error.
Safe rewrite — first dedup source via ROW_NUMBER() in a CTE. Then run explicit UPDATE FROM CTE and INSERT WHERE NOT EXISTS in a transaction.
Same conceptual output; no MERGE, no bug.
Aaron Bertrand's blog "Please stop using MERGE" documents this pattern extensively.
On other engines (Snowflake, BigQuery, Databricks), the same deduped MERGE works fine — the bug is specific to SQL Server.

Output.

Approach	Result
Buggy MERGE (dup source)	Msg 8672 error
Deduped MERGE	Works fine on non-SQL-Server
CTE + separate UPDATE + INSERT	Works on all engines including SQL Server

Rule of thumb. On SQL Server, always dedup source before MERGE, and consider CTE + explicit INSERT + UPDATE for critical paths.

Worked example — race-safe concurrent UPSERT under 10K QPS

Detailed explanation. A high-QPS API upsert on Postgres. Multiple concurrent workers all hit the same key; the correct pattern is atomic INSERT ... ON CONFLICT.

Question. Show the race-safe concurrent UPSERT pattern for a page_views counter with 10 K QPS on a single key.

Input.

CREATE TABLE page_views (page_id INT PRIMARY KEY, n BIGINT NOT NULL);

Code.

INSERT INTO page_views (page_id, n) VALUES (%s, 1)
ON CONFLICT (page_id) DO UPDATE SET n = page_views.n + 1
RETURNING n;

Step-by-step explanation.

Worker A calls with page_id = 42. Row doesn't exist. INSERT. Row (42, 1). Return 1.
Worker B calls concurrently with page_id = 42. Row now exists (or is being inserted). Under exclusive row lock, worker B waits for A's commit, then sees (42, 1). UPDATE fires: n = 1 + 1 = 2. Return 2.
Under 10K QPS on same key — Postgres serialises writers on the page_id = 42 row. Throughput is bounded by row-lock hold time (~microseconds). Cluster-wide throughput ~50K QPS on modern hardware for hot keys; scales linearly for distributed keys.
No lost increments. Every call increments by exactly 1.
Comparison — MERGE for this pattern would be much slower (higher per-call overhead), and it's harder to guarantee atomicity across concurrent MERGEs. Native ON CONFLICT is the right tool.

Output.

Concurrent workers	Final n after 10,000 calls
1	10,000
10	10,000
100	10,000
1,000	10,000
10,000	10,000

Rule of thumb. For high-QPS atomic increments, always use native INSERT ... ON CONFLICT DO UPDATE on Postgres/SQLite (or ON DUPLICATE KEY UPDATE on MySQL). Never MERGE — the per-call overhead is too high for API calls.

Worked example — the SCD Type 2 pattern with MERGE

Detailed explanation. SCD Type 2 preserves history — every change to a dimension row creates a new version, with valid_from / valid_to timestamps. MERGE handles it in two passes: close the current version, insert the new version.

Question. Write the SCD Type 2 MERGE on Snowflake for a dim_customer table with history preservation.

Input.

CREATE TABLE analytics.dim_customer (
  customer_id INT NOT NULL,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  valid_from TIMESTAMPTZ NOT NULL,
  valid_to TIMESTAMPTZ NOT NULL DEFAULT '9999-12-31',
  is_current BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE UNIQUE INDEX idx_current ON dim_customer(customer_id) WHERE is_current;

Code.

-- Step 1: Close current versions of rows that will get a new version
UPDATE analytics.dim_customer t
SET valid_to = CURRENT_TIMESTAMP(), is_current = FALSE
WHERE is_current
  AND EXISTS (
    SELECT 1 FROM stage.stg_customer s
    WHERE s.customer_id = t.customer_id
      AND (s.name <> t.name OR s.email <> t.email)
  );

-- Step 2: Insert new versions (including brand-new customers)
INSERT INTO analytics.dim_customer (customer_id, name, email, valid_from, valid_to, is_current)
SELECT s.customer_id, s.name, s.email, CURRENT_TIMESTAMP(), '9999-12-31', TRUE
FROM stage.stg_customer s
LEFT JOIN analytics.dim_customer t
  ON t.customer_id = s.customer_id AND t.is_current
WHERE t.customer_id IS NULL
   OR t.name <> s.name
   OR t.email <> s.email;

Step-by-step explanation.

Step 1 closes existing "current" rows that have a changed source row. Sets valid_to = NOW() and is_current = FALSE. History preserved.
Step 2 inserts new rows for (a) brand-new customers not in target, and (b) existing customers whose columns changed (they now have a closed old row and need a fresh current row).
Unique index on (customer_id) WHERE is_current ensures at most one current row per customer at any time.
Some engines allow this in a single MERGE with WHEN MATCHED THEN UPDATE ... AND then INSERT. Snowflake accepts multi-branch MERGE for this pattern.
dbt's snapshot macro implements this exact pattern with additional book-keeping.

Output.

customer_id	name	email	valid_from	valid_to	is_current
42	alice-v1	a@old.com	2026-01-01	2026-07-12 09:00	FALSE
42	alice-v2	a@new.com	2026-07-12 09:00	9999-12-31	TRUE
43	bob	b@x.com	2026-07-12 09:00	9999-12-31	TRUE

Rule of thumb. SCD Type 2 needs history preservation — never overwrite the old row. Use two-step MERGE (close-then-insert) or dbt's snapshot macro.

`mysql on duplicate key update` interview question on choosing among UPSERT, MERGE, and REPLACE

A senior interviewer asks: "You're consulting on a MySQL application that ships REPLACE INTO for its user-profile save endpoint at 5K QPS. Why is this a bad choice, and what would you change?"

Solution Using ON DUPLICATE KEY UPDATE with row alias + RETURNING via ROW_COUNT()

-- BEFORE (bad): REPLACE INTO
-- Semantics: DELETE if exists, then INSERT — breaks FK, fires DELETE triggers,
-- re-numbers AUTO_INCREMENT.
REPLACE INTO users (id, name, email, updated_at)
VALUES (?, ?, ?, ?);

-- AFTER (good): INSERT ... ON DUPLICATE KEY UPDATE with row alias
INSERT INTO users (id, name, email, updated_at) VALUES (?, ?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
  name = new.name,
  email = new.email,
  updated_at = new.updated_at;

-- Bonus: distinguish INSERT vs UPDATE via ROW_COUNT()
-- 1 → insert, 2 → update, 0 → no-op (row unchanged if you added a WHERE-like guard)
SELECT ROW_COUNT();

Step-by-step trace.

Step	REPLACE (bad)	ON DUPLICATE KEY UPDATE (good)
1	Client sends PUT /users/42	Same
2	DELETE FROM users WHERE id=42 → FK cascades kick in	INSERT attempts → PK conflict on id=42
3	INSERT users (42, ...) → AUTO_INCREMENT bumps	ON DUPLICATE branch → UPDATE cols
4	Delete triggers fire (audit-log spam)	Update triggers fire (correct)
5	If FK child rows referenced user 42 → violation OR cascade	No FK impact
6	Latency: 2 SQL statements internally	Latency: 1 statement
7	Idempotent? YES but destructive to FK graph	Idempotent AND safe

Output:

Metric	REPLACE	ON DUPLICATE KEY UPDATE
FK integrity	Breaks (cascade may delete child rows)	Preserved
DELETE triggers fired	YES (audit spam)	NO
UPDATE triggers fired	NO (only DELETE + INSERT)	YES (correct semantic)
AUTO_INCREMENT bumps	YES (gap in id sequence)	NO
Wall clock per call	~2 ms	~1 ms
Correctness under FK	Breaks	Preserved

Why this works — concept by concept:

INSERT ... ON DUPLICATE KEY UPDATE preserves FK graph — the target row's PK doesn't change; foreign key references from child tables remain valid. REPLACE would DELETE the row (potentially cascading), then INSERT a fresh row with the same id but different internal identity.
Correct trigger semantics — UPDATE triggers fire on the UPDATE branch; INSERT triggers fire on the INSERT branch. REPLACE fires DELETE triggers on every "update", which is wrong.
No AUTO_INCREMENT bump — the target row keeps its id; no gap in the sequence. REPLACE bumps AUTO_INCREMENT because internally it's a fresh INSERT.
Row alias AS new — MySQL 8.0.19+ cleaner spelling. Preferred over the legacy VALUES() function.
ROW_COUNT() for distinguishing INSERT vs UPDATE — returns 1 for insert, 2 for update. Useful in application code that needs to know which branch fired.
Cost — one statement per call, ~1 ms wall clock. At 5K QPS, total DB CPU is bounded and predictable.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — joins/sql SQL join drills

Practice →

Cheat sheet — MERGE / UPSERT recipe list

Two primitives, one contract. MERGE (ANSI, three-branch) for batch idempotent writes; native UPSERT (INSERT ... ON CONFLICT / ON DUPLICATE KEY UPDATE) for OLTP single-row atomic upserts.
Never SELECT-then-INSERT. Two-statement pattern has a race window. Always single-statement atomic UPSERT.
Never REPLACE INTO on MySQL. DELETE-then-INSERT wart. Use ON DUPLICATE KEY UPDATE instead.
Postgres UPSERT skeleton. INSERT INTO t (id, v) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v.
Postgres UPSERT with guard. ... WHERE t.updated_at < EXCLUDED.updated_at — LWW semantics.
Postgres counter increment. INSERT INTO c (id, n) VALUES (?, 1) ON CONFLICT (id) DO UPDATE SET n = c.n + 1 — atomic increment.
Postgres INSERT-or-skip. INSERT ... ON CONFLICT (id) DO NOTHING — no update on conflict.
Postgres RETURNING. Add RETURNING (xmax = 0) AS was_insert, * to distinguish INSERT vs UPDATE and return the persisted row.
MySQL UPSERT skeleton (8.0.19+). INSERT INTO t (id, v) VALUES (?, ?) AS new ON DUPLICATE KEY UPDATE v = new.v.
MySQL UPSERT skeleton (pre-8.0.19). INSERT INTO t (id, v) VALUES (?, ?) ON DUPLICATE KEY UPDATE v = VALUES(v).
MySQL branch detection. ROW_COUNT() returns 1 for INSERT, 2 for UPDATE.
SQLite UPSERT skeleton. INSERT INTO t (id, v) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET v = excluded.v — same as Postgres.
ANSI MERGE skeleton. MERGE INTO t USING s ON t.pk = s.pk WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ....
ANSI MERGE three-branch (full sync). Add WHEN NOT MATCHED BY SOURCE THEN DELETE.
Timestamp guard. WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE — protects against stale-event overwrites.
Batch dedup. Wrap source in ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) = 1 — one row per key. Every warehouse MERGE needs this.
SQL Server MERGE bugs. Cardinality error on multiple source matches, plan-cache surprises. Prefer CTE + INSERT + UPDATE for critical paths.
SQL Server safe rewrite. WITH deduped AS (...) UPDATE FROM deduped; INSERT FROM deduped WHERE NOT EXISTS.
Oracle MERGE. Full ANSI two-branch. No WHEN NOT MATCHED BY SOURCE (must separate).
Snowflake MERGE. Full ANSI three-branch. Cluster target on merge key for pruning.
Snowflake MERGE cost. Warehouse tier × wall clock. Rewrites affected micro-partitions.
BigQuery MERGE. Full ANSI three-branch. Partition + cluster target. Use --dry_run first.
BigQuery MERGE cost. On-demand: bytes billed × $5/TB. Flat-rate: slot_ms. Partition prune predicate in ON is essential.
Delta MERGE. Full ANSI three-branch on Delta tables (Parquet won't accept). Photon accelerates. Schedule OPTIMIZE ZORDER BY periodically.
Optimistic concurrency (Delta). Two concurrent MERGEs — first commits, second retries.
SCD Type 2 pattern. Two-step: close current rows, insert new versions. Preserves history.
CDC apply-loop pattern. Dedup source, WHEN MATCHED AND fresher, WHEN NOT MATCHED INSERT, WHEN MATCHED AND is_deleted DELETE.
API upsert pattern. PUT /users/:id handler → single INSERT ... ON CONFLICT DO UPDATE — no race window.
Bulk load pattern. COPY into staging → MERGE from staging → drop staging.
When OFF the beaten path. For very high QPS or cross-database, consider queue-based writes (Kafka → downstream apply job) instead of direct UPSERT / MERGE.
Return values across engines. Postgres/SQLite: RETURNING. MySQL: SELECT LAST_INSERT_ID() / ROW_COUNT(). Warehouses: query result summary (rows inserted / updated / deleted).
Idempotency test. Run MERGE twice on same batch. Same final state = correct. Different state = bug.
Rollback plan. Snowflake Time Travel, Delta version history. Retain enough history to rollback a bad MERGE.

Frequently asked questions

What's the difference between MERGE and UPSERT?

MERGE is the ANSI SQL:2003 statement with up to three branches — WHEN MATCHED THEN UPDATE / DELETE, WHEN NOT MATCHED [BY TARGET] THEN INSERT, WHEN NOT MATCHED BY SOURCE THEN UPDATE / DELETE — designed for batch idempotent writes. It scans the source once and joins against the target via the ON condition, routing each row to exactly one branch. UPSERT is the informal term for "insert if new, update if existing"; on Postgres and SQLite it's spelled INSERT ... ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col, on MySQL it's INSERT ... ON DUPLICATE KEY UPDATE col = new.col, and on warehouses it's a two-branch MERGE. UPSERT is the OLTP-native primitive optimised for single-row, high-QPS atomic writes with conflict detection inside the write path (via unique index). MERGE is the warehouse-native primitive optimised for batch idempotent loads that can affect thousands or millions of rows in a single statement. Both provide the same idempotency contract; MERGE is the superset (adds full-sync DELETE), UPSERT is the two-branch case (INSERT + UPDATE only). Rule of thumb — high-QPS API endpoints use UPSERT; batch pipelines (dbt incremental, CDC apply-loops) use MERGE.

When should I use INSERT ... ON CONFLICT vs MERGE in Postgres?

Postgres 15+ ships both, so the question is real. Use INSERT ... ON CONFLICT (col) DO UPDATE SET for single-row API upserts, high-QPS backend writes, atomic counter increments, and any workload that fits the "insert one row, resolve via unique index" pattern. It's atomic, low-latency (~1 ms), race-free, and the community-blessed idiom. Use MERGE INTO for batch loads — dbt incrementals, CDC apply-loops, bulk staging-to-analytics moves, and full-sync patterns that need WHEN NOT MATCHED BY SOURCE (delete-if-not-in-source semantics). MERGE is more expressive (three branches with conditions), but has higher per-call overhead — you don't want it for single-row API calls at 10K QPS. In practice, most Postgres codebases stick with INSERT ... ON CONFLICT for everything except full-sync scenarios, because it was the only idiomatic option pre-Postgres 15 and it's already the shared reflex across the Postgres community. New Postgres 15+ codebases can start using MERGE for batch, but ON CONFLICT remains the correct choice for OLTP.

Why does SQL Server's MERGE have known bugs?

SQL Server's MERGE has a documented history of correctness issues — cardinality errors when multiple source rows match the same target row (raising Msg 8672: The MERGE statement attempted to UPDATE or DELETE the same row more than once), trigger fire-order surprises (double-firing on both INSERT and UPDATE triggers in some patched-and-repatched versions), plan-cache instability with parameter sniffing, race conditions with concurrent inserts even under SERIALIZABLE isolation, and semantic surprises with WHEN NOT MATCHED BY SOURCE on partitioned tables. Aaron Bertrand's widely-referenced "Please stop using MERGE" post (updated across the years) catalogs the specific issues and Microsoft's Connect / Feedback item history acknowledging them. The safe pattern on SQL Server for critical paths is to dedup source in a CTE and then run explicit UPDATE FROM CTE and INSERT ... WHERE NOT EXISTS inside a transaction — verbose, but bug-free and equivalent in semantics. On other engines (Postgres, Snowflake, BigQuery, Databricks), MERGE works as specified and is the correct choice. If you're on SQL Server and you must use MERGE, always: (1) dedup the source before merging, (2) test with concurrency, (3) verify trigger fire-order in your environment, (4) prefer INSERT + UPDATE via CTE for anything mission-critical.

How do I write an idempotent MERGE in Snowflake vs BigQuery?

Snowflake — the canonical dbt-incremental pattern. Source-scan-once, cluster target on the merge key, dedup source in the USING subquery, timestamp guard on WHEN MATCHED, and let Snowflake serialise concurrent writers automatically:

MERGE INTO analytics.dim_orders AS t
USING (
  SELECT id, name, updated_at
  FROM (
    SELECT id, name, updated_at,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
    FROM stage.orders_batch
  ) WHERE rn = 1
) AS s
ON t.id = s.id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET name = s.name, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (id, name, updated_at) VALUES (s.id, s.name, s.updated_at);

BigQuery — same shape but add a partition-prune predicate in the ON clause so the target scan skips partitions outside the source's date range. Without this, BigQuery scans the full target and costs 30–100× more:

MERGE INTO `project.analytics.dim_orders` AS t
USING (SELECT DISTINCT ... FROM stage.orders_batch) AS s
ON t.id = s.id
   AND DATE(t.created_at) BETWEEN DATE '2026-07-01' AND DATE '2026-07-31'
   AND DATE(s.created_at) BETWEEN DATE '2026-07-01' AND DATE '2026-07-31'
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET ...
WHEN NOT MATCHED BY TARGET THEN INSERT ...;

Verify BigQuery costs with bq query --dry_run before running — if estimated bytes exceed expectations, add or refine the partition prune predicate. In both engines, idempotency is guaranteed by the timestamp guard + source dedup — replaying the same batch is a no-op after the first run.

What's the race condition in a naive UPSERT?

The classic race: two workers concurrently receive the same request (PUT /users/42). Both open a transaction. Both run SELECT id FROM users WHERE id = 42 and get "no row." Both decide to INSERT. Under READ COMMITTED isolation, both attempt the INSERT — one wins (commits first), the other hits a UNIQUE-constraint violation on the primary key and raises an error to the client. Symptoms — sporadic 500 errors under load, audit-log noise (Duplicate key violation in every log rotation), retry storms, and (in some engines) the loser's subsequent RETRY may silently overwrite the winner's data if the retry logic is naive. The fix — use atomic UPSERT via a single statement: INSERT INTO users (id, name) VALUES (42, 'alice') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name. Postgres detects the unique-index conflict inside the write path (before the constraint check completes) and atomically routes execution to the UPDATE branch. No two-statement race, no error, no retry. The same applies to MySQL (ON DUPLICATE KEY UPDATE), SQLite (ON CONFLICT DO UPDATE), and warehouse MERGE INTO (though warehouses also serialize concurrent MERGE statements). Rule — every backend UPSERT must be a single statement; never a SELECT followed by an INSERT or UPDATE.

How do I handle SCD Type 2 with MERGE?

SCD Type 2 preserves history — every dimension change creates a new version, with valid_from and valid_to timestamps and an is_current flag. The MERGE pattern is two-step: first close the current version of changed rows, then insert the new versions. Snowflake and BigQuery both express this in two statements (or two MERGE branches):

-- Step 1: close current versions for changed rows
UPDATE dim_customer t
SET valid_to = CURRENT_TIMESTAMP(), is_current = FALSE
WHERE is_current AND EXISTS (
  SELECT 1 FROM stg_customer s
  WHERE s.customer_id = t.customer_id
    AND (s.name <> t.name OR s.email <> t.email)
);

-- Step 2: insert new versions (new customers + changed existing customers)
INSERT INTO dim_customer (customer_id, name, email, valid_from, valid_to, is_current)
SELECT s.customer_id, s.name, s.email, CURRENT_TIMESTAMP(), '9999-12-31', TRUE
FROM stg_customer s
LEFT JOIN dim_customer t ON t.customer_id = s.customer_id AND t.is_current
WHERE t.customer_id IS NULL
   OR t.name <> s.name
   OR t.email <> s.email;

A unique partial index (customer_id) WHERE is_current enforces "one current version per customer" at the storage layer. dbt's snapshot macro implements this pattern with additional book-keeping (dbt_valid_from, dbt_valid_to, dbt_scd_id). For queries, downstream analytics either filter by WHERE is_current (Type 1 view) or by WHERE :as_of_date BETWEEN valid_from AND valid_to (Type 2 view). The idempotency contract still holds — replaying the same batch produces the same final state because the WHERE EXISTS ... AND s.col <> t.col guard skips unchanged rows.

Practice on PipeCode

Drill the SQL practice library → — 450+ DE-focused questions covering INSERT ... ON CONFLICT, MERGE INTO, and every adjacent pattern.
Sharpen the join reflex on SQL join drills → — MERGE is fundamentally a join with row-level actions per matched / unmatched row.
Layer SQL indexing drills → — unique indexes, composite unique constraints, and partial indexes are what MERGE / UPSERT depend on for atomicity.
Push the difficulty ceiling with SQL optimization drills → — read MERGE execution plans, spot the missing partition prune, and rewrite the MERGE for 100× cost reduction.
Warm up with aggregation drills → — pre-aggregate source before MERGE, dedup with ROW_NUMBER(), use MAX() for LWW semantics.
Layer window function drills → — ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) is the batch-dedup pattern every MERGE ships with.
For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql merge upsert` recipe above ships with hands-on practice rooms where you type `INSERT ... ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col` on a live Postgres, migrate the same UPSERT to MySQL's `ON DUPLICATE KEY UPDATE col = new.col` row-alias spelling, translate it to a Snowflake three-branch `MERGE INTO ... USING ... WHEN MATCHED [AND cond] THEN UPDATE / WHEN NOT MATCHED THEN INSERT`, benchmark it against a BigQuery MERGE with partition-prune predicate and `--dry_run` cost gate, and finally dodge the SQL Server MERGE cardinality bug with the CTE + INSERT + UPDATE safe rewrite — the exact 8-engine `postgres on conflict` / `mysql on duplicate key update` / `snowflake merge` fluency that senior DE interviews probe. PipeCode pairs every UPSERT and MERGE concept with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your idempotent-write answer holds up under a senior interviewer's depth probes.

Practice SQL now →
Optimization drills →

1. Why idempotent writes matter in 2026

The sql merge upsert mental model — idempotency as a contract, the two loading anti-patterns, and where MERGE shows up in every real DE pipeline

Worked example — the race condition in the naive SELECT-then-INSERT pattern

Worked example — idempotent replay of a Kafka batch

Worked example — a dbt incremental model that compiles to a MERGE

sql merge upsert interview question on choosing between MERGE and native UPSERT

Solution Using dual-endpoint architecture — INSERT ... ON CONFLICT for OLTP, MERGE INTO for warehouse

2. SQL Standard MERGE anatomy

sql standard merge — the ANSI SQL:2003 grammar, the three row-level branches, and how WHEN NOT MATCHED BY SOURCE extends the two-branch UPSERT into a three-way sync

Worked example — the two-branch MERGE (UPSERT) on Snowflake

Worked example — the three-branch MERGE with DELETE (full sync)

Worked example — conditional UPDATE using WHEN MATCHED AND cond

sql standard merge interview question on writing a three-branch MERGE

Solution Using three-branch MERGE with soft-delete on WHEN NOT MATCHED BY SOURCE

3. Postgres ON CONFLICT + MySQL ON DUPLICATE KEY + SQLite UPSERT

The OLTP-native sql upsert — postgres on conflict, mysql on duplicate key update, and sqlite upsert — three spellings of the same atomic conflict-resolution primitive

Worked example — the atomic counter increment on Postgres

Worked example — LWW UPSERT with timestamp guard on Postgres

Worked example — MySQL UPSERT with the row alias

postgres on conflict interview question on multi-column conflict target

Solution Using composite conflict target + atomic increment via EXCLUDED

4. Snowflake + BigQuery + Databricks MERGE

Warehouse-native snowflake merge, bigquery merge, and Databricks MERGE — the same ANSI grammar, three different storage-rewrite models, three different cost surfaces

Worked example — dbt incremental on Snowflake, from raw batch to MERGE

Worked example — BigQuery MERGE with partition prune predicate

Worked example — Databricks Delta MERGE with tombstone deletes

bigquery merge interview question on cost-optimised MERGE

Solution Using dry-run + partition prune + cluster on merge key

5. Anti-patterns and dialect matrix

The sql merge upsert failure modes — SQL Server's cardinality bug, concurrent MERGE races, non-deterministic sources — and the 8-engine dialect matrix that ties it all together

Worked example — SQL Server MERGE cardinality bug and the CTE workaround

Worked example — race-safe concurrent UPSERT under 10K QPS

Worked example — the SCD Type 2 pattern with MERGE

mysql on duplicate key update interview question on choosing among UPSERT, MERGE, and REPLACE

Solution Using ON DUPLICATE KEY UPDATE with row alias + RETURNING via ROW_COUNT()

Cheat sheet — MERGE / UPSERT recipe list

Frequently asked questions

What's the difference between MERGE and UPSERT?

When should I use INSERT ... ON CONFLICT vs MERGE in Postgres?

Why does SQL Server's MERGE have known bugs?

How do I write an idempotent MERGE in Snowflake vs BigQuery?

What's the race condition in a naive UPSERT?

How do I handle SCD Type 2 with MERGE?

Practice on PipeCode

The `sql merge upsert` mental model — idempotency as a contract, the two loading anti-patterns, and where MERGE shows up in every real DE pipeline

`sql merge upsert` interview question on choosing between MERGE and native UPSERT

Solution Using dual-endpoint architecture — `INSERT ... ON CONFLICT` for OLTP, `MERGE INTO` for warehouse

`sql standard merge` — the ANSI SQL:2003 grammar, the three row-level branches, and how `WHEN NOT MATCHED BY SOURCE` extends the two-branch UPSERT into a three-way sync

Worked example — conditional UPDATE using `WHEN MATCHED AND cond`

`sql standard merge` interview question on writing a three-branch MERGE

The OLTP-native `sql upsert` — `postgres on conflict`, `mysql on duplicate key update`, and `sqlite upsert` — three spellings of the same atomic conflict-resolution primitive

`postgres on conflict` interview question on multi-column conflict target

Warehouse-native `snowflake merge`, `bigquery merge`, and Databricks MERGE — the same ANSI grammar, three different storage-rewrite models, three different cost surfaces

`bigquery merge` interview question on cost-optimised MERGE

The `sql merge upsert` failure modes — SQL Server's cardinality bug, concurrent MERGE races, non-deterministic sources — and the 8-engine dialect matrix that ties it all together

`mysql on duplicate key update` interview question on choosing among UPSERT, MERGE, and REPLACE