We are planning to use the BigQuery Data Transfer Service PostgreSQL connector with full ingestion from Cloud SQL PostgreSQL. During one transfer run, are all source tables read from a single PostgreSQL transaction or exported snapshot?
In general, what are the transactional or data consistency guarantees?
The BigQuery Data Transfer Service (DTS) PostgreSQL connector does not execute transfer runs under a single PostgreSQL transaction or export snapshot across all source tables. During a run, DTS reads tables independently , often spawning parallel query worker threads to export data in chunks which means individual table extractions occur at different points in time. Consequently, cross-table transactional consistency is not guaranteed. While PostgreSQL’s REPEATABLE READ or SERIALIZABLE isolation guarantees row-level or query-level consistency for each specific batch or table being read, modifications made to other source tables while the transfer run is actively progressing can result in temporary data skew across your destination BigQuery dataset.
If your use case strictly requires point-in-time, multi-table snapshot consistency, consider taking an export from a Cloud SQL database backup/replica or leveraging change-data-capture (CDC) streaming solutions like Google Cloud Datastream instead.