BigQuery to Amazon Redshift: How to Move Your Data

Moving BigQuery to Amazon Redshift is a warehouse-to-warehouse migration across two clouds, and the thing that separates a smooth version from an expensive one is understanding that both ends bill you differently for the same work.

BigQuery charges for the bytes a query scans. Redshift charges for a cluster you are already paying for whether it is busy or idle. So on the source side every sync has a price tag attached to how much data it reads, and a full refresh of a large table is not merely slow, it is invoiced. Add cross-cloud egress from Google to AWS and you have three cost lines where most pipelines have none. None of this is a reason not to build it. It is a reason to decide sync modes on purpose.

S3 staging is not optional

The Redshift destination loads by writing data to S3 as a set of files with a manifest, then issuing a COPY command. That is Redshift's own recommended approach rather than an Airbyte quirk, and it means you cannot configure this connector without a staging bucket. Budget for it in the setup plan.

Three practical points follow. The bucket should sit in the same AWS region as the Redshift cluster, which keeps networking costs down and the COPY fast. The IAM user needs put, get, delete and list permissions on that bucket, and the delete matters because staging files are cleaned up after a successful COPY unless you turn purging off. And 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 that assumes everything passes through a bastion is wrong.

You also need 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 for reliability. Tell whoever reviews your Redshift object list that the second one is expected, before they find it and ask.

Choose sync modes with the invoice in mind

On most pipelines, picking full refresh for a moderately sized table is a performance decision. Here it is a financial one, because BigQuery bills the scan. A daily full refresh of a table that a cursor could have read incrementally is a recurring charge for data you already have.

So identify a cursor column for every table where one exists, even the ones small enough that it does not seem to matter, because the cost of doing it is ten minutes once. Where a table genuinely has no reliable cursor, consider whether it needs syncing daily or whether weekly would answer the same questions. And if your BigQuery tables are partitioned, that partitioning is doing work for you on the scan, so preserving cursor alignment with the partition column is worth checking.

The egress line is the one people forget entirely. Data leaving Google Cloud for AWS is charged by Google, and a large initial migration is a real number rather than a rounding error. It is worth pricing before the first sync rather than explaining after it.

Types and structure

The two warehouses do not model data the same way. BigQuery supports nested and repeated fields natively, and analytics teams use them heavily because the query language makes them pleasant. Redshift's relational model has no direct equivalent, so anything nested has to be flattened or serialised on the way through.

Handle this deliberately rather than discovering it in the destination. If a table leans on repeated fields, build a flattened view in BigQuery and sync that instead of the base table. You get a schema that maps cleanly to Redshift, and as a side effect you scan fewer columns, which is the cost point again. On namespaces, the destination maps a namespace to a Redshift schema, so a source dataset structure carries across if you want it to.

Frequently asked questions

Do I need an S3 bucket to load into Redshift?

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

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.

Does an SSH tunnel protect the staging traffic?

No. The tunnel applies to the SQL connection only. Data moving to the S3 staging bucket goes over HTTPS instead.

How do I keep BigQuery costs down on this pipeline?

Use incremental sync wherever a cursor column exists, sync fewer columns by syncing a view rather than a wide base table, and reduce the frequency of any table that must run full refresh.

Get your BigQuery data into Amazon Redshift

Set up the staging bucket in the right region with the right four permissions, expect the second schema, flatten nested fields in BigQuery before they travel, and treat every full refresh as a line item rather than a default. This pipeline is not technically difficult. It is just the one where careless sync modes show up on a bill.

For the same source moving to a different warehouse, see BigQuery to Snowflake. For BigQuery feeding a streaming platform rather than another warehouse, see BigQuery to Kafka.

Start syncing now →