Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Clustering in BigQuery

Snowflake, BigQuery & Databricks · BigQuery

Clustering in BigQuery

Mediumwarehouses-24
bigqueryclusteringblock-pruningpartitioning

Question

How does clustering work in BigQuery, and how is it different from partitioning?

Solution

Clustering sorts the data inside each table (or each partition) by up to four columns that you choose. Queries that filter or aggregate on those columns can skip blocks of storage that cannot contain matching rows.

How it works

CREATE TABLE analytics.events
PARTITION BY DATE(event_ts)
CLUSTER BY customer_id, event_type
AS SELECT * FROM staging.events;

BigQuery stores data in blocks, and keeps min and max statistics for the clustering columns per block. A filter WHERE customer_id = 'C42' lets it skip blocks whose range excludes that value.

Points that matter

  • Order matters. Data is sorted by the first column, then the second inside it, and so on. Filters on the first column prune best. A filter only on the second column prunes less. Put the column you filter by most (and with high enough cardinality) first.
  • It is applied to the table as data is loaded. As more rows arrive, the sort order drifts, and BigQuery re-clusters in the background, automatically and at no charge to you.
  • Unlike partitioning, you do not get an exact cost estimate beforehand. A dry run reports the bytes for the whole partition range, because block pruning happens at runtime. The actual bytes billed are often lower than the estimate.
  • Clustering works on columns of many types (strings, integers, dates, booleans and more), and it helps filters, joins and aggregations on those columns.

How it differs from partitioning

Partitioning: separate physical segments, limited to ~10,000, exact pruning, cost known up front
Clustering:   sorted within storage blocks, up to 4 columns, high cardinality is fine, estimate approximate

Partitioning suits a column with a modest number of values, like a date. Clustering suits high-cardinality columns like customer_id or product_id, which would produce far too many partitions.

In practice

Use both on big tables: partition by date, cluster by the most common filter columns. For small tables (under a gigabyte or so) the gain is small, so do not bother. The benefit grows with table size and with how selective the filters are.

PreviousNext