Table layout in BigQuery

Each MySQL table becomes two BigQuery tables.

Up-to-date copy: analytics.orders

Same columns as MySQL, the same primary key, clustered by it, and optionally partitioned by a date column. Three system columns are added:

• _ds_seq, _ds_row: the binary log position of the change that produced this version of the row.
• _ds_updated_at: when the row was last refreshed in BigQuery.

Change history: analytics_changelog.orders

One row per change, partitioned by arrival day and expired after the retention you choose:

• _ds_op: c insert, u update, d delete.
• _ds_seq, _ds_row: position in the binary log (orders changes exactly as MySQL committed them).
• _ds_ingested_at: when the change arrived in BigQuery.
• _ds_gen: copy generation; increases when a table is re-copied.

Example: the state of a row over time

SELECT _ds_op, _ds_ingested_at, status, total
FROM `your-project.analytics_changelog.orders`
WHERE id = 1042
ORDER BY _ds_seq, _ds_row;