Change data capture: why CDC falls behind

The warehouse says the company has 48,000 active customers. Production says 46,200. The difference is every customer deleted in the last two years, because the pipeline reads rows changed since its last run, and a deleted row doesn’t change. It disappears.

Capturing changes from databases is the first step of data integration. Most teams start with a nightly query on a modified-date column, because it is easy to write and it works on day one. It falls behind from day two.

For the definition, see what change data capture is. This post covers why CDC pipelines fall behind, and what to fix first.

Why CDC pipelines fall behind

1. They poll a timestamp instead of reading the log. A query on updated_at misses every hard delete, and every update the application makes without touching that column.
2. The snapshot and the stream don’t overlap. The initial snapshot ends, the stream starts, and rows written during the gap belong to neither.
3. Updates arrive out of order. Two updates to the same row reach the target in the wrong order, and the older value wins.
4. Deletes are dropped. The pipeline handles inserts and updates and ignores deletes, so the target becomes a store of everything that ever existed.
5. The position is saved too early. The pipeline records how far it read before the write to the target succeeds. A failure in between loses those changes.
6. A schema change stops the pipeline. A column is added or renamed in production, and the pipeline either stops or drops the column without saying so.
7. Full extracts take too long. Teams that avoid CDC pull whole tables instead, and the extract takes longer every month.

What it costs

Every report built on the warehouse inherits the gap. Customer counts, churn rates and revenue all disagree with production by a few percent. Someone reconciles them by hand each month, and nobody trusts either number.

Full extracts cost people and time. Moving data out of production by brute force takes a team of people and a large part of every week.

What to fix first

1. Read the database log. Use the database’s own change log instead of a timestamp column: the write-ahead log in PostgreSQL, read through logical decoding, or the binary log in MySQL.
2. Overlap the snapshot and the stream. Start the stream before the snapshot ends, and deduplicate the overlap.
3. Keep order per key. Two changes to the same row must arrive in the order they happened. Changes to different rows can arrive in any order.
4. Treat a delete as an event. Carry it to the target as a delete, or as a deleted flag with a timestamp.
5. Save the position after the write lands. If the write fails, the pipeline reads the same changes again.
6. Pause on a schema change. Detect it, stop that table, and tell its owner, instead of dropping the column.

The guide to building a change data capture pipeline has the prompts for each step.

What it looks like at scale

“We were struggling with pulling 9M+ records out of our production environment, which took an 8-person team 40 hours a week. We got that down to 5 minutes a day, and it's fantastic.”

Where CDC fits

CDC is usually where data enters the warehouse from your own products. Business apps such as Salesforce and NetSuite arrive through their APIs instead, which Salesforce to Snowflake covers. A schema change is the most common reason a CDC pipeline stops, and schema change management covers catching it first.

For all seven steps, see the data integration process.