MySQL CDC to BigQuery: Why periodic syncs miss deletes and intermediate updates
MySQL CDC to BigQuery: what periodic syncs miss, and how binlog avoids it
Periodic SELECT-based syncs can't see rows that were deleted between runs or intermediate states of rows that changed multiple times. Change Data Capture (CDC) reads MySQL's binlog to capture every INSERT, UPDATE, and DELETE in order, with full row state, regardless of when batches run. This guide covers the prerequisites: ROW-format binlog with FULL row images, no PARTIAL_JSON, proper user privileges, unique server-ids, and sufficient binlog retention. It also explains how to land CDC data reliably in BigQuery, emphasizing completeness over speed.
A CDC pipeline that runs once an hour is still fundamentally more reliable than a batch sync that runs once a minute, because it captures everything that happened, not just the latest snapshot.