Partitioning BigQuery tables replicated from MySQL

BigQuery bills queries by the data they scan. Partitioning a large table by date lets queries that filter on that date read only the relevant partitions, which is often the biggest single saving on large replicated tables.

Which column

Pick a column you filter on and that doesn't change after the row is created, such as created_at or order_date. A column that changes (like updated_at) moves rows between partitions and makes refreshes more expensive.

Which granularity

• Day for tables with a lot of data per day that you query by recent dates.
• Month or year for long histories with little data per day. BigQuery limits how many partitions a table can have, so daily partitions over many years can exceed it.

Changing partitioning later

BigQuery can't change a table's partitioning in place. The table has to be rebuilt: create a partitioned copy, verify it, and swap it in. Quayen does this for you from the table's menu, pausing refreshes for that table while changes keep being captured, and keeps the original if the copy doesn't match.

Small tables (under a few gigabytes) usually don't need partitioning at all.