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.
- The editor marks the estimate for
fact_mot_testas an upper bound. - Filters on clustering columns use
=,INor comparisons with fixed values, the same shapes as a date filter. - Block skipping follows the order of the clustering columns. A filter on
vehicle_id, the first column, can skip the most, and a filter on onlymake, the third, can skip little or nothing. - The partition column still has to be filtered, whatever the clustering columns are.
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.