SSSamarth Sinha

Note

Change data capture into BigQuery, without the surprises

Getting operational changes into the warehouse in minutes: capture, ordering, merging and the cost traps.

Nov 2026 · 1 min read

  • CDC
  • BigQuery
  • Streaming
Log-based change data capture into BigQuery Operational database to Log-based capture. Log-based capture to Change stream. Change stream to Raw change table. Raw change table to Merge by key. Merge by key to Current table. Merge by key to Continuous checks. Operational database transaction log Log-based capture inserts, updates, deletes Change stream Kafka or managed CDC Raw change table append-only history Merge by key latest state wins Current table partitioned, clustered Continuous checks counts, lag, schema
IllustrativeLog-based change data capture into BigQueryScroll →

Why CDC

Nightly extracts make reports a day old and put heavy reads on production databases. Log-based change data capture reads the database's own change log instead, so changes arrive in minutes with little load on the source.

The pattern

  1. Capture from the log, not by polling a timestamp column: updates and deletes both come through.
  2. Land every change append-only, with the operation type, source timestamp and log position. This raw table is your audit trail and lets you replay.
  3. Merge into a current-state table per primary key, as often as the business needs fresh data.
  4. Order by log position, not arrival time. Retries and parallelism deliver events out of order.
  5. Check continuously: row counts against the source, replication lag and schema changes.

Cost traps

  • A frequent MERGE over a large target scans a lot of data. Partition and cluster the target to match the merge, and merge only recent partitions where you can.
  • Very frequent tiny load jobs add overhead and run into quotas; use the streaming path, or batch where freshness allows.
  • Put the raw change tables on a retention policy.

Managed or self-run?

Managed services (for example Datastream into BigQuery) remove most of the plumbing. A Kafka-based pipeline makes sense when the same changes feed several consumers.