TopolearnClustering prunes on columns that partitioning can't, such as customer or country, at no maintenance cost.
Clustering sorts a table's data by up to four columns, in the order you list them, and stores it in blocks with known value ranges. Filters on those columns let BigQuery skip blocks, and aggregations or joins on them work on co-located data.
CREATE TABLE web.events (...) PARTITION BY DATE(event_ts) CLUSTER BY country, event_name;How it compares with partitioning:
- Partitioning gives hard boundaries, exact cost estimates, partition-level expiration and management. It works on a single column.
- Clustering works on several columns, including high-cardinality ones like
user_id, and has no partition-count limit. Block pruning happens at run time, so the dry-run estimate is an upper bound: the actual bytes billed can be much lower. - They combine well: partition by date, cluster by the columns you filter or group by most.
Column order matters. Filters help most on the first clustering column, then on the first and second together, and so on, much like a composite index.
New data is written into new blocks, which slowly degrades clustering. BigQuery performs automatic reclustering in the background at no charge. You can change the clustering columns of an existing table with bq update --clustering_fields or the API; only new data follows the new spec unless you rewrite the table.
Clustering helps most on tables of at least several GB per partition. On small tables there are too few blocks to skip.
Example
CREATE TABLE web.events_clustered
PARTITION BY DATE(event_ts)
CLUSTER BY country, event_name
AS SELECT * FROM web.events WHERE DATE(event_ts) >= '2025-01-01';
-- Prunes partitions (date) AND blocks (country, event_name)
SELECT user_id, COUNT(*) AS n
FROM web.events_clustered
WHERE DATE(event_ts) = '2025-06-01'
AND country = 'DE'
AND event_name = 'purchase'
GROUP BY user_id;
-- Filters only on event_name (second clustering column): less effective
SELECT COUNT(*) FROM web.events_clustered
WHERE DATE(event_ts) = '2025-06-01' AND event_name = 'purchase';
-- Inspect partitioning and clustering of a table
SELECT column_name, is_partitioning_column, clustering_ordinal_position
FROM web.INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'events_clustered';Remember
- CLUSTER BY up to four columns; order matters like a composite index
- Combine: partition by date, cluster by frequent filter columns
- Dry-run estimates are upper bounds on clustered tables
- Reclustering is automatic and free
Watch out for
- Expecting the dry-run estimate to drop after clustering; the savings show up in bytes billed after running.