Amazon Redshift to Amazon Redshift: How to Move Your Data

Moving Amazon Redshift into Amazon Redshift means copying between two clusters, usually during a migration. Organisations split a cluster, merge two after an acquisition, move between accounts or regions, or shift from provisioned to serverless, and all of those need data in two places for a while.

This guide covers the managed path with Airbyte, and the first thing to say is that a pipeline is often not the right tool for this. AWS has its own mechanisms for cluster copies, and they beat a pipeline comfortably for a straight like-for-like move.

Amazon Redshift to Amazon Redshift at a glance:

Why move data from Amazon Redshift to Amazon Redshift?

One situation genuinely suits a pipeline, and it is narrower than it first appears.

The good case is a selective or reshaping move. Copying some schemas rather than all of them, merging two clusters into one, or landing tables with a different physical design on the target are things a snapshot cannot do, because a snapshot restores what you had rather than what you want.

The poor case is a straight copy of everything, where AWS's own snapshot and restore is faster, cheaper and less work. Use it. And if the real intention is leaving Redshift rather than moving within it, Amazon Redshift to Snowflake is the guide you want instead.

What do you need before you start?

Four things, and the first decides whether you should be here at all:

A reason that a snapshot cannot satisfy. Selectivity, reshaping or a period of parallel running. If you want an identical copy of everything as of a point in time, close this and use AWS's restore instead.

A cursor column per table you want incrementally. The source relies on a user-defined column such as an updated timestamp, and a table without one can only be fully refreshed. The Redshift source documentation covers cursors and primary keys.

An S3 bucket in the target cluster's region. Loading goes through S3 staging, and a bucket in the wrong region adds transfer cost and latency to every sync of a migration that is already moving a lot of data.

A physical design for the target. Sort and distribution keys do not travel with the data, so the new cluster's tables need those chosen rather than inherited, which is an opportunity as much as a task.

If either cluster restricts traffic by IP, add the Airbyte Cloud IP addresses to the relevant allow lists before you begin.

How do you build an Amazon Redshift to Amazon Redshift pipeline in Airbyte?

Step 1: Confirm a pipeline is the right tool

Ask what a snapshot restore would fail to give you, and answer honestly. If the answer is nothing, a restore is faster and involves no cursors, no staging bucket and no reconciliation. If the answer is that you need a subset, a different shape on the target, or both clusters live at once, a pipeline earns its place and the rest of this guide applies.

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 for each table you want incrementally, and a primary key for any table you want deduplicated.

Step 3: Configure the Redshift destination

Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the target cluster details and your S3 staging bucket. Name the two connections clearly, because a source and destination of the same type in one workspace is genuinely easy to confuse at three in the afternoon.

Step 4: Create the connection and plan the cutover

Click Connections, then New connection, select your streams and a sync mode. Then schedule vacuum and analyze on the target, and agree a cutover date, because the longer two clusters run in parallel the more they diverge for the reason below.

Watch the load on the source cluster too, since it is presumably still serving whoever has not moved yet.

Why not just restore a snapshot?

For a like-for-like copy you should, and saying otherwise would be dishonest. AWS's snapshot and restore mechanism produces a complete cluster from a point in time with none of the machinery this guide describes, and for the common case of moving a cluster somewhere else unchanged it is the obvious answer.

What a restore cannot do is change anything on the way. It brings the whole cluster rather than three schemas, it recreates the physical design you already had rather than the one you decided you wanted, and it cannot merge two clusters into one because a restore creates a cluster rather than adding to an existing one.

A pipeline is therefore the right tool when the migration is also a redesign, or when the target already holds data you intend to keep. Consolidating after an acquisition is the clearest example, since you are adding tables to a live cluster and choosing new sort and distribution keys because the combined query patterns differ from either original.

How do two clusters drift while both are running?

Through deletions, which never arrive. Replication here is cursor based, so the connector finds rows whose cursor has moved past the last sync and brings those. A row removed from the source has no updated timestamp to move, so it stays on the target indefinitely and the two clusters disagree by a little more each week.

During a migration that matters more than it usually would, because the entire point is that the target should eventually be trustworthy enough to replace the source. A divergence that nobody is tracking is exactly the thing that surfaces on cutover day, when somebody compares a total and finds the new cluster reporting more than the old one.

So keep the parallel period short, and reconcile deliberately rather than hoping. Compare row counts per table between clusters on a schedule, full refresh the tables where deletions actually happen, and set a cutover date early. A pipeline of this kind is a bridge rather than an arrangement, and the longer it stands the more it costs to trust.

Frequently asked questions

Should I use a pipeline or a snapshot restore?

A restore for a complete like-for-like copy. A pipeline when you need a subset, a different physical design on the target, or both clusters running at once.

Do my sort and distribution keys come across?

No. The target's physical design is yours to choose, which is one of the better reasons to use a pipeline rather than a restore if your query patterns have changed.

Will deletions on the source reach the target?

No. Replication is cursor based, so removed rows persist on the target. Reconcile counts per table and full refresh where deletions are common.

Which tables can sync incrementally?

Those with a usable cursor column that you nominate, such as an updated timestamp. Deduplicated syncs additionally need you to specify a primary key.

Can I do this without writing code?

The pipeline, yes. Designing the target's keys, reconciling the two clusters and scheduling maintenance are not, and they are what makes the migration trustworthy.

Get your Amazon Redshift data into Amazon Redshift

Start by checking that a snapshot restore would not simply do this, because for a full like-for-like copy it would and with far less effort. If you need selectivity, a redesign or a parallel run, nominate cursor columns per table, keep staging in the target region, and choose new sort and distribution keys rather than recreating the old ones. Then reconcile row counts while both clusters live, because deletions never cross and a bridge is worth dismantling early.

Airbyte's connector catalog includes 600+ pre-built connectors, so a cluster migration can be selective rather than wholesale. For another enterprise database into the same destination, see Oracle Database to Amazon Redshift, and for the same source into an operational database, Amazon Redshift to PostgreSQL.

Start syncing now →