Optimizing BigQuery Costs with Partitioning & Clustering

What’s the pain point?

When a BigQuery table grows to tens or hundreds of terabytes, a careless SELECT * can scan the entire table, driving up costs and latency. The key to keeping both under control is to let BigQuery skip the data it doesn’t need.

Thesis: Partitioning + Clustering = cheaper, faster queries

Partitioning slices a table into horizontal buckets based on a column value (usually a date). Clustering sorts the rows inside each partition on one or more columns. Together they let BigQuery prune both whole partitions and large blocks of data, reducing the amount of data it reads.

Partitioning – the coarse filter

BigQuery supports three kinds of partitioning:

• Time‑unit partitioning – DATE, TIMESTAMP, or DATETIME columns.
• Ingestion‑time partitioning – BigQuery creates a partition for each day the data is loaded.
• Integer‑range partitioning – a numeric column, useful for ID ranges or status codes.

When a query includes a filter on the partition column, BigQuery scans only the matching partitions. The pruning happens before the query is executed, so you pay for fewer bytes.

Clustering – the fine‑grained sorter

Up to four columns can be specified for clustering. BigQuery stores data in block files that are sorted on those columns. If a query filters on a clustered column, the engine can skip blocks that don’t match the predicate.

Clustering does not have a hard limit like partitioning, but it does need background work to reorganise data. New rows are appended to the end of the table and are re‑sorted during periodic maintenance.

Concrete example: user_events table

Suppose you have a table that logs every click on a website. You want to run analytics per user per day.

# Run in the BigQuery console or via gcloud
CREATE TABLE `my_project.my_dataset.user_events` (
  event_id STRING,
  user_id INT64,
  event_type STRING,
  event_timestamp TIMESTAMP,
  payload JSON
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type;

Query that benefits from both optimisations:

SELECT COUNT(*) AS total
FROM `my_project.my_dataset.user_events`
WHERE DATE(event_timestamp) = '2026-10-18'
  AND user_id = 12345
  AND event_type = 'click';

Verification:

1. Run the query in the BigQuery UI.
2. Open the Execution details tab and check Bytes processed. It should be far less than the full table size.
3. In the Query Plan view, look for a PartitionPrune node that lists the specific partition(s) scanned.

Trade‑offs and limitations

• Missing partition filter – If you forget DATE(event_timestamp) = …, BigQuery scans the entire table and you pay the full cost.
• Over‑partitioning – Creating a partition for every hour or minute can lead to thousands of tiny partitions, increasing metadata overhead and slowing query planning.
• Clustering latency – New rows are not immediately reordered. For very recent data, the benefit may be small until the next re‑optimisation cycle.

Actionable take‑away

When designing a new table or refactoring an existing one, follow these steps:

1. Identify a natural time column (date or timestamp) and use it for partitioning.
2. Choose 1–2 high‑cardinality columns that appear in the most common filters and cluster on them.
3. Always include the partition column in the WHERE clause of analytical queries.
4. Use the BigQuery UI or bq show --format=prettyjson to inspect partitioning_type and clustering_fields.
5. Periodically run EXPLAIN or look at the query plan to confirm that pruning is happening.

By following these guidelines, you can keep query costs predictable and keep latency low even as your data grows.