Post

BigQuery Partitioning and Clustering: A Practical Guide

In this article, let us understand partitioning and clustering in BigQuery, why we would use them, and how they affect the amount of data scanned by a query. We will create a sample table, run a few queries, and look at the choices I normally make when designing a table for an analytics pipeline.

BigQuery is serverless, so we do not manage indexes or database servers. That makes it easy to start, but a table can still become expensive when every query scans all its rows. Partitioning and clustering are the two main table-design options we have to avoid that.

Partitioning and clustering in simple terms

Partitioning divides a table into separate sections based on a date, timestamp, datetime, or integer column. When a query filters that column, BigQuery can ignore partitions that are not needed.

Clustering sorts and groups data blocks using up to four columns. BigQuery uses clustering metadata to skip blocks that do not contain the values requested by a query. We do not manage these blocks ourselves. BigQuery maintains them as data is loaded.

OptionBest suited forTypical filterMain benefit
PartitioningA date, timestamp, or integer rangeevent_date = '2026-11-01'Skips complete partitions
ClusteringFrequently filtered or grouped columnscustomer_id = 'C100'Skips blocks inside partitions
BothLarge event or transaction tablesDate plus customer or statusReduces data at both levels

For most event tables, I start with a date partition and then consider clustering. Clustering is not a replacement for a sensible partition column.

Create a partitioned table

Let us assume that we receive ecommerce order events. The table has an event timestamp, order ID, customer ID, country, status, and amount. We can create a date-partitioned table using the below SQL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE TABLE `demo_analytics.sales.order_events`
(
  event_timestamp TIMESTAMP,
  order_id STRING,
  customer_id STRING,
  country_code STRING,
  order_status STRING,
  amount NUMERIC
)
PARTITION BY DATE(event_timestamp)
OPTIONS (
  require_partition_filter = TRUE,
  partition_expiration_days = 730,
  description = 'Order events partitioned by event date'
);

Here, DATE(event_timestamp) creates one partition for each calendar date. The require_partition_filter option is useful because it prevents users from accidentally running a query without a usable partition filter. I prefer enabling it on large tables, although it can surprise users when they first query the table.

The expiration setting deletes partitions older than 730 days. This is useful only when the business agrees that older data can be removed. Do not add an expiration just to reduce cost without confirming the retention requirement.

Now a query for one day can prune all other partitions.

1
2
3
4
5
6
7
8
SELECT
  country_code,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM `demo_analytics.sales.order_events`
WHERE event_timestamp >= TIMESTAMP('2026-11-01')
  AND event_timestamp < TIMESTAMP('2026-11-02')
GROUP BY country_code;

Before running the query, the BigQuery console displays an estimate of bytes processed. This is a simple way to verify partition pruning. Compare the estimate with a query covering a month and we should see a clear difference.

Ingestion time or a business date?

BigQuery also supports ingestion-time partitioning using _PARTITIONTIME and _PARTITIONDATE. It can be convenient when incoming rows do not have a reliable event date.

1
2
3
4
5
6
CREATE TABLE `demo_analytics.raw.api_events`
(
  payload JSON,
  source STRING
)
PARTITION BY _PARTITIONDATE;

For our use case, the source event timestamp is normally better. If late data for November 1 arrives on November 3, column-based partitioning still places it in the November 1 partition. With ingestion-time partitioning, it goes into November 3 unless the load job explicitly writes to a partition decorator. That difference matters when reports follow the business date.

We should also avoid partitioning on a date that queries rarely filter. Having partitions does not help if reports filter only by created_timestamp while the table is partitioned by updated_timestamp.

Add clustering

