# CDC at Scale: Five Years of Scars

*Published 2024-09-17 · Dmitry Shirokov · shirokoff.ca/blog/cdc-at-scale-production-lessons*

CDC demos beautifully — point a connector at a database, watch rows appear in a topic, afternoon well spent. Then you run it for five years. Across Postgres, MySQL, SQL Server and Oracle the pattern is consistent: **almost nothing that goes wrong with CDC is about the change stream itself.** Steady-state streaming works. The initial load, the boundary with the source database's operational reality, and the day you need to do it all again — that's where the scars are. (How log-based capture works: [the Debezium article](https://shirokoff.ca/blog/debezium-cdc).)

## Scar 1: the initial snapshot

**The hardest part of streaming a table is the first read of it.** On a 4TB table with live writes, a consistent snapshot isn't a background detail — it's what makes a DBA revoke your access. What goes wrong: the snapshot **never finishes** (eleven hours, restart on hour ten, start over, while the log you need ages out); the snapshot **blocks writers**; or it succeeds and **nobody can tell**, so someone builds a report on a half-loaded table.

Defaults: **incremental snapshotting** (chunked reads interleaved with live changes, so restarts resume), **one table at a time** for anything large, and an explicit **per-table readiness signal** downstream rather than inferring from row counts.

## Scar 2: the replication slot that filled the disk

A slot guarantees the database keeps WAL until the consumer confirms it. That guarantee is the feature and a loaded weapon: **a consumer that stops consuming makes the source retain WAL forever.** Connector dies 6pm Friday, Saturday the primary's disk fills, and it's no longer a pipeline incident — it's a production outage with your name on it. MySQL/SQL Server have their own version of the shape.

Three boring, mandatory controls: **alert on slot lag in bytes** as a page, not a ticket; **cap slot retention** where the engine allows, accepting an invalidated consumer over a downed primary; and a **dead-man's handle** so a long-dead connector drops its slot and re-snapshots instead of holding the database hostage. Plus a **heartbeat table** written on a timer — on quiet databases the connector has nothing to confirm, so the slot never advances even though the pipeline is healthy.

## Scar 3: schema drift

| Source change | What happens | Do this |
|---|---|---|
| Add nullable column | Usually fine | Nothing, if the sink evolves |
| Drop column | Sink rejects, or keeps a stale field forever | Registry compatibility; treat as a contract change |
| Rename | Reads as drop + add; old data doesn't follow | Two-phase at the source: add, backfill, cut over, drop |
| **Type change** | Parse failures or silent truncation | Compatibility set to reject — fail loudly at the boundary |
| **Primary key change** | Keys change → partitioning and compaction break | Re-snapshot. No clever alternative. |
| Truncate/swap | On several engines, **no events at all** | Detect out of band; trigger a re-snapshot |

The structural fix isn't technical: **CDC makes your source database's schema a public API, and nobody told the team that owns it.** A [registry with compatibility rules](https://shirokoff.ca/blog/schema-registry-avro-protobuf) is the mechanical gate; a written agreement about breaking changes is what stops the gate being hit at 2am.

## Scar 4: deletes and tombstones

Deletes arrive as a near-empty payload plus, on compacted topics, a **tombstone** (null value). Sinks that treat null payloads as malformed will happily skip your deletes, leaving rows in the warehouse the source no longer has. And **soft deletes never produce delete events at all** — they're updates, and every consumer needs to know a filter is required. Land the operation as a column rather than applying destructively, then derive current state as a view (last change per key by **log position**, deletes excluded) — a bad delete flood becomes recoverable instead of a restore-from-backup.

## Scar 5: ordering

**Ordering is per key, not global** — a consumer can see a child row before its parent, so land without FK enforcement and resolve downstream. And **order by log sequence number, not timestamps**: clocks drift, batch transactions share milliseconds, and a failover can move the clock backwards. Every dedup bug I've debugged traced back to someone sorting by `updated_at`.

## The 3am problem

Every pipeline eventually needs replaying. The question that decides how bad that night is: *can you rebuild a target from the change log alone, or do you need the source database again?* Two insurances: **land the raw change log to cheap storage** before any transformation, indefinitely — the difference between a replay and an outage; and **make sinks idempotent on (key, log position)** so re-running is a no-op. Teams that get paged at 3am and are asleep by 3:30 did both before they needed them.

## Defaults for a new pipeline

Page on lag-in-bytes and connector liveness · heartbeat table · incremental snapshots with readiness signals · raw change log landed and retained · idempotent sinks keyed on log position · registry compatibility plus a real agreement with the source team · **a nightly reconciliation job** (counts + sampled checksums, source vs target). Lag metrics say the pipeline is moving; only reconciliation says it's *right* — every silent CDC bug I've found in five years was found by a count comparison.

*Related: [Debezium and CDC](https://shirokoff.ca/blog/debezium-cdc) · [Schema registry, Avro and Protobuf](https://shirokoff.ca/blog/schema-registry-avro-protobuf) · [Streaming databases](https://shirokoff.ca/blog/streaming-databases) · [Apache Hudi internals](https://shirokoff.ca/blog/apache-hudi-internals) · [Kafka internals](https://shirokoff.ca/blog/kafka-internals)*