In BigQuery you pay for what you scan, with on-demand pricing, or for the slots your queries keep busy, with capacity pricing. Either way, the cheapest byte is the one you never read. Partitioning and clustering are the two table design features that decide how much of a table a query has to read. Get them right and a dashboard query that scanned 2 TB scans 20 GB. Get them wrong and you pay full price for every query, plus extra metadata overhead.
This guide is for data engineers who build and run BigQuery tables. It explains how each feature works, when to use one, the other or both, and the mistakes that quietly turn partition pruning off.
How BigQuery reads a table
BigQuery stores tables in columnar storage, so selecting fewer columns already cuts the bytes scanned. Partitioning and clustering cut the rows as well:
- Partitioning splits a table into separate segments by a date, timestamp or integer column. A filter on that column lets BigQuery skip whole partitions before the query starts, and the dry-run estimate reflects this.
- Clustering sorts the data inside each partition (or the whole table) by up to four columns and stores it in blocks. A filter or aggregation on those columns lets BigQuery skip blocks while the query runs.
Partitioning is a hard boundary that you can predict before the query runs. Clustering is a finer, best-effort optimisation that you only see in the bytes billed after the query has run.
Partitioning in practice
The three partitioning types
| Type | Partition column | Granularity | Typical use |
|---|---|---|---|
| Time-unit column | DATE, TIMESTAMP or DATETIME | Hour, day, month or year (DATE: day, month or year) | Event and fact tables with a business timestamp |
| Ingestion time | Pseudo-columns _PARTITIONTIME / _PARTITIONDATE | Hour, day, month or year | Raw landing tables where load time is what matters |
| Integer range | INT64 with start, end and interval | Fixed-width ranges | Tables filtered by a numeric key, such as store or tenant ID |
Daily granularity is the default, and partition boundaries are in UTC. Rows with a NULL partition value go to a special __NULL__ partition. Values outside the supported range (before 1960-01-01 or after 2159-12-31 for time partitioning, or outside your integer range) go to __UNPARTITIONED__.
Creating a partitioned and clustered table
Here is a typical event table: partitioned by day on the event timestamp, clustered by the columns most queries filter on, with a mandatory partition filter and automatic expiry of old data.
CREATE TABLE analytics.events
(
event_ts TIMESTAMP NOT NULL,
customer_id STRING,
country STRING,
event_type STRING,
amount NUMERIC
)
PARTITION BY DATE(event_ts)
CLUSTER BY country, event_type, customer_id
OPTIONS (
require_partition_filter = TRUE, -- reject queries that would scan every partition
partition_expiration_days = 400 -- drop partitions older than ~13 months
);require_partition_filter is the single most useful guard-rail in BigQuery. An analyst who forgets the WHERE clause gets an error instead of a full-table scan.
Writing queries that actually prune
Pruning only happens when BigQuery can work out which partitions to read from the filter. Compare these:
-- Prunes: constant expression on the partition column
SELECT country, SUM(amount) AS revenue
FROM analytics.events
WHERE DATE(event_ts) BETWEEN '2026-10-01' AND '2026-10-26'
GROUP BY country;
-- Prunes: date arithmetic on constants is still constant
SELECT COUNT(*)
FROM analytics.events
WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY);
-- Risky: the filter value comes from another column, so
-- BigQuery cannot limit partitions up front
SELECT e.*
FROM analytics.events AS e
JOIN config.report_window AS w
ON e.event_ts >= w.start_ts;The rule of thumb from the BigQuery documentation is to filter the partition column with a constant expression and not to compare it with other columns. If the date range lives in a config table, read it into a variable first with a short script and use the variable in the filter.
Clustering in practice
Clustering works on columns of type STRING, INT64, NUMERIC, BIGNUMERIC, DATE, DATETIME, TIMESTAMP, BOOL, GEOGRAPHY and RANGE. They must be top-level and non-repeated. You can specify up to four columns, and the order matters: the data is sorted by the first column, then the second, and so on. A filter on only the third column gets much less benefit than a filter on the first.
- Put the column used in most filters first, usually a low to medium cardinality column such as country, tenant or status.
- Follow it with columns that are often filtered or joined together with it, such as event type, then customer ID.
- High-cardinality columns such as IDs work well for clustering, which is a big advantage over partitioning: you cannot partition on a STRING column at all.
BigQuery reclusters tables automatically in the background as new data arrives. This is free and does not use your query capacity. Because pruning happens at run time, a dry run may not give an accurate estimate for a clustered table. Check total_bytes_billed after the query instead.
Partitioning vs clustering: which one?
| Question | Partitioning | Clustering |
|---|---|---|
| Columns | One: date/time or integer | Up to four, most scalar types |
| Cost known before the query runs? | Yes, in the dry-run estimate | No, only after the query runs |
| Limit | 10,000 partitions per table | No partition-style limit |
| Best for | Time-sliced queries, data expiry, partition-level loads | Filters on high-cardinality or several columns |
| Maintenance | Partition expiry, partition-level overwrite and delete | Automatic, free reclustering |
| Long-term storage pricing | Applies per partition, so old partitions get cheaper | Applies at table level |
Google’s own guidance is that clustering is generally a good first option, and that unpartitioned tables larger than about 64 MB are likely to benefit from it. Add partitioning on top when you need a firm cost estimate before a query runs, when partitions would average at least 10 GB each, or when you want old partitions to qualify for long-term storage pricing while new data keeps arriving.
For most fact and event tables the answer is both: partition by day (or month for smaller tables), then cluster by your main filter columns.
Checking that it works
Two INFORMATION_SCHEMA views tell you whether your design is paying off. First, look at partition sizes. Many tiny partitions are a warning sign:
SELECT
partition_id,
total_rows,
ROUND(total_logical_bytes / POW(1024, 3), 2) AS logical_gb,
last_modified_time
FROM analytics.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'events'
ORDER BY partition_id DESC
LIMIT 30;Then find the queries that scan the most data against the table over the last week:
SELECT
user_email,
LEFT(query, 120) AS query_start,
ROUND(total_bytes_billed / POW(1024, 3), 1) AS gb_billed
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND job_type = 'QUERY'
AND EXISTS (
SELECT 1 FROM UNNEST(referenced_tables) AS t
WHERE t.dataset_id = 'analytics' AND t.table_id = 'events'
)
ORDER BY total_bytes_billed DESC
LIMIT 20;If the top queries are billing close to the full table size, they are missing a partition filter or filtering on the wrong clustering column. Change region-eu to the region your dataset lives in.
Common pitfalls
- Over-partitioning. Hourly partitions on a modest table create thousands of tiny partitions and lots of metadata. The 10,000-partition limit is about 27 years of daily partitions but only about 416 days of hourly ones. If partitions would be well under 10 GB, use daily or monthly partitions and let clustering do the rest.
- Forgetting the partition predicate in MERGE. A
MERGEinto a partitioned table scans the whole target unless you add a partition filter, for exampleAND t.event_date >= '2026-10-20'in theONclause or a pre-filtered subquery as the target. - Filtering on the wrong column. Partitioning on
event_tsdoes nothing for queries that filter onload_ts. Partition on the column people actually filter by. - Bad clustering order. Clustering by
customer_id, countryhelps little when most queries filter only on country. Put the most common filter first. - Trusting the dry run on clustered tables. The estimate can be far higher than the real bytes billed. Measure with
INFORMATION_SCHEMA.JOBS. - Trying to change partitioning in place. You cannot add or change partitioning on an existing table. Create a new table with
CREATE TABLE ... PARTITION BY ... CLUSTER BY ... AS SELECT, then swap it in. - Ignoring NULLs. Rows with a NULL partition column all go into
__NULL__, which can grow into one huge partition that every query touches. Make the partition columnNOT NULLwhere you can.
Key takeaways
- Partitioning gives hard, predictable pruning on one date or integer column. Clustering gives finer pruning on up to four columns.
- For most large fact tables, partition by day or month and cluster by your top filter columns, in the order queries use them.
- Turn on
require_partition_filterand set partition expiry to protect both cost and storage. - Filter partition columns with constant expressions, and add partition predicates to every
MERGE. - Check partition sizes and bytes billed in
INFORMATION_SCHEMArather than assuming the design works.
Want more on warehouse cost? See our Snowflake cost optimisation playbook and building a data warehouse with BigQuery.
