BigQuery to PostgreSQL: How to Move Your Data

Moving BigQuery to PostgreSQL runs against the usual direction of travel, and it is a pattern more teams are adopting. The modelled tables in your warehouse are exactly what an application needs to serve, but BigQuery is built to scan petabytes rather than answer a lookup by primary key in single-digit milliseconds. Postgres does that cheaply.

Airbyte connects the two with certified connectors on both ends, no code, and a schedule you control. This guide covers how to build it, how to choose a sync approach that keeps your BigQuery bill down, and what to know about the destination side.

Why push warehouse data back into Postgres?

Three situations account for most of these pipelines. The first is a serving layer: a customer-facing dashboard, an in-product analytics panel, or a recommendation surface that queries the output of a dbt model. Every one of those needs sub-second row lookups and predictable cost per query, which is not what a scan-based warehouse is priced for.

The second is operational enrichment. A churn score or a lifetime value calculation is computed in BigQuery but needs to sit next to the application data that acts on it. The third is simple access: analysts and internal tools that speak Postgres, without giving everyone a BigQuery seat and a query budget.

In all three, Airbyte handles the movement and scheduling so the modelled tables land in Postgres on a cadence you set, without anyone maintaining an export script.

How do you set up the BigQuery source in Airbyte?

The BigQuery source needs three things: a Project ID, a Service Account Key in JSON format, and optionally a Default Dataset ID. It reads your tables without altering the schema in BigQuery.

The service account needs the BigQuery User and BigQuery Data Editor roles. That second one surprises people on a read-only pipeline, but it is what grants permission to run BigQuery jobs and read table metadata, both of which the connector depends on. Airbyte supports JSON keys only, and Google shows you the key contents exactly once, so download it at creation. Delete it from your machine once the source is configured.

Set the Default Dataset ID if you are only interested in one schema. It is marked optional, but Airbyte's documentation notes it dramatically speeds up the discovery operation. On a project with many datasets, the difference between a fast setup and a slow one is often just this field.

Which sync approach should you choose?

This decision matters more with BigQuery than with almost any other source, because BigQuery charges by bytes scanned. Every sync Airbyte runs is a query you pay for, so the sync mode is a cost decision as much as a data decision.

Incremental works by cursor. Airbyte queries with a filter on the cursor field, keeps the maximum value it saw as state, and resumes from there next time. There is no change data capture on the BigQuery source, so deletions are invisible: a row removed from your warehouse stays in Postgres until a full refresh rebuilds the table.

The practical pattern most teams land on is incremental on the large fact tables with a cursor that matches the partitioning column, and full refresh on the small dimension tables where deletions matter and the scan is cheap anyway. Airbyte lets you set the mode per stream, so you do not have to pick one policy for everything.

Choose the cursor carefully. A column that is not reliably increasing will silently skip rows, and nothing will raise an error when it happens.

Configuring the PostgreSQL destination

Add a Postgres destination with host, port, database, default schema, and a user with write permission. Airbyte Cloud connects over SSL or TLS by default. If your instance restricts inbound traffic, add the Airbyte Cloud IP addresses to its allow list.

From version 3.0.0 the destination uses Direct Load, writing records straight into final tables with no intermediate raw tables. Each table carries your columns plus _airbyte_raw_id, _airbyte_extracted_at, and an _airbyte_meta JSONB column recording per-row typing changes. For a serving layer that is worth knowing, because your application queries will see those columns alongside the ones you modelled.

Two things to plan for. Airbyte does not create indexes, so add whatever your query patterns need on the Postgres side after the first sync, since a serving layer without an index on its lookup column will not be fast regardless of how quickly the data arrives. And Airbyte's documentation recommends Postgres destinations for roughly 10GB or less, which suits this use case well: you are typically moving modelled output rather than a raw warehouse.

Frequently asked questions

Why does a read-only source need BigQuery Data Editor?

That role grants permission to run BigQuery jobs and read table metadata, both of which the connector needs. Pair it with BigQuery User, as the documentation specifies, and scope the service account to only the project you are syncing from.

How do I keep BigQuery query costs down?

Use incremental sync with a cursor that matches your table's partitioning column, so each sync prunes partitions rather than scanning the whole table. Reserve full refresh for small tables, and set a sync frequency that matches how fresh the serving layer actually needs to be.

Are deletions in BigQuery reflected in Postgres?

Not with incremental sync, because the BigQuery source has no CDC. A periodic full refresh is the practical way to bring the destination back in line if rows get deleted in your warehouse.

Why is schema discovery so slow?

You have probably left Default Dataset ID blank on a project with many datasets. Setting it scopes discovery to one schema and speeds the operation up considerably.

Will Airbyte create indexes on the Postgres tables?

No. Airbyte replicates table data, not schema objects. Add indexes matching your application's query patterns after the first sync, and remember they need recreating if you ever drop and rebuild a table.

Get your BigQuery data into PostgreSQL

Set the Default Dataset ID, pick incremental with a partition-aligned cursor on your large tables, and index the destination for the queries your application actually runs. Airbyte handles the rest on whatever schedule you choose. Replicating into Postgres from an operational database instead? See our guides to PostgreSQL to PostgreSQL and MySQL to PostgreSQL.

Start syncing now →