Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 37 ยท Partitioning and Clustering

Clustered Tables

Learn how BigQuery clustering sorts storage blocks by up to four columns, how it differs from partitioning, and when it speeds up filters and aggregations.

Partitioning is coarse: it splits a table into segments by one column. Clustering is finer. BigQuery sorts the data inside the table (or inside each partition) by the values of up to four columns you choose and stores it in blocks. Each block records the minimum and maximum of the clustering columns, so a filter such as customer_id = 'c42' can skip every block whose range does not include it.

Clustering needs no partitions, costs nothing extra to maintain, and combines well with partitioning. This lesson explains how it works, how to create clustered tables, and how to model block skipping so you can reason about it.

How clustering works

When data is written, BigQuery orders rows by the clustering columns and splits them into storage blocks. Rows with similar values end up together. A query that filters on a clustering column reads only the blocks that might contain matches. This is block pruning.

flowchart TB
  T["Table sorted by country, then city"] --> B1["Block 1<br/>BR ... DE"]
  T --> B2["Block 2<br/>DE ... JP"]
  T --> B3["Block 3<br/>JP ... US"]
  Q["WHERE country = 'JP'"] -->|skip| B1
  Q -->|read| B2
  Q -->|read| B3

Each block holds a range of country values; blocks whose range cannot contain the filter value are skipped.

BigQuery also re-clusters in the background as new data arrives, at no charge, so the table stays well sorted without you running maintenance jobs.

Creating a clustered table

List the clustering columns in CLUSTER BY. Read-only BigQuery DDL:

SQL
CREATE TABLE shop.orders (
  order_id    INT64,
  customer_id STRING,
  country     STRING,
  status      STRING,
  order_date  DATE,
  total       NUMERIC
)
CLUSTER BY country, status, customer_id;

Rules to remember:

  • Up to four clustering columns.
  • Order matters. The table is sorted by the first column, then by the second inside it, and so on. A filter on only the third column benefits much less than a filter on the first.
  • Supported types include STRING, INT64, NUMERIC, BIGNUMERIC, BOOL, DATE, DATETIME, TIMESTAMP and GEOGRAPHY. Columns of FLOAT64, ARRAY or STRUCT type cannot be used.
  • You can add or change clustering later with ALTER TABLE ... CLUSTER BY; BigQuery applies it to new data and re-clusters existing data in the background.

Block skipping in a model

The example below stores rows in blocks of three, sorted by country. For each block it computes the min and max country, then shows which blocks a filter for 'JP' would read.

SQL Which blocks does a filter read?

Press Run (or Ctrl + Enter) to see the result here.

Only block 3 can contain JP, so the other three blocks cost nothing. Now imagine the table were not sorted: every block would contain a mix of countries, each block's min/max would span the whole range, and no block could be skipped. Sorting is what makes the min/max metadata useful.

Clustering and partitioning together

Partitioning prunes by one coarse column; clustering prunes inside what is left. A common design for event data is to partition by day and cluster by the columns that analysts filter on most:

SQL
CREATE TABLE shop.events (
  event_ts   TIMESTAMP,
  user_id    STRING,
  event_name STRING,
  country    STRING
)
PARTITION BY DATE(event_ts)
CLUSTER BY country, event_name, user_id;

A query for one day and one country first prunes to that day's partition, then prunes blocks inside it.

SQL Two levels of pruning

Press Run (or Ctrl + Enter) to see the result here.

The third query reads two blocks out of six because both filters prune.

What to expect from cost estimates

Partition pruning is exact before the query runs, so the dry-run estimate is accurate. Block pruning for clustered tables happens at run time, so the estimate shown before the query is an upper bound and the final bytes billed are often lower. The final bill uses the bytes actually processed after clustering helped. Clustering therefore helps both cost and speed, but you cannot predict the saving precisely from the dry run.

When clustering helps

Clustering works best when:

  • the table is larger than about a gigabyte (smaller tables fit in few blocks);
  • queries filter or aggregate on the clustering columns;
  • the clustering columns have many distinct values (high cardinality), such as customer_id, or at least many groups.

It also speeds up GROUP BY, ORDER BY and joins on clustered columns because rows that belong together sit together. It does not help queries that never filter on those columns, and filters that wrap the column in a function can defeat pruning.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How many columns can you cluster a BigQuery table by?

    BigQuery allows at most four clustering columns.
  2. 2. Why does the order of clustering columns matter?

    A filter on the first clustering column prunes the most; a filter on only a later one benefits much less.
  3. 3. A dry run on a clustered table shows 10 GB. What does that number mean?

    Block pruning happens at run time, so the estimate can be higher than the actual charge.
  4. 4. Which column type cannot be used for clustering?

    FLOAT64, ARRAY and STRUCT columns are not supported as clustering columns.

Summary

  • Clustering sorts data by up to four columns into blocks that store min/max metadata.
  • Filters on clustering columns skip blocks that cannot match; column order matters.
  • BigQuery re-clusters automatically and for free.
  • Combine it with partitioning: partitions prune first, clusters prune inside them.
  • Dry-run estimates for clustered tables are upper bounds.

Next: Choosing Partition and Cluster Keys