Debezium to ClickHouse: sub-second CDC without losing consistency
Change data capture delivers at-least-once, and an analytical store counts duplicates rather than correcting them. Here is why the binlog beats a batch extract, where deduplication actually belongs, and the two failure modes — schema evolution and silent snapshot gaps — that break pipelines quietly.
Change data capture moves rows out of a transactional database without querying it. Debezium reads the database’s own replication log, publishes each change as an event to Kafka, and a sink applies those events to an analytical store. Done properly this gives sub-second freshness at effectively no cost to the source. Done naively it gives you a dashboard that is quietly wrong.
I built this pipeline at FyreGig to feed loan-risk dashboards: MySQL to ClickHouse via Debezium and Kafka, over a million records, sub-second, at 99.9% consistency. This post is about the parts that are not obvious until the second week.
Why not just query the database on a schedule?
A batch extract — SELECT * WHERE updated_at > :last_run, every few minutes — is the obvious
alternative, and it fails in two directions simultaneously.
It is always the wrong interval. Short enough to feel current means hammering the primary with analytical scans; long enough to be cheap means the dashboard shows an hour-old view of risk. There is no setting that is both.
More seriously, it is query-driven, so it only sees end state. A row updated three times between runs looks like a single change. A row inserted and deleted between runs never existed. A row deleted at all simply stops appearing, with no event marking when or why. For an audit trail or a risk model, the intermediate states are frequently the interesting part.
The binary log has none of these problems, because it is not a view of the data — it is the ordered record of every change the database actually made.
The binlog is already being written
This is the detail that makes CDC cheap: MySQL writes its binary log for replication and crash
recovery whether or not you read it. Debezium attaches as a replication client, which is a path
the database is already built to serve. Capture adds no query load to the primary, competes with
no transaction, and needs no updated_at column to have been designed in advance.
Kafka then sits between capture and delivery, and that decoupling is what makes the pipeline survive ordinary operations. If ClickHouse is slow, restarting, or briefly unreachable, events accumulate in the log and the consumer resumes from its committed offset. No backpressure reaches MySQL. No changes are lost to a window that closed during an outage.
The consistency problem nobody mentions in the tutorial
Change data capture delivers at-least-once. Retries, consumer restarts, rebalances and connector failovers all mean a change event can arrive more than once.
In a transactional store with a primary key, a duplicate insert is rejected and the problem
announces itself. In an append-oriented analytical store, a duplicate is not corrected — it is
counted. Your row total drifts up. Your SUM(exposure) drifts up. Nothing errors, nothing
alerts, and the dashboard keeps rendering a number that is now wrong in the direction that matters
most.
So the sink must be idempotent with respect to the source’s primary key: whatever happens in
delivery, the store must converge on one row per key. In ClickHouse the usual mechanism is a
ReplacingMergeTree ordered by the primary key, with a version column — typically the event’s log
sequence number or timestamp — so that when duplicates merge, the newest wins.
The trap is that merges are asynchronous. ReplacingMergeTree guarantees eventual
deduplication, not immediate. A query that runs before a merge completes still sees both rows.
This is why FINAL, or an explicit aggregation that picks the latest version per key, belongs in
the read path — not as a nicety, but because without it the correctness guarantee you think you
have is one you only get later.
When I quote 99.9% data consistency, that is a statement about this reconciliation holding, not about whether events arrived.
Deletes
Debezium represents a delete as an event with a null after state, usually followed by a
tombstone. A sink that only handles inserts and updates will silently ignore both, and deleted
rows live on in the analytical store forever.
For loan-risk analysis, a deleted record that still appears is not a cosmetic bug — it is exposure that does not exist. The usual approach is a soft-delete column the sink sets, with deleted rows filtered in the read path, which also preserves the deletion as a fact worth keeping.
Snapshots, and the gap that eats your history
Before streaming can start, the sink needs the rows that already existed. Debezium handles this with an initial snapshot, and the naive version takes a lock and reads the whole table — fine for a small table, an outage for a large one.
Incremental snapshotting reads existing rows in chunks while streaming continues, interleaving the two. It is the right default for any table big enough to care about. The important property is that the snapshot and the stream overlap rather than hand off at a point in time, because a handoff is exactly where a gap hides: rows that changed after the snapshot read them and before the stream started are, at that moment, in neither.
Schema evolution is a design input, not an incident
Somebody will add a column upstream. That is not a risk to mitigate; it is a certainty to plan for.
When it happens, Debezium publishes events with a changed schema. A sink not designed for it does one of two things: reject the write loudly, or accept it and drop the unknown field silently. The loud failure is the good outcome. The silent one means the dashboard keeps rendering, the new column is simply always empty, and nobody notices until someone asks why a metric has been flat since the release.
Decide which of those you want before it happens, and make it explicit.
What I would build differently
I would make the consistency number a continuously running reconciliation job rather than a measurement taken once. The useful version of “99.9%” is not a figure in a report — it is a check that alerts when it starts slipping. Row counts and checksums per key range, compared between source and sink on a schedule, turn a claim into a monitor.
I would also instrument consumer lag from day one. Throughput and error rate both look healthy while a consumer group falls further and further behind, and by the time anyone notices the dashboard is stale, the backlog is large enough that catching up is its own incident.