BigQuery Partitioned Tables: How Pruning Cuts Cost

BigQuery partitioned tables in simple terms

On a table with two years of daily data, a query for a single day scans all 730 days of rows if the table is not partitioned. With partitioning, the same query touches only one day’s worth of data.

The problem partitioning solves is straightforward: large tables are expensive and slow to scan in full. Partitioning lets BigQuery skip the irrelevant portions automatically, as long as your query filters on the partition column. If you are working with BigQuery for the first time, think of partitioning as the most direct way to control scan volume on growing tables.

Why partitioning matters

BigQuery charges for on-demand queries based on the number of bytes scanned. On a non-partitioned table with two years of event data, a query asking about yesterday scans every byte in the table. On a date-partitioned table, the same query reads only yesterday’s partition.

Over hundreds of queries per day, those savings compound fast. For a deeper look at how scan volume affects your bill, see BigQuery pricing explained.

Performance improves for the same reason. Fewer bytes to read means fewer workers needed and faster results. On large production tables, partitioning is not a nice-to-have. It is a baseline requirement.

How partition pruning works

Partition pruning is the mechanism that makes partitioning useful. When BigQuery compiles your query, it checks whether your WHERE clause filters on the partition column. If it does, BigQuery identifies which partitions match and reads only those. Every other partition is skipped entirely.

Here is a query that prunes correctly on a table partitioned by DATE(event_timestamp):

-- Partition pruning: reads only the 2026-03-01 partition
SELECT user_id, event_type, COUNT(*) AS events
FROM analytics.events
WHERE DATE(event_timestamp) = '2026-03-01'
GROUP BY user_id, event_type;

And here is a query on the same table that does not prune:

-- No pruning: scans every partition in the table
SELECT user_id, event_type, COUNT(*) AS events
FROM analytics.events
WHERE user_id = 'usr-5678'
GROUP BY user_id, event_type;

Partition types in BigQuery

Ingestion-time partitioning

Rows are assigned to partitions based on when BigQuery ingested them. The partition column is a pseudo-column called _PARTITIONTIME.

When to use it: when you do not have a reliable timestamp column in your data, or when you are streaming data and want automatic daily partitions without schema changes.

-- Create a table with ingestion-time partitioning
CREATE TABLE analytics.raw_logs (
  log_message STRING,
  severity STRING
)
PARTITION BY _PARTITIONTIME;

Time-unit column partitioning

Rows are partitioned by the value of a top-level DATE, TIMESTAMP, or DATETIME column in your schema. You choose the granularity: hour, day, month, or year.

-- Create a table partitioned by day on a TIMESTAMP column
CREATE TABLE analytics.events (
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP
)
PARTITION BY DATE(event_timestamp);

Tradeoff: you must choose the right granularity. Partitioning by hour on a table that receives only a few hundred rows per hour creates many tiny partitions with high metadata overhead. Match granularity to your data volume and query patterns.

Integer-range partitioning

Rows are partitioned by an INTEGER column using a defined range and interval. For example, partition a customer_id column into ranges of 10,000.

When to use it: when your natural query filter is an integer key rather than a date. This is less common but useful for tables where every query filters on an ID range.

-- Create a table with integer-range partitioning
CREATE TABLE analytics.customer_metrics (
  customer_id INT64,
  metric_name STRING,
  metric_value FLOAT64
)
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 1000000, 10000));

Tradeoff: you must define the range boundaries upfront. Values outside the defined range go into an overflow partition. If your ID space grows beyond the defined range, the overflow partition accumulates data and pruning becomes less effective for those rows.

Partitioning vs clustering

Partitioning and clustering both reduce bytes scanned, but they work at different levels. Partitioning divides the table into separate physical segments that BigQuery can skip entirely. Clustering sorts the rows within each partition by up to four columns, allowing BigQuery to skip blocks of rows within a partition.

For more on combining these techniques, see BigQuery performance optimisation.

When to use partitioned tables

• Event and clickstream tables where queries almost always filter by date range
• Daily fact tables in a data warehouse that are queried by business date
• Billing and export data such as BigQuery billing export tables that grow continuously and are queried by invoice period
• Log and time-series analytics where retention windows drive queries to recent data
• Large append-heavy analytics tables that grow by millions of rows per day and are queried for recent windows

