The Silent CDC Bug: Data Loss With No Errors

Every dashboard is green. The connector says RUNNING, capture lag is under a second, and the warehouse is missing 4,812 orders. Nobody gets paged, because nothing failed.

This is Part 3 of CDC in Production. In Part 2 we built a Debezium pipeline from Postgres to Kafka. This post covers four ways that pipeline can lose data without a single error, and the reconciliation check that catches all of them.

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

Video link: The Silent CDC Bug: Data Loss With No Errors

Why nothing alerts

The usual signals measure flow, not completeness. Task status tells you the connector is alive. Lag tells you how far behind the newest event is. Neither tells you whether every change made it. A pipeline that skipped a thousand rows an hour ago looks exactly like a healthy one now.

Failure 1: the slot moves on after a failover

A replication slot is Postgres's bookmark for a logical decoding reader: it remembers the last position the reader confirmed. Here's how it goes wrong.

The primary dies at 14:00 and a standby is promoted. On self-managed Postgres 16 or earlier, logical slots don't follow a promotion. So someone recreates the slot on the new primary and the connector reconnects. A new slot starts at the current position in the log, so everything written between the promotion and that moment is never decoded.

Why no error? The connector's stored offset is now behind the slot. Debezium's offset.mismatch.strategy defaults to no_validation, and on Postgres 15 and newer the server streams from the slot's confirmed position. The gap is skipped and the connector reports RUNNING throughout.

Defences:

• Postgres 17+: failover slots. Set slot.failover to true in the connector so it creates a failover-enabled slot, and keep it in sync on the standby:

  # primary: postgresql.conf
  synchronized_standby_slots = 'standby1_slot'   # the standby's physical slot

  # standby: postgresql.conf
  sync_replication_slots = on
  hot_standby_feedback = on
  primary_slot_name = 'standby1_slot'
  # primary_conninfo must include a dbname for slot sync to work

• Managed services. Debezium's docs note that some managed services, Amazon RDS among them, keep the slot through a failover because they replicate the storage. Check what yours does before you need it.
• Fail loudly. "offset.mismatch.strategy": "trust_offset" makes the connector stop instead of silently skipping. It's a technology preview in current Debezium, so test it first.

Failure 2: the table that was never captured

Someone adds public.refunds to table.include.list and restarts the connector. But the publication was created by hand (as in Part 2), and nobody added refunds to it. Postgres only decodes tables in the publication, so no refund events ever arrive. The connector is healthy; the topic is just empty.

Fix the publication first:

ALTER PUBLICATION dbz_shop ADD TABLE public.refunds;
GRANT SELECT ON public.refunds TO debezium;

Then there's a second trap: adding a table only captures changes from that point on. Existing rows are never sent unless you ask. Debezium's docs suggest an incremental snapshot (a chunked copy that runs alongside streaming) after you add a table. You trigger one through a signal table, a small table Debezium watches for commands:

CREATE TABLE public.debezium_signal (
  id   varchar(42) PRIMARY KEY,
  type varchar(32) NOT NULL,
  data varchar(2048)
);
GRANT SELECT, INSERT ON public.debezium_signal TO debezium;
ALTER PUBLICATION dbz_shop ADD TABLE public.debezium_signal;

Point the connector at it with "signal.data.collection": "public.debezium_signal", then send the request:

INSERT INTO public.debezium_signal (id, type, data)
VALUES ('add-refunds', 'execute-snapshot',
        '{"data-collections": ["public.refunds"], "type": "incremental"}');

Until you do, the warehouse has every new refund and none of the old ones.

Failure 3: the TOAST placeholder

Postgres stores large values out of line, in what it calls TOAST storage. When an update doesn't change a TOASTed value, that value isn't included in the WAL message, unless the table uses REPLICA IDENTITY FULL. Debezium can't safely fill it in, so it sends a placeholder: __debezium_unavailable_value.

Now picture a sink that writes the after image straight into the table. Order 1042 has long delivery notes. Someone changes only the status. The event arrives as:

{ "id": 1042, "status": "shipped", "notes": "__debezium_unavailable_value" }

and the sink overwrites the real notes with the placeholder. Row counts still match; the data is quietly wrong.

Two fixes. Set REPLICA IDENTITY FULL on tables with large columns you need downstream. Or make the sink keep the old value when it sees the placeholder, for example in a Snowflake MERGE:

WHEN MATCHED THEN UPDATE SET
  t.status = s.status,
  t.notes  = IFF(s.notes = '__debezium_unavailable_value', t.notes, s.notes)

Failure 4: dropped downstream

This one happens after Kafka. In Kafka Connect, errors.tolerance defaults to none, so any bad record stops the task. Teams flip it to all to stop the pages. Now records that fail conversion or transformation are skipped. Without a dead letter queue (DLQ, a topic that collects failed records), the only trace is a log line, and only if error logging is on. If you tolerate errors in a sink connector, at least keep them:

errors.tolerance=all
errors.log.enable=true
errors.log.include.messages=true
errors.deadletterqueue.topic.name=dlq.shop.orders
errors.deadletterqueue.context.headers.enable=true

(The DLQ settings apply to sink connectors.) Then alert when the DLQ topic receives anything.

The other downstream gap is retention. A topic keeps messages for seven days by default (retention.ms=604800000). If the sink is down longer than that, the oldest events are deleted before it reads them. When it comes back, its committed offset no longer exists, so a Connect sink resets to the earliest message that's left and carries on.

