ClickHouse as a Real-Time Analytical Destination

Most analytics pipelines still run on stale data. A batch ETL job pulls rows out of a transactional database on an hourly or sub-hourly schedule, and by the time anyone refreshes a dashboard, the operational reality it describes has already moved on. Change Data Capture, paired with ClickHouse, closes that gap almost entirely: CDC captures every INSERT, UPDATE, and DELETE at the database log level, and ClickHouse's columnar engine queries billions of rows in milliseconds, so a committed transaction can become a queryable analytical row in under five seconds.

That pairing looks simple from a distance, but it is not a write-through in any straightforward sense. ClickHouse belongs to the MergeTree family of column-oriented databases, and it writes data in immutable sorted parts that background merge operations periodically compact into larger, more efficient parts. This append-only design, the source of ClickHouse's write throughput and compression ratios, turns CDC's continuous stream of updates and deletes into an architectural problem rather than a plumbing exercise. A database designed around immutable parts has no native concept of modifying a row in place.

ClickHouse does provide ALTER TABLE ... UPDATE and lightweight deletes, but both are heavyweight mutations that rewrite entire data parts. They suit infrequent bulk corrections, the kind of cleanup a team runs once a quarter, not a stream of CDC events arriving thousands of times per second from a production Postgres or MySQL instance. Running that volume of individual mutations against ClickHouse would overwhelm the merge process and degrade the query speed that makes ClickHouse worth using. The rest of this piece is about the patterns that resolve that tension: specific table engines, specific query strategies, and specific pipeline designs that let CDC's update-heavy stream coexist with a storage engine that was built to never update anything.

ReplacingMergeTree: the primary table engine for CDC

ReplacingMergeTree is the table engine most teams reach for first, and for good reason: it deduplicates rows that share the same sorting key during background merges. Rather than modifying a row, every CDC event, insert, update, or delete alike, lands as a brand-new row in the table. ClickHouse then collapses those duplicates down to one row per key as part of its ordinary background merge cycle, with no special mutation required.

That collapsing depends on a version column. A table built for CDC typically looks something like this:

CREATE TABLE orders (
    order_id UInt64,
    status String,
    updated_at DateTime,
    _version UInt64,
    _is_deleted UInt8
) ENGINE = ReplacingMergeTree(_version, _is_deleted)
ORDER BY order_id

During a merge, ClickHouse keeps only the row with the highest _version for a given ORDER BY key and discards the rest. The pipeline feeding this table has to populate _version with something monotonically increasing: a Kafka offset, a database LSN, or a millisecond-precision timestamp all work, as long as every later event carries a strictly higher value than the one before it.

Deduplication is not instantaneous. When a new version of a row arrives, the old and new rows sit side by side in the table until a background merge actually runs, so a plain SELECT with no deduplication logic can return both. That is a fundamental characteristic of how MergeTree works rather than a defect, and query patterns have to be built with that in mind from the start. The is_deleted parameter, available since ClickHouse 23.2, gives teams a clean way to handle deletes: rows flagged for deletion are dropped automatically during merges, and combined with FINAL, those deleted rows disappear from query results without any explicit WHERE clause filtering them out.

Teams migrating off Postgres run into a specific trap here. In Postgres, an UPDATE modifies a row in place, and a PRIMARY KEY enforces uniqueness at write time. In ReplacingMergeTree, an UPDATE becomes an INSERT carrying a higher version, and ClickHouse offers no uniqueness enforcement at all, only eventual deduplication during merges. ORDER BY columns are also immutable in practice: whatever column a team designates for sorting must never change on the source side, because ClickHouse has no mechanism for re-sorting a row that already exists in an immutable part.

FINAL vs. argMax: the query-time trade-off teams must resolve before going to production

Because merges happen asynchronously, every ReplacingMergeTree deployment needs an explicit strategy for deduplicating at query time rather than assuming the background process has already done the work. Two options dominate in practice: the FINAL keyword and the argMax() pattern. Each solves the same problem with a different performance profile, and the choice has direct consequences for whether a production dashboard stays fast under load.

FINAL is the more straightforward of the two. Appending it to a query forces ClickHouse to apply deduplication logic at query time and return only the latest version of each row. Doing that requires reconciling all the relevant parts before returning a result, so its cost scales with how many unmerged parts exist at the moment the query runs. On a table that has had time to merge, FINAL adds negligible overhead. On a table in the middle of active ingestion, with many parts still unmerged, that overhead can grow substantially, and a dashboard querying a table with billions of rows during an active ingestion window can become unreliable exactly when it matters most.

