Datastream — Serverless CDC & Replication

Google Cloud's serverless CDC (Change Data Capture) service. Reads a source database's transaction log and replicates changes into BigQuery or Cloud Storage, near real-time, with no infrastructure to run.

Mental model (from AWS)

Datastream has the same three-part shape as AWS DMS:

Sources: PostgreSQL, MySQL, Oracle, SQL Server. Destinations: BigQuery (managed merge) or Cloud Storage (Avro/JSON files you process yourself).

Object model

• Connection profile — one endpoint, decoupled from any stream. Holds address + credentials + connectivity method. Reusable across streams. Creating one validates connectivity immediately (it dials the DB).
  • Source (postgresql_profile): hostname, port, user, password, database.
  • Destination (bigquery_profile {}): empty — authenticates as the Datastream service agent, which needs roles/bigquery.dataEditor.
• Stream — the running pipeline. References two profiles, selects tables (include_objects), sets backfill strategy + destination config. Stateful and long-lived (Running / Paused / Not started / Failed / Draining).
• Private connectivity config — optional VPN/PSC path (skip if using IP allowlisting).

Scope & hierarchy

PostgreSQL hierarchy: server → databases → schemas → tables.

MySQL treats schemas as databases, so one MySQL stream can span several. The one-database limit is a PostgreSQL property, not a Datastream one.

Backfill vs CDC

CDC only captures changes from slot-creation forward — pre-existing rows never appear in the log. Backfill fills that gap.

• Backfill + CDC run concurrently; CDC starts from the pinned LSN, backfill loads the snapshot, and once a table's backfill completes it is CDC-only.
• backfill_all {} — auto-backfill every table. backfill_none {} — CDC-only (used to trigger backfills manually later).
• "No backfills in progress" = initial load finished → pure CDC steady state.

Is backfill complete / assured?

Yes for all supported data — and it's a real guarantee, not best-effort:

1. The replication slot is created first, pinning a starting LSN; from that instant every change is retained in WAL.
2. Backfill snapshots existing rows (may take hours).
3. Rows that change during backfill are also captured by CDC.
4. BigQuery MERGE is keyed on the primary key, ordered by source timestamp/LSN → the newest version wins. The table converges (eventual consistency), even if backfill read a stale row.

Exceptions (surface as Unsupported events, not silent loss): tables without a primary key, oversized rows / LOBs (truncated), unsupported column types.

How long does backfill take?

No fixed SLA — throughput-bound. For hundreds of GB cross-cloud, plan for hours to ~a day, usually limited by the source→GCP link, not Datastream.

Run a small POC (one table), measure GB/hour, extrapolate — that number also sets your required WAL-retention window (see Operational risk).

Scheduling / staggering backfill

No native "run at 2 AM" cron, but equivalent control:

• backfill_none + manual per-object backfill triggered in off-hours (the clean "backfill at night" pattern).
• Throttle with max_concurrent_backfill_tasks.
• Backfill from a read replica for isolation (Postgres caveats apply).
• Stagger by table via Terraform: apply a stream with wave-1 tables in include_objects; once stable, add wave-2 tables and terraform apply (in-place update — backfills only the new objects). Staggering also lowers peak WAL retention, because CDC drains the slot between waves.

Postgres → BigQuery mapping

Flat, 1:1 table mapping. Relationships are NOT preserved. Each source table → one BigQuery table; foreign keys become ordinary columns. Datastream does not join, nest, or denormalize — that's the silver layer's (dbt) job.

Example — a normalized FSM schema with FKs:

regions(id PK, name)
customers(id PK, name, region_id→regions, created_at timestamptz, metadata jsonb)
work_orders(id PK, customer_id→customers, status, amount numeric(10,2), tags text[])
work_order_lines(id PK, work_order_id→work_orders, product_id→products, qty int)
products(id PK, sku, name, price numeric)

→ 5 independent flat BigQuery tables (typically <schema>_<table>, e.g. public_work_orders — verify with bq ls). FK columns are plain INT64; you rebuild relationships with JOINs downstream.

Type mapping (common):

Plus a datastream_metadata column (STRUCT: source_timestamp, uuid). In merge mode the BQ table holds current state (deletes remove rows), keyed/clustered on the source PK.

PostgreSQL source prerequisites

