Postgres to BigQuery

The pipeline as it appears on the Pipeloom canvas. You can add transforms or triggers on the same canvas later.

What to know about Postgres into BigQuery

Example: query inside a jsonb column

SELECT
  id,
  JSON_VALUE(PARSE_JSON(payload), '$.customer.email') AS customer_email
FROM public.events
WHERE _airbyte_extracted_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY);

Assumes a Postgres table events with a jsonb column payload, synced into a dataset named public. Names will differ in your setup.

Set it up

1. Create the Postgres sourceAuthenticate with A read-only Postgres user (plus REPLICATION permission for CDC). You need: A read-only Postgres user that can read the tables you want to copy. For CDC only: logical replication turned on, a replication slot, and a publication with a replica identity on each table.
2. Create the BigQuery destinationAuthenticate with A Google Cloud service account with the BigQuery User and BigQuery Data Editor roles, using a JSON key. You need: A Google Cloud project with BigQuery turned on, and a dataset to write to. Create it in the same location as the datasets you will join against. A service account with the BigQuery User and BigQuery Data Editor roles. For production loads, a Cloud Storage bucket for staging with an HMAC key, and the Storage Object Admin role for the service account.
3. Connect them and pick streamsDraw the edge on the canvas and choose streams and sync modes. Postgres supports every sync mode; BigQuery supports every mode: full refresh overwrite, append and overwrite deduped, incremental append and incremental append deduped.
4. Run, then scheduleThe first sync loads each stream in full. Then set the schedule.

Step-by-step guides: Postgres and BigQuery.

What lands in BigQuery

• A table per stream with typed columns plus _airbyte_raw_id, _airbyte_generation_id, _airbyte_extracted_at and _airbyte_meta.
• Tables partitioned by day on _airbyte_extracted_at and clustered by that column and the primary key, so filtering on the partition column scans less data.

Type mapping

• object and array → JSON
• string → STRING
• integer → INT64
• number → NUMERIC
• timestamp with time zone → TIMESTAMP
• timestamp without time zone → DATETIME
• boolean → BOOL

When Postgres's schema changes

The destination tables are updated as the source changes. Namespaces map to BigQuery datasets, and invalid characters in names are replaced with underscores.

Streams you can sync

Includes Tables, Views, Materialized views and more. The full list is in the reference.

What it costs

A sync is metered at 1 credit per vCPU-minute, and a default sync uses 2.5 vCPUs. As an example, not a benchmark, a sync that takes 3 minutes uses 8 credits. Run 24 times a day, that is about 5,760 credits a month, more than Free's 1,500 but within Starter's 12,000 ($19 a month). Your own sync time decides the real figure, so try the calculator with your numbers.

Questions and fixes