MongoDB to ClickHouse: How to Replicate Your Data

Replicating MongoDB to ClickHouse means taking documents with no fixed shape and landing them in a store that is fastest when columns are strictly typed. The two systems disagree about schema at a fundamental level, and how you resolve that disagreement is the decision that determines whether your ClickHouse tables are pleasant to query or a pile of JSON strings.

This guide covers the MongoDB prerequisites that stop setups before they start, the two schema modes and which to pick, and the one ClickHouse query habit that keeps your results correct.

MongoDB to ClickHouse at a glance:

Why move MongoDB data into ClickHouse?

MongoDB is built to serve an application: fetch this document, update that one, do it fast and at scale. It is not built to scan four hundred million documents and return a grouped aggregate, and aggregation pipelines that attempt it compete directly with the workload the cluster exists to handle.

ClickHouse is the opposite specialism. Columnar storage makes those aggregates cheap, the analytical load moves entirely off the operational cluster, and analysts get SQL instead of aggregation pipeline syntax. The cost is a copy to maintain and a schema decision to make.

What does MongoDB require before you start?

Most failed setups trace back to one of these, and all of them stop the connection at configuration rather than at runtime.

• A replica set. Airbyte reads changes from MongoDB change streams, which only exist on a replica set. A standalone mongod will not work. On Atlas this means a dedicated M10 tier or above, since shared tiers can fail during setup.
• A user with readAnyDatabase. Since connector version 2.0.0 a single source can sync across multiple databases, and that access needs the readAnyDatabase privilege. Without it the connection fails with an authorization error.
• A consistent _id type. Every document in a collection must use the same data type for _id. Mixed types are unsupported. Views, capped collections, clustered collections, and empty collections are excluded too.
• TLS. Required by the connector, and on by default for Atlas clusters.

One performance note for the setup itself. Because collections are schemaless, the MongoDB source samples documents to infer fields, 10,000 by default and configurable between 1,000 and 100,000. Discovery runs $sample aggregations against every collection in parallel, which puts real pressure on a busy production cluster. Point discovery at a secondary if that is a concern.

Schema-enforced or schemaless: which mode fits?

This is the decision that shapes everything downstream, and it interacts with a ClickHouse setting in a way worth understanding before you commit.

In schema-enforced mode, the default, Airbyte samples documents, builds a field list, and lets you deselect fields you do not need. Top-level fields become typed ClickHouse columns, which is what makes the destination fast. Choose this when your collections are stable enough to model.

In schemaless mode, every document produces a record with just _id and a data object holding the whole document. Nothing is dropped and nothing is rejected, but the unpacking happens in ClickHouse afterwards.

Here is the part that matters. If you pick schemaless, enable the JSON option on the ClickHouse destination. With it on, object fields land as the native JSON type and stay queryable with ClickHouse JSON functions. With it off, that data object becomes a String, and you are parsing text on every query. For a schemaless MongoDB pipeline, that single toggle is the difference between a usable table and an awkward one.

Arrays are the exception in both modes. They always become String regardless of the JSON setting, so array-heavy documents need parsing downstream either way.

How do you configure the ClickHouse destination?

Create a dedicated user rather than reusing an existing one. The ClickHouse destination needs grants to create and manage databases, to create, alter, drop and truncate tables, and to insert and select data. Server 21.8.10.19 or later is required.

If async_insert is enabled on your instance, disable it for the Airbyte user with ALTER USER airbyte_user SETTINGS async_insert = 0; Otherwise the connection check fails with an error about rows not reaching the check table, which gives no hint about the real cause.

Enter the hostname without a protocol prefix, the port (8123 for HTTP or 8443 for HTTPS), the database, and credentials. Then create the connection, select your collections, and choose incremental with CDC to keep ClickHouse current and propagate deletes. For more on change streams and resume tokens, see our MongoDB CDC guide.

Why do your queries need FINAL?

Deduplication uses the ReplacingMergeTree engine, which collapses duplicate rows during background merges rather than at write time. Merges run when ClickHouse decides, not when your sync finishes. Until one completes, a plain SELECT can return duplicated rows, or documents that CDC already deleted in MongoDB.

Add the FINAL operator to guarantee deduplicated results: SELECT * FROM your_table FINAL Skip it and your dashboards will disagree with MongoDB in a way that looks exactly like a broken pipeline.

One MongoDB-side limit is worth planning around: during CDC, MongoDB's 16MB BSON document limit can surface as a BSONObjectTooLarge error when change events carry large documents. The MongoDB troubleshooting guide covers the fix.

Frequently asked questions

Do I need a MongoDB replica set?

Yes. The connector reads from change streams, which only exist on a replica set. Atlas clusters on the M10 dedicated tier or above qualify, and shared tiers may fail at setup.

Should I use schema-enforced or schemaless mode?

Schema-enforced when collections are stable enough to model, because typed columns are what make ClickHouse fast. Schemaless when documents genuinely vary, and in that case enable the JSON option on the destination so nested data stays queryable rather than becoming a String.

Why am I seeing duplicate rows?

Your query is almost certainly missing FINAL. ReplacingMergeTree deduplicates during background merges, so until one runs, duplicates and deleted documents remain visible to a plain SELECT.

Are deletes propagated to ClickHouse?

Yes, when the stream uses incremental CDC. Full refresh re-reads the collection instead and does not track individual deletions.

How do nested documents and arrays land?

Objects become the native JSON type if you enabled that option, and String otherwise. Arrays always become String regardless, so array-heavy documents need parsing with ClickHouse JSON functions either way.

Get your MongoDB data into ClickHouse

Pick a schema mode deliberately, enable the JSON option if you went schemaless, disable async_insert, and remember FINAL. Sending the same collections to a relational database or a cloud warehouse instead? See our guides to MongoDB to PostgreSQL and PostgreSQL to ClickHouse.

Start syncing now →