# 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.

Source: https://docs.querylab.io/bigquery-errors/cannot-query-over-table-without-a-filter-over-columns/

Updated: 2026-10-25

```text
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

```sql
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:

```sql
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:

```sql
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](https://docs.querylab.io/bigquery/partitioning/).

## Turning it off

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

```sql
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](https://docs.querylab.io/writing-sql/cost-limits/) and [How a query is billed](https://docs.querylab.io/bigquery/how-queries-are-billed/).

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Querying partitioned tables](https://cloud.google.com/bigquery/docs/querying-partitioned-tables)

- [Creating partitioned tables](https://cloud.google.com/bigquery/docs/creating-partitioned-tables)

- [Query externally partitioned data](https://cloud.google.com/bigquery/docs/hive-partitioned-queries)