Suppose most queries select a date range and then filter by customer_id, country_code, or order_status. We can partition by event date and cluster the same table.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE TABLE `demo_analytics.sales.order_events_clustered`
(
  event_timestamp TIMESTAMP,
  order_id STRING,
  customer_id STRING,
  country_code STRING,
  order_status STRING,
  amount NUMERIC
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY customer_id, country_code, order_status
OPTIONS (require_partition_filter = TRUE);

The order of clustering columns is important. BigQuery gets the best pruning when filters use the columns from left to right. A query filtering customer_id and country_code matches this design well. A query filtering only order_status may get less benefit.

1
2
3
4
5
SELECT order_id, order_status, amount
FROM `demo_analytics.sales.order_events_clustered`
WHERE event_timestamp >= TIMESTAMP('2026-11-01')
  AND event_timestamp < TIMESTAMP('2026-11-08')
  AND customer_id = 'C10042';

I would not add four clustering columns simply because BigQuery allows four. Start with columns used in common selective filters. A boolean column is usually a weak first clustering key because it has only two values. High-cardinality columns such as customer IDs often work better, provided queries actually filter them.

Load data into the table

A scheduled query or pipeline can load one processing window at a time. An INSERT is enough for an append-only table, but for rerunnable pipelines I normally use MERGE.

1
2
3
4
5
6
7
8
9
10
11
12
MERGE `demo_analytics.sales.order_events_clustered` AS target
USING `demo_analytics.staging.order_events` AS source
ON target.order_id = source.order_id
AND DATE(target.event_timestamp) = DATE(source.event_timestamp)
WHEN MATCHED THEN
  UPDATE SET
    order_status = source.order_status,
    amount = source.amount
WHEN NOT MATCHED THEN
  INSERT (event_timestamp, order_id, customer_id, country_code, order_status, amount)
  VALUES (source.event_timestamp, source.order_id, source.customer_id,
          source.country_code, source.order_status, source.amount);

Include a partition condition where possible. A poorly constrained MERGE can scan many target partitions even when the source batch is small. In a production pipeline, I would calculate the minimum and maximum event dates in staging and use those values to limit the target scan.

Check whether the design is working

Do not assume a table is efficient because its DDL contains PARTITION BY and CLUSTER BY. Check actual jobs after realistic queries run. The INFORMATION_SCHEMA.JOBS_BY_PROJECT view gives us processed bytes and slot usage.

1
2
3
4
5
6
7
8
9
10
11
SELECT
  job_id,
  user_email,
  total_bytes_processed,
  total_slot_ms,
  query
FROM `region-australia-southeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
ORDER BY total_bytes_processed DESC
LIMIT 20;

This helps find dashboard queries that scan too much data. Dry runs through the API or bq query --dry_run are also useful in CI when we want to catch an unexpectedly expensive query before deployment.

Practical limitations

Very small partitions add metadata without much benefit. Hourly partitions are available for timestamp columns, but daily partitions are enough for many pipelines. Choose hourly only when data volume and query patterns justify it.

Clustering does not guarantee that every query will scan a fixed number of bytes. Its benefit depends on table size, data distribution, and filters. Small tables may show no meaningful improvement. BigQuery also performs automatic reclustering, so recently streamed or modified data may not be perfectly organized immediately.

Be careful with wildcard tables created as one table per day. A partitioned table is generally easier to query and govern. Also remember that partitioning and clustering do not replace good SQL. Selecting unnecessary columns, scanning wide date ranges, or repeatedly transforming the same raw data can still be expensive.

For a simple demo, creating the table manually in SQL is fine. In production, I would define it in Terraform or another deployment process, add labels and descriptions, set retention deliberately, monitor query cost, and test schema changes. I would also review real query history before changing clustering keys because rebuilding a large table only on a guess is not ideal.

Conclusion

Partitioning should follow the date or range used by most queries, while clustering should follow the selective columns commonly used inside those partitions. Start with a straightforward design, confirm pruning using estimated and actual bytes, and then adjust it using production query patterns. For large BigQuery tables, these two settings can make a noticeable difference without adding a separate indexing system to manage.

This post is licensed under CC BY 4.0 by the author.