CDC Deletes, Tombstones and the Idempotent MERGE

Order 1042 was deleted at 14:10. At 15:00 it's back in the warehouse, marked shipped. Nobody re-created it. A retried batch did.

This is Part 4 of CDC in Production. In Part 3 we caught silent data loss and repaired it by re-sending changes. Crashes re-send changes too. So this post covers the order CDC events really arrive in, how deletes and tombstones work, and a Snowflake MERGE that gets the right answer even when an event shows up twice, or late.

Prefer to watch? Here's the 7-minute video version with animated diagrams:

Video link: CDC Deletes, Tombstones and the Idempotent MERGE

The ordering promise, and its fine print

Start with what is actually guaranteed. Debezium uses the row's primary key as the Kafka message key. Kafka writes every message with the same key to the same partition (one ordered slice of a topic), and a partition is read in exactly the order it was written. So changes to one row arrive in order. Changes to different rows, on different partitions, have no order between them at all.

Two pieces of fine print:

• Adding partitions breaks the mapping. The partition for a key is derived from the key and the partition count. Add partitions to a live topic and new events for a row can land on a different partition from its old ones, so a consumer can read them out of order.
• Delivery is at least once. After a crash, Debezium can re-send events it already sent, because its stored position is only committed every so often. Your sink must expect duplicates.

So the sink rule is: expect duplicates, and never assume an order across keys.

Every change already has a version

To cope with duplicates and late events, each change needs a version number that only goes up. You already have one. Every Debezium event has a source block, and on Postgres it carries lsn, the log sequence number: the position of the change in the write-ahead log (WAL). On Oracle the equivalent is scn, the system change number.

"source": {
  "connector": "postgresql",
  "table": "orders",
  "txId": 88240,
  "lsn": 3456121520
}

Row locks mean only one transaction can change a given row at a time, so for a single row a later change always has a higher position. That's exactly the property we need. A timestamp doesn't have it: many changes can share the same millisecond.

Deletes and tombstones

A delete event has op set to d, the old row in before, and after set to null. With the default replica identity (see Part 2), before holds only the primary key:

{ "before": { "id": 1042 }, "after": null, "op": "d" }

Then, by default (tombstones.on.delete is true), Debezium sends a second message: a tombstone, with the same key and a null value. It exists for log compaction, the Kafka cleanup mode (cleanup.policy=compact) that keeps only the latest message per key. A tombstone tells compaction it may eventually drop every message for that key.

Three things trip people up here:

1. Tombstones expire. On a compacted topic, delete.retention.ms keeps them for one day by default (86400000), then they're removed. A consumer that is more than a day behind can miss the delete entirely.
2. The Debezium JDBC sink ignores deletes by default. delete.enabled is false. Turning it on also requires primary.key.mode=record_key.
3. Flattening can drop them. Most warehouse loaders want flat rows, not the before/after envelope, so they use the ExtractNewRecordState transform. Its delete.tombstone.handling.mode defaults to tombstone: the delete event is dropped and only the tombstone is kept. A loader that skips null values then never sees the delete.

Set the mode to rewrite and you get a flat row instead, with a __deleted field. Add the log position and commit time while you're there:

transforms=unwrap
transforms.unwrap.type=io.debezium.transforms.ExtractNewRecordState
transforms.unwrap.delete.tombstone.handling.mode=rewrite
transforms.unwrap.add.fields=op,lsn,source.ts_ms

A delete of order 1042 now arrives as an ordinary record:

{ "id": 1042, "status": null, "amount_cents": null,
  "__deleted": "true", "__op": "d", "__lsn": "3456121520", "__source_ts_ms": "1792591800000" }

The other columns are null because, with the default replica identity, the delete only carried the key. rewrite also removes the tombstones; if you still need them for compaction, there's a rewrite-with-tombstone mode.

Primary key changes are two rows

One more event shape catches people out. If an UPDATE changes the primary key (say id 1042 becomes 2042), Debezium doesn't send an update. It sends a delete and a tombstone for the old key, then a create for the new key. The delete carries a __debezium.newkey header and the create carries __debezium.oldkey, linking the two.

Those are different keys, so they can land on different partitions and nothing orders them. Your sink must treat them as two independent rows.

How the zombie row happened

Back to order 1042. The loader writes micro-batches (small groups of events loaded every few minutes) into Snowflake:

1. Batch 7 holds the update to shipped. It times out.
2. Batch 8 holds the delete. It succeeds, and the row is gone.
3. The loader retries batch 7. The MERGE finds no row with that id, so it inserts one.

The deleted order is back, and the counts even look plausible. Two design choices made it possible: the warehouse forgot the delete, and it applied events without checking whether they were newer than what it already had.

The fix: soft deletes plus "only newer wins"

The fix has two parts:

• Never forget a delete. Keep it as a soft-deleted row: is_deleted = TRUE, plus the position it happened at.
• Only apply an event if its position is higher than the one the row already holds.

