Postgres to BigQuery pipeline

A Postgres to BigQuery pipeline is a data flow that moves data from PostgreSQL into BigQuery for analytics, reporting, or machine learning.

Common approaches

1) Batch ETL/ELT

• Extract data from Postgres on a schedule
• Load it into BigQuery
• Transform in BigQuery if needed

Good for: daily/hourly reporting, lower complexity
Tools: Airflow, dbt, Fivetran, Stitch, Cloud Data Fusion, custom Python jobs

2) Near real-time CDC (change data capture)

• Capture inserts/updates/deletes from Postgres
• Stream changes into BigQuery

Good for: fresh analytics, dashboards, operational reporting
Tools: Debezium, Datastream, Kafka, Dataflow, managed connectors

3) Manual/custom pipeline

• Query Postgres
• Write results to files (CSV/Parquet)
• Upload to BigQuery

Good for: small/simple use cases
Tools: Python, pandas, SQLAlchemy, BigQuery client

---

Typical architecture

Postgres → Extract/CDC → Staging (optional) → BigQuery → Transform/BI

Example batch flow

1. Read new/changed rows from Postgres using a watermark column like updated_at
2. Write to GCS or directly load to BigQuery
3. Merge into target BigQuery tables using MERGE
4. Run downstream transformations

---

Key design decisions

Incremental strategy

• Use updated_at or an increasing ID
• For deletes, use CDC or soft-delete flags
• Handle late-arriving updates

Data type mapping

Watch for:

• JSONB → BigQuery JSON or STRING
• TIMESTAMP WITH TIME ZONE → BigQuery TIMESTAMP
• NUMERIC/DECIMAL → BigQuery NUMERIC or BIGNUMERIC
• ARRAY/UUID/BYTEA need special handling

Idempotency

Make sure reruns don’t duplicate data:

• load to staging
• MERGE into final tables
• use unique keys

Schema evolution

• Additive changes are easiest
• Handle new columns with schema updates
• Avoid breaking downstream models

---

Simple example pattern

1. Extract from Postgres

SELECT *
FROM public.orders
WHERE updated_at > :last_sync_time

2. Load into staging table in BigQuery

Use a temporary or staging table such as: project.dataset.orders_staging

3. Merge into final table

MERGE `project.dataset.orders` T
USING `project.dataset.orders_staging` S
ON T.order_id = S.order_id
WHEN MATCHED THEN
  UPDATE SET
    status = S.status,
    amount = S.amount,
    updated_at = S.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, updated_at)
  VALUES (S.order_id, S.status, S.amount, S.updated_at)

---

Recommended options by use case

Small/simple

• Python + Cloud Scheduler + BigQuery load jobs

Medium complexity

• Airflow + custom extraction + BigQuery load/merge

Enterprise/near real-time

• Debezium or Datastream + Dataflow + BigQuery

Low-code managed

• Fivetran / Stitch / Airbyte

---

Things to watch out for

• Time zones and timestamp precision
• Deletes and updates
• Primary keys or unique identifiers
• Large tables and performance
• BigQuery load limits and partitioning
• Network/security: VPC, IAM, service accounts, secrets

If you want, I can provide:

1. a Python example for Postgres → BigQuery
2. an Airflow DAG
3. a CDC architecture on GCP
4. a dbt-based pipeline design