Rank, don't dedupe: duplicate-safe CDC MERGE patterns

Latest means ranked by event time

A CDC change window is a timeline of events, not a list of rows. One key can carry insert → update → delete inside a single window, and feeding that straight into MERGE INTO fails with DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE.

The prescribed fix is to preprocess the change dataset and retain only the latest change for each key before applying it. The key word is latest: latest means ranked by event time, not merely deduped. Dedupe without ordering picks an arbitrary survivor.

Setup

CREATE OR REPLACE TABLE cdc_staging (
  customer_id INT, name STRING, email STRING, op_type STRING, event_time TIMESTAMP, seq INT
);
INSERT INTO cdc_staging VALUES
  (1, 'Ada',    'ada@x.com', 'insert', TIMESTAMP '2026-10-01 10:00:00', 1),
  (1, 'Ada L.', 'ada@x.com', 'update', TIMESTAMP '2026-10-01 10:05:00', 2),
  (2, 'Bob',    'bob@x.com', 'insert', TIMESTAMP '2026-10-01 10:01:00', 3),
  (2, NULL,     NULL,        'delete', TIMESTAMP '2026-10-01 10:06:00', 4),
  (3, 'Cy',     'cy@x.com',  'insert', TIMESTAMP '2026-10-01 10:02:00', 5),
  (4, NULL,     NULL,        'delete', TIMESTAMP '2026-10-01 10:03:00', 6);

CREATE OR REPLACE TABLE customers (customer_id INT, name STRING, email STRING);
INSERT INTO customers VALUES
  (1, 'Ada', 'ada@old.com'),
  (2, 'Bob', 'bob@x.com');

Before

Step 1 — rank to one row per key

The latest change event wins.

CREATE OR REPLACE TEMP VIEW latest_changes AS
SELECT customer_id, name, email, op_type, event_time
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_time DESC, seq DESC) AS rn
  FROM cdc_staging
  WHERE customer_id IS NOT NULL
) ranked
WHERE rn = 1;

The window implements a deterministic tiebreaker after event_time via seq, the source sequence number. If two events share a timestamp, the higher seq wins.

Step 2 — merge with op-type dispatch

MERGE INTO customers AS t
USING latest_changes AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op_type = 'delete' THEN DELETE
WHEN MATCHED THEN UPDATE SET t.name = s.name, t.email = s.email
WHEN NOT MATCHED AND s.op_type IN ('insert', 'update')
THEN INSERT (customer_id, name, email) VALUES (s.customer_id, s.name, s.email);

Clause-ordering note: with multiple WHEN MATCHED clauses, all except the last must carry conditions — this pattern obeys that rule. Explicit column lists are used, not INSERT * or UPDATE SET *, so the extra op_type and event_time columns in the source cannot break the write.

Validate per key

Use assertions per key, never row-count equality: a batch with delete tombstones legitimately changes row counts.

SELECT customer_id, COUNT(*)
FROM latest_changes
GROUP BY customer_id
HAVING COUNT(*) > 1;  -- expect zero rows

SELECT *
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM latest_changes WHERE op_type = 'delete'
);  -- expect zero rows

After

That no-op is the op_type guard doing its job. Without it, an unconditional insert would have resurrected a deleted row.

On Lakeflow Declarative Pipelines, stop hand-rolling: use AUTO CDC / APPLY CHANGES, which sequences out-of-order events for you.

1. Stage the change window as a table: business key, payload columns, op_type, event_time.
2. Rank to one row per key: ROW_NUMBER() OVER (PARTITION BY key ORDER BY event_time DESC, <deterministic tiebreaker>) = 1.
3. Filter NULL keys — they can never match.
4. MERGE: matched + delete tombstone → DELETE; matched → UPDATE with explicit columns; not matched + insert/update → INSERT with explicit columns.
5. Never use WHEN NOT MATCHED BY SOURCE on a change window.
6. On Lakeflow, use AUTO CDC / APPLY CHANGES instead of hand-rolling.

Use this when

• You hand-roll CDC MERGE in SQL or notebooks.
• A change window can hold multiple events per key.
• Delete tombstones are part of the feed.
• You are not on Lakeflow.

Avoid this when

• You are on Lakeflow Declarative Pipelines — use AUTO CDC / APPLY CHANGES.
• The source is a single full-snapshot load — a plain MERGE without ranking is fine.
• The target is not a Delta table.
• You are tempted to use dropDuplicates(['key']) as a “latest event” picker.
• You are tempted to add WHEN NOT MATCHED BY SOURCE to a change window.

See also Issue 006, “One key, one row: dedupe the source before you MERGE”, for the plain duplicate-key variant of this failure. This issue covers ordered CDC change windows, a different problem.