The argMax() pattern takes a different route: it selects the row with the highest version for each key using ClickHouse's columnar aggregation engine, which is heavily optimized for this kind of operation. Benchmarks show argMax() queries running 2 to 5 times faster than the equivalent FINAL query on wide tables with many columns. The trade-off is that argMax() requires listing every column explicitly in the query, which adds complexity and a maintenance burden every time the schema changes. Teams should weigh that cost consciously rather than defaulting to one approach out of habit.

For dashboards running against actively-ingesting, wide tables, argMax() is the safer default. FINAL remains appropriate where merge lag is well controlled and the query pattern doesn't demand enumerating every column by hand.

CollapsingMergeTree: when aggregation-heavy workloads or high delete volume justify a different engine

CollapsingMergeTree handles the same underlying problem, updates and deletes in an immutable store, through a different mechanism. For every update event, the pipeline inserts two rows: one with sign = -1 carrying the old values, which cancels the previous state, and one with sign = 1 carrying the new values. ClickHouse collapses these opposite-sign pairs together during merges, leaving a consistent final state without rewriting anything.

The advantage of this model is that aggregations come out correct without requiring FINAL and without the query-time gymnastics that ReplacingMergeTree sometimes demands, provided materialized views are built properly around it. That makes CollapsingMergeTree a strong fit for workloads that are primarily aggregation-heavy, summing revenue across orders or counting active sessions, rather than workloads built around point lookups of individual rows.

The cost appears on the pipeline side. The application feeding CollapsingMergeTree has to track the previous state of each row in order to emit the correct cancellation row, which is additional complexity that ReplacingMergeTree simply doesn't require. A team running an order-status aggregation dashboard with high update frequency, and a CDC source producing a heavy volume of deletes, is a reasonable candidate for CollapsingMergeTree over ReplacingMergeTree: deletes are a first-class part of the model rather than a flag that depends on background merge timing to take effect.

Two pipeline architectures for getting CDC events into ClickHouse

Choosing a table engine only determines how deduplication happens. Teams still have to decide how CDC events physically get from the source database into ClickHouse, and two architectures dominate in practice, each carrying trade-offs independent of whatever engine sits at the destination.

The first routes CDC events through Kafka and consumes them using ClickHouse's built-in Kafka table engine, with a materialized view piping data from that Kafka engine table into the final ReplacingMergeTree or CollapsingMergeTree table. This pattern is appealing because it stays entirely inside ClickHouse, with no external service to operate. It also has real limits: the Kafka engine runs single-threaded per table, error handling is opaque when something goes wrong mid-stream, and schema evolution requires manual DDL changes applied by hand.

The second architecture has an external process or managed service consume CDC events directly and insert them into ClickHouse over HTTP or native TCP. This gives a team far more control over batching, error handling, retries, and schema mapping, at the cost of operating that external layer as its own piece of infrastructure. The single-threaded nature of the Kafka engine path, paired with its opaque error handling, is the kind of limitation that pushes teams toward managed services once CDC volume grows past a trivial scale.

Whichever architecture a team chooses, partition key discipline is not optional. ClickHouse only performs physical merges within a single partition, so if duplicate rows for the same sorting key land in two different partitions, they are never permanently collapsed, no matter how long the table sits idle. A table partitioned by event date, for instance, will silently fail to deduplicate a row whose update arrives a day after its insert, because the update lands in a different partition than the original row. The partitioning key has to be chosen so that it never changes for a given row across its lifetime, guaranteeing that every update for that row routes to the same partition as the original.

Schema evolution: the operational challenge that breaks pipelines built on the Kafka engine path

A schema change at 2 AM, an added column on a production Postgres table, a type change pushed in a routine migration, turns into a pipeline outage the moment a CDC pipeline isn't built to absorb it. Schema drift is one of the hardest operational realities any CDC-to-ClickHouse pipeline has to face, because source schemas change constantly in production and the pipeline has to absorb those changes without breaking or losing data.

Three scenarios account for most of what happens in practice, and each demands its own handling. Adding a column requires the pipeline to detect the new field and create a matching column in the ClickHouse table to receive it. Removing a column is safest handled by ignoring it at the destination silently, since an error that halts replication is worse than a column quietly going unused. Changing a column's type is the trickiest of the three: ClickHouse is strongly typed, so a type change typically requires adding a new column with a suffix identifying the new type, something like ColumnName_new_type, while preserving the original column so historical data stays intact and queryable.

