CDC to ClickHouse: Sub-Second Analytics Pipeline

Most analytics pipelines operate on stale data. A batch ETL job extracts rows from your transactional database every hour, maybe every fifteen minutes if you are feeling ambitious, and loads them into an analytical store. By the time a dashboard refreshes, the underlying reality has already shifted. Change Data Capture paired with ClickHouse eliminates that staleness entirely. CDC captures every INSERT, UPDATE, and DELETE at the database log level, and ClickHouse’s columnar engine can query billions of rows in milliseconds. Together, they form a pipeline where a committed transaction becomes a queryable analytical row in under five seconds.

This guide walks through the architecture, table engine choices, query patterns, and operational considerations for building a production CDC-to-ClickHouse pipeline.

ClickHouse’s Storage Model and Why CDC Is Different

ClickHouse belongs to the MergeTree family of column-oriented databases. Data is written in immutable sorted parts, and background merge operations periodically compact those parts into larger, more efficient ones. This append-only design is what gives ClickHouse its extraordinary write throughput and compression ratios.

The critical implication for CDC is that ClickHouse does not natively support row-level UPDATE or DELETE operations in the traditional RDBMS sense. When your source database updates a customer’s email address, you cannot simply issue UPDATE customers SET email = 'new@example.com' WHERE id = 42 in ClickHouse and have it behave the same way. ClickHouse does support ALTER TABLE ... UPDATE and lightweight deletes, but these are heavyweight mutations that rewrite entire data parts. They are designed for infrequent bulk corrections, not for continuous streams of CDC events arriving thousands of times per second.

This means you need to choose a table engine and query pattern that translates CDC’s stream of changes into something ClickHouse can handle efficiently. The two primary choices are ReplacingMergeTree and CollapsingMergeTree.

ReplacingMergeTree for CDC

ReplacingMergeTree is the most common table engine for CDC workloads. It works by deduplicating rows that share the same sorting key (typically the primary key from your source database) during background merges. You insert every CDC event as a new row, and ClickHouse eventually collapses duplicates down to one row per key.

Here is a typical schema for a CDC-fed orders table:

