Amazon Redshift to ClickHouse: How to Move Your Data
Move Amazon Redshift into ClickHouse with Airbyte. Why the second analytical database needs a stated job, and why the copy drifts without deletions.
Moving Amazon Redshift into ClickHouse puts analytical data somewhere built for latency rather than for governance. Both are columnar stores, so this is rarely about capability and usually about serving something that needs answers in milliseconds at high concurrency.
This guide covers the managed path with Airbyte. Two things shape the build: having two analytical databases needs a reason you can state, and cursor-based replication means the second one drifts from the first.
Amazon Redshift to ClickHouse at a glance:
Why move data from Amazon Redshift to ClickHouse?
Two situations account for most of these pipelines, and they lead to different pipelines.
The first is a serving layer. Redshift stays the governed warehouse and ClickHouse carries a subset that something latency-sensitive reads, typically a customer-facing analytics feature or a dashboard hundreds of people open at once.
The second is migration, where the cluster is being retired. If the destination you are moving to is another governed warehouse rather than a speed layer, Amazon Redshift to Snowflake is the comparison worth reading.
What do you need before you start?
Four things, and the first shapes everything after it:
A stated reason for the second database. Serving or migrating, because a serving layer is a narrow subset kept current while a migration is everything, once, with a cutover.
A cursor column per table you want incrementally. The source relies on a column you nominate, so a table without a reliable updated timestamp can only be fully refreshed. The Redshift source documentation covers cursors and primary keys.
A ClickHouse database and a sorting key per table. Chosen from the queries your serving layer will run, which are usually narrower and more repetitive than the ones Redshift answers.
A plan for deletions. They do not travel, so if rows are removed in Redshift you need a reconciliation rather than an assumption.
If either system restricts traffic by IP, add the Airbyte Cloud IP addresses to the allow lists before you begin.
How do you build an Amazon Redshift to ClickHouse pipeline in Airbyte?
Step 1: Decide whether this is a subset or everything
Settle whether you are building a serving layer or moving off the cluster, because the two want opposite selections. A serving layer takes the handful of tables something reads constantly, aggregated where possible, and keeps them current. A migration takes everything and ends. Trying to do both at once produces a copy that is too broad to be fast and too partial to replace anything.
Step 2: Configure the Redshift source
Click Sources in the left navigation, then New Source, and select Redshift, following adding a source. Supply the cluster details, database and credentials, then nominate a cursor column per table. Consider syncing a view that aggregates rather than the raw table, since a serving layer rarely needs the grain a warehouse keeps.
Step 3: Configure the ClickHouse destination
Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply the host, port, database and credentials. Records land in typed columns over the native protocol, and the types settled at creation are the ones you keep.
Step 4: Create the connection and sort for the new job
Click Connections, then New connection, select your streams and a sync mode. Then set sorting keys from the queries this copy will serve rather than from how the Redshift tables were designed, since the two are answering different questions.
Then schedule the reconciliation, because nothing will tell you the two have drifted apart.
Why keep two analytical databases?
Because they are good at different things despite both being columnar. Redshift is where an organisation governs its warehouse, integrates subject areas and lets analysts ask unpredictable questions. ClickHouse is where a known query runs in milliseconds for hundreds of concurrent users, which is a different job entirely.
Customer-facing analytics is the clearest case. A product showing every account its own usage dashboard generates concurrency a warehouse was never sized for, and each of those queries is narrow and repetitive, which is exactly what a sorted table serves well. The warehouse remains the source of truth and the serving layer is derived from it.
What does not justify it is preferring the newer tool. Two analytical databases mean two sets of definitions, two places a number can be computed differently and a pipeline somebody maintains, so the second one needs a job the first genuinely cannot do. If you cannot name that job, this is a migration you have not committed to rather than an architecture.
How far apart will the two drift?
Further every week, because cursor-based replication never sees a deletion. A row removed from Redshift simply stops being returned, nothing instructs the copy to remove it, and your serving layer keeps showing something the warehouse no longer holds.
That matters more for a serving layer than for an archive, since the divergence is visible to whoever is reading the product. A customer seeing a record that was corrected or removed upstream is a support ticket, and nobody looking at either system will spot it without being told to check.
So reconcile deliberately. A periodic full refresh of the serving tables is usually simplest, since they are a subset by design and rebuilding them is cheap, and it removes the question entirely rather than managing it. Where that is too heavy, compare row counts and identifiers on a schedule so somebody knows before a customer does.
Frequently asked questions
Will deletions reach ClickHouse?
No. Replication is cursor based and sees inserts and updates only, so periodically refreshing the serving tables is the simplest remedy.
Should I copy my Redshift sort keys?
No. Choose sorting keys from the queries this copy serves, which are usually narrower and more repetitive than the ones the warehouse answers.
Why can a table not sync incrementally?
It has no column usable as a cursor. You nominate that column, so a table without a reliable updated timestamp can only be fully refreshed.
Should I move raw tables or aggregates?
For a serving layer, usually aggregates. Sync a view that computes what the product displays rather than the grain a warehouse keeps for unpredictable questions.
Can I do this without writing code?
The pipeline, yes. The aggregating views in Redshift and the sorting keys in ClickHouse are SQL, and they are what makes this a serving layer rather than a copy.
Get your Amazon Redshift data into ClickHouse
Name the job the second database is doing, because two analytical stores mean two sets of definitions and the newer one has to earn that. If it is a serving layer, take a narrow subset and aggregate it in Redshift before it moves. Then sort for the queries this copy answers rather than inheriting the warehouse design, and reconcile on a schedule since deletions never travel.
Airbyte's connector catalog includes 600+ pre-built connectors, so a warehouse can feed the systems that serve from it. For the same source into an operational database, see Amazon Redshift to PostgreSQL, and for the same source into another warehouse, Amazon Redshift to BigQuery.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.