The Kafka engine path handles none of this automatically. Any schema change requires manual DDL changes applied by a human. Every schema drift event on the source side becomes a point where someone has to intervene in the pipeline directly. For a team with an actively evolving source schema, that requirement is not a minor inconvenience, it is an operational load that scales with how often the source changes, and it is the clearest argument against relying on the Kafka engine path as a long-term production architecture. Teams running that path need an explicit schema governance strategy, because the engine itself provides none.

MongoDB adds its own layer on top of this problem. A MongoDB connector has to automatically map nested documents, arrays, and embedded objects into ClickHouse's native JSON data type, while reconciling inconsistencies baked into real-world document data, timestamps stored as numbers in some records and ISO strings in others, or a field that's a plain string in some documents and a nested struct in others. That reconciliation work has to happen continuously, because document shape in MongoDB can vary from one write to the next, not as a one-time migration step.

Source-specific replication mechanics: Postgres, MySQL, and MongoDB connectors in ClickHouse Cloud

ClickHouse Cloud has built native CDC connectors, branded ClickPipes, for its most common source databases, and the maturity of each connector differs enough that teams need to understand what's available before committing to a deployment.

The Postgres connector is furthest along, in public beta as of February 2025. Following ClickHouse's acquisition of PeerDB, the company announced a native Postgres CDC connector built to replicate PostgreSQL databases into ClickHouse Cloud with near real-time data freshness. The connector uses _peerdb_version as the ReplacingMergeTree version column and _peerdb_is_deleted to mark deletes: an update is modeled as an insert carrying a newer version of the row, and a delete is an insert carrying a newer version with the delete flag set to true, which maps directly onto the ReplacingMergeTree pattern described earlier in this piece. Parallel snapshotting gives the connector 10 times faster initial loads than a naive single-threaded snapshot. Production deployments are already running at scale: Syntage migrated a 30TB Aurora database using the connector, and Neon's billing team reports data freshness within seconds and minimal load placed on their production Postgres database.

MySQL's connector sits in private preview as of spring 2025. It's purpose-built for MySQL, supporting both continuous replication and one-time migration regardless of where the source database runs, RDS, Aurora, CloudSQL, or on-premises, with the exception that Azure Flexible Server currently supports one-time migration only. It offers fast backfills during initial load, continuous replication once the backfill completes, and schema change replication, directly addressing the manual DDL burden that weighs down the Kafka engine path.

MongoDB's connector entered private preview in August 2025. It replicates MongoDB collections, whether hosted on Atlas or self-managed, into ClickHouse Cloud, supporting both continuous replication and one-time loads. It uses MongoDB's native Change Streams feature to capture changes, and replication latency runs as low as a few seconds. Nested documents, arrays, and embedded objects get mapped automatically into ClickHouse's native JSON type, handling the document-shape inconsistencies described in the previous section without requiring manual mapping rules.

DynamoDB has no confirmed native ClickPipes connector at this stage. Teams needing to move DynamoDB streams into ClickHouse currently have to build an intermediary path themselves, routing through something like Kinesis into a custom consumer that inserts into ClickHouse over HTTP.

ClickHouse as an analytical destination for AI agents and real-time applications

AI agents and real-time applications need the same thing analytics dashboards need, a current, queryable picture of operational state, but they need it with less tolerance for staleness and more demand for query concurrency, since an agent issuing hundreds of lookups per session behaves nothing like a human refreshing a dashboard every few minutes. ClickHouse's columnar engine, built to scan billions of rows in milliseconds, suits that access pattern directly, and the CDC architecture described throughout this piece is what makes the underlying data trustworthy enough to build on. A ReplacingMergeTree table with a disciplined version column and a well-chosen query strategy, fed through a pipeline that handles schema drift without manual intervention, gives an agent or a real-time application a source of truth that reflects production state within seconds rather than hours.

That reliability is not automatic. It depends on every choice this piece has walked through: the right table engine for the workload, a query-time deduplication strategy matched to ingestion load, a pipeline architecture that doesn't buckle under schema change, and, where available, a native connector that has already solved the hardest parts of that mapping. Teams that get those choices right end up with an analytical store that keeps pace with the operational systems feeding it, the promise of pairing CDC with ClickHouse.