What are Partitioning and Clustering in BigQuery, and how do they reduce query costs by 90%?

BigQuery Partitioning organizes tables into segments based on a specified column, while Clustering sorts data within those partitions by one or more columns. Both techniques drastically reduce query costs and improve performance by minimizing the amount of data BigQuery scans.

Partitioning

Partitioning divides a large table into smaller, more manageable parts. Queries targeting specific partitions only scan relevant data, significantly reducing the amount of data processed and thus query costs.

How it works: BigQuery creates separate physical blocks for data belonging to different partition values.
Types:
Date/Timestamp/Datetime column: Most common. Partitions by a specific column's date/timestamp value.
Integer-range: Partitions by a range of integer values.
Benefits: Ideal for time-series data or data frequently filtered by date, providing substantial cost savings.

CREATE TABLE my_dataset.partitioned_table (
    id STRING,
    event_time TIMESTAMP,
    value INT
)
PARTITION BY DATE(event_time)
OPTIONS(
    description="A table partitioned by event_time"
);

-- Querying only scans the relevant partition for '2023-01-01'
SELECT *
FROM my_dataset.partitioned_table
WHERE DATE(event_time) = '2023-01-01';

Clustering

Clustering sorts data within each partition (or the entire table if not partitioned) based on the values in specified columns. This co-locates rows with similar values, making range scans or equality filters more efficient.

How it works: BigQuery physically sorts the data blocks by the clustered columns. When a query filters on these columns, BigQuery can quickly skip irrelevant blocks.
Benefits: Improves performance and reduces scan costs for queries with WHERE clauses, GROUP BY, or JOIN operations on the clustered columns.

CREATE TABLE my_dataset.clustered_table (
    id STRING,
    event_time TIMESTAMP,
    category STRING,
    value INT
)
PARTITION BY DATE(event_time)
CLUSTER BY category, id
OPTIONS(
    description="A table partitioned by event_time and clustered by category, id"
);

-- This query efficiently scans within the specified partition and cluster blocks for 'sports'
SELECT SUM(value)
FROM my_dataset.clustered_table
WHERE DATE(event_time) = '2023-01-01' AND category = 'sports';

When to Use Which (or Both)

Partitioning: Use when queries frequently filter by a date, timestamp, or a specific integer range. It's the primary optimization for time-series data and the first step to cost reduction.
Clustering: Apply after partitioning (or on unpartitioned tables) when queries frequently filter, group, or join on non-partitioning columns. It refines data organization within partitions, further reducing scan costs for specific column lookups.
Combined: The most powerful approach. Partition by a date column, then cluster by frequently filtered categorical or ID columns. This provides a two-stage optimization.

Cost Reduction

BigQuery charges based on the amount of data scanned. By scanning only relevant partitions and then efficiently navigating clustered blocks within those partitions, you can often achieve 90% or greater cost savings compared to scanning an entire, unoptimized table. For instance, a query scanning 100GB on an unoptimized table might scan only 10GB or less on a properly partitioned and clustered table, directly translating to lower costs and faster query execution.