Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 37 ยท Partitioning and Clustering

Choosing Partition and Cluster Keys

A practical method for picking BigQuery partition and clustering columns from your real query workload, with a worked example and common design mistakes.

You now know the mechanics of partitioning and clustering. The harder part is deciding which columns to use. A wrong choice does not break anything, but it wastes the savings: a table partitioned on a column nobody filters by behaves like an unpartitioned one.

This lesson gives a repeatable method: look at the queries that actually run, count which columns they filter on, then match columns to the right tool. The runnable examples analyze a small model of a query log, which is exactly what you would do in BigQuery with INFORMATION_SCHEMA.JOBS.

Start from the workload, not the schema

Do not choose keys by looking at column names. Choose them by looking at the WHERE clauses of your most important and most expensive queries. For a table you already run, BigQuery records every query in INFORMATION_SCHEMA.JOBS_BY_PROJECT, including the text, the bytes billed and the tables referenced (read-only):

SQL
SELECT
  query,
  total_bytes_billed,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_billed DESC
LIMIT 50;

Read the top queries and note, for each one, which columns appear in WHERE, JOIN ... ON and GROUP BY. For a table that does not exist yet, write down the ten questions the business will ask and do the same.

A rule-of-thumb decision list

Partition key

A date or time Filtered in most queries
Bounded count Well under 10,000 partitions
Big segments Hundreds of MB or more each

One coarse column that bounds the scan.

1 / 2

For the partition column, pick a date or timestamp almost every query filters on. The partitions should be reasonably large, because BigQuery handles a few thousand big partitions better than tens of thousands of tiny ones. If the table is under roughly a gigabyte, skip partitioning.

For clustering columns, pick columns that appear in filters and joins, ordered with the most frequently filtered, most selective column first. Low-cardinality columns such as status (a handful of values) cluster well only when used together with a higher-cardinality one, because they split data into just a few ranges.

Counting filter columns with SQL

Imagine a log of queries with the columns each one filtered on. A little SQL ranks the candidates.

SQL Which columns are filtered most?

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

Here event_date leads, so it is the partition column. Next come country, event_name and user_id, which become clustering candidates. The order of the cluster columns is a judgment call: country, event_name, user_id puts the most common filter first.

Estimating cardinality

A clustering column helps only if it splits data into many groups. Count distinct values on a sample before you commit.

SQL Cardinality of candidate columns

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

In BigQuery you would use APPROX_COUNT_DISTINCT(user_id) on the real table, which is faster and cheaper than an exact distinct count. A column whose distinct count is close to the row count (a unique ID) clusters very well for lookups by that ID. A column with two or three values (a boolean flag) is a weak first clustering key.

Worked example

Design for a shop.events table that receives 200 GB per day. The top queries are daily funnel reports for one country, per-user history lookups and event-type counts for a date range.

SQL
CREATE TABLE shop.events (
  event_ts   TIMESTAMP,
  user_id    STRING,
  event_name STRING,
  country    STRING,
  props      JSON
)
PARTITION BY DATE(event_ts)
CLUSTER BY country, event_name, user_id
OPTIONS (
  partition_expiration_days = 400,
  require_partition_filter = TRUE
);

The reasoning: daily partitions of 200 GB are large and a 400-day window gives 400 partitions, far from the limit. country and event_name serve the funnel reports, user_id serves history lookups. Expiration keeps storage bounded and require_partition_filter blocks accidental scans.

Common design mistakes

  • Partitioning by a high-cardinality column such as user_id with a date-like type, producing thousands of tiny partitions.
  • Clustering by a column that is never filtered.
  • Putting a low-cardinality column first and the selective one last, so the selective column barely prunes.
  • Changing keys constantly. Rewriting a large table is a billable job. Decide from evidence, and test on a sample table first.
  • Forgetting that queries must filter on the raw column. WHERE LOWER(country) = 'us' cannot use clustering.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What should drive the choice of partition and cluster columns?

    Keys only save money when queries filter on them, so look at actual or expected WHERE clauses.
  2. 2. Which is the best first clustering column for lookups by a unique customer ID?

    A high-cardinality column that queries filter on prunes the most blocks.
  3. 3. Why is partitioning a 200 MB table usually not worth it?

    Small tables fit in few blocks, so pruning saves little while adding metadata overhead.
  4. 4. Which INFORMATION_SCHEMA view lists past query jobs and their billed bytes?

    JOBS_BY_PROJECT records query text and total_bytes_billed for jobs in a project.

Summary

  • Choose keys from the real workload: look at WHERE, JOIN and GROUP BY columns of the costly queries.
  • Partition by one date or time column that most queries filter on; keep partitions large and few.
  • Cluster by up to four selective columns, most frequently filtered first.
  • Check cardinality, ideally with APPROX_COUNT_DISTINCT, before committing.
  • Add partition_expiration_days and require_partition_filter for safety.

Next: Introduction to BigQuery ML