# Partitioned tables in BigQuery

> A partitioned BigQuery table is split by a date, timestamp or integer column. A filter on that column makes BigQuery skip the other partitions and bill only the bytes it reads. Which filters prune, which silently don't, and the limits that bite.

Source: https://docs.querylab.io/bigquery/partitioning/

Updated: 2026-10-14

A partitioned table is stored in separate pieces, one per day, month, hour, year or integer range of a column you choose. When a query filters on that column, BigQuery reads only the pieces that can match and leaves the rest alone. This is called **partition pruning**. Because on-demand queries are [billed by the bytes read](https://docs.querylab.io/bigquery/how-queries-are-billed/), pruning is the most direct way to make a query on a big table cheaper.

Partitioning is chosen when the table is created and covers one column. Everything below is about picking that column and writing filters BigQuery can use.

## Partitions in the cost estimate

`bigquery-public-data.wikipedia.pageviews_2020` holds every Wikipedia page view of 2020, one row per page per hour. It is partitioned by day on the `datehour` column. Reading the `views` column for one day:

**one day of a year-long table**

```sql
SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour >= '2020-03-01' AND datehour < '2020-03-02'
```

BigQuery dry run: this query processes 2.4 GB.

That is 2.4 GB. A week reads seven partitions:

**seven days**

```sql
SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour >= '2020-03-01' AND datehour < '2020-03-08'
```

BigQuery dry run: this query processes 16.5 GB.

A filter that matches the whole year reads every partition. The same one column costs 833 GB:

**a filter that excludes nothing**

```sql
SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour >= '2020-01-01'
```

BigQuery dry run: this query processes 833.2 GB.

One day is about 1/350 of the year, and the estimate shows it. The estimate is available before the query runs, which is the other reason to partition: you can see the price of a query on a partitioned table before you pay it. Querylab.io shows it in the status bar as you type ([cost estimates](https://docs.querylab.io/writing-sql/cost-limits/#what-gets-estimated)), and the BigQuery console shows it too.

## Three ways to partition

**Time-unit column.** Partition on a `DATE`, `TIMESTAMP` or `DATETIME` column. The granularity is daily (the default), hourly, monthly or yearly. `DATE` columns can't be partitioned by hour. The pageviews table above is of this kind.

```sql
CREATE TABLE shop.orders (
  order_id INT64,
  placed_at TIMESTAMP,
  country STRING
)
PARTITION BY DATE(placed_at);

-- monthly instead of daily
PARTITION BY TIMESTAMP_TRUNC(placed_at, MONTH)
```

**Ingestion time.** BigQuery assigns each row to the partition of the moment it was loaded. The table gets two pseudocolumns, `_PARTITIONTIME` (a `TIMESTAMP`) and, for daily tables, `_PARTITIONDATE`. They are not returned by `SELECT *`; select them by name with an alias.

```sql
CREATE TABLE shop.raw_events (payload STRING)
PARTITION BY _PARTITIONDATE;

SELECT _PARTITIONTIME AS loaded_at, payload
FROM shop.raw_events
WHERE _PARTITIONDATE = '2026-10-01';
```

**Integer range.** Partition on an `INT64` column by giving a start, an end and an interval. Values outside the range go to a partition called `__UNPARTITIONED__`, and `NULL` goes to `__NULL__`. Time-unit tables have the same two special partitions: `NULL` dates go to `__NULL__`, and dates before 1960-01-01 or after 2159-12-31 go to `__UNPARTITIONED__`.

```sql
CREATE TABLE shop.customers (customer_id INT64, name STRING)
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100000, 1000));
```

These statements are not run on this page. The partition expression has to be one of a short list, and BigQuery says so when it isn't:

**STRING can't be a partition column**

```sql
CREATE TEMP TABLE events (
  event_id INT64,
  country STRING
)
PARTITION BY country;
SELECT * FROM events
```

BigQuery rejects this query:

```text
Query error: PARTITION BY expression must be _PARTITIONDATE, DATE(_PARTITIONTIME), DATE(<timestamp_column>), DATE(<datetime_column>), DATETIME_TRUNC(<datetime_column>, DAY/HOUR/MONTH/YEAR), a DATE column, TIMESTAMP_TRUNC(<timestamp_column>, DAY/HOUR/MONTH/YEAR), DATE_TRUNC(<date_column>, MONTH/YEAR), or RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<int64_value>, <int64_value>[, <int64_value>])) at [1:1]
```

**neither can FLOAT64**

```sql
CREATE TEMP TABLE readings (
  sensor_id INT64,
  temperature FLOAT64
)
PARTITION BY temperature;
SELECT * FROM readings
```

BigQuery rejects this query:

```text
Query error: PARTITION BY expression may not be a floating point type at [5:14]
```

## Rules for the partition column

- One column only. BigQuery can't partition by two columns.
- A top-level column. A field inside a `STRUCT` can't be used, and neither can an `ARRAY` column.
- You can't turn an existing table into a partitioned one. Create a new table with `CREATE TABLE ... PARTITION BY ... AS SELECT` and copy the data over.
- Day, hour, month and year boundaries are in UTC.

The UTC rule catches people with timestamps. An order placed at 23:30 in New York on March 1 is 04:30 UTC on March 2, so it lands in the March 2 partition:

**the partition day is the UTC day**

```sql
WITH orders AS (
  SELECT 1 AS order_id, TIMESTAMP '2026-03-01 23:30:00-05:00' AS placed_at UNION ALL
  SELECT 2, TIMESTAMP '2026-03-01 12:00:00-05:00' UNION ALL
  SELECT 3, TIMESTAMP '2026-03-02 01:00:00+09:00'
)
SELECT
  order_id,
  DATE(placed_at) AS partition_day,
  DATE(placed_at, 'America/New_York') AS new_york_day
FROM orders
ORDER BY order_id
```

Result in BigQuery (3 rows):

| order_id | partition_day | new_york_day |
| --- | --- | --- |
| 1 | 2026-03-02 | 2026-03-01 |
| 2 | 2026-03-01 | 2026-03-01 |
| 3 | 2026-03-01 | 2026-03-01 |

Filters on `DATE(placed_at)` match the partition day, not the New York day. If the business day is local, filter with the UTC range that covers it, or store a separate local `DATE` column and partition on that.

## Which filters prune

BigQuery decides what to read before it starts the query, from the filter itself. It can do that when the filter compares the partition column to constants, written so that the column stands alone on one side or sits inside one of a short list of functions. Google lists `DATE_ADD`, `DATE_SUB`, `DATE_DIFF`, `DATE_TRUNC`, `DATETIME_DIFF`, `TIMESTAMP_ADD`, `TIMESTAMP_SUB`, `TIMESTAMP_DIFF`, `TIMESTAMP_TRUNC`, `EXTRACT` with `DATE` or `YEAR`, and `FORMAT_TIMESTAMP` with `%F`, `%Y-%m-%d` or `%Y%m%d`, each with constant extra arguments.

Wrapping the column in `DATE()` still prunes. It reads the same bytes as the plain range:

**DATE(datehour) = ...**

```sql
SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE DATE(datehour) = '2020-03-01'
```

BigQuery dry run: this query processes 2.4 GB.

Other columns in the `WHERE` clause are fine, but they don't add to the pruning on an unclustered column. This query has the same estimate as the plain one-day query:

**views > 100 does not reduce the estimate**

```sql
SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour >= '2020-03-01' AND datehour < '2020-03-02'
  AND views > 100
```

BigQuery dry run: this query processes 2.4 GB.

### A filter that doesn't prune

The lower bound here comes from a subquery. BigQuery can't know its value before running the query, so it can't use it to choose partitions. It prunes with the other, constant bound only, and reads every partition from January 1 up to March 2:

**a subquery as the bound**

```sql
SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour >= (
    SELECT MIN(datehour)
    FROM `bigquery-public-data.wikipedia.pageviews_2020`
    WHERE datehour >= '2020-03-01' AND datehour < '2020-03-02'
  )
  AND datehour < '2020-03-02'
```

BigQuery dry run: this query processes 146.2 GB.

This query is meant to read one day, and the rows it returns are the same as in the one-day version, but its estimate is 146 GB instead of 2.4 GB. That is the usual shape of a pruning bug: the result is right and the bill is about 60 times larger. Compare the estimate before and after you add a filter, and look at the number, not at whether the query ran.

Google's docs name cases that never prune: a filter that compares the partition column to another column of the same table, arithmetic on an integer partition column (`customer_id + 1 BETWEEN 30 AND 50`), and functions outside the list above, such as `EXTRACT(MONTH ...)` or `FORMAT_TIMESTAMP` with a pattern other than the three listed.

## Require a partition filter

A table can refuse any query that has no usable filter on the partition column. The pageviews table is set up this way. Run without a filter, BigQuery rejects the query before it reads anything:

```
SELECT views FROM `bigquery-public-data.wikipedia.pageviews_2020`

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

That is the output of a dry run, shown as text because a dry run that fails can't be replayed on this page. Turn the setting on when you create the table or later:

```sql
CREATE TABLE shop.orders (order_id INT64, placed_at TIMESTAMP)
PARTITION BY DATE(placed_at)
OPTIONS (require_partition_filter = TRUE);

ALTER TABLE shop.orders SET OPTIONS (require_partition_filter = TRUE);
```

The filter has to be joined to the rest of the `WHERE` clause with `AND`. This version is rejected with the same error, because the `OR` could return rows from any partition:

```sql
WHERE datehour >= '2020-03-01' AND datehour < '2020-03-02'
   OR wiki = 'en'
```

A filter that only uses `EXTRACT(MONTH FROM datehour)` is rejected too, since BigQuery can't turn it into a list of partitions. The requirement also applies to views and materialized views built on the table. In Querylab.io a table's Details tab shows **Partition Filter Required** under Partitioning & Clustering ([table information](https://docs.querylab.io/schema-browser/table-information/#what-the-details-tab-reports)), and the Missing Partition Filter check warns before you run ([code quality](https://docs.querylab.io/writing-sql/code-quality/)).

## Limits that matter

| Limit                                                     | Value  |
| --------------------------------------------------------- | ------ |
| Partitions per table                                      | 10,000 |
| Partitions one job can modify                             | 4,000  |
| Partition modifications per day, column-partitioned table | 30,000 |
| Ranges in a range-partitioned table                       | 10,000 |

At 10,000 partitions, daily partitioning covers about 27 years and hourly partitioning about 14 months. A load or `INSERT` that writes to more than 4,000 partitions at once is rejected, which is what you hit when you load years of history into a daily table in one job. Load it in batches, or partition by month.

Google's advice on size: a partition should hold at least about 10 GB. (The pageviews table above has 366 daily partitions averaging 6.3 GiB, so the number is a rule of thumb.) Many small partitions make the table's metadata large and slow down planning. For small tables, partition by month or year, or [cluster](https://docs.querylab.io/bigquery/clustering/) instead.

## Partitions also help outside queries

- **Expiration.** `partition_expiration_days` deletes each partition that many days after the end of its UTC period, with no job to schedule.
- **Cheaper storage.** Each partition counts separately for the long-term storage discount. A partition that hasn't been modified for 90 days is billed at the lower rate even while newer partitions are being written. In an unpartitioned table one write resets the clock for all of it.
- **Targeted loads and deletes.** A load can write to one partition with the decorator `table$20260301`, and a partition can be deleted without scanning the rest of the table.

## Partitioning and sharding

Before partitioned tables existed, people made one table per day, `events_20260301`, and queried them with a [wildcard](https://docs.querylab.io/bigquery/wildcard-tables/). That still works, and public datasets use it, but Google recommends partitioning: sharded tables each carry their own schema and metadata, and BigQuery checks permissions on each one. If you have shards, they can be converted into one ingestion-time partitioned table.

To choose between partitioning and clustering for a new table, see [Partitioning vs clustering](https://docs.querylab.io/bigquery/partitioning-vs-clustering/).

See how much a partition filter saves on a real table. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

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

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

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

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

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