Sub-second MySQL to ClickHouse replication

The problem

Loan risk analysis is only as current as the data under it. The analytical dashboards needed operational data from MySQL, but MySQL is a transactional store — row-oriented, tuned for point lookups and writes, and actively hostile to the wide aggregate scans analytics asks for. Running those queries against the primary competes with the transactions the business actually runs on.

The conventional answer is a periodic batch extract. It fails in two directions at once. The window is always wrong — short enough to be current means hammering the source constantly; long enough to be cheap means the dashboard shows an hour-old view of risk. And batch extracts are query-driven, so they see only end state: a row updated three times between runs looks like one change, and a row deleted is simply absent with no record of when.

Constraints

Analytical queries could not run against the transactional primary. The dashboards needed data fresh enough for real-time risk decisions, not hourly snapshots. More than a million records had to arrive intact — a risk dashboard that is quietly missing rows is worse than one that is visibly down.

Architecture

The pipeline reads MySQL’s binary log through Debezium rather than querying the database. This is the decision everything else follows from. The binlog is already being written for replication and crash recovery, so reading it adds no query load to the primary, and it is an ordered record of every change — including deletes and every intermediate update that a batch extract would collapse.

Debezium publishes those change events to Apache Kafka, which decouples capture from delivery. The source does not wait on the sink. If ClickHouse is slow, restarting or briefly unavailable, events accumulate in the log and the consumer resumes from its offset — no backpressure reaches MySQL, and no changes are lost to a window that closed during an outage.

ClickHouse is the sink because the workload is analytical: column-oriented storage reads only the columns a query touches, which is what makes aggregate scans over a million-plus rows fast enough to put behind an interactive dashboard.

The consistency problem nobody mentions

Change data capture delivers at-least-once. Retries, consumer restarts and rebalances all mean a change event can arrive twice, and in an append-oriented analytical store a duplicate is not corrected — it is counted. In a risk dashboard, double-counted exposure is the specific failure you cannot ship.

So the sink has to be idempotent with respect to the source’s primary key: the pipeline must converge on one row per key regardless of how many times its change events were delivered. The measured 99.9% data consistency is a statement about that reconciliation holding, not about whether events arrived.

Results

More than 1 million records replicate from MySQL to ClickHouse at sub-second latency with 99.9% consistency. The loan-risk dashboards read from an analytical store built for their query shape, and the transactional database never sees an analytical query.

What I would do differently

I would treat schema evolution as a design input rather than an incident waiting to happen. A column added upstream flows through Debezium as a changed event schema, and a sink that was not designed for it either rejects the write or silently drops the field — and silently is the dangerous one, because the dashboard keeps rendering. I would also build the consistency check as a continuously running reconciliation job rather than a measurement taken once, since the useful version of “99.9%” is the one that alerts when it starts slipping.