CockroachDB to ClickHouse: How to Move Your Data

Moving CockroachDB into ClickHouse takes data from a distributed transactional database and puts it somewhere built for analytical queries at speed. CockroachDB is good at surviving node failures and spanning regions, and none of that helps the analyst who wants to aggregate across a billion rows.

This guide covers the managed path with Airbyte. One assumption to clear up first: CockroachDB speaks the PostgreSQL wire protocol, so people expect log-based replication to work. It does not, and the pipeline you build should reflect that rather than fight it.

CockroachDB to ClickHouse at a glance:

Why move data from CockroachDB to ClickHouse?

Two situations account for most of these pipelines.

The first is analytical load. Aggregations across large tables are expensive on a cluster sized for transactional work, and running them during business hours makes a distributed database slower for the application it exists to serve. Moving the data puts that work on separate compute built for it.

The second is speed and cost of analysis. ClickHouse is very fast at exactly the queries CockroachDB is slow at, and cheap to run for the volumes involved. If your analytical needs are modest and served by a few indexed queries, this adds a system for little gain.

What do you need before you start?

Most of the trouble with this connector is set up on day one and discovered months later:

A read-only SQL user. Created in CockroachDB with SELECT on the schema you intend to replicate, rather than reusing an application account.

Default privileges for future tables. Run ALTER DEFAULT PRIVILEGES for that schema as well. Without it the grant covers only tables that existed the moment you ran it, so anything created afterwards is invisible to the sync user. Nothing errors, new tables simply never appear in the catalogue, and nobody notices until someone asks why a table is missing.

A cursor column per table. Incremental sync needs a column that only ever increases and is set on every write. For ClickHouse it should also be a timestamp or numeric type rather than a string, because deduplication orders duplicates by it.

One source per schema. The connector is scoped to a single schema per source configuration, so data spanning several schemas means several sources. Each then gets its own schedule and its own failure blast radius, which is a mild consolation.

On the ClickHouse side you need an instance and a user able to create tables in the target database. If it restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

How do you build a CockroachDB to ClickHouse pipeline in Airbyte?

Step 1: Create the user and both grants

Create the read-only user, grant SELECT on the target schema, then run ALTER DEFAULT PRIVILEGES for the same schema so future tables are covered. Doing both in one session is the highest-value five minutes in this build, because retrofitting the second grant works only after somebody has noticed the gap.

Step 2: Configure the CockroachDB source

Click Sources in the left navigation, then New Source, and select CockroachDB, following adding a source. Supply the host, port, database, schema and the credentials from step 1. Repeat once per schema if your data spans several.

Step 3: Configure the ClickHouse destination

Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply host, port, database and credentials. The destination writes through ClickHouse's native protocol into typed columns and uses the ReplacingMergeTree engine for deduplication.

Step 4: Create the connection and nominate cursors

Click Connections, then New connection, select your tables, nominate a cursor field for each and choose a sync mode. Use a timestamp cursor where you can, because a string cursor makes ClickHouse fall back to Airbyte's extraction timestamp for deduplication ordering, which does not necessarily reflect the natural order of your data. The sync logs warn about this.

Run the first sync and check the resulting tables, because a badly chosen cursor shows itself most cheaply then rather than after a month of accumulated data.

Why can't Airbyte use CDC with CockroachDB?

Wire compatibility is about the conversation between client and server, not the storage engine underneath. PostgreSQL exposes its write-ahead log through logical replication slots, and that surface is what change data capture attaches to. CockroachDB, being a different database that answers the same protocol, has no equivalent for Airbyte to read.

CockroachDB does have changefeeds of its own, which is why this trips people up more than it should. They are a real feature and they are not what the Airbyte source reads, so their existence does not give you delete capture through this connector.

So two things will not reach ClickHouse. Rows updated without touching the cursor column are never picked up again, and rows deleted in CockroachDB persist indefinitely, because a query filtered on a cursor cannot observe an absence.

How should you handle deletes and duplicates?

For analytics, missing deletes often cost you nothing. If the tables you care about are append-oriented, orders placed, events recorded, payments taken, there are no deletions to miss and cursor-based replication is entirely adequate.

Where deletions do happen, a soft-delete column in CockroachDB that your application sets instead of issuing a DELETE is the clean answer, with the filtering done in ClickHouse. The blunt answer is a periodic full refresh on the affected tables, which on a columnar destination is expensive but not prohibitive.

Separately, expect duplicates to appear briefly. ReplacingMergeTree collapses them during background merges, which happen when ClickHouse decides rather than when your sync finishes, so a query run shortly after a sync can return rows that the same query returns cleanly an hour later. Add the FINAL operator where exact counts matter, and put it in a view so analysts do not have to remember.

Frequently asked questions

Can I use CDC from CockroachDB with Airbyte?

No. The CockroachDB source is cursor-based only. CockroachDB's own changefeeds are not what the connector reads, so it cannot replicate deletes or catch updates that leave the cursor untouched.

Why does my sync miss tables I created recently?

Almost always the missing ALTER DEFAULT PRIVILEGES step. Grant it for the schema, then refresh the source schema in Airbyte so the new tables appear in the catalogue.

Why does my ClickHouse table contain duplicate rows?

ReplacingMergeTree removes duplicates during background merges rather than immediately. Query with FINAL if you need guaranteed deduplication before those merges run.

What happens to rows deleted in CockroachDB?

They remain in ClickHouse. Use a soft-delete flag in the source and filter downstream, or schedule a periodic full refresh on the tables where deletions actually occur.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. You will want ClickHouse views using FINAL for correctness-critical queries, which is short SQL.

Get your CockroachDB data into ClickHouse

Fix the default privileges once, nominate a timestamp cursor on every table so deduplication orders correctly, decide deliberately what a deletion should mean, and teach your analysts what FINAL does before they report duplicate rows as a bug.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same source can feed a relational destination too. For that pairing, see CockroachDB to PostgreSQL, and for ClickHouse as a source rather than a destination, ClickHouse to MySQL.

Start syncing now →