Postgres to BigQuery: how to sync PostgreSQL data to BigQuery

Connecting Postgres to BigQuery through OptimaFlo takes two things: a read-only database user, and a BigQuery dataset to receive Gold-layer output. Initial setup runs about 15 to 20 minutes; after that, syncs are scheduled pipeline runs, not a one-time migration.

What actually happens

OptimaFlo does not write straight from Postgres into BigQuery. It lands the data as open Apache Iceberg tables in your own cloud first (the Raw layer), then the AI data team builds Clean and Ready models on top, and only the Gold output exports to BigQuery. That extra step is the point: you get versioned, queryable history in Iceberg regardless of what happens to the BigQuery side, and BigQuery becomes one destination among several (Snowflake, S3, GCS, another Postgres, MySQL) rather than the only copy of your modeled data.

The Postgres connector itself uses SQLAlchemy 2.0's reflection API (inspect()) to read column names and types directly from the database catalog, not from a hand-maintained schema file. It accepts either a full postgres:// connection string or discrete host, port, database, and credential fields, and it supports a raw SQL query as an alternative to a bare table name for sources that are not a single clean table. All synchronous database calls run on a thread pool so they never block the event loop.

Full refresh, not CDC

This is the honest limit worth stating plainly: the connector does not do log-based change data capture. Every run issues a fresh SELECT, either against the whole table or against whatever query you configured. For most analytics workloads (events, orders, daily snapshots) that is fine, especially on an incremental schedule. If you need row-level change tracking on a large, frequently-updated table, the practical pattern today is a custom query with a WHERE updated_at > ... filter, which the connector supports as a table alternative, not something the pipeline builder generates for you automatically.

Schema and identifiers

Schema inference requires either a selected table or a query. If neither is set, the connector fails loud with a clear error rather than silently returning nothing. Table and schema names are validated against a safe identifier pattern before they touch SQL, and the actual data read goes through SQLAlchemy's Table/select() expression API instead of string-interpolated queries, so a data source name with unusual characters cannot become a SQL injection vector.

Getting to BigQuery

Once Ready-layer tables exist, the destination export writes to a BigQuery project, dataset, and (implicitly, via the shared output-table field) table, in one of two modes: append new rows or overwrite the table. BigQuery is also OptimaFlo's own warehouse compute engine for larger workloads (100 GB to 10 TB), but that is a separate, internal use of BigQuery from the export destination described here.

What is not supported

There is no built-in binlog-style or WAL-based replication, no automatic incremental watermarking, and schema-from-query is not implemented (a query-based source returns an empty schema rather than a guess). If your Postgres table needs sub-minute freshness in BigQuery, this is not that tool yet.