Bootstrapping CDC: open the slot first, copy second

CDC pipelines lose and resurrect rows in the hours between the initial copy and the log taking over. A runbook and a MERGE that make overlap harmless and a gap impossible.

Source database Replication slot Change topic Chunked snapshot Merge apply Lake table Reconciliation check opened first by key range newer position wins counts and checksums
The source feeds a replication slot and a chunked snapshot; both meet at a merge that keeps the newer log position, then get reconciled.
On this page
  1. What the first attempt did
  2. Where the gap came from
  3. Six steps, slot first
  4. One MERGE for both paths
  5. The second attempt
  6. Two columns forever, and a slot to watch

A row count that is off by 380 out of 260 million looks like a pass. That’s 0.00015 percent, and most people would sign off on it.

I’m using an invented company to show why they shouldn’t. Marlowe Tickets doesn’t exist and neither do its numbers, but the failure is the standard one for a hand-rolled CDC bootstrap.

What the first attempt did

Marlowe has a 260-million-row orders table in Postgres and wants it in an Iceberg table, kept fresh by Debezium through Kafka. The plan sounded reasonable: export the table in key-range chunks, load the chunks into the lake, then start the connector with its own snapshot turned off so it streams from the current log position.

The export took 5 hours 40 minutes, and 1.9 million changes landed on orders while it ran.

Each chunk was its own read, so each chunk saw the table as it was when it ran. Most of those 1.9 million changes hit recent orders at the top of the key range, which the export reached last, so the export picked them up by luck. The rest were refunds and cancellations on older orders whose chunk had already been written.

Afterwards a full comparison found 4,112 updates missing from the lake, and 380 rows still present there that had been deleted at the source during the export. New orders were fine, because the team re-ran the top chunk once the connector was up.

The count check only ever saw the 380. A lost update doesn’t change a count. Four thousand orders with refund_state still none in the lake and refunded in the source are invisible until someone compares content, which is why step 5 below uses checksums.

Where the gap came from

The stream started after the copy ended, so there was a window covered by neither. Every change in that window to an already-exported row was gone for good.

The usual patch is to line the two up exactly: export inside one consistent snapshot and stream from that position. Postgres can do it, since creating a logical slot over the replication protocol can export a snapshot. But it means one transaction held open for 5 hours 40 minutes on a busy primary, and a failed chunk restarts everything.

I’d rather stop needing the alignment. Open the replication slot before the copy starts, so the stream covers everything from that point on. The copy and the stream now overlap by hours instead of missing each other by hours. Then make the apply step indifferent to overlap: every row in the lake carries the log position of the change that produced it, and a write only wins if its position is higher.

Two details make that rule correct.

A snapshot row needs a position too, and I give it zero. A chunk read isn’t a point in the log, and picking a WAL position safely below what it saw is harder than it looks, because a transaction can write its changes early and commit late. Zero sidesteps the question: a snapshot row fills a hole and never beats a stream event. That’s safe because the slot was open before the first chunk ran, so any row that changed after that gets its final state from the stream. A stream event may briefly put a snapshot row back to a slightly older state, and the next event corrects it.

Deletes have to leave a marker. If the stream’s delete arrives before the snapshot chunk that still contains the row, a physical delete has nothing to remove, and the chunk then inserts the row back. Opening the slot first would only trade Marlowe’s 380 ghosts for a fresh set. Keep a tombstone with the delete’s position and the late snapshot row loses the comparison.

Six steps, slot first

  1. Create the publication, then the slot: CREATE PUBLICATION marlowe_orders_pub FOR TABLE orders; and then SELECT * FROM pg_create_logical_replication_slot('marlowe_orders', 'pgoutput');. Note the LSN the second one returns. A slot older than its publication fails to decode with “publication does not exist”.
  2. Start the connector on that slot and publication with its snapshot off, so changes flow into the Kafka topic right away. This also keeps the slot from pinning hours of WAL on the primary. Set topic retention well past the expected copy time.
  3. Snapshot in key-range chunks. Stamp every row with lsn = 0 and op = 'r'. A sequential key helps here, which is one more argument from the primary key post.
  4. Apply snapshot chunks and stream batches through the same MERGE below, in any order.
  5. Reconcile per key range: count and a checksum over the business columns, source against lake, excluding tombstones. Re-check a mismatched range once after the stream catches up, since the source is still moving.
  6. Retire the snapshot path. Once every range matches, drop the chunk jobs and read SELECT pg_current_wal_lsn() - '0/0'::pg_lsn; on the source. When the stream has passed that number, purge the tombstones below it.

One MERGE for both paths

Spark SQL against an Iceberg v2 table. staging.orders_changes holds either a snapshot chunk or a batch of flattened Debezium events, with lsn taken from the event’s source.lsn.

MERGE INTO lake.orders AS t
USING (
  SELECT ticket_order_id, event_id, seat_count, refund_state, updated_at, op, lsn
  FROM (
    SELECT *,
           row_number() OVER (PARTITION BY ticket_order_id ORDER BY lsn DESC) AS rn
    FROM staging.orders_changes
  ) AS ranked
  WHERE rn = 1
) AS s
ON t.ticket_order_id = s.ticket_order_id
WHEN MATCHED AND s.lsn > t.source_lsn AND s.op = 'd' THEN
  UPDATE SET is_deleted = true, source_lsn = s.lsn
WHEN MATCHED AND s.lsn > t.source_lsn THEN
  UPDATE SET
    event_id     = s.event_id,
    seat_count   = s.seat_count,
    refund_state = s.refund_state,
    updated_at   = s.updated_at,
    is_deleted   = false,
    source_lsn   = s.lsn
WHEN NOT MATCHED THEN
  INSERT (ticket_order_id, event_id, seat_count, refund_state, updated_at,
          is_deleted, source_lsn)
  VALUES (s.ticket_order_id, s.event_id, s.seat_count, s.refund_state,
          s.updated_at, s.op = 'd', s.lsn);

-- step 6, after reconciliation passes
DELETE FROM lake.orders
WHERE is_deleted AND source_lsn < 412316860416;  -- position read in step 6

The inner query keeps one row per key because MERGE fails when two source rows match the same target. A delete for a key the lake hasn’t seen yet inserts a tombstone, which is what stops a late chunk from reviving it. Readers go through a view that filters is_deleted.

The second attempt

Marlowe reran it this way. The same 5 hours 40 minutes of copying and roughly the same 1.9 million changes, this time sitting in the topic instead of falling into a gap. Chunk rows for orders the stream had already touched lost the comparison and did nothing.

Reconciliation split the key space into 1,000 ranges. All 1,000 matched on count and checksum.

Two columns forever, and a slot to watch

You carry two extra columns for good, and in merge-on-read mode every merge writes delete files that somebody has to compact, which I covered in the Iceberg chores post. The slot also needs watching: if the connector stalls, WAL piles up on the primary.

Debezium’s incremental snapshots, driven by a signalling table, do a version of this for you by deduplicating chunk rows against the stream between watermarks. I’d use them where they fit. I’d still keep the position column and the comparison in my own apply step, because that rule also covers a connector restart that replays events and the day someone re-runs a chunk by hand.