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.

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

What a partition looks like 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:

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

That is 2.4 GB. A week reads seven partitions:

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

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

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

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), 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.

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.

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

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:

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

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

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:

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

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:

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

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:

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

A filter that looks like pruning and isn’t

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:

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'

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:

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:

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), and the Missing Partition Filter check warns before you run (code quality).

Limits that matter

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

In Querylab.io the estimate in the status bar updates as you edit the WHERE clause, so a filter that doesn’t prune shows up as a number that didn’t move.