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.
- karakanb
Disclaimer: I am cofounder of Bruin (https://github.com/bruin-data/bruin), we are a competitor to Erathos.
It seems like a relatively straightforward marketing article. I was pleasantly surprised to learn about Erathos though, nice product!
I am personally not a big fan of CDC in prod. Streaming data movement is generally prone to confusion, and it feeds into bad data patterns like hard deletes without any audit logs, no timestamps on updates or deletes, etc. which are usually the reason why batch loads cannot be utilized. They require a decent operational understanding of the underlying database, and have some gotchas like the Erathos folks mentioned in the article. We offer CDC both in our cloud platform, as well our open-source tools, but if I could, I would always pick an incremental batch load with a cursor value over a CDC connection.
I understand it is sometimes required due to organizational complexity or legacy database reasons, mine is just a personal preference.
If anyone is looking for an open-source CDC tool that runs as a standalone Go CLI, check out ingestr: https://github.com/bruin-data/ingestr
- gpaulbagetti
Wrote this after seeing the same failure mode too many times: a MySQL sync job that looks fine in the dashboard but has been silently dropping deletes and intermediate updates for months, because it's comparing snapshots instead of reading the binlog. Tried to lay out exactly what has to be true on the MySQL side (row image, binlog_row_value_options, server-id, retention) for CDC to actually be complete, not just "eventually consistent." Happy to go deeper on any of it in the comments.