Error messagesCost

Cannot query over table without a filter over columns that can be used for partition elimination

The table has require_partition_filter set, so BigQuery refuses any query that cannot prune partitions before reading. Which filters count is specific, and a subquery or an OR does not.

Cannot query over table 'bigquery-public-data.wikipedia.pageviews_2024' without a filter over column(s) 'datehour' that can be used for partition elimination

What BigQuery means

Somebody set require_partition_filter = TRUE on that table, and your query gives BigQuery no way to work out which partitions it needs before it starts reading.

The flag is a guard rail. The table is large enough that a full scan is expensive, so whoever owns it made the filter mandatory. The error itself is cheap, because it happens at planning time, so nothing is read and nothing is billed.

Note that the message names the partitioning column for you. That is the column your filter has to be on.

The smallest query that reproduces it

SELECT COUNT(*) FROM `bigquery-public-data.wikipedia.pageviews_2024`

Even COUNT(*) is refused. BigQuery does not make an exception for queries that happen not to read column data.

The fix

Filter on the column the message names, with values it can evaluate before the query runs:

SELECT COUNT(*)
FROM `bigquery-public-data.wikipedia.pageviews_2024`
WHERE datehour >= '2024-01-01' AND datehour < '2024-01-02'

Which filters count

The test is whether BigQuery can reduce the partition list before it reads anything. Mentioning the column is not enough.

These work:

Filter	Why
`WHERE datehour = '2024-01-01'`	a constant
`WHERE datehour BETWEEN '2024-01-01' AND '2024-01-02'`	a constant range
`WHERE datehour > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)`	a function of constants, evaluated at planning time
`WHERE datehour = '2024-01-01' OR datehour = '2024-01-02'`	every branch of the `OR` restricts the partition column
`WHERE DATE(datehour) = '2024-01-01'`	accepted, though it prunes less well than a direct comparison
`... FROM t JOIN (SELECT TIMESTAMP '2024-01-01' AS d) b ON t.datehour = b.d`	a join condition counts, not just a `WHERE`
a `WHERE` in the outer query over a CTE that selects from the table	BigQuery pushes the filter down into the CTE
`FROM t \|> WHERE datehour = '2024-01-01'`	pipe syntax counts the same as a `WHERE` clause
`WHERE datehour IS NOT NULL`	passes the check, but matches every partition, so it silences the error without saving anything

These do not:

Filter	Why
`WHERE datehour = '2024-01-01' OR views > 5`	the second branch lets any partition through
`WHERE datehour >= (SELECT MIN(datehour) FROM ...)`	the value is not known until the subquery runs
no `WHERE` at all, including in a pipe query that goes straight to `\|> AGGREGATE`	nothing to prune with

The subquery case looks wrong because the query does filter on the right column. BigQuery needs the partition list before it executes anything, and a subquery result is not available then. If you need a dynamic bound, run it as two statements and put the first result into a scripting variable, or widen the filter to a constant range you know contains the answer.

Ingestion-time partitioned tables

If the table is partitioned by ingestion time rather than by a column of its own, there is no real column to filter on. Filter on the pseudocolumn instead:

SELECT COUNT(*) FROM `my-project.logs.events`
WHERE _PARTITIONTIME = TIMESTAMP '2026-10-01'

_PARTITIONTIME holds the ingestion time truncated to the partition boundary, hourly or daily or whatever the table uses. Daily-partitioned tables also have _PARTITIONDATE, the UTC date of that same value, which is easier to filter against. It does not exist on hourly, monthly or yearly tables, so _PARTITIONTIME is the one that always works. See Partitioning.

Turning it off

If you own the table and the requirement is in your way:

ALTER TABLE `my-project.logs.events` SET OPTIONS (require_partition_filter = FALSE)

Think about it first. The flag is usually there because someone has already paid for the full scan once.

Variants

Message	Cause
the same text naming several columns	an externally partitioned (Hive-style) table with more than one partition key
`Custom quota exceeded: Your usage exceeded the custom quota for QueryUsagePerDay`	a daily byte quota set by an admin, not a partition requirement

The job’s error_result.reason is invalidQuery.

In Querylab.io

Querylab.io warns about a partitioned table with no filter on its partitioning column, before you run the query, with a quick fix that adds the WHERE clause. The warning covers every partitioned table, not only the ones with require_partition_filter set, because the cost argument is the same either way. On the wikipedia table above it also reports the size of the scan and offers to add a TABLESAMPLE.

See Cost limits and How a query is billed.