Change Data Capture for Pipelines

Scenario

Example company: the warehouse copies customers by polling updated_at every 10 minutes. A privacy request deletes a customer in production with DELETE FROM customers WHERE id = 42.

What you see in production

• The deleted customer still appears in the warehouse and in marketing exports weeks later.
• Cancelled orders whose rows were deleted by a cleanup job still count as revenue.
• The polling queries show up near the top of the production database's slow-query list.

The naive code

-- every 10 minutes, for every table
SELECT * FROM customers WHERE updated_at > :watermark;    -- a deleted row has no row to select

Reproduce it

The simulation applies the same production changes in two ways: polling for updated rows, and replaying the database's change log.

log = [  # what the database wrote to its log, in commit order
    {"op": "insert", "id": 41, "name": "Ada"},
    {"op": "insert", "id": 42, "name": "Bob"},
    {"op": "update", "id": 41, "name": "Ada L."},
    {"op": "delete", "id": 42},
]

# Production table after all changes
production = {}
for e in log:
    if e["op"] == "delete":
        production.pop(e["id"])
    else:
        production[e["id"]] = e["name"]

# Polling: copy the rows that exist now (the warehouse started with both customers)
polled = {41: "Ada", 42: "Bob"}
polled.update(production)

# CDC: apply each change event in order
cdc = {41: "Ada", 42: "Bob"}
for e in log[2:]:
    if e["op"] == "delete":
        cdc.pop(e["id"], None)
    else:
        cdc[e["id"]] = e["name"]

print("production:", production)
print("polling:   ", polled, "<- customer 42 never disappears")
print("CDC:       ", cdc)

production: {41: 'Ada L.'}
polling:    {41: 'Ada L.', 42: 'Bob'} <- customer 42 never disappears
CDC:        {41: 'Ada L.'}

Root cause

A query can only return rows that exist. Polling sees the current state, not the changes, so deletes are invisible, and intermediate updates between polls are lost too. Polling also puts repeated scan load on the primary database.

The fix

Use log-based change data capture (CDC). Databases already record every committed change in a log for replication: PostgreSQL's write-ahead log via logical decoding, and MySQL's binary log. A CDC tool such as Debezium, or a managed connector, reads that log and emits one event per insert, update and delete, in commit order, usually to a message broker.

The pipeline then:

1. Takes an initial snapshot of each table.
2. Applies change events in order per primary key: upsert for inserts and updates, delete (or mark deleted) for deletes.
3. Tracks its log position so it resumes exactly where it stopped.

If CDC isn't possible, use soft deletes (deleted_at) in the source so polling can see them.

Trade-offs

• CDC needs replication permissions, a replication slot or binlog retention, and monitoring. A stuck consumer can make the source database retain log files and fill its disk.
• Schema changes flow through the stream and must be handled downstream.
• Events are usually delivered at least once, so the apply step must be idempotent.
• For small tables, a nightly full reload may still be simpler.

Detect it in production

• Replication lag: how far the CDC consumer is behind the source log.
• Source disk usage and the age of the replication slot.
• Reconcile row counts, including deleted rows, between source and warehouse.

Key points

• Polling sees current rows, so it misses deletes and intermediate changes.
• Log-based CDC reads the database's own change log and emits every change in commit order.
• Apply changes idempotently and in order per key; track the log position.
• Monitor consumer lag: a stuck CDC consumer can fill the source database's disk.