When partitioning is the wrong tool

Many tiny partitions. If your partition column has extremely high cardinality or very low data volume per partition, you end up with thousands of small partitions. Each partition carries metadata overhead, and BigQuery has a limit of 4,000 partitions per partitioned table. In these cases, clustering alone may be a better fit.

Multi-column or high-cardinality filters. If your queries filter on several columns and rarely use a single dominant column, clustering across those columns often provides better pruning than partitioning on one of them.

Queries rarely filter on the partition column. Partitioning only helps when queries include a filter on the partition column. If most of your queries use full-table aggregations or filter on unrelated columns, partitioning adds complexity without benefit. For these workloads, focus on other performance techniques such as column selection and approximate aggregation.

How to create and use a partitioned table

Create a partitioned table

CREATE TABLE analytics.events (
  event_id STRING,
  user_id STRING,
  event_type STRING,
  event_timestamp TIMESTAMP,
  payload STRING
)
PARTITION BY DATE(event_timestamp);

Create a partitioned and clustered table

Combining partitioning and clustering gives you both partition-level and block-level pruning. If you are loading data into BigQuery for analytics, this is the recommended table setup for large event tables.

CREATE TABLE analytics.events
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type
AS SELECT * FROM staging.raw_events;

Query with a partition filter

-- Prunes to one day's partition, then uses clustering to skip blocks
SELECT user_id, COUNT(*) AS event_count
FROM analytics.events
WHERE DATE(event_timestamp) = '2026-03-01'
  AND event_type = 'purchase'
GROUP BY user_id;

Set require_partition_filter

This option causes BigQuery to reject queries that do not filter on the partition column. It is a cost-control guardrail that prevents accidental full-table scans.

-- Enable require_partition_filter on an existing table
ALTER TABLE analytics.events
SET OPTIONS (require_partition_filter = true);

-- This query would be rejected with require_partition_filter enabled
SELECT COUNT(*) FROM analytics.events WHERE event_type = 'purchase';

Inspect partitions with INFORMATION_SCHEMA

You can check which partitions exist and how large they are using INFORMATION_SCHEMA.PARTITIONS.

SELECT
  partition_id,
  total_rows,
  total_logical_bytes
FROM `project.analytics.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'events'
ORDER BY partition_id DESC
LIMIT 10;

Using the bq CLI

You can also create partitioned tables and query partition metadata from the command line. This is useful when scripting table creation in data pipelines.

# Create a partitioned table from the command line
bq mk \
  --table \
  --time_partitioning_field event_timestamp \
  --time_partitioning_type DAY \
  --clustering_fields user_id,event_type \
  analytics.events \
  event_id:STRING,user_id:STRING,event_type:STRING,event_timestamp:TIMESTAMP,payload:STRING

How to know it is working

Check bytes processed. After running a query, look at the “Bytes processed” line in the BigQuery console or in the query job metadata. If your partition filter is working, bytes processed should be a fraction of the total table size.

-- Dry run to estimate bytes scanned without executing the query
-- In the BigQuery console, click "More" > "Dry run" before running
-- Or use the bq CLI:
-- bq query --dry_run --use_legacy_sql=false 'SELECT ...'

Inspect existing partitions. Use the INFORMATION_SCHEMA.PARTITIONS query from the previous section to verify that data is distributed across partitions as expected. If all data is in a single partition, your partitioning column or granularity may need adjustment.

Avoid accidental full-table scans. Enable require_partition_filter on tables where full scans are never intentional. Monitor the total_bytes_processed field in INFORMATION_SCHEMA.JOBS to catch queries that scan more than expected. If a query is scanning too much data, see troubleshooting BigQuery query too large for guidance.

Partition expiry

Partition expiry automatically deletes partitions older than a specified number of days. This is useful for event tables, logs, and any data with a defined retention window.

-- Set partition expiry to 90 days at table creation
CREATE TABLE analytics.events (
  event_id STRING,
  user_id STRING,
  event_timestamp TIMESTAMP
)
PARTITION BY DATE(event_timestamp)
OPTIONS (partition_expiration_days = 90);

-- Or add it to an existing table
ALTER TABLE analytics.events
SET OPTIONS (partition_expiration_days = 90);