In BigQuery, how does clustering differ from partitioning, and when do you need both?

## Two different mechanisms Both partitioning and clustering exist to read less data, but they work at different granularities, are decided at different times, and have different limits. **Partitioning** splits the table into physical partitions on a single expression — ingestion time, a time-unit column, or an integer range bucket. BigQuery records each partition's identity in table metadata, so the planner can exclude partitions statically, before reading anything. Consequences: exactly one partitioning axis per table; a hard cap on how many partitions a table may have; and pruning that shows up in a dry-run byte estimate. **Clustering** does not split anything. `CLUSTER BY a, b, c` tells BigQuery to store the rows of each partition sorted by `a`, then `b`, then `c`. Because sorted data has narrow value ranges per storage block, BigQuery can consult block metadata during execution and skip blocks whose range cannot satisfy the predicate. Consequences: up to four clustering columns; no cardinality limit at all; and savings that appear only in the *actual* bytes billed, never in the dry-run estimate. ```sql CREATE TABLE ds.events ( event_ts TIMESTAMP, tenant_id STRING, user_id STRING, amount NUMERIC ) PARTITION BY DATE(event_ts) CLUSTER BY tenant_id, user_id; ``` ## Why the column order in CLUSTER BY matters Clustering is a sort by a composite key, so it behaves like a prefix. A filter on `tenant_id` narrows the block set sharply, because all rows of a tenant sit together. A filter on `tenant_id AND user_id` narrows it further. A filter on `user_id` **alone** helps much less — those rows are scattered across every tenant's region of the partition. So order the clustering columns by how often each is filtered on and, secondarily, from lower to higher cardinality — the most commonly filtered column first. Getting the order wrong is the usual reason clustering "doesn't do anything". ## Which column goes where The decision rule follows the limits. Partition on the axis with a modest number of buckets that almost every query constrains — nearly always a date. Cluster on the high-cardinality columns that queries filter or group by: `tenant_id`, `user_id`, `country`, `event_type`, `device_id`. You cannot partition on `user_id` in any useful way; millions of distinct values would blow past the partition cap and produce tiny partitions whose per-partition overhead outweighs any saving. Clustering has no such problem — it is a sort, not a split. Clustering also helps operations other than filtering. Because rows sharing a clustering value are colocated, aggregations and joins keyed on the clustering prefix do less work, and a `WHERE` on the clustering prefix reads fewer blocks even without an equality match — a range works too. ## Maintenance and cost A clustered table degrades as you write into it: each load or streaming batch arrives sorted only within itself, so blocks from different writes overlap in value range and block skipping gets weaker. BigQuery handles this with **automatic reclustering**, a background service that rewrites blocks back into sorted order. It is free — it does not consume your slots and is not billed — and requires no operator action. That is a meaningful difference from systems where re-sorting is a job you schedule and pay for. Clustering adds no storage cost. Partitioning does not add storage cost either, but it does add metadata and, if the granularity is too fine, a lot of small partitions that read inefficiently. ## The dry-run asymmetry This is the most-asked detail. Because partition elimination is a planning-time decision, `bq query --dry_run` reports the post-pruning byte count and you can trust it. Because block skipping from clustering happens during execution, the dry run reports the byte count as if no clustering existed — an upper bound. A clustered table therefore routinely bills far less than its estimate, and cost-control tooling built on the estimate (maximum-bytes-billed limits, pre-flight checks) will be pessimistic. To see the real number, look at the completed job's statistics or query `INFORMATION_SCHEMA.JOBS` for `total_bytes_billed`. ## Changing your mind Partitioning is fixed at creation; changing it means rewriting the table. Clustering is friendlier: `ALTER TABLE ... SET OPTIONS` can change or drop the clustering columns, and newly written data adopts the new specification, with the background service converging existing data over time. If you want the change to take effect immediately across all existing data, rewrite the table. That asymmetry is worth remembering: get the partitioning axis right up front, and treat the clustering column list as something you can tune later against real query logs.