Merge Semantics: In-Batch Duplicates, Tied Versions, and Redelivery

Level 8: Batch Pipelines & Orchestrationhard26 minmerge on a business keydedup before mergeROW_NUMBER composite tiebreakerat-least-once redeliverytombstone deleteINSERT...ON CONFLICT DO UPDATE

Apply a redelivered change batch to a dimension when two changes for one key carry the same timestamp, and keep the merge idempotent.

You have built this before, and that is deliberate

Implementation 2 is the keyed merge: collapse the change batch to one row per business key, upsert the survivors, delete the tombstones. You already wrote exactly that in sql-l5-cdc-changelog-apply, where a monotonic version column ordered the changes and ROW_NUMBER() OVER (PARTITION BY pk ORDER BY version DESC) = 1 picked the winner.

The mechanic is not new. This lesson exists because the two things that break it in production were both engineered out of that seed, and both of them are what the interview follow-up probes.

Pressure 1: the ordering column ties

Real ordering columns are timestamps, and real timestamps have finite resolution. A source that commits at second resolution can emit two changes for one customer inside the same second. Now this is nondeterministic:

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY changed_at DESC)

Two rows tie for rank 1. The engine may return either. Which means your dimension can hold the newer tier on Monday and the older one on Tuesday, from the same input, with no error anywhere. The fix is a composite tiebreaker: a second, strictly unique column appended to the ordering.

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY changed_at DESC, change_id DESC)

change_id is monotonic within the feed, so the pair (changed_at, change_id) is unique and the winner is decided. Windowed dedup with ROW_NUMBER is the single most-cited SQL task in data interviews, and the tiebreaker is the half that separates a candidate who has shipped one from a candidate who has read about one.

Pressure 2: the batch redelivers what you already applied

At-least-once delivery means the producer will resend. A batch does not politely contain only new rows; it can contain the whole previous batch again, plus today's changes. Your merge has to be correct on a superset of what it already applied.

Keyed upsert survives this for free. Reapplying a change that is already in the dimension writes the same values over themselves, which is a no-op. That is the canonical sentence made concrete: at-least-once delivery plus an idempotent sink equals effectively exactly-once.

Table

The four things a real change batch throws at a merge. Dedup with a composite tiebreaker, then upsert, then delete the tombstones.
what the batch contains	what a blind apply does	what the keyed merge does
a verbatim redelivery of yesterday's rows	rewrites the same values, or duplicates them if the sink is an append	writes identical values over themselves, a no-op
two changes for one key, tied on changed_at	picks either one, differently on different runs	picks the higher change_id, the same one every run
a key created and deleted inside the batch	inserts a row that should never exist	the delete wins the dedup, so the key never lands
a key deleted and then re-created inside the batch	deletes it, because the batch contains a 'd' for that key	the create wins the dedup, so the key survives

The four things a real change batch throws at a merge. Dedup with a composite tiebreaker, then upsert, then delete the tombstones.

The apply, in three moves

Dedup to one winner per key with ROW_NUMBER() OVER (PARTITION BY key ORDER BY changed_at DESC, change_id DESC) = 1.
Upsert the winners whose op is a create or an update with INSERT ... ON CONFLICT(key) DO UPDATE SET ....
Delete the winners whose op is a delete. Note the word winners. "This key has a 'd' somewhere in the batch" is not the same test: a key that was deleted and then re-created has a 'd' in the batch and must survive. A delete that lost the dedup to a later change never fires, and an insert that lost to a later delete never lands.

Common mistake: upserting the raw batch without collapsing it first. It appears to work whenever the input happens to arrive in order, and it corrupts silently the first time a key shows up twice. A real MERGE will not even let you: Snowflake, BigQuery, and Iceberg all raise a nondeterministic-merge error when two source rows match one target row.

Interview nuance: the follow-up that filters candidates is "what breaks if the batch contains the same key twice?" The complete answer names both failures: the duplicate itself, which a real MERGE rejects outright, and the tie inside it, which no engine will warn you about because it is not an error, just a coin flip.

On a real platform this differs. A warehouse writes all three moves as one statement: MERGE INTO dim_customer USING deduped_changes ON ... WHEN MATCHED AND op = 'd' THEN DELETE WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ..., available in Snowflake, BigQuery, Databricks, and Iceberg. SQLite has no MERGE keyword, so the grading here is INSERT ... ON CONFLICT DO UPDATE plus a keyed DELETE, which is precisely what a MERGE compiles down to. The dedup step transfers unchanged, because those engines require it.

Sample data for this example

CREATE TABLE demo_changes (change_id INTEGER, customer_id INTEGER, tier TEXT, changed_at TEXT);
INSERT INTO demo_changes (change_id, customer_id, tier, changed_at) VALUES
  (105, 42, 'gold',     '2026-03-14 08:30:00'),
  (106, 42, 'platinum', '2026-03-14 08:30:00'),
  (108, 66, 'silver',   '2026-03-14 08:45:00');

Worked example (SQL)

-- Customer 42 changed twice in the same second. One ordering is a coin flip, the other is not.
SELECT change_id, customer_id, tier, changed_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY changed_at DESC) AS rank_ambiguous,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY changed_at DESC, change_id DESC) AS rank_decided
FROM demo_changes
ORDER BY customer_id, change_id;

Apply

Your turn

The task this lesson builds to.

Write a script that applies stg_customer_changes to dim_customer so the highest change_id wins each tie, op = 'd' removes the row, redelivered rows change nothing, and rerunning the script changes nothing, over stg_customer_changes(change_id, customer_id, op, name, tier, changed_at) and dim_customer(customer_id PRIMARY KEY, name, tier, updated_at).

Dedup to one winner per customer_id ordered by changed_at DESC, change_id DESC, upsert the 'c' and 'u' winners with ON CONFLICT(customer_id) DO UPDATE setting updated_at to the winner's changed_at, then delete the keys whose winner is a 'd'. Customer 42 has two changes stamped at the same second, rows 101 to 104 are a verbatim redelivery of a batch the dimension already holds, customer 77 is created and then deleted inside this batch, and customer 88 is deleted and then re-created inside it. Customer 99 is in the dimension and nowhere in the batch, so it must come out untouched: a merge owns the keys the batch carries and nothing else. The file arrives in delivery order, so you cannot lean on the row order you see.

3 hints and 5 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 2 bonus drills.

Write a script that merges the product price feed into dim_product keyed on sku, over stg_product_prices(feed_id, sku, op, product_name, price_usd, feed_ts) and dim_product(sku PRIMARY KEY, product_name, price_usd, updated_at).

The feed redelivers yesterday's rows alongside today's, two rows for SKU-100 share a feed_ts, op = 'd' discontinues a product, and SKU-500 is discontinued and then relisted inside the same feed. SKU-700 is in dim_product and nowhere in the feed, so it has to survive exactly as it is. The higher feed_id wins a tie, updated_at takes the winner's feed_ts, and rerunning the script must change nothing.

1 hint and 5 automated checks are waiting in the workspace.