Change Data Capture (CDC): Real-Life Use Cases and Pitfalls

Batch pipelines that run "every night at 2am" are simple — but they mean your data is always up to a day stale. Change Data Capture (CDC) solves this by streaming just the changes (inserts, updates, deletes) from a source database the moment they happen, instead of re-extracting everything on a schedule.

📘 What CDC Actually Captures

Most CDC tools read a database's transaction/replication log (e.g., MySQL's binlog, PostgreSQL's WAL) rather than querying tables directly. This means CDC sees every change as an event, without adding query load to your production database.

🧪 A Simplified CDC Event

A CDC tool like Debezium or Google Cloud's Datastream converts a database change into a structured event, roughly like this:

{
  "op": "UPDATE",
  "table": "orders",
  "before": { "order_id": "1001", "status": "pending" },
  "after":  { "order_id": "1001", "status": "shipped" },
  "timestamp": "2026-09-17T10:15:00Z"
}

Downstream systems (BigQuery, a search index, a cache) consume this event stream and apply it, keeping them near-real-time instead of hours behind.

🎯 Common Real-World Use Cases

• Real-time analytics dashboards — replacing "refresh every night" with "updated within seconds"
• Keeping a search index in sync with the source-of-truth database without re-indexing everything
• Migrating databases with zero downtime — CDC streams ongoing changes from the old database to the new one during cutover
• Feeding event-driven microservices without those services querying the database directly

🛠️ Landing CDC Events in BigQuery

A common pattern: stream CDC events into a staging table, then MERGE them into the target table (this is exactly what the BigQuery MERGE statement is built for):

MERGE analytics.orders AS T
USING staging.orders_cdc AS S
ON T.order_id = S.order_id
WHEN MATCHED AND S.op = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET T.status = S.after_status
WHEN NOT MATCHED THEN INSERT (order_id, status) VALUES (S.order_id, S.after_status);

⚠️ Common Pitfalls

• Out-of-order events — network or processing delays can deliver events out of sequence; always include a timestamp/sequence number and handle ordering explicitly, don't assume arrival order is event order.
• Schema changes breaking the pipeline — if a column is added or renamed at the source, CDC tools can silently drop or misinterpret fields unless schema evolution is handled (see the related post below).
• Underestimating replication lag under load — CDC is near-real-time, not instant; spikes in source database write volume can create lag that catches teams off guard.
• Forgetting DELETE events — teams often build the INSERT/UPDATE logic and forget deletes need explicit handling too, leaving "zombie" rows downstream.

🔗 Related Posts

• BigQuery MERGE Statement – Explained
• Managing Schema Evolution Without Losing Data