# Clustered tables in BigQuery

> A clustered BigQuery table keeps rows with similar values in up to four columns stored together, so a filter on those columns reads fewer blocks. How column order decides what gets pruned, which filters defeat it, and why the cost estimate is only an upper bound.

Source: https://docs.querylab.io/bigquery/clustering/

Updated: 2026-10-06

A clustered table is sorted by up to four columns you name. BigQuery stores the data in blocks, and each block records the smallest and largest value of every clustered column. A query that filters on those columns reads only the blocks whose range can match. That is **block pruning**, and the skipped blocks are not billed.

BigQuery has no indexes on ordinary columns (only [search](https://docs.querylab.io/bigquery/search/) and [vector](https://docs.querylab.io/bigquery/vector-search/) indexes), and clustering is the closest thing. It costs nothing to maintain, because BigQuery re-sorts the data in the background without charging for it. It only helps when the filter is on the first clustered column, or on the first few in order.

## Creating a clustered table

```sql
CREATE TABLE shop.orders (
  order_id INT64,
  customer_id INT64,
  country STRING,
  placed_at TIMESTAMP
)
CLUSTER BY customer_id, country;
```

The order of the list is the sort order. You can also cluster a [partitioned table](https://docs.querylab.io/bigquery/partitioning/): `PARTITION BY DATE(placed_at) CLUSTER BY customer_id`. Each partition is then sorted separately.

The rules:

- At most four clustering columns, each named once. A fifth is an error:

**five columns**

```sql
CREATE TEMP TABLE page_views (
  site INT64,
  section INT64,
  page INT64,
  country INT64,
  device INT64
)
CLUSTER BY site, section, page, country, device;
SELECT * FROM page_views
```

BigQuery rejects this query:

```text
Invalid value: 5 clustering fields specified, exceeding the limit of 4. at [1:1]
```

- Top-level columns of type `BOOL`, `DATE`, `DATETIME`, `TIMESTAMP`, `INT64`, `NUMERIC`, `BIGNUMERIC`, `STRING`, `GEOGRAPHY` or `RANGE`. Not `FLOAT64`, `ARRAY`, `STRUCT` fields or `JSON`:

**FLOAT64 can't be clustered**

```sql
CREATE TEMP TABLE readings (
  sensor_id INT64,
  temperature FLOAT64
)
CLUSTER BY temperature;
SELECT * FROM readings
```

BigQuery rejects this query:

```text
Query error: CLUSTER BY expression may not be a floating point type at [5:12]
```

- For `STRING` columns BigQuery sorts on the first 1,024 characters only.
- Adding clustering to an existing table sorts only the data written afterwards. Old rows stay where they are until you rewrite them.

Google says clustering is worth it for tables above about 64 MB, and that on smaller tables the gain is negligible.

## The first column does the work

The public table `bigquery-public-data.covid19_genome_sequence.metadata_clustered` holds 30 million records. It is clustered by `acc` (the record ID), then `releasedate`, then `organism`. It is not partitioned, so it shows clustering on its own. Reading the `acc` column without a filter costs 363 MB:

**the whole acc column**

```sql
SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
```

BigQuery dry run: this query processes 362.6 MB.

A filter on `acc` shrinks that to 395 KB:

**acc = one value**

```sql
SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE acc = 'SRR11454611'
```

BigQuery dry run: this query processes 395.3 KB.

The estimate dropped to 1/940 of the column. BigQuery compared the value with the minimum and maximum `acc` stored for each block and skipped nearly all of them. A range or a prefix works the same way, as long as it is a comparison on the bare column:

**acc LIKE 'SRR114%'**

```sql
SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE acc LIKE 'SRR114%'
```

BigQuery dry run: this query processes 1.8 MB.

That reads 1.8 MB, because more blocks fall into a prefix than a single value.

## Column order matters

A query that filters on the second clustered column alone is not helped much. Rows are sorted by `acc` first. `releasedate` only orders the rows that share an `acc`, so its values are spread across nearly all blocks, and most blocks can't be skipped.

Reading `acc` and `releasedate` together costs 595 MB:

**two columns, no filter**

```sql
SELECT acc, releasedate
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
```

BigQuery dry run: this query processes 594.7 MB.

Adding `WHERE releasedate = '2022-01-01'` (and reading only `acc`) costs 566 MB:

**a filter on the second column**

```sql
SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE releasedate = '2022-01-01'
```

BigQuery dry run: this query processes 565.5 MB.

That is a 5% saving, from a filter on a single date. The third column behaves the same way, only more so. A filter on `organism` alone costs the same as reading the whole column, 734 MB:

**organism, no filter**

```sql
SELECT organism
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
```

BigQuery dry run: this query processes 734.3 MB.

**a filter on the third column**

```sql
SELECT organism
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE organism = 'Severe acute respiratory syndrome coronavirus 2'
```

BigQuery dry run: this query processes 734.3 MB.

Once the first column is in the filter, the later ones can add to the pruning. This query filters on `acc` and `organism`, and reads 998 KB:

**first and third column**

```sql
SELECT organism
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE acc = 'SRR11454611'
  AND organism = 'Severe acute respiratory syndrome coronavirus 2'
```

BigQuery dry run: this query processes 998.0 KB.

The practical rule is to list the columns in the order your queries filter on them, most-filtered first. If every query filters on `country` but only some also filter on `customer_id`, cluster by `country, customer_id`. If a column is never filtered on, don't cluster by it.

## Functions on the column stop pruning

Block pruning compares the filter value with each block's stored minimum and maximum. If the column is wrapped in a function, those values say nothing about the result, and BigQuery reads every block:

**UPPER(acc) = ...**

```sql
SELECT acc
FROM `bigquery-public-data.covid19_genome_sequence.metadata_clustered`
WHERE UPPER(acc) = 'SRR11454611'
```

BigQuery dry run: this query processes 362.6 MB.

The estimate is 363 MB again, exactly the unfiltered cost. Compare the bare column with a constant, or move the function to the constant side of the comparison.

## Clustering works inside partitions

On a table that is both partitioned and clustered, pruning happens in two steps: the date filter chooses partitions, then the clustering filter chooses blocks inside them. `wikipedia.pageviews_2020` is partitioned by `datehour` and clustered by `wiki`, then `title`. One day of the `views` column is 2.4 GB. Adding a filter on the language edition:

**one day, one wiki**

```sql
SELECT views
FROM `bigquery-public-data.wikipedia.pageviews_2020`
WHERE datehour >= '2020-03-01' AND datehour < '2020-03-02'
  AND wiki = 'en'
```

BigQuery dry run: this query processes 621.6 MB.

brings it to 622 MB. Combining the two works well when each partition is large enough to have many blocks and your queries filter on a few columns besides the date. See [Partitioning vs clustering](https://docs.querylab.io/bigquery/partitioning-vs-clustering/).

## The estimate is an upper bound

Every estimate above is labeled "upper bound" by BigQuery. For a clustered table it is a ceiling, and the query can read less. Google's documentation says BigQuery can't always estimate the bytes exactly, because the blocks that match are only known while the query runs. The final figure is the bytes in the blocks that were read.

We ran a few of these queries for real to see how far off the estimate was. For `acc = 'SRR11454611'` and for `acc LIKE 'SRR114%'`, the job processed exactly what the dry run had said, 404,736 and 1,879,790 bytes. With a `LIMIT` the two differ. `SELECT acc FROM ... LIMIT 10` has the same estimate as the full column, 363 MB, but the job stopped after reading 196 MB. Google's docs say a `LIMIT` doesn't reduce the cost on a table that is not clustered; on a clustered table it can.

Two consequences:

- An estimate can't promise a price, only a maximum. This is one reason to partition when you need a firm number.
- Maximum bytes billed compares against the estimate. A query on a clustered table can be rejected for exceeding the limit although it would have read less.

## Reclustering is automatic

New data may not be grouped with existing rows that have the same clustering values. BigQuery re-sorts in the background and doesn't charge for it. For partitioned tables the sorting stays within each partition.

To see which columns a table is clustered by, query `clustering_ordinal_position` in `INFORMATION_SCHEMA.COLUMNS`. It is 1 for the first clustered column and `NULL` for columns that aren't clustered.

In Querylab.io the estimate in the status bar changes as you add a filter on a clustered column, so you can see whether the filter prunes ([Cost limits](https://docs.querylab.io/writing-sql/cost-limits/)). It is the same upper bound.

See what a filter on a clustering column saves. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to clustered tables](https://cloud.google.com/bigquery/docs/clustered-tables)

- [Query clustered tables](https://cloud.google.com/bigquery/docs/querying-clustered-tables)

- [Manage clustered tables](https://cloud.google.com/bigquery/docs/manage-clustered-tables)

- [Estimate and control costs](https://cloud.google.com/bigquery/docs/best-practices-costs)