BigQuery Partitioning and Clustering

BigQuery is a serverless, highly scalable data warehouse that separates compute from storage. Queries incur costs based on the amount of data processed (scanned). Partitioning and clustering are two techniques to limit the data scanned, thereby reducing cost and improving performance.

When you create a partitioned table, BigQuery stores each partition as a separate storage unit. Metadata tracks which partitions exist. At query time, the query planner analyzes the WHERE clause to determine which partitions are needed. For example, if a table is partitioned by DATE and the query filters WHERE date = '2023-01-01', only that partition is read. If the filter is WHERE date BETWEEN '2023-01-01' AND '2023-01-31', only those 31 partitions are read.

Partitioning can be based on: - Ingestion time (_PARTITIONTIME pseudo-column): Automatically partitions by the time data was ingested. Useful for streaming or append-only data. - Date/timestamp column: A column of type DATE, TIMESTAMP, or DATETIME. - Integer range: Partition by an integer column with specified range start, end, and interval. For example, RANGE_BUCKET(customer_id, START=0, END=1000000, INTERVAL=10000) creates 100 partitions each covering 10,000 IDs.

Key limitations:

Clustering sorts the data within each partition (or the entire table) by the cluster key columns. BigQuery automatically reorganizes data as it is written, using a sort-based approach. The sorted data is stored in blocks of approximately 100 MB each. Metadata for each block stores the minimum and maximum values of the cluster keys. When a query filters on cluster keys, BigQuery can skip blocks whose min/max range does not overlap the filter.

For example, a table clustered by customer_id and order_date. A query WHERE customer_id BETWEEN 100 AND 200 will only read blocks that might contain those IDs. If the data is well-clustered, many blocks will be skipped.

Clustering columns must be top-level columns (not nested) of type: STRING, BYTES, INT64, FLOAT64, NUMERIC, BIGNUMERIC, BOOL, DATE, TIMESTAMP, DATETIME, or TIME. You can specify up to 4 cluster columns. Order matters: the first column has the highest priority for sorting.

Common pattern: Partition by date, cluster by customer_id or region. This allows time-based pruning and then efficient lookup within each day.

Creating a partitioned table (date column):

CREATE TABLE mydataset.mytable
(
  transaction_id INT64,
  transaction_date DATE,
  amount FLOAT64
)
PARTITION BY transaction_date
OPTIONS (
  partition_expiration_days = 365,
  require_partition_filter = true
);

Creating a partitioned table (ingestion time):

CREATE TABLE mydataset.mytable
(
  transaction_id INT64,
  amount FLOAT64
)
PARTITION BY _PARTITIONDATE
OPTIONS (partition_expiration_days = 365);

Creating a clustered table:

CREATE TABLE mydataset.mytable
(
  customer_id INT64,
  order_date DATE,
  amount FLOAT64
)
PARTITION BY order_date
CLUSTER BY customer_id
OPTIONS (partition_expiration_days = 365);

Verifying partitioning and clustering:

Use INFORMATION_SCHEMA.TABLES:

SELECT table_name, partition_by, cluster_by
FROM `region-us.INFORMATION_SCHEMA.TABLES`
WHERE table_name = 'mytable';

Or bq show:

bq show --format=prettyjson mydataset.mytable

The ACE exam tests your understanding of when to use partitioning vs. clustering, how to create them, and how they affect cost and performance. Be prepared to identify the correct partition key for a given query pattern, and to know the limitations (e.g., max 4000 partitions, max 4 cluster columns, cannot modify partition key after creation).

A large e-commerce company stores 2 TB of daily order data. Queries typically filter by order_date (last 30 days) and customer_id (to analyze specific customers). They partition by order_date and cluster by customer_id. This reduces scanned data from 2 TB to about 200 GB (last 30 days), and within that, block-level pruning further reduces to ~50 GB. Without clustering, scanning 200 GB costs ~$1 per query; with clustering, ~$0.25. They also set partition_expiration_days=365 to automatically drop partitions older than a year, saving storage costs. A common misconfiguration is forgetting to set require_partition_filter — a developer once ran a query without a date filter, scanning all 2 TB and costing $10. After enabling the filter, such queries fail with an error, alerting the team to add a filter.

A smart building company ingests sensor readings every minute. The table is partitioned by ingestion time (_PARTITIONDATE) and clustered by sensor_id and building_id. Queries often ask for readings from a specific sensor in a specific building over the last hour. Partitioning by ingestion time prunes to the last few partitions (e.g., today's partition only). Clustering on (building_id, sensor_id) then allows efficient block-level pruning. However, they initially clustered by sensor_id only, which was suboptimal because queries always included building_id. After recreating the table with (building_id, sensor_id) in that order, performance improved 3x. They also learned that streaming data is not immediately clustered — a query right after ingestion might scan many blocks. They now wait 90 minutes after peak ingestion before running critical reports.

A bank stores millions of transactions daily, with queries filtering by transaction_date and account_id. They partition by transaction_date and cluster by account_id. However, they also have queries that filter by transaction_type (e.g., 'withdrawal'). Since transaction_type has low cardinality (5 types), they considered partitioning by it, but that would create only 5 partitions — not effective. Instead, they added transaction_type as a third cluster column (after account_id). This works because clustering can handle low-cardinality columns by block pruning. But they discovered that clustering on three columns with the first column having millions of distinct values (account_id) makes the second and third columns less effective. They reordered to (transaction_type, account_id) for queries that always filter by type first. The trade-off: queries filtering only by account_id now scan more blocks. They accepted this based on actual query frequency analysis.