ClickHouse to Amazon Redshift: How to Move Your Data

Moving ClickHouse into Amazon Redshift is a pipeline between two columnar analytical engines, which means the usual mismatches do not apply. Nothing needs flattening, wide tables are fine at both ends, and there is no volume ceiling to design around.

That leaves two things worth your attention, and one of them originates entirely in ClickHouse. Depending on which table engine you read from, the source table may hold duplicate rows at the moment your sync runs, and those duplicates are copied into Redshift where nothing will ever collapse them. This guide covers the managed path with Airbyte.

ClickHouse to Amazon Redshift at a glance:

Why move data from ClickHouse to Amazon Redshift?

Two situations account for most of these pipelines, and neither is about ClickHouse being inadequate.

The first is consolidation. Redshift is the analytical platform the rest of the business already uses, the BI tools point at it, and ClickHouse holds one high-volume dataset that needs to join everything else. Rather than teaching every reporting tool to speak to two engines, you replicate one into the other.

The second is governance and access. Analysts already have Redshift credentials and know how it behaves, and giving a wider group direct access to a ClickHouse cluster serving production queries is not always welcome. A replicated copy separates the two audiences.

What do you need before you start?

The Redshift side has a hard prerequisite, and the ClickHouse side has a question only you can answer:

An S3 staging bucket. The Redshift destination writes data to S3 as files with a manifest, then issues a COPY command. That is Redshift's own recommended approach rather than an Airbyte quirk, so you cannot configure this connector without one. Put it in the same AWS region as the cluster.

IAM permissions on that bucket. Put, get, delete and list. The delete matters because staging files are cleaned up after a successful COPY unless you turn purging off.

Knowledge of your ClickHouse table engines. Specifically whether the tables you are reading use an engine that deduplicates during background merges. This decides whether you sync the table or a view over it, and it is the difference between correct and quietly wrong. The ClickHouse source documentation covers the connection options, including SSL and SSH tunnel support.

Redshift entities created in advance. A database, a schema and a user with rights to write data and manage staging operations. Airbyte writes into two schemas, the one you nominate and a raw data schema it manages, so tell whoever reviews your Redshift objects that the second one is expected.

If you were planning to route this through an SSH tunnel, note that the tunnel covers the SQL connection only. Staging traffic goes to S3 over HTTPS regardless, so a network design assuming everything passes through a bastion is wrong.

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

Step 1: Check your table engines, then build views if needed

For each table you intend to replicate, check which engine it uses. If any deduplicate on merge, create a view over them that applies the FINAL operator and sync the view instead of the table. This costs query performance on the ClickHouse side and it is the price of not copying duplicates into a system that cannot remove them.

Step 2: Configure the ClickHouse source

Click Sources in the left navigation, then New Source, and select ClickHouse, following adding a source. Supply the host, port, database and credentials, and enable SSL or configure an SSH tunnel if your instance sits behind a bastion. Namespaces are supported and enabled by default, so your ClickHouse database structure carries across rather than flattening.

Step 3: Configure the Redshift destination

Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster host, database, schema and credentials, along with your S3 bucket and IAM key. Staging files are removed after a successful COPY by default, which is usually what you want, and can be retained if you need them for debugging.

Step 4: Nominate cursors and schedule

Click Connections, then New connection, select your tables or views, and nominate a cursor field so you can use an incremental sync mode. The connector only offers incremental where at least one column can serve as a cursor, so a table with no incremental option means no suitable column exists rather than a connector limitation.

Set frequency against how often the source data actually changes rather than how fresh anyone would like it. Reading a large ClickHouse table repeatedly consumes resources on a cluster that is probably also serving queries, and applying FINAL in a view makes each of those reads more expensive than it would otherwise be.

Why might your source table contain duplicates?

Some ClickHouse table engines, notably ReplacingMergeTree, deduplicate rows during background merges rather than on write. Between a write and a merge, the table genuinely holds more than one version of a row, and that is by design rather than a fault.

Querying such a table without FINAL returns those pre-merge duplicates. A pipeline reading it is doing exactly that kind of query, so whatever duplicates exist at sync time are copied faithfully into Redshift, where nothing will ever collapse them because Redshift has no equivalent background merge.

This is worse than the equivalent problem inside ClickHouse. There, an analyst who sees duplicates can add FINAL and get the right answer. In Redshift the duplicate rows are simply data, indistinguishable from legitimate ones, and correcting them means identifying and deleting them after the fact. Handle it at the source with a view rather than downstream.

What does the cursor miss?

Incremental sync here is cursor-based, and the connector's feature table lists both incremental deletes and log-based replication as coming soon. So today a row removed from ClickHouse persists in Redshift indefinitely, and nothing reports the discrepancy.

On this particular source that matters less than it would elsewhere, because analytical tables in ClickHouse tend to be append-oriented: events, metrics, aggregates recomputed rather than edited. If your table is rebuilt each night rather than mutated in place, there are no deletions to miss.

The same applies to updates that leave the cursor untouched. Where rows do change without advancing the cursor, or genuinely leave the result set, a periodic full refresh on that table is the practical remedy. On a columnar source and a columnar destination that is expensive but not prohibitive, which is one of the few places this pairing is more forgiving than most.

Frequently asked questions

Why does my Redshift table have duplicate rows?

Your ClickHouse source table probably uses an engine that deduplicates during background merges. Reading it without FINAL returns pre-merge duplicates, which are then copied permanently. Sync a view that applies FINAL instead.

Do I need an S3 bucket for this?

Yes. The Redshift destination stages data in S3 and loads it with COPY, which is Redshift's recommended approach. There is no direct insert path.

Does the ClickHouse source support change data capture?

Not currently. Incremental sync is cursor-based, and both incremental deletes and log-based replication are listed as coming soon on the connector.

Why is there an extra schema in my Redshift cluster?

Airbyte writes to your target schema and to a raw data schema it manages to improve reliability. Both are expected.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. You will want ClickHouse views applying FINAL where your table engines need it, which is short SQL and the most important part of the build.

Get your ClickHouse data into Amazon Redshift

Check your table engines first and sync views with FINAL where they deduplicate on merge, because that is the one thing this pipeline gets wrong silently. Then set up the staging bucket in the right region, expect the second schema, and set frequency against how often the data actually changes.

Airbyte's connector catalog includes 700+ pre-built connectors, so the same ClickHouse source can feed a lakehouse alongside the warehouse. For that pairing, see ClickHouse to Databricks, and for another warehouse landing in Redshift, BigQuery to Amazon Redshift.

Start syncing now →