What clustering buys you

Clustering is the second storage setting on fact_mot_test. Knowing what it does for the estimate keeps you from expecting savings that never show.

How it works

The table is clustered by vehicle_id, test_result and make. Inside each month, rows with the same values in those columns are stored next to each other. A query that filters those columns can skip blocks of rows while it runs. On-demand billing charges the bytes a run actually processes after blocks are skipped, rounded up to a whole MiB with at least 10 MiB per table. On a table this small the 10 MiB floor is the whole bill, so a saving from skipping blocks does not show here at all.

Common mistake

Do not widen a date range because another filter looks selective. Decide the months first, then add the other conditions. Narrowing works the other way too: a shorter range is a different question, so narrow it only when you know the rows you want are inside it, and check that the row count is the one you expected.

Worked example

SELECT vehicle_id, make, test_result
FROM `learn.mot.fact_mot_test`
WHERE completed_date = '2024-06-12'
  AND make = 'AUDI'

The date filter picks the June partition. The make condition is a filter on a clustering column.

The query reads three columns of that partition and one more for the date.

All BigQuery lessons