CDC Replay Corruption and the 23:00 Revenue Close
An online marketplace streams order events from a PostgreSQL OLTP database into a cloud warehouse using Debezium, Kafka and a MERGE-based loader. Peak volume is 1,200 events per second. After a connector restart the pipeline reprocessed four hours of the write-ahead log and, because the loader appends, roughly 1.9 million duplicate rows appeared. Order headers and order lines travel on separate topics consumed by separate consumer groups, so a line can land before its header. Finance reports daily revenue overstated by about 0.7%, concentrated in orders modified in the 20 minutes before the 23:00 UTC close. A full re-snapshot to repair the damage took nine hours, held a long transaction on the source, and doubled row counts again because it overlapped live traffic. The team is under pressure to publish the daily revenue figure by 06:00.
1. Which delivery guarantee should the pipeline target, and how should the loader be redesigned so replays and restarts cannot corrupt the warehouse?
Read the worked answer
Exactly-once cannot be guaranteed end to end here. Kafka transactions can make the read-process-write loop atomic within Kafka, but the warehouse sink commits outside that transaction, so a crash between the sink commit and the offset commit will always duplicate. The achievable target is at-least-once delivery with idempotent, deterministic writes, which makes duplicates harmless.
Concretely: land raw events append-only with the Debezium event identifier retained, then MERGE into the target on the business key with a monotonic comparison - WHEN MATCHED AND source.source_lsn > target.source_lsn THEN UPDATE. PostgreSQL LSN or a source commit timestamp supplies that monotonic version; without one, a replayed old event overwrites a newer row. Handle tombstones explicitly for deletes, and keep transforms deterministic - no CURRENT_TIMESTAMP or random values inside the load, or re-running the same event produces a different row.
Commit consumer offsets only after the MERGE transaction commits, accepting duplicates rather than losing events. Validate with hourly source-versus-target counts and a checksum on a sample of orders, alerting on drift above a tenth of a percent.
2. How should ordering and the 23:00 close be handled so the published revenue figure is correct?
Read the worked answer
Two separate problems. For header and line ordering, key both topics on order_id so related events land in one partition and are processed by a single task, or keep separate topics but perform a stateful stream-stream join with a grace period that holds lines until the header arrives or the window expires. Separately, order the data by event time, not ingestion time: carry source commit time, apply a watermark of about 30 minutes, and buffer late events rather than applying them blindly to whichever batch is open.
For revenue, compute the daily figure from the immutable raw layer as of event_time before 23:00, with the watermark applied, and publish at 06:00 - the seven-hour gap exists precisely to absorb late modifications that currently leak into the next day. Do not silently mutate yesterday's closed number; late events produce an append-only restatement against the prior business date, with the original figure preserved. That 0.7% overstatement almost certainly reflects edits landing after close being counted twice or in the wrong period, which reconciliation will now expose.
Add a daily source-versus-warehouse reconciliation on order count and gross amount, alerting above 0.1%.
3. How should the re-snapshot have been performed, and what should be done about the current duplicated state?
Read the worked answer
The nine-hour blocking export was the wrong instrument. A full snapshot on a live OLTP source at 1,200 events per second holds a long transaction, inflates the write-ahead log, and - because it overlapped live traffic without deduplication - produced the second wave of duplicates.
The correct approach is an incremental, chunked snapshot: Debezium's signal table triggers a snapshot that reads primary key ranges with a watermark, throttled to a chunk size the source can absorb, while live change events continue to stream. The same idempotent MERGE absorbs the overlap, so the snapshot and the stream can coexist safely. If the connector cannot support that, snapshot into a staging table during a low-traffic window and swap.
For the current state, do not patch row counts. Rebuild the affected order tables from the retained raw event layer using the deduplicated MERGE, verify against source aggregates for the contaminated window, and only then resume the normal publication schedule. Add a monitoring rule on duplicate-key rate so the next replay is caught within minutes rather than discovered by finance.