BigQuery partitioned tables: how to create, query and manage them

What a partition filter saves, measured

Here is a real table: a Search Console export with 58.8 million rows and 10.35 GiB, in 882 daily partitions on data_date. Three versions of the same sum, with the bytes BigQuery reported it would process:

The filter on the partition column cut the scan by a factor of more than a thousand. The third query asks for the same day and costs twice as much as no filter at all: wrapping the partition column in a function stopped the pruning, and the query now reads a second column on top.

That is the one rule to remember about querying partitioned tables: filter on the bare partition column, with a constant on the other side.

Create a table partitioned by date or timestamp

The most common setup: one partition per day, on a date column.

CREATE TABLE mydataset.orders (
  order_id INT64,
  customer STRING,
  amount NUMERIC,
  order_date DATE
)
PARTITION BY order_date;

On a TIMESTAMP column, wrap it to choose the granularity:

CREATE TABLE mydataset.events (
  event_id STRING,
  event_ts TIMESTAMP
)
PARTITION BY DATE(event_ts);

For month or hour partitions, use DATE_TRUNC(order_date, MONTH) or TIMESTAMP_TRUNC(event_ts, HOUR) in the PARTITION BY clause.

How to choose the granularity, from Google’s introduction to partitioned tables (checked October 2026):

• Daily is the default and suits data spread over a wide range of dates.
• Hourly suits high volume over a short range, typically under six months.
• Monthly or yearly suits little data per day over a long range, combined with clustering on the same column.

Partition boundaries are in UTC. A row timestamped 23:30 in New York on the 6th lands in the partition for the 7th. If your reporting day is in another zone, keep that in mind when you filter. See DATE vs DATETIME vs TIMESTAMP.

Partition by ingestion time

If the data has no usable date column, BigQuery can partition by arrival time:

CREATE TABLE mydataset.raw_events (
  payload STRING
)
PARTITION BY _PARTITIONDATE;

The table gets two pseudocolumns, _PARTITIONTIME and _PARTITIONDATE. They do not appear in SELECT *, and you filter on them like any column:

SELECT payload
FROM mydataset.raw_events
WHERE _PARTITIONDATE = '2026-10-06';

Use this for raw landing tables. For anything you report on, a real date column is better, because arrival time is not when the event happened.

Partition by an integer range

CREATE TABLE mydataset.customers (
  customer_id INT64,
  name STRING
)
PARTITION BY RANGE_BUCKET(
  customer_id, GENERATE_ARRAY(0, 100, 10));

This creates ranges of 10 from 0 to 99. Values outside the range go to a partition named __UNPARTITIONED__, and rows with NULL go to __NULL__. Those two special partitions exist for date partitioning as well.

Partition expiration and required filters

Two table options do most of the housekeeping:

CREATE TABLE mydataset.events (
  event_id STRING,
  event_ts TIMESTAMP
)
PARTITION BY DATE(event_ts)
OPTIONS (
  partition_expiration_days = 90,
  require_partition_filter = TRUE
);

• partition_expiration_days deletes each partition that many days after its date. Old data leaves on its own, and so does its storage cost.
• require_partition_filter rejects any query that does not filter on the partition column. It is the cheapest protection against an accidental full scan.

Both can be changed later:

ALTER TABLE mydataset.events
SET OPTIONS (partition_expiration_days = 365);

More on changing tables in modifying BigQuery tables with DDL.

Partition an existing table

You cannot add partitioning to a table that was created without it. You create a new, partitioned table from the old one:

CREATE TABLE mydataset.orders_partitioned
PARTITION BY order_date
AS
SELECT * FROM mydataset.orders;

Then check the row counts match, drop the old table, and rename the new one. The copy is a query over the whole table, so it is billed once as a full scan.

Date-sharded tables, the events_20261006 kind, are a separate case. Google recommends partitioning over sharding, because each shard carries its own schema and metadata. Until you convert them, wildcard tables are how you query them together.

Partitioning by string or by several columns: use clustering

Neither is possible with partitioning. Clustering covers both: it sorts the data inside each partition by up to four columns, of nearly any type, so a filter on those columns also reads less.

CREATE TABLE mydataset.orders (
  order_id INT64,
  country STRING,
  customer STRING,
  amount NUMERIC,
  order_date DATE
)
PARTITION BY order_date
CLUSTER BY country, customer;

A sensible default for an events or orders table is exactly this: partition by date, cluster by the columns you filter and join on most. Clustering is also the way out when a table would need more partitions than the limit allows.

Limits

From Google’s quotas and limits page, checked October 2026:

• 10,000 partitions per table. Daily partitions reach that after about 27 years; hourly partitions after about 14 months.
• 4,000 partitions modified by a single job. A backfill that writes more than 4,000 days in one query is rejected. Split it.
• One partitioning column per table.

Inspect the partitions of a table

SELECT
  partition_id,
  total_rows,
  ROUND(total_logical_bytes / POW(1024, 2), 1)
    AS mb
FROM mydataset.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'orders'
ORDER BY partition_id DESC
LIMIT 10;

This shows whether the data is spread the way you expected. A very large __NULL__ or __UNPARTITIONED__ partition means many rows have a missing or out-of-range partition value, and no filter will ever prune them.

Delete or replace one partition

DELETE FROM mydataset.orders
WHERE order_date = '2026-10-06';

A DELETE that covers whole partitions is efficient, because BigQuery can drop them without rewriting data. To reload one day, delete it and insert the corrected rows, or overwrite the partition from a load job. More in the DML guide.

Mistakes that cancel the benefit

1. A function around the partition column, as measured above.
2. A filter that is not constant. Comparing the partition column to a value from a subquery or another table can prevent pruning. Compute the date first, or pass it as a parameter.
3. Filtering on a different date column. A table partitioned by created_at gets no help from a filter on updated_at.
4. SELECT *. Partitioning limits rows; BigQuery still bills by column. Select only what you need.
5. Too many small partitions. If most partitions hold little data, use monthly partitions and cluster by the date.

To see what a query will scan before running it, use the dry-run estimate in the console, or review past queries in the query history. For how scanned bytes become cost, see BigQuery pricing.

Who makes sure the filter is there

Partitioning only pays off when every query that touches the table filters on the partition column. The table owner can force that with require_partition_filter. What they cannot control is the dashboards and spreadsheets that run their own queries on a schedule, each written by someone else.

That is easier when those reports read from one governed query. In OWOX Data Marts a Data Mart is your SQL, with the partition filter already in it, saved with a description and an owner; reports in spreadsheets and dashboards are built on it and run in your warehouse on the schedule you set. OWOX does not partition tables or tune queries for you. It makes the query you tuned the one that actually runs.

Google BigQueryHow to Load Data into BigQuery: Every Method and When to Use ItOctober 6, 2026→Google BigQueryIs BigQuery a Data Warehouse or a Data Lake? A Marketing GuideOctober 6, 2026→Google BigQueryBigQuery aggregate functions: COUNT, SUM, STRING_AGG, ARRAY_AGG and moreOctober 16, 2026→

See all articles →