BigQuery Partitioned Tables: How PARTITION BY Cuts Query Cost

A BigQuery partitioned table is one physical table split into separate segments, usually by a date or timestamp column, so a query that filters on that column scans only the matching segments instead of the whole table. Because BigQuery on-demand pricing charges by bytes scanned, at $6.25 per TiB in 2026, partition pruning is one of the biggest levers you have on both query cost and speed. You define it with a PARTITION BY clause when you create the table. Below is how to create a partitioned table, the partition types, how pruning works, and the mistakes that quietly cancel the savings.

PARTITION BY in a CREATE TABLE statement

Partitioning is set once, at table creation, on a single column. The most common choice is a date or timestamp.

CREATE TABLE sales.orders
PARTITION BY DATE(created_at)
AS
SELECT * FROM sales.orders_staging;

Now the table is physically grouped by day. A query with WHERE created_at >= '2026-07-01' reads only July's partitions, not the whole history. Note this is a different PARTITION BY from the one in window functions; there it defines a calculation group, here it defines how the table is stored on disk.

The partition types

Daily partitioning on a timestamp is the default answer for most analytics tables. Drop to monthly or yearly only if daily would create tens of thousands of tiny partitions, since BigQuery caps a table at 10,000 partitions and very small ones add overhead.

How partition pruning saves money

Pruning only happens when the query filters directly on the partition column with a value BigQuery can resolve before running. This prunes:

-- scans one month of partitions, not the whole table
SELECT COUNT(*)
FROM sales.orders
WHERE created_at BETWEEN '2026-07-01' AND '2026-07-31'

You can confirm the saving before you run anything: the query validator in the BigQuery console shows the estimated bytes to be processed, and it drops sharply once a partition filter is in place. Watching that number is the habit that keeps ad-hoc query spend under control, which our guide on BigQuery cost control for ad-hoc queries covers in full.

The mistakes that cancel the savings

Wrapping the partition column in a function stops pruning, because BigQuery can no longer map the filter to specific partitions.

-- does NOT prune: the function hides the partition column
WHERE DATE(created_at) = '2026-07-15'

-- prunes: filter the column directly
WHERE created_at >= '2026-07-15' AND created_at < '2026-07-16'

Two more traps. Filtering on a different column than the one you partitioned by scans everything, so partition on the column people actually filter by. And joining a partitioned table without a partition filter reads the whole table, so push the date filter down before the join, not after.

Require a partition filter to protect the bill

You can force every query to include a partition filter, so nobody accidentally scans the full history. Set it at creation or alter it later.

CREATE TABLE sales.orders
PARTITION BY DATE(created_at)
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM sales.orders_staging;

With that option on, a query without a filter on created_at is rejected instead of running up a large scan. It is a cheap guardrail on a table that many people query, and it pairs well with monitoring tools that keep an eye on table freshness and volume as your warehouse grows.

Partitioning vs clustering

Partitioning and clustering are complementary. Partitioning splits the table into segments you can prune. Clustering sorts the data inside each partition by up to four columns, so filters on those columns read fewer blocks. A common, effective setup is to partition by date and cluster by the columns you filter next, such as customer or region.

CREATE TABLE sales.orders
PARTITION BY DATE(created_at)
CLUSTER BY region, customer_id
AS SELECT * FROM sales.orders_staging;

Partition first for the date filter, cluster second for the secondary filters. Together they can turn a full-table scan into reading a small fraction of the data.

Get the pruning without hand-writing SQL

Whether a query prunes depends on writing the filter the right way, which is exactly where people slip. Agentsql connects read-only to BigQuery, turns a plain-English question into SQL that filters partitioned tables correctly, runs it, and shows you the query and what it scanned so you can see the cost before it adds up. See the BigQuery integration and how it keeps access read-only and safe, then see how it works.