How do I move data from Postgres to BigQuery automatically?

The simplest way is to use an automated ETL/ELT tool or a scheduled pipeline.

Common options

1) Managed connectors / ETL tools

These are easiest if you want minimal maintenance:

• Fivetran
• Airbyte
• Stitch
• Matillion
• Hevo

How it works:

1. Connect Postgres as the source
2. Connect BigQuery as the destination
3. Choose tables/schemas
4. Set sync frequency or CDC (change data capture)
5. The tool handles incremental updates automatically

Best for: fast setup, ongoing sync, minimal code.

---

2) Custom pipeline with scheduled jobs

If you want more control or lower cost, build it yourself:

Typical architecture:

• Extract from Postgres
• Load into BigQuery staging tables
• Transform/Merge into final tables

Common tools:

• Python scripts
• psycopg2 / SQLAlchemy for Postgres
• google-cloud-bigquery for BigQuery
• Airflow / Cloud Composer / Prefect / Dagster / cron for scheduling

Basic flow:

1. Query only new/changed rows from Postgres
2. Write results to CSV/JSON/Parquet
3. Upload to Google Cloud Storage or load directly into BigQuery
4. Run a BigQuery MERGE for upserts

---

3) Use CDC for near-real-time replication

If you need continuous sync:

• Postgres logical replication
• Tools like Debezium
• Managed services that support CDC

This captures inserts/updates/deletes and streams them to BigQuery.

Best for: analytics near real-time, frequent updates.

---

Recommended approach by use case

• Small team, want easy setup: Fivetran or Airbyte
• Need low cost and custom logic: Python + scheduler
• Need near real-time: CDC-based pipeline
• Already using GCP: Cloud Composer / Dataflow / Airbyte on GCP

---

A practical setup example

Option: Airbyte + BigQuery

1. Deploy Airbyte
2. Add Postgres source
3. Add BigQuery destination
4. Configure sync mode:
   • Full refresh initially
   • Incremental/CDC afterward
5. Schedule syncs every 5 min / hourly / daily

---

If you want to build it yourself

A simple robust pattern is:

1. Add an updated_at column in Postgres tables
2. Track the latest synced timestamp
3. Pull rows where updated_at > last_sync_time
4. Load into BigQuery staging
5. MERGE staging into target tables
6. Store the new watermark

This works well if your data changes can be tracked by timestamp.

---

Notes

• BigQuery is append-friendly; for updates/deletes, use MERGE
• Consider data type mapping carefully (timestamps, arrays, UUIDs, JSONB)
• For large tables, incremental sync is much better than full reloads
• Use partitioned tables in BigQuery if data is time-based

If you want, I can give you:

1. a no-code setup with Airbyte/Fivetran, or
2. a Python script + cron/Airflow example for Postgres → BigQuery.