How to sync MySQL deletes to BigQuery

A common surprise with MySQL to BigQuery syncs: rows deleted in MySQL are still in BigQuery weeks later. Here's why, and how to fix it.

Why deletes go missing

Incremental syncs usually copy rows whose updated_at is newer than the last run. A deleted row has no updated_at: it's simply gone, so the query never returns it and BigQuery keeps the old copy. Workarounds like soft-delete columns or periodic full reloads add work and cost.

Capture deletes from the binary log

MySQL's binary log records every delete with the row's primary key and values. A log-based CDC pipeline reads that event and:

• removes the row from the up-to-date BigQuery table, so it matches MySQL, and
• records the delete (_ds_op = 'd') in the change history, so you can still see that the row existed and when it was removed.

Querying deleted rows

SELECT id, _ds_ingested_at AS deleted_at
FROM `your-project.analytics_changelog.orders`
WHERE _ds_op = 'd'
ORDER BY _ds_ingested_at DESC;

With Quayen, deletes are handled automatically for every table; see table layout.