Replacing a nine-hour nightly dump with change data capture

Choose managed CDC (Datastream) with a BigQuery destination. The requirement set is: capture in-place updates, capture hard deletes, do it continuously, and do not run a 9-hour lock-heavy job. A nightly extract reads the whole table and cannot see deletes at all - a delete either never propagates or forces a full reload, which is exactly the 9-hour problem.

Source prerequisites, which is where the real project risk sits:

• wal_level=logical. This requires a restart, so it must be booked as a maintenance change - the DBA constraint, not the tooling, sets your go-live date.
• A replication user with REPLICATION plus SELECT on the 60 tables.
• Headroom in max_replication_slots and max_wal_senders.
• A primary key on every replicated table. Datastream needs it to identify rows in BigQuery; tables without one are effectively backfill-only.
• Consider pointing the stream at a read replica so the initial snapshot does not compete with production I/O.

Sizing. Initial snapshot is 4 TB; at 300 MB/s line rate that is ~3.7 hours best case - plan one to three days. Steady-state CDC is a few GB/day.

Rejected alternatives. A Dataflow JDBC-to-BigQuery template is batch and has the same delete blindness. Federated queries to the source put load on the OLTP system and are unusable at 1.2 B rows. Datastream's cost is per GB processed, so the one-time snapshot dominates the bill, not steady state.

Landing. Datastream appends change rows (never overwrites) into a landing table carrying a datastream_metadata struct: UUID, source timestamp, change sequence number, and change type in append-only mode. Partition that table by ingestion date and expire partitions after 7 days - it contains PII and must not become a permanent copy of the database.

Micro-batch MERGE, every 5-15 minutes:

MERGE target T
USING (
  SELECT * EXCEPT(rn) FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY order_id
                              ORDER BY datastream_metadata.change_sequence_number DESC) rn
    FROM staging.changes
    WHERE ingest_date = CURRENT_DATE())
  WHERE rn = 1
) S
ON T.order_id = S.order_id
WHEN MATCHED AND S.change_type = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED AND S.change_type != 'DELETE' THEN INSERT ...

The ROW_NUMBER is what makes replays safe: re-applying the same change batch converges on the same state.

Why this is affordable. The 1.2 B-row figure is a trap - the USING side is only the keys in this micro-batch, so cost scales with change volume, not table size. Two things must hold: the target clustered on order_id and the staging table partitioned by ingest_date with the same clustering, so the join is a pruned lookup rather than a scan. Dry-run a representative merge and read bytes billed before committing to the cadence.

If SQL merges still cannot keep up at your change rate, move the dedup into Dataflow (stateful, keyed by primary key) and write the collapsed current state with the Storage Write API. Keep the SQL merge as the simpler first implementation and escalate only on measured evidence.

Three mechanisms, all required.

1. Propagate. The MERGE above converts DELETE change rows into physical deletes in the current-state table. Downstream marts must either be rebuilt or filter with NOT EXISTS against the current-state table - never against a stale dimension snapshot, or the customer reappears in yesterday's join.

2. Prove. Write one immutable audit row per delete into deletion_audit: a salted hash of the customer key, source timestamp, source table, and run id. No personal data, fully provable, and the hash is stable across runs so repeat deletions dedupe.

3. Verify. A nightly assertion that SELECT COUNT(*) FROM current_state WHERE customer_key_hash IN (hashes_deleted_today) = 0, plus a monitor on replication-slot lag. If the slot falls behind, deletes stop propagating and you breach the 24-hour SLA silently.

Source risks to agree with the DBA before go-live. The replication slot pins WAL on the primary: if Datastream pauses, WAL accumulates and can fill the source disk. Set max_slot_wal_keep_size, alert on retained bytes in pg_replication_slots, agree a documented kill switch that drops the slot, and watch for idle-in-transaction sessions that block slot advancement. These are the failure modes that turn a clean CDC rollout into an outage on the OLTP system, and they are a DBA conversation, not a BigQuery one.