⚡ Streaming & Kafka · Chapter 1

CDC with Debezium: reading the database log instead of the database

Polling a production database every five minutes is how analytics teams get their access revoked. CDC reads the log the database already writes — and gets every change, in order, including the deletes.

Study this lesson free in the app →

The basics

Change Data Capture (CDC) means getting every insert, update and delete out of a database by reading its internal change log, instead of repeatedly querying its tables. Debezium is the open-source tool that does this and publishes each change to Kafka.

The problem it solves: analytics needs a copy of production data — orders, payments, rides. The naive approach polls SELECT * FROM orders WHERE updated_at > ? every few minutes. It loads the production database, misses changes that happen between polls, and never sees deletes: a deleted row simply stops appearing, so the warehouse keeps it forever.

The mental model: a bank doesn't photograph your balance every five minutes to know what happened — it keeps a passbook with every transaction in order. Databases keep a passbook too (Postgres calls it the WAL, MySQL the binlog) for crash recovery and replicas. Debezium registers as a replica, reads that passbook, and turns each entry into an event with the row before and after the change.

Explained

SELECT * FROM orders WHERE updated_at > ? every five minutes.

It misses rows updated inside the same second. It misses deletes entirely. It hammers production. And it silently drifts, so nobody notices for months.

Every transactional database already keeps an ordered log of every change, so it can crash-recover and replicate. Postgres calls it the WAL, MySQL the binlog.

Change Data Capture means reading that instead of querying tables.

Debezium pretends to be a replica. The database streams it every insert, update and delete — in commit order, with the before and after image — and Debezium publishes them to Kafka.

You get the complete change history, in order, at near-zero cost to the source. The database was writing this anyway.

What a change event looks like

Each event carries:

before — the row as it was. null for an insert. after — the row as it now is. null for a delete. op — c create, u update, d delete, r read (snapshot). source — LSN/offset, transaction id, table, timestamp.

The before image is the part polling can never give you. "The fare changed from 240 to 190" is a different fact from "the fare is 190", and reconciliation needs the first one.

Snapshot then stream. On first connect Debezium reads the whole table (op: r), then switches to the log at exactly the right position. Getting that handover right without missing or duplicating is the hard part it solves for you.

The consumer that is right the second time

# Kafka key = primary key -> all changes for one row land in one partition,
# so they stay in order. This is the whole reason the key matters.
for msg in consumer:
    ev = msg.value
    pk = ev['after']['id'] if ev['after'] else ev['before']['id']

    if ev['op'] == 'd':
        target.execute('DELETE FROM rides WHERE id = %s', [pk])
    else:
        # upsert, not insert — the same event may arrive twice
        target.execute('''
            INSERT INTO rides (id, fare, status, _lsn) VALUES (%s,%s,%s,%s)
            ON CONFLICT (id) DO UPDATE SET
              fare = EXCLUDED.fare, status = EXCLUDED.status, _lsn = EXCLUDED._lsn
            WHERE rides._lsn < EXCLUDED._lsn''',        # never apply an older change
            [pk, ev['after']['fare'], ev['after']['status'], ev['source']['lsn']])

Two defences, both required: upsert because delivery is at-least-once, and the _lsn guard because a retry can deliver an older event after a newer one.

A ride is cancelled and the row is deleted.

With polling on updated_at: the row simply stops appearing. Your warehouse still shows it as active, forever. Revenue is overstated and no error is ever raised.

With CDC: you get op: d with the before image. You know it existed, what it was, and exactly when it went.

This is the case that usually justifies the whole project. Deletes are invisible to polling, and finance eventually finds them.

Replication slots are dangerous. In Postgres, a slot holds WAL until the consumer confirms. Stop your connector on a Friday and by Monday the disk is full and the production database stops accepting writes. Monitor slot lag as a production alert, not a nice-to-have.

The initial snapshot on a 500GB table takes hours and loads the source. Use an incremental snapshot, or a read replica.

Schema changes propagate. Someone adds a column and every downstream consumer sees it. Use a schema registry with compatibility rules or you find out in production.

Topic per table is the default. A hundred tables is a hundred topics to size and monitor.

Not monitoring replication slot lag. The one that takes prod down.

Inserting rather than upserting. Delivery is at-least-once. You will get duplicates.

Ignoring event order on retry. Without an LSN guard, a redelivered old event overwrites a newer value.

Assuming CDC gives you a table. It gives you a stream of changes. Turning that back into current state is your job — that is what the upsert is doing.

Running the snapshot against the primary at peak. Use a replica.

The database already writes the log. Read that, not the tables.

You get every change in commit order, with before and after, including deletes — and production barely notices.

Key by primary key for ordering. Upsert for duplicates. Guard on LSN for out-of-order retries. Alert on slot lag or you will take prod down.

Words to know

In two minutes

• CDC reads the database's own change log (WAL/binlog) instead of polling tables.
• Debezium acts like a replica and publishes each change to Kafka with before, after, op and LSN.
• Unlike polling on updated_at, CDC sees deletes, every intermediate update, and costs the source little.
• It starts with a snapshot (op = r), then switches to streaming from the exact log position.
• Kafka key = primary key, so all changes to one row stay ordered in one partition.
• Consumers must upsert (at-least-once delivery), guard on LSN (stale redeliveries), and handle deletes and tombstones.
• The operational risk is the Postgres replication slot: a stopped connector makes WAL pile up until the disk fills — alert on slot lag.

Test yourself

There are 8 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.