Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Datastream and CDC on GCP

Cloud · GCP in Depth

Datastream and CDC on GCP

Mediumcloud-31
gcpdatastreamcdcbigquerypostgres

Question

How would you replicate a Cloud SQL or on-prem Postgres database into BigQuery continuously?

Solution

To continuously replicate a PostgreSQL database into BigQuery, deploy Google Cloud Datastream for serverless, log-based change data capture that streams WAL events directly into BigQuery tables. Datastream automatically manages historical table backfills alongside ongoing change streams, writing upserts directly to target tables. For cost and latency control, BigQuery max staleness settings govern how frequently background merge operations consolidate change logs, while open-source alternatives use Debezium with Kafka and Dataflow.

Continuous replication mechanics

Datastream reads transaction logs directly from the source database engine without polling tables or issuing intrusive select queries.

Postgres (WAL Log) ---> Datastream CDC ---> BigQuery Storage Write API
     |                                             |
Replication Slot                           Continuous Staging Tables
                                                   | (Max Staleness Merge)
                                            Unified Table View

Setting up this pipeline requires specific configuration steps on both source and target:

  • The PostgreSQL instance requires logical replication enabled, a dedicated replication user with proper permissions, and an active replication slot with a pgoutput plugin.
  • Datastream coordinates initial table snapshots with real-time log ingestion, sequencing records through transaction log sequence numbers to prevent data loss or duplicate updates.
  • In BigQuery, tables apply max staleness configurations. This parameter tells BigQuery how stale query results can be, allowing the storage engine to batch background MERGE operations instead of rewriting partitions on every single incoming row.
  • Setting max staleness to fifteen minutes keeps query performance high while dramatically reducing the compute costs of frequent table compaction.

Architectural alternatives

When managed Datastream does not meet corporate networking or custom transformation requirements, teams adopt event-driven alternatives:

  • Debezium running on Kafka Connect captures PostgreSQL replication events and streams change records into Kafka or Pub/Sub topics.
  • Apache Beam pipelines running on Dataflow consume these topic records, apply data masking or cleansing transformations, and stream structured payloads into BigQuery through the Storage Write API.
  • This alternative gives developers complete control over record schema evolution but introduces operational overhead for Kafka cluster maintenance and consumer offset monitoring.
PreviousNext