Amazon Redshift to BigQuery: How to Move Your Data
Move Amazon Redshift into BigQuery with Airbyte. Why source concurrency is the real constraint, the immutable dataset location, and partition filtering for cost.
Moving Amazon Redshift into BigQuery is a warehouse to warehouse pipeline, usually driven by a move between clouds rather than by any shortcoming in either engine. Both are columnar and analytical, so nothing needs flattening and wide tables travel without complaint.
What is left is a resource question with an unusual shape. The constraint is not what BigQuery can absorb, it is what your Redshift cluster can serve while still answering the queries your analysts are running, because every sync occupies a slot they would otherwise use.
Amazon Redshift to BigQuery at a glance:
Why move data from Amazon Redshift to BigQuery?
Two situations account for most of these pipelines.
The first is a move between clouds. The organisation is standardising on Google Cloud, Redshift holds years of history, and a pipeline running alongside the old warehouse keeps the new one current while teams migrate their reporting across.
The second is consolidation. One part of the business runs on AWS and produces data in Redshift, while the analytics everybody looks at lives in BigQuery alongside Google Analytics or Firebase. Replicating one into the other beats teaching every dashboard to span two clouds.
What do you need before you start?
Most of the preparation is on the Redshift side, because that is where the cost lands:
A read-only Redshift user. With select on the schemas you intend to replicate. Create one for the pipeline rather than reusing an analyst account, so its queries are identifiable in your monitoring when you go looking for what is consuming the cluster.
A cursor column per table. The source is cursor-based, so each table needs a column that only increases and is set on every write. Warehouse tables often carry an audit column recording when a row was loaded, and that is usually the most reliable choice.
Knowledge of your Redshift workload. Specifically when the cluster is quiet and how your workload management is configured, because the sync is another concurrent query competing for the same slots as everything else.
A BigQuery service account and a decided dataset location. With the BigQuery User and BigQuery Data Editor roles. BigQuery queries can only reference datasets in the same physical location and the location cannot be changed after creation, so match it to whatever you intend to join against.
If your cluster restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.
How do you build a Redshift to BigQuery pipeline in Airbyte?
Step 1: Find your cursors and your quiet window
Go through the tables you intend to replicate and identify a suitable cursor column on each, favouring audit columns your loading process maintains over business timestamps that may be back-dated. At the same time, find out when your Redshift cluster is least busy, because that is when this pipeline should run.
Step 2: Configure the Redshift source
Click Sources in the left navigation, then New Source, and select Redshift, following adding a source. Supply the host, port, database, schema and the read-only credentials. The connector reads over JDBC, so it appears in your cluster's query monitoring like any other client.
Step 3: Configure the BigQuery destination
Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key, then pick a loading method. Batched standard inserts is the default and simplest; Cloud Storage staging gives control over where staging files live at the cost of a bucket and an HMAC key, and is worth considering for a large migration load.
Step 4: Nominate cursors and schedule off-peak
Click Connections, then New connection, select your tables, nominate a cursor field for each and choose an incremental sync mode. Schedule it after your overnight transformation jobs finish rather than during business hours, since both compete for the same Redshift resources.
Split your heaviest tables into their own connection so they do not hold up everything else, and so you can give them a different schedule if they turn out to need one.
Why is the source the constraint rather than the destination?
Because BigQuery will absorb whatever you send it, and Redshift has to produce it while also serving everybody else. A cluster has a finite number of concurrency slots, and a replication query occupying one is a slot an analyst cannot use.
That inverts the usual planning. The useful questions here are when your cluster is quiet, how your workload management queues are configured, and whether the pipeline should have its own queue with a modest allocation so it cannot crowd out interactive work.
The initial migration load deserves separate treatment from the ongoing sync. A full historical read is substantial, so run it during a genuinely quiet period, table by table if necessary, rather than pointing everything at the cluster at once and hoping.
How should you query the resulting tables?
Output tables are partitioned daily on the extraction timestamp and clustered on that column plus the primary keys. That is not decoration, it is the main lever you have on query cost, because BigQuery bills for the bytes a query scans.
Filtering on the partitioning column prunes partitions and reduces what a query costs. On warehouse tables that have been accumulating for years and now grow indefinitely in BigQuery, the difference between a query that filters on it and one that does not is substantial and recurring.
Establish that convention while there is one query to change rather than a migrated set of dashboards. This matters particularly on a migration, because analysts arriving from Redshift are used to a different cost model and will not naturally think about bytes scanned unless somebody tells them to.
Frequently asked questions
Does the Redshift source support change data capture?
No. Incremental sync is cursor-based, so deletions are not captured and updates are only seen when they advance the cursor column.
Why is my sync slowing down other Redshift queries?
Because it occupies a concurrency slot like any other client. Schedule it off-peak, and consider giving it its own workload management queue with a modest allocation.
Can I change the dataset location later?
No. It is fixed at creation, and BigQuery queries can only reference datasets in the same physical location, so match it to whatever you intend to join against.
How do I keep BigQuery query costs down after the migration?
Filter on the extraction timestamp, which is the partitioning column. Tell migrating analysts explicitly, since Redshift's cost model does not train that habit.
Can I do this without writing code?
Yes. The setup above is entirely UI-driven. On a migration you will want reconciliation queries comparing the two warehouses, which is short SQL.
Get your Redshift data into BigQuery
Plan around your Redshift cluster rather than your BigQuery project, because the source is what this pipeline competes with. Create the dataset in the right location since that is permanent, find reliable cursor columns, schedule after your overnight models, and teach migrating analysts to filter on the partitioning column.
Airbyte's connector catalog includes 600+ pre-built connectors, so a migration can run alongside the sources feeding both warehouses. For the same source into another warehouse, see Amazon Redshift to Snowflake, and for the reverse direction, BigQuery to Amazon Redshift.
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.