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.
- A filter limits the partitions read when it names one run of dates that BigQuery can find before the query starts. Comparing
completed_datewith fixed values, using=,IN,BETWEEN,>=or<, does that. - A function on the column works when its answer is still one run of dates. Take
DATE_TRUNC(completed_date, MONTH) = '2024-06-01'. It is the days of June 2024, so it reads the same bytes as aBETWEENover June. Now takeEXTRACT(YEAR FROM completed_date) = 2024. It is the days of 2024, so it reads the whole year. - A month filter matches June in every year the table holds, which is a separate piece of the calendar for each one. BigQuery does not list separate pieces ahead of the query. So a month filter on its own gives it nothing to use. Put a year filter beside it and the query reads the whole year, because the month adds nothing BigQuery can use.
- Turning the column into text hides the range.
FORMAT_DATE('%Y', completed_date) = '2024'andCAST(completed_date AS STRING)are not recognised, and this table rejects them with the same error as a query that has no date filter at all. - Those two are examples of one rule each. Whether a filter satisfies the requirement to name
completed_dateand whether it narrows the partitions are two separate questions, and the Cost optimization track comes back to a filter that answers them differently. - A date read from another table with
= (SELECT ...)or a join does not prune, and this table rejects such a filter. A value BigQuery can compute ahead of the query does prune, such asCURRENT_DATE(). - Clustering keeps similar values together inside each partition. It is a storage order, and the table keeps no index to look rows up by.
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 5The 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.