Change data capture, explained by building it

The sync job everyone writes first

Your OLTP database is the source of truth. Your warehouse needs a copy for analytics. So you write the obvious thing: a nightly job that queries everything changed since yesterday.

SELECT * FROM customers WHERE updated_at > now() - interval '1 day';

It works. It is also wrong in three specific ways, and all three bite eventually.

1. Load. That query scans the production database on a schedule you do not control, while real users are on it. Add more tables and more columns and it gets worse.
2. Deletes are invisible. A deleted row does not have a later updated_at. It simply stops appearing. Your warehouse keeps it forever, silently, and your revenue numbers drift.
3. Latency. Best case, your analytics are a day old. That is fine until someone asks a question about today.

The deeper problem is that you are reconstructing change by interrogating the database. But the database already recorded every change. It is sitting in the write-ahead log.

The WAL is already a change log

Every relational database keeps a log of intended writes before it applies them — Postgres calls it the write-ahead log. It exists for crash recovery, but it happens to contain exactly what a replica needs: every insert, update, and delete, in commit order, with before and after values.

Reading that log instead of polling tables is Change Data Capture (CDC). In Postgres you enable it with one setting:

# infra/docker-compose.yml
command: [postgres, -c, wal_level=logical, ...]

wal_level=logical makes Postgres publish row-level change data that a logical decoding plugin (pgoutput) can stream to a client. Turn it up to logical, and the log becomes an API.

The shape of the system

A production-shaped CDC pipeline has five parts, each of which earns its place:

Postgres (WAL)  ->  Debezium (decode)  ->  Kafka (log)  ->  consumer  ->  ClickHouse
   source            translator             buffer          transform      store

Part	Job	Why it cannot be skipped
Postgres	Writes change data to the WAL	It is the source of truth; we never poll it
Debezium	Decodes the WAL into a JSON envelope	Handles replication slots, snapshots, and before/after diffing
Kafka	Durable, ordered, replayable log	Decouples producer from consumer; events wait while the consumer is down
Consumer	Decode, transform, batch, write, commit	Where correctness actually lives
ClickHouse	Columnar analytics store	Its merge engine absorbs duplicate writes cleanly (part 2)

The Debezium envelope

Debezium does not forward raw WAL bytes — those are not portable. It emits a JSON envelope with a stable shape:

{
  "before": { "customer_id": 101, "city": "Jakarta" },
  "after":  { "customer_id": 101, "city": "Bandung" },
  "source": { "db": "shopdb", "schema": "public", "table": "customers", "ts_ms": 1789899890000 },
  "op": "u",
  "ts_ms": 1789899890123
}

The op field is the whole story, and it determines which image holds your data:

op	Meaning	Data lives in
c	create	after
u	update	before and after
d	delete	before only — after is null
r	read (initial snapshot)	after

Two details matter more than they look:

• source.ts_ms is the database’s change timestamp. Part 2 of this series uses it as a version number, and that choice is what makes replays safe.
• op = r means “snapshot”, not “insert”. Your very first run does not stream; it reads every existing row and labels it r. Skip understanding this and your first deployment looks like a mass insert event.

Snapshot, then stream

With snapshot.mode: initial, the connector does a consistent read of every included table and emits those rows as op = r, then switches to streaming live changes from the log. The source connector config is small enough to read in one sitting:

{
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "topic.prefix": "shopdb",
  "table.include.list": "public.customers,public.orders,public.order_items",
  "plugin.name": "pgoutput",
  "slot.name": "debezium_shop",
  "snapshot.mode": "initial"
}

topic.prefix plus schema plus table yields the topic name shopdb.public.customers. One topic per table, appended to in commit order.

Why Kafka sits in the middle

You could, technically, run a consumer directly against the logical replication slot. People do, and they get hurt by it. A replication slot is a piece of state on your production database — if the consumer is down too long, the slot retains WAL and your disk fills up.

Kafka’s job is to absorb that coupling:

• Durability. Events persist and stay replayable; a consumer outage does not back up the source.
• Replayability. A new consumer group can re-read the log from the beginning. That property is the entire foundation of part 3, where we deliberately break the pipeline.
• Fan-out. One change stream, many independent readers, later.

What this looks like running

Five containers, healthchecked, started with one command:

And the consumer, logging every lifecycle event as structured JSON plus a per-run report:

That report is the honest record of a run: how many events were processed, how many rows landed, how many failed to decode, and the observed lag. If you cannot answer those four questions about a pipeline run, you cannot operate the pipeline.

The cheat-sheet

• Poll tables for changes and you inherit load, invisible deletes, and latency.
• Read the WAL instead — the change log already exists.
• wal_level=logical is the single line that enables it in Postgres.
• Debezium turns the log into a versioned JSON envelope with op ∈ {c, u, d, r}.
• source.ts_ms is the change’s true time — keep it, you will need it.
• Kafka in the middle decouples the source from the consumer and makes replay possible.
• Watch the replication slot, not just the consumer.

Check yourself

Check yourself

1. Your nightly sync uses updated_at > yesterday. Why does a deleted row never disappear from the warehouse?
   Answer

   A delete removes the row; it does not produce a later updated_at. The row stops matching the query but is never updated or removed, so the warehouse keeps a stale copy forever. Deletes are simply invisible to a polling sync.
2. What does wal_level=logical actually enable?
   Answer

   It makes Postgres publish row-level change data that a logical decoding plugin (pgoutput) can stream to a client. At lower levels, the WAL exists but does not carry enough information to reconstruct row images.
3. An event arrives with op = "d" and after = null. Which image holds the data, and why?
   Answer

   The before image. A delete has no after-state, so the last-known values live in before. Emitting after for a delete is a common bug that produces null rows.
4. Why is a replication slot a production risk, and what is Kafka’s role in reducing it?
   Answer

   A slot retains WAL until it is consumed, so an idle consumer fills the source database’s disk. Kafka decouples consumption: the connector keeps draining the slot into a durable, replayable log, so a downstream consumer can be down without stalling the slot.
5. Why does the first run of the pipeline look like a bulk insert?
   Answer

   snapshot.mode: initial reads every existing row first and emits them as op = "r" before streaming live changes. It is a consistent initial load, not a flood of new inserts.

Where this goes next

This post built the path. The next two in the series attack the parts that actually break:

• Part 2 — Effectively-once: Kafka only gives you at-least-once delivery, so duplicates are inevitable. Making them harmless is a design decision, not a setting.
• Part 3 — Break it on purpose: a lab where we kill the consumer mid-batch, poison the topic, and replay a consumer group, then watch the safety mechanisms behave.

The whole thing is runnable: github.com/akmalariq/streaming-cdc-pipeline-with-debezium-and-kafka — make up, make register-connector, make consume. Start there.

References

• Martin Kleppmann, Designing Data-Intensive Applications, chapter 11 — stream processing and log-based messaging.
• Postgres documentation — Logical Decoding and wal_level.
• Debezium documentation — the Postgres connector and the op field semantics.