The source DBA must enable, before a stream can run:

• wal_level = logical
• A publication: CREATE PUBLICATION <name> FOR ALL TABLES;
• A logical replication slot (pgoutput plugin)
• A role with REPLICATION + SELECT on replicated tables
• Network reachability (IP allowlist / SSH tunnel / private connectivity)

Operational risk — WAL retention & replication slots

WAL is not a permanent history. Segments are recycled after checkpoints once all consumers have passed them. A replication slot pins WAL at its restart_lsn — Postgres will not delete WAL the slot hasn't confirmed. That is what makes CDC lossless and dangerous.

The backfill trap: while a long backfill runs, the slot's restart_lsn can't advance, so all WAL generated during backfill accumulates on the source disk.

required free WAL space ≥ peak_WAL_rate (GB/hr) × backfill_hours × safety_margin

Example: 15 GB/hr × 18 h ≈ 270 GB → provision ~350 GB free on the WAL volume before starting.

Right config: size WAL disk for worst-case backfill and set max_slot_wal_keep_size as a backstop. Monitor slot lag: pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn).

How it processes the log — two stages

Source WAL ──(A) stream continuously──► Datastream ──(B) merge on cadence──► BigQuery
           logical replication slot                  Storage Write API + MERGE
           (event-driven, at commit)                 (≤ data_freshness)

• Postgres logical decoding emits changes at COMMIT, in commit order.
• data_freshness is a ceiling, not an interval — actual lag can be seconds even with a 900s setting.
• Lower data_freshness = more frequent merges = more BigQuery cost.

BigQuery destination

• Storage Write API + periodic MERGE → BQ table is a current-state mirror (upserts + deletes applied, not append-only).
• Lands the bronze layer; dbt builds silver/gold. Datastream's job ends at bronze — it replicates, it does not transform.
• single_target_dataset (all tables → one dataset) or source_hierarchy_datasets (dataset per source schema).

Pricing — the free-tier trap

• Billed on GB processed — an internal representation typically 2–5× raw size. Backfill priced separately (first 500 GB/month free).
• Perpetual free tier (100 GiB CDC/month) applies ONLY to first-party sources: AlloyDB and Spanner — NOT Cloud SQL PostgreSQL, NOT self-managed Postgres.

Terminology

Monitoring

gcloud datastream streams describe STREAM --location=REGION --format="value(state)"
gcloud datastream objects list --stream=STREAM --location=REGION   # per-table backfill

Console → stream → Monitoring: Throughput, Data freshness, Events processed, Unsupported events. Metrics: datastream.googleapis.com/stream/{total_latency,throughput,unsupported_event_count}.

Reading the panel: Events processed = backfill + CDC (e.g. 3 seed rows + 46 changes = 49). Unsupported = 0 is healthy. Freshness spiking then returning to 0 = a merge cycle completed; BQ is caught up.

Terraform skeleton

resource "google_datastream_connection_profile" "source" {
  connection_profile_id = "src-postgres"
  location              = var.region
  postgresql_profile {
    hostname = "..."   # validated on create — source must be reachable NOW
    port     = 5432
    username = "datastream"
    password = "..."
    database = "app"
  }
}

resource "google_datastream_connection_profile" "dest" {
  connection_profile_id = "dest-bq"
  location              = var.region
  bigquery_profile {}   # empty — uses the Datastream service agent + IAM
}

resource "google_datastream_stream" "s" {
  stream_id     = "pg-to-bq"
  location      = var.region
  desired_state = "RUNNING"
  source_config {
    source_connection_profile = google_datastream_connection_profile.source.id
    postgresql_source_config {
      publication      = "datastream_pub"
      replication_slot = "datastream_slot"
      include_objects { postgresql_schemas { schema = "public" } }
    }
  }
  destination_config {
    destination_connection_profile = google_datastream_connection_profile.dest.id
    bigquery_destination_config {
      data_freshness = "900s"
      single_target_dataset { dataset_id = google_bigquery_dataset.bronze.id }
    }
  }
  backfill_all {}
}

Gotcha: the connection profile validates connectivity on create (not the stream), so the source must be reachable before the profile applies.

Q&A

free WAL space ≥ peak_WAL_rate (GB/hr) × backfill_hours × margin