Now the retried update from batch 7 (at lsn 500, say) arrives after the delete (at lsn 520). Is 500 greater than 520? No, so it's ignored.

The target table needs two extra columns, and a third if you want to purge by age later:

CREATE TABLE orders (
  id           NUMBER PRIMARY KEY,
  status       VARCHAR,
  amount_cents NUMBER,
  is_deleted   BOOLEAN NOT NULL DEFAULT FALSE,
  lsn          NUMBER  NOT NULL,
  changed_at   TIMESTAMP_NTZ
);

Each batch lands in a staging table, orders_changes, with the flattened fields mapped to columns: is_deleted from __deleted = 'true', lsn from __lsn cast to a number, and changed_at from TO_TIMESTAMP_NTZ(__source_ts_ms, 3) (the 3 means the value is in milliseconds).

The idempotent MERGE

Idempotent means applying the same input again leaves the result unchanged. Here's the MERGE that gets you there:

MERGE INTO orders t
USING (
  SELECT * FROM orders_changes
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY id ORDER BY lsn DESC) = 1
) s
ON t.id = s.id
WHEN MATCHED AND s.lsn > t.lsn THEN UPDATE SET
  status = s.status, amount_cents = s.amount_cents,
  is_deleted = s.is_deleted, lsn = s.lsn, changed_at = s.changed_at
WHEN NOT MATCHED THEN INSERT
  (id, status, amount_cents, is_deleted, lsn, changed_at)
  VALUES (s.id, s.status, s.amount_cents, s.is_deleted, s.lsn, s.changed_at);

Step by step:

1. Deduplicate the batch. QUALIFY filters on a window function, keeping only the latest event per id (highest lsn). This isn't optional in Snowflake: by default, several source rows matching one target row makes an updating MERGE fail with a nondeterministic-merge error (ERROR_ON_NONDETERMINISTIC_MERGE).
2. Join on the key.
3. When matched, only newer wins. The update runs only if s.lsn > t.lsn, and it sets the deleted flag too, so a delete becomes a soft delete and a later re-insert of the same key revives the row.
4. When not matched, insert, even a delete. That way the delete's position is remembered, and a late update for the same row loses.

If you'd rather keep the last known values on deleted rows instead of the nulls a delete brings, use status = IFF(s.is_deleted, t.status, s.status) and the same for other columns.

Why this is safe:

• Same batch twice: every lsn is equal, not higher, so nothing changes the second time.
• Batches out of order: older events lose to newer ones already applied.
• Re-snapshots from Part 3: read events go through the same check, so they can't overwrite a row the warehouse already holds at a newer position.

You can still hard delete, as a separate job that only removes soft-deleted rows older than any replay you'd ever run:

DELETE FROM orders
WHERE  is_deleted
  AND  changed_at < DATEADD(day, -30, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ);

Pick the window to be longer than your Kafka retention and your longest possible backfill.

Test it on purpose

Don't trust the MERGE until you've tried to break it. Take one batch, shuffle it, apply it twice, then apply an older batch on top. The final table must equal one clean, in-order pass. With both results in tables, the comparison is two set differences:

(SELECT * FROM orders_test  MINUS SELECT * FROM orders_clean)
UNION ALL
(SELECT * FROM orders_clean MINUS SELECT * FROM orders_test);

Zero rows means they match. Any rows means you have an ordering bug, and it's better you find it than production.

Five common mistakes

1. Ordering by a timestamp. Many changes share the same millisecond. Use the log position.
2. Hard deletes in the sink. The position is forgotten, and late events bring rows back.
3. Leaving the JDBC sink's delete.enabled at false. Deleted rows live forever downstream.
4. Adding partitions to a live change topic. Per-key order breaks for existing keys.
5. Capturing a table with no primary key. With the default replica identity, Debezium emits only creates for it, no updates or deletes. Give it a key, or change its replica identity, before you capture it.

Recap

• Order is per key, and only while the partition count stays put. Delivery is at least once.
• Deletes arrive as a delete event plus a tombstone, and tombstones expire.
• Version every row by its log position and keep deletes as soft deletes.
• Deduplicate each batch, then apply only newer events.

If you run one test this week, replay yesterday's batches into a copy of the table, shuffled and twice, and compare it with the real one.

The CDC in Production series

• Part 1: Change Data Capture (CDC) Explained (article · video)
• Part 2: Debezium Postgres to Kafka: Every Setting Explained (video)
• Previous, Part 3: The Silent CDC Bug: Data Loss With No Errors (video)
• Part 4: CDC Deletes, Tombstones and the Idempotent MERGE (this post · video)
• Next, Part 5: CDC Schema Changes: What Breaks and How to Ship Them (video, out October 31)
• Coming up: Part 6 (the WAL slot trap)

---

If this was useful, the video version has the animated diagrams for every step: watch it on YouTube. Subscribe to Pipeline Whiteboard for more diagram-first data engineering explainers. Next up, in Part 5: schema changes, registry compatibility, and what breaks in Snowflake and the lakehouse.