CREATE TABLE orders
(
    id          UInt64,
    customer_id UInt64,
    status      String,
    total       Decimal(18, 2),
    updated_at  DateTime64(3),
    _version    UInt64,
    _deleted    UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(_version)
ORDER BY id;

The _version column is essential. When ClickHouse merges parts, it keeps only the row with the highest _version for each unique ORDER BY key. Your CDC pipeline should populate _version with a monotonically increasing value, such as the Kafka offset, a database log sequence number (LSN), or a millisecond-precision timestamp.

How Deduplication Actually Works

It is important to understand that deduplication is not instant. When you insert a new version of a row, both the old and new versions coexist in ClickHouse until a background merge runs. Merges happen asynchronously, and ClickHouse decides when to run them based on part sizes, part counts, and available resources. This means that at any given moment, a simple SELECT * FROM orders WHERE id = 42 might return multiple rows.

This is not a bug. It is a fundamental characteristic of the MergeTree architecture, and your query patterns need to account for it.

The FINAL Keyword

The simplest way to get deduplicated results is the FINAL keyword:

SELECT * FROM orders FINAL WHERE customer_id = 1001;

FINAL forces ClickHouse to apply the deduplication logic at query time, returning only the latest version of each row. The trade-off is performance. FINAL triggers an in-memory merge across all parts, which can slow queries significantly on large tables. For dashboards hitting tables with billions of rows, this overhead may be unacceptable.

The argMax() Alternative

For better performance on large datasets, use argMax() aggregation to select the row with the highest version for each key:

SELECT
    id,
    argMax(customer_id, _version) AS customer_id,
    argMax(status, _version)      AS status,
    argMax(total, _version)       AS total,
    argMax(updated_at, _version)  AS updated_at,
    argMax(_deleted, _version)    AS _deleted
FROM orders
WHERE customer_id = 1001
GROUP BY id
HAVING _deleted = 0;

This approach lets ClickHouse use its columnar aggregation engine, which is heavily optimized. In benchmarks, argMax() queries frequently run 2-5x faster than the equivalent FINAL query on wide tables with many columns.

CollapsingMergeTree Alternative

CollapsingMergeTree takes a different approach. Instead of keeping the latest version, it cancels out rows using a sign column. An insert has sign = 1, and a cancellation (preceding an update or representing a delete) has sign = -1.

CREATE TABLE orders_collapsing
(
    id          UInt64,
    customer_id UInt64,
    status      String,
    total       Decimal(18, 2),
    updated_at  DateTime64(3),
    sign        Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY id;

For each CDC update event, your pipeline inserts two rows: one with sign = -1 carrying the old values (to cancel the previous state) and one with sign = 1 carrying the new values. During merges, ClickHouse collapses pairs of rows with opposite signs.

CollapsingMergeTree shines when your queries are aggregation-heavy. You can compute sums, counts, and averages directly over the sign column:

SELECT
    status,
    sum(total * sign) AS total_revenue,
    sum(sign)         AS order_count
FROM orders_collapsing
GROUP BY status;

The downside is complexity. Your CDC pipeline must emit the previous row values alongside the new ones, which requires either storing previous state in the pipeline or receiving it from the CDC source. For most teams, ReplacingMergeTree is the simpler and more practical choice.

Handling Deletes

Deletes are the trickiest part of any CDC-to-ClickHouse pipeline. Since ClickHouse cannot physically remove a single row on demand, you need a strategy.

Soft Deletes with a Flag Column

The most common pattern is a _deleted flag. When your CDC source emits a DELETE event, your pipeline inserts a new row with _deleted = 1 and a higher _version. Every query includes WHERE _deleted = 0 or HAVING _deleted = 0 in the argMax() pattern.

ReplacingMergeTree with is_deleted (ClickHouse 23.2+)

Starting with version 23.2, ReplacingMergeTree accepts a second parameter for automatic delete filtering:

CREATE TABLE orders
(
    id          UInt64,
    customer_id UInt64,
    status      String,
    total       Decimal(18, 2),
    updated_at  DateTime64(3),
    _version    UInt64,
    _deleted    UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(_version, _deleted)
ORDER BY id;

With this engine configuration, rows where _deleted = 1 are automatically removed during merges. When combined with FINAL, deleted rows are excluded from results without needing an explicit WHERE clause. This is the cleanest approach available today.

CollapsingMergeTree Approach

In CollapsingMergeTree, deletes are simply a row with sign = -1 matching the last known state. The merge process cancels out the positive and negative rows, and the record effectively vanishes.

Architecture Patterns

There are two dominant architectures for getting CDC data into ClickHouse.

CDC to Kafka to ClickHouse Kafka Engine

The first pattern routes CDC events through Kafka, then uses ClickHouse’s built-in Kafka table engine to consume them:

CREATE TABLE orders_kafka
(
    id          UInt64,
    customer_id UInt64,
    status      String,
    total       Decimal(18, 2),
    updated_at  DateTime64(3),
    _version    UInt64,
    _deleted    UInt8
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'broker1:9092,broker2:9092',
    kafka_topic_list = 'cdc.public.orders',
    kafka_group_name = 'clickhouse_orders',
    kafka_format = 'JSONEachRow';

A materialized view then pipes data from the Kafka engine table into the final ReplacingMergeTree table. This pattern is self-contained within ClickHouse, but the Kafka engine has limitations: it runs single-threaded per table, error handling is opaque, and schema evolution requires manual DDL changes.

CDC to Direct HTTP Insert

The second pattern uses an external process or managed service to consume CDC events and insert them into ClickHouse via the HTTP or native TCP interface. This gives you more control over batching, error handling, retries, and schema mapping. Platforms like Streamkap use this approach, managing the full pipeline from CDC capture through to ClickHouse delivery with built-in handling for ReplacingMergeTree schema mapping, version column management, and delete semantics.

Schema Mapping

Mapping source database types to ClickHouse types requires attention. ClickHouse’s type system is strict and does not always have a one-to-one correspondence with PostgreSQL, MySQL, or MongoDB types.

Nullable Considerations

ClickHouse Nullable columns carry a performance penalty. Each Nullable column adds an internal bitmask array to track null states, which increases storage and slows queries. If your source column has a sensible default, prefer using a default value over Nullable. For example, use String with a default of '' rather than Nullable(String) when empty strings are semantically equivalent to null in your domain.

Query Patterns for CDC Data

Beyond FINAL and argMax(), there are practical patterns worth knowing.

Materialized Views for Pre-Aggregation

If you always query the same aggregation, a materialized view can maintain the result incrementally:

CREATE MATERIALIZED VIEW orders_by_status
ENGINE = SummingMergeTree()
ORDER BY status
AS
SELECT
    status,
    count()     AS order_count,
    sum(total)  AS total_revenue
FROM orders FINAL
WHERE _deleted = 0
GROUP BY status;

This precomputes the aggregation at insert time, giving you instant dashboard queries.

Handling Eventual Consistency

In practice, the window between insert and merge is short, often under a minute. But your application should not assume instant deduplication. Use FINAL or argMax() in any query that must return exactly one row per key. For approximate analytics such as trend lines and histograms, skipping FINAL and accepting occasional duplicates may be an acceptable trade-off for significantly faster queries.

A useful technique is to use OPTIMIZE TABLE orders FINAL after a bulk backfill to force an immediate merge. Do not run this on a schedule during normal CDC streaming, as it is expensive and blocks other merges.

Performance Tuning

Batch Insert Size

ClickHouse performs best with inserts of 1,000 to 100,000 rows per batch. Inserting one row at a time creates a new data part for every row, which quickly overwhelms the merge scheduler. If your CDC events arrive individually, buffer them in your pipeline and flush at regular intervals or when the buffer reaches a threshold. A flush interval of 1-5 seconds with a minimum batch of 1,000 rows is a solid starting point.

ClickHouse’s async insert feature (async_insert = 1) can handle this server-side, buffering small inserts and flushing them together:

SET async_insert = 1;
SET wait_for_async_insert = 0;
SET async_insert_max_data_size = 10000000;  -- 10 MB
SET async_insert_busy_timeout_ms = 2000;     -- 2 seconds

Partition Keys

Partitioning by date is the most common strategy for CDC tables:

ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(updated_at)
ORDER BY id;

Partitioning controls how data parts are organized on disk. ClickHouse only merges parts within the same partition, so merges stay bounded and efficient. Avoid over-partitioning. If you partition by day and retain a year of data, you will have 365 partitions, each with independent merge schedules. Monthly partitions are usually the right granularity.

ORDER BY Optimization

The ORDER BY clause in a MergeTree table is not just a sort order. It defines the primary index. ClickHouse uses this index to skip entire granules (blocks of 8,192 rows by default) during queries. Place the columns you filter on most frequently first:

ORDER BY (customer_id, id)

This makes WHERE customer_id = X queries extremely fast because ClickHouse can skip all granules that do not contain that customer. If your most common query pattern is by id, put id first.

Monitoring

A CDC-to-ClickHouse pipeline has several failure modes that you should monitor continuously.

Part Count and Merge Health

Query the system.parts table to track the number of active parts per table:

SELECT
    table,
    count()          AS part_count,
    sum(rows)        AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS disk_size
FROM system.parts
WHERE active AND database = 'default'
GROUP BY table
ORDER BY part_count DESC;

If the part count for a table climbs steadily, merges are falling behind inserts. This leads to degraded query performance and, eventually, the dreaded “too many parts” error which rejects new inserts. Alert when part count exceeds 300 per partition.

Insert Lag

Measure the delay between the CDC event timestamp and the ClickHouse insert timestamp. If your pipeline adds metadata columns (such as _ingested_at), you can compute lag directly:

SELECT
    max(_ingested_at) - max(updated_at) AS current_lag
FROM orders;

A healthy pipeline shows lag under 5 seconds. Lag above 30 seconds typically indicates a bottleneck in the CDC consumer, the network path, or ClickHouse itself.

Merge Rate and Duration

The system.merges table shows active merges:

SELECT
    table,
    elapsed,
    progress,
    num_parts,
    formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges
ORDER BY elapsed DESC;

Long-running merges (over 10 minutes) or a consistently full merge queue signal that you may need to increase max_threads for merges or revisit your partitioning strategy.

Consumer Group Lag (Kafka)

If you use Kafka in your architecture, monitor consumer group lag for the ClickHouse consumer. Tools like Kafka’s consumer-groups.sh or Streamkap’s built-in monitoring provide visibility into how far behind the consumer is from the latest offset. Rising lag means your ClickHouse insert rate cannot keep up with the CDC event rate.

Building a CDC-to-ClickHouse pipeline is not inherently complex, but it does require deliberate choices about table engines, query patterns, and operational monitoring. ReplacingMergeTree with a version column and soft deletes covers the vast majority of use cases. Pair it with batched inserts, sensible partitioning, and active monitoring of part counts and merge health, and you have a pipeline that delivers sub-second analytical queries over continuously fresh data.