Partitioning and clustering in BigQuery are two table-level features that physically organize your data to reduce query cost and improve performance. Partitioning splits a table into segments (partitions) based on a date, timestamp, or integer column (or ingestion time), while clustering sorts data within each partition by one or more columns. Together, they let BigQuery scan only the relevant slices of a table instead of the whole thing, which is the single most effective way to keep queries fast and cheap as data grows.
What Partitioning Does
A partitioned table is divided into smaller storage units called partitions. When you query with a filter on the partition column, BigQuery prunes (skips) entire partitions that don’t match, so it only reads the bytes it needs. You can partition by:
- Time-unit columns – DATE, TIMESTAMP, or DATETIME columns, with daily, hourly, monthly, or yearly granularity.
- Ingestion time – using the pseudo-column
_PARTITIONTIME(or_PARTITIONDATEfor date-based). This is automatic and requires no actual timestamp column. - Integer range – partition by an INTEGER column with a start, end, and interval (e.g., customer IDs 0–1,000,000 by 100,000).
For example, a table of sales orders with an order_date column partitioned by day means a query with WHERE order_date = '2025-03-14' scans only that day’s data, not the entire history.
What Clustering Does
Clustering is a secondary organization that sorts rows within each partition (or within the whole table if it’s not partitioned) based on the values of one or more columns. It doesn’t create separate storage segments like partitions; instead, it arranges the data in a sorted order, which lets BigQuery use block-level pruning. When you filter on a clustering column, the query engine can skip blocks of data that fall outside the filter’s range.
Clustering works best on columns with high cardinality (many distinct values) that are frequently used in filters or sorts, such as customer_id, product_id, or region. You can specify up to four clustering columns, and the order matters—the first column has the most significant sorting effect.
How They Work Together
Partitioning and clustering are complementary, not mutually exclusive. Partitioning provides coarse-grained pruning (skip whole date ranges), while clustering provides fine-grained pruning within each partition (skip blocks of rows). The classic pattern is to partition by a date column (e.g., event_date) and cluster by a commonly filtered dimension (e.g., user_id). This way, a query for a specific user on a specific day reads only a small slice of the table.
One important nuance: clustering is most effective when the data is inserted in a way that matches the clustering order. If you load data in random order, BigQuery will still sort it during initial write, but subsequent DML operations (like UPDATE or DELETE) can cause clustering to degrade over time. You can manually re-cluster with CREATE OR REPLACE TABLE or use the CLUSTER BY option during creation.
How to Choose Partitioning vs. Clustering
There’s no single rule, but these guidelines help:
- Use partitioning when you have a date or timestamp column that appears in most queries, and you need to manage data lifecycle (e.g., delete old partitions). Partitioning also allows you to set partition expiration to automatically drop old data.
- Use clustering when you filter on columns that aren’t dates, or when you need more granular pruning than partitioning alone provides. Clustering also helps with queries that sort or group by those columns.
- Combine both when you have a date column plus one or more high-cardinality filter columns. This is the most common production setup.
- Avoid over-partitioning – too many partitions (e.g., partitioning by hour for a small table) creates metadata overhead and can make queries slower. BigQuery has a limit of 4,000 partitions per table (for time-unit and integer-range partitioning), and each partition has a minimum storage cost.
For example, a 10 TB table of web analytics events: partition by event_date (daily) and cluster by user_id and event_name. A query for a specific user’s actions on a specific day scans just a few MB instead of GB.
Common Problems and Misconceptions
A frequent misconception is that partitioning alone is enough for all performance issues. In reality, if you don’t filter on the partition column, partitioning does nothing for that query. Similarly, clustering only helps if your filter uses the clustering columns in a way that matches the sort order—using a function like LOWER(customer_email) on a clustering column defeats pruning because the expression isn’t sargable.
Another common problem is choosing the wrong partition granularity. Daily partitioning on a table with only a few thousand rows per day creates thousands of tiny partitions, increasing metadata overhead and making queries slower than a non-partitioned table. Also, be aware that SELECT * from a partitioned table still scans all partitions—partitioning doesn’t help if you don’t filter.
Finally, clustering does not guarantee a strict sort order that you can rely on for merge joins or ordered output. It’s an optimization, not a promise of physical order. For guaranteed order, use an explicit ORDER BY in your query.
Cost and Performance Impact
The main benefit is cost reduction: BigQuery charges per byte scanned (on-demand pricing) or per slot (flat-rate). Partitioning and clustering directly reduce bytes scanned, so you pay less. For example, a query that scans 1 GB instead of 100 GB costs roughly 1% as much (with on-demand pricing).
Performance also improves because less I/O means faster queries. However, there’s a small overhead: writing to a partitioned and clustered table takes slightly longer because BigQuery must sort and organize the data. This is usually negligible compared to the query savings.
Storage costs: partitioning doesn’t change storage cost per byte, but it enables features like partition expiration, which can automatically delete old partitions and reduce storage costs over time. Clustering itself doesn’t add storage overhead.
How to Create Partitioned and Clustered Tables
You define both at table creation time. Here’s an example using DDL:
CREATE TABLE `my_dataset.sales` (
order_id INT64,
order_date DATE,
customer_id INT64,
amount NUMERIC
)
PARTITION BY order_date
CLUSTER BY customer_id;
You can also create a partitioned table from a query:
CREATE TABLE `my_dataset.sales_partitioned` (
order_id INT64,
order_date DATE,
customer_id INT64,
amount NUMERIC
)
PARTITION BY order_date
CLUSTER BY customer_id
AS SELECT * FROM `my_dataset.raw_sales`;
For ingestion-time partitioning, use PARTITION BY _PARTITIONDATE (or _PARTITIONTIME). For integer-range partitioning, use PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 1000000, 100000)) – but note that this is a more advanced pattern.
To change partitioning or clustering on an existing table, you must recreate it (e.g., CREATE OR REPLACE TABLE). There’s no ALTER TABLE to add clustering after creation.
Best Practices and Maintenance
Here are practical tips:
- Choose partition columns that match your most common WHERE clauses. If most queries filter by date, partition by date.
- Limit clustering columns to 2–3 – more than that adds sorting overhead with diminishing returns.
- Monitor query bytes scanned using the Information Schema or the query plan; if you see full table scans, your filters aren’t using the partitioning/clustering effectively.
- Re-cluster periodically if you do many updates/deletes – you can run
CREATE OR REPLACE TABLE ... CLUSTER BY ... AS SELECT * FROM ...to re-sort. - Use partition expiration for time-series data to automatically delete partitions older than a threshold (e.g., 365 days).
Remember that clustering columns are limited to primitive types (STRING, INT64, NUMERIC, DATE, TIMESTAMP, etc.) and cannot be of type ARRAY, STRUCT, or GEOGRAPHY.
Frequently Asked Questions
Can I cluster a table without partitioning it?
Yes. You can create a non-partitioned table with CLUSTER BY – clustering works on the entire table. This is useful when you don’t have a natural date column but have high-cardinality filter columns.
Does clustering work with wildcard tables?
No. Clustering is not supported on wildcard tables (tables with * in the name). You must use a partitioned table or query each table separately.
What happens if I filter on a clustering column but not the partition column?
BigQuery will still use clustering to prune blocks within each partition, but it must scan all partitions. So you get some benefit, but not as much as if you also filtered on the partition column.
Can I change the partitioning or clustering of an existing table?
Not in place. You must recreate the table using CREATE OR REPLACE TABLE with the new definition, and then copy the data. This is a common migration task.
Is there a limit on the number of partitions?
Yes, BigQuery allows up to 4,000 partitions per table (for time-unit and integer-range partitioning). If you need more, consider using a coarser granularity (e.g., month instead of day) or a different design.
Does partitioning help with DELETE or UPDATE performance?
Yes. If you delete rows from a specific partition, BigQuery can drop the entire partition (using DELETE with a filter on the partition column) much faster than scanning the whole table. Similarly, updates that touch a single partition are more efficient.
Related
- XLSX or XLS Viewer: Which File Format Should You Use?
- Excel XPS Export Options: How to Save as XPS
- Excel Wrap Text in Quotes? How to Add Quotation Marks
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Number Format Brackets for Negative Numbers ExplainedTo show negative values in parentheses in Excel, apply a custom number format such as #,##0;(#,##0); to show…
- Excel Group Rows Shortcut: How to Collapse and ExpandIn Excel for Microsoft 365 on Windows, select the rows you want to group and press Alt+Shift+Right Arrow.…
- Excel Consolidation: Combine Data from Multiple SheetsTo consolidate multiple Excel sheets, use Data > Data Tools > Consolidate to summarize matching ranges, or use…
- Dynamically Resize an Excel Table as You Add New DataTo make an Excel table grow as you add data, enter or paste new records directly in the…
- How to Join Two Columns in Google Sheets With a FormulaTo join two columns in Google Sheets, enter a formula such as =A2&" "&B2 in an empty column,…
- Excel Solver Add-In Not Showing in Ribbon? FixesIf the Solver button is missing from Excel’s ribbon, first enable the built-in add-in: go to File >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.