How BigQuery skips data

Traditional databases find a row through an index. BigQuery reads whole chunks of a table instead, and a good date filter decides how many it can skip.

How it works

fact_mot_test is partitioned by month of completed_date, and every query must filter that column. BigQuery works out which months to read from your filter before it opens any row. Inside a partition the rows are stored in the order of vehicle_id, test_result and make, which is clustering.

Common mistake

A date filter can look right and still be refused when it formats or casts the column. Run it, read the error, and rewrite the filter as a comparison between completed_date and dates.

Worked example

SELECT make, COUNT(*) AS tests
FROM `learn.mot.fact_mot_test`
WHERE completed_date >= '2024-03-01' AND completed_date < '2024-04-01'
GROUP BY make
ORDER BY tests DESC, make
LIMIT 5

The two comparisons give BigQuery the range of March 2024 before it reads anything.

Only the March partition is read, for the two columns the query names.

Pruning matters for a run that is not answered from the cache.

All BigQuery lessons