CDC Changelog Apply: MERGE-Shaped Upsert with Deletes and Version Ordering

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewshard38 minstreamingCDC change streamROW_NUMBER latest-per-keyON CONFLICT upserttombstone deletelast-write-winsidempotency

Apply an I/U/D change stream to a target: dedup to the latest version per key, upsert inserts and updates, delete tombstones, stay idempotent.

CDC hands you a stream of changes, and you apply them to a table

Change Data Capture (CDC) turns a source table's inserts, updates, and deletes into a stream of change rows. Each row carries a primary key, an operation (I for insert, U for update, D for delete), a version (a sequence number or commit timestamp), and the new payload. Your job is to apply that changelog to a target table so it ends up matching the source, which is the MERGE-shaped upsert every pipeline runs.

The apply, in three moves

Given changelog(pk, op, version, payload):

Dedup to the latest version per key. A key can have several changes in one batch, possibly out of order. Keep only the newest with ROW_NUMBER() OVER (PARTITION BY pk ORDER BY version DESC) = 1. This is not optional: a real MERGE raises a nondeterministic-merge error when two source rows match one target row, so you must collapse to one row per key first.
Upsert the inserts and updates. For the surviving I and U rows, INSERT ... ON CONFLICT(pk) DO UPDATE SET ... writes a new row or overwrites the existing one. Insert and update are the same operation once you hold the latest payload.
Delete the tombstones. For keys whose latest op is D, DELETE them from the target. A delete that lost to a later insert in version order never fires, because step 1 already dropped it.

Last write wins, and idempotency

The version ordering is what makes this last-write-wins: the highest version for a key is the truth, whatever order the changes arrived in. And because every step is keyed on pk with upsert and delete, running the whole apply twice leaves the same table. That run-twice-same-result property is what lets a pipeline retry a failed batch safely.

Interview nuance: the dedup is the answer they are listening for. A candidate who upserts the raw changelog without collapsing to the latest version per key has written a query that works by luck on ordered input and corrupts on duplicates. Say "dedup to the latest version per key first, because MERGE is nondeterministic on duplicate source rows".

In the warehouse this differs. This is one statement in Snowflake, BigQuery, and Delta: MERGE ... WHEN MATCHED AND op = 'D' THEN DELETE WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT. SQLite splits it into ON CONFLICT plus a DELETE, which is exactly what the MERGE compiles to. Snowflake and BigQuery also error on duplicate source keys, so the ROW_NUMBER dedup transfers directly.

Sample data for this example

CREATE TABLE changelog (pk INTEGER, op TEXT, version INTEGER, payload TEXT);
INSERT INTO changelog VALUES
  (1, 'I', 1, 'a1'), (1, 'U', 3, 'a3'), (1, 'U', 2, 'a2'),   -- pk 1: latest is version 3
  (2, 'I', 1, 'b1'), (2, 'D', 2, NULL);                      -- pk 2: latest is a delete

Worked example (SQL)

-- The rn = 1 row per pk is the winner: pk 1 -> U version 3, pk 2 -> D version 2.
SELECT pk, op, version, payload,
       ROW_NUMBER() OVER (PARTITION BY pk ORDER BY version DESC) AS rn
FROM changelog
ORDER BY pk, version DESC;

Apply

Your turn

The task this lesson builds to.

Write a script that applies a changelog to the target so it matches the last-write-wins end state, over changelog(pk, op, version, payload) (op is I, U, or D) and a pre-seeded target(pk, version, payload).

Dedup to the latest version per pk, upsert the surviving I and U rows into target with ON CONFLICT(pk) DO UPDATE, and delete the keys whose final op is D.

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

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a script that applies two changelog batches in sequence to the target and stays correct even when the later batch carries a stale (older-version) update, over an empty target(pk, version, payload) and changelog_batch1 then changelog_batch2 (each pk, op, version, payload).

Apply each batch as an upsert plus a tombstone delete, and guard the update so a change overwrites the target only when its version is newer, using WHERE excluded.version > target.version on the ON CONFLICT update.

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