Reconciliation: compare the data itself

All four failures share one property: the only reliable way to catch them is to compare the data. Reconciliation means running the same query on the source and on the warehouse and comparing the answers.

Not one grand total, though. Split the table into buckets, say one per hour of updated_at, so a mismatch tells you where to look. For each bucket compute three numbers:

• Row count catches missing rows.
• Sum of a numeric column like amount_cents catches wrong amounts.
• Checksum catches everything else: hash each row's key and values, turn the hash into a number, and add them up. Addition doesn't care about row order, so both systems can agree.

The Postgres side:

SET TIME ZONE 'UTC';

SELECT date_trunc('hour', updated_at)  AS bucket,
       count(*)                        AS row_count,
       sum(amount_cents)               AS amount_sum,
       sum(('x' || left(md5(concat_ws('|',
             id, status, amount_cents, coalesce(notes, '<null>'))), 15)
           )::bit(60)::bigint)         AS checksum
FROM   public.orders
WHERE  updated_at < '2026-10-21 15:00:00+00'   -- the watermark
GROUP  BY 1
ORDER  BY 1;

The Snowflake side builds the identical string and hash. TO_NUMBER with an X format parses hex:

ALTER SESSION SET TIMEZONE = 'UTC';

SELECT DATE_TRUNC('hour', updated_at)  AS bucket,
       COUNT(*)                        AS row_count,
       SUM(amount_cents)               AS amount_sum,
       SUM(TO_NUMBER(LEFT(MD5(CONCAT_WS('|',
             id, status, amount_cents, COALESCE(notes, '<null>'))), 15),
           'XXXXXXXXXXXXXXX'))         AS checksum
FROM   analytics.orders
WHERE  updated_at < '2026-10-21 15:00:00'
GROUP  BY 1
ORDER  BY 1;

(If the warehouse keeps soft-deleted rows, as in Part 4, add AND NOT is_deleted so they don't count as extra rows.)

Look at the WHERE clause. That's the watermark: only rows last updated before a cutoff (here, at least 30 minutes ago, rounded down to the hour) are compared, because anything newer may still be in flight. Compute the cutoff once and pass the same value to both queries.

Load both result sets into one place and compare bucket by bucket:

SELECT COALESCE(p.bucket, w.bucket) AS bucket,
       p.row_count AS src_rows, w.row_count AS wh_rows,
       p.checksum  AS src_sum,  w.checksum  AS wh_sum
FROM   recon.pg_buckets p
FULL OUTER JOIN recon.wh_buckets w ON p.bucket = w.bucket
WHERE  p.row_count  IS DISTINCT FROM w.row_count
    OR p.amount_sum IS DISTINCT FROM w.amount_sum
    OR p.checksum   IS DISTINCT FROM w.checksum
ORDER  BY 1;

In the video's example, the 14:00 bucket is short by 4,812 rows: that's the failover window. A later bucket has matching counts but a different checksum, so the values are wrong, not missing (hello, TOAST placeholder). A row changed during the check can cause a false alarm, so re-check a mismatched bucket before alerting. Real loss stays; in-flight changes go away.

Repair: re-snapshot only the bad bucket

Repair is targeted. The signal table accepts an execute-snapshot request with additional-conditions, so you re-snapshot only the broken bucket, not the whole table:

INSERT INTO public.debezium_signal (id, type, data) VALUES (
  'repair-orders-1400', 'execute-snapshot',
  '{"data-collections": ["public.orders"],
    "type": "incremental",
    "additional-conditions": [{
      "data-collection": "public.orders",
      "filter": "updated_at >= ''2026-10-21 14:00+00'' AND updated_at < ''2026-10-21 15:00+00''"
    }]}');

The rows arrive as read events (op = r) while streaming carries on. Your sink has to apply them safely on top of what's already there, which is exactly what Part 4 is about.

Five reconciliation mistakes

1. Treating RUNNING and low lag as proof nothing is missing.
2. Reconciling one total count. A lost insert and a lost delete cancel out.
3. No watermark. Comparing live data produces false alarms and teaches everyone to ignore the alert.
4. Hashing differently on each side. Timestamps, decimals and nulls must become exactly the same text in both systems before hashing. (Note that Snowflake's CONCAT_WS returns NULL if any argument is NULL, while Postgres skips nulls. Hence the COALESCE.)
5. Assuming a failover keeps your slot. Check your Postgres version and provider before the failover, not after.

Recap

• Silent loss comes from slots that move on, tables never captured, placeholders that overwrite real values, and records dropped downstream.
• Status and lag can't see any of it.
• Bucketed counts, sums and checksums below a watermark can.
• A targeted incremental snapshot repairs what they find.

If you add one check this week, make it an hourly row count on your most important table, source versus warehouse.

The CDC in Production series

• Part 1: Change Data Capture (CDC) Explained (article · video)
• Previous, Part 2: Debezium Postgres to Kafka: Every Setting Explained (video)
• Part 3: The Silent CDC Bug (this post · video)
• Next, Part 4: CDC Deletes, Tombstones and the Idempotent MERGE (video, out October 29)
• Coming up: Part 5 (schema changes), 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 4: ordering, deletes and tombstones, and a MERGE that applies every change correctly even when it arrives twice.