BigQuery to Amazon Redshift: How to Move Your Data

Moving BigQuery into Amazon Redshift is a warehouse to warehouse pipeline, which sounds redundant until you look at why people build it. Usually one part of the business standardised on Google Cloud and another on AWS, and the reporting layer that everybody actually uses sits on Redshift.

This guide covers the managed path with Airbyte. Two things shape it: Redshift requires staging through S3 rather than direct inserts, and BigQuery bills for the bytes each query scans, which makes what you select on the source side a recurring cost rather than a one-off decision.

BigQuery to Amazon Redshift at a glance:

Why move data from BigQuery to Amazon Redshift?

Two situations account for most of these pipelines.

The first is consolidation across clouds. A team using Google Analytics or Firebase has data landing in BigQuery, while the company's reporting runs on Redshift. Rather than teaching every dashboard to query two warehouses, you replicate one into the other.

The second is governance and access. Analysts already have Redshift credentials and know how it behaves, and provisioning Google Cloud access for everyone who needs a number is often more trouble than a replicated copy. If both warehouses genuinely serve different teams with different needs, this pipeline may be solving an organisational problem rather than a technical one.

What do you need before you start?

The Redshift side has a hard prerequisite and the BigQuery side has a cost question:

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.

A narrow view on the BigQuery side. Because BigQuery charges for bytes scanned, syncing a wide table is a recurring bill rather than a one-off transfer. Build a view containing only the columns and rows Redshift actually needs, and sync that instead.

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 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 BigQuery to Redshift pipeline in Airbyte?

Step 1: Narrow the result in BigQuery

Create a view containing exactly the columns and rows Redshift needs, with a timestamp column maintained on every write so there is something to use as a cursor. This is the step that controls your ongoing BigQuery bill, and it is far easier to do now than to retrofit once dashboards depend on a wide table.

Step 2: Configure the BigQuery source

Click Sources in the left navigation, then New Source, and select BigQuery, following adding a source. Supply the project identifier, an optional dataset to scope the sync, and a service account key with read access. Scoping to a dataset is worth doing, because it keeps the catalogue comprehensible on a project with many datasets.

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 a cursor and schedule

Click Connections, then New connection, select your view, nominate a cursor field and choose an incremental sync mode. Incremental matters more here than usual, because a full refresh re-scans the source and BigQuery charges for it every single time rather than once.

Set the frequency against how often the underlying data actually changes. Syncing hourly from a table rebuilt nightly costs you twenty-three unnecessary scans a day, which is the kind of thing nobody notices until the bill arrives.

Why is S3 staging not optional?

Because it is how Redshift is designed to be loaded. The destination writes data to S3 as files with a manifest, then issues a COPY command pointing at them, which is Redshift's own recommended path for bulk loading rather than something Airbyte invented. There is no direct insert alternative to fall back on.

That has practical consequences worth planning for. You need a bucket in the same region as your cluster to avoid cross-region transfer, IAM credentials with put, get, delete and list on it, and a decision about whether staging files are purged after each successful load. Purging is the default and retaining them is occasionally useful when debugging a failed sync.

It also affects your network design. An SSH tunnel covers the SQL connection to the cluster and not the staging traffic, which reaches S3 over HTTPS regardless. If your security model assumes all traffic to this pipeline passes through a bastion, that assumption needs revisiting before somebody discovers it in a review.

What does BigQuery charge you for this?

For the bytes each sync scans, which makes this the rare pipeline where the source is the expensive end. Every run is a query against BigQuery, and a full refresh of a wide table is that query's full cost repeated on every schedule rather than paid once during a migration.

Because BigQuery is columnar, the fix is largely about columns. A view selecting six columns from a table of two hundred scans a small fraction of the data, and that difference is multiplied by however many times a day you sync. Narrowing rows helps too where your source table is partitioned and the view filters on the partitioning column.

The other lever is sync mode. Incremental scans only what the cursor admits, so it is both cheaper and faster, and it is worth the effort of maintaining a timestamp column on the source view specifically to enable it. Note that the source is cursor-based rather than log-based, so deletions in BigQuery will not reach Redshift and need a soft-delete flag or a periodic full refresh.

Frequently asked questions

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.

Why is my BigQuery bill higher than expected?

Every sync is a query billed on bytes scanned. Sync a narrow view rather than a wide table, use incremental rather than full refresh, and match the schedule to how often the data actually changes.

Does the BigQuery source support change data capture?

No. Incremental sync is cursor-based, so deletions are not captured. Use a soft-delete flag in the source view or a periodic full refresh where rows genuinely disappear.

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 a BigQuery view narrowing the result, which is short SQL and the thing that controls your ongoing cost.

Get your BigQuery data into Amazon Redshift

Narrow the result in BigQuery before anything else, because that decides what this pipeline costs to run forever. Set up the staging bucket in the right region, expect the second schema, use incremental sync, and revisit any network design that assumed the SSH tunnel covered everything.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same BigQuery source can feed an operational database alongside the warehouse. For that pairing, see BigQuery to MySQL, and for another columnar source landing in Redshift, ClickHouse to Amazon Redshift.

Start syncing now →