Partitioning and clustering for query cost.

How BigQuery actually charges for a query

On demand BigQuery pricing bills by the volume of data the query scans, not by rows returned or time taken. The published rate is roughly 6.25 dollars per TiB scanned (indicative, confirm against the current BigQuery pricing page for your region). A column store helps already, because a query reads only the columns it names, but it still reads every value in those columns across the whole table unless you give the engine a way to skip blocks. Partitioning and clustering are those two mechanisms.

The buyer takeaway is simple. The cheapest byte is the one you never scan. Both techniques are free to apply and reduce the bytes scanned figure that drives the bill, so the return on the engineering time is usually measured in days.

What does partitioning do to the bill?

A partitioned table is split into segments by a single column. BigQuery supports three partition types: time unit columns such as a date or timestamp, ingestion time, and integer range. When a query filters on the partition column, the engine prunes every partition that cannot match and scans only the survivors.

Consider a 2 TB events table partitioned by event date across 365 days, roughly 5.5 GB per day. A dashboard query that filters to a single day scans about 5.5 GB rather than 2 TB. At the indicative rate that is a move from about 12.50 dollars per run to about 3 cents per run. Run that query a few thousand times a month and the annual difference is material.

Two settings protect the savings. Set a partition expiration so old partitions drop automatically rather than scanning forever, and turn on require partition filter so a query without a date bound is rejected instead of silently scanning the full table.

When does clustering pay on top of partitioning?

Clustering sorts the data inside each partition by up to four columns and records the value ranges per block, so the engine can skip blocks that cannot contain the filtered value. It shines on high cardinality columns that you filter or aggregate on often, such as customer id, product id, or region, where a partition would create too many segments to be practical.

Clustering is most effective when filters and aggregations hit the leading cluster column. Order matters: put the column you filter by most first. Unlike partitioning, clustering gives an approximate guarantee, because BigQuery re clusters in the background as you write, so the byte savings vary with how sorted the table currently is. On a well clustered 50 GB partition, a selective customer filter can read a few GB instead of the whole partition.

A worked cost comparison

Figures are indicative and verified against billing data on anonymized client workloads; your ratios depend on data distribution. The pattern holds: each pruning layer removes an order of magnitude of scanned bytes when the query filters match the table design.

Does any of this matter under capacity pricing?

If you have moved to BigQuery Editions with reserved slots, you no longer pay per byte directly. You pay for slot time, so the question becomes how long the query holds slots, not how many bytes it scans. Pruning still helps, because reading less data means shorter slot seconds and less contention, which lets a fixed slot pool serve more queries before you need to buy more capacity. The cost signal changes from bytes to slot hours, but the engineering move is the same: prune aggressively so each query does less work.

For teams on on demand pricing, partitioning and clustering are the fastest lever. For teams on capacity, they raise the throughput of the slots you already pay for. Either way the design choice compounds across every query that touches the table.

The decision you can make this week

Pull your most expensive queries from the information schema jobs view, sort by bytes processed, and look at the tables behind them. Any large table scanned in full by date or id filtered queries is a candidate to repartition and recluster. Rebuild it once, enforce require partition filter, and the savings apply to every future run with no change to the queries your analysts write.

Cut your BigQuery bill without slowing the warehouse

Our buyer side guide shows how to set partition and cluster keys, enforce partition filters, and decide between on demand and capacity pricing on real workloads. We take zero provider commissions, so the recommendation follows your data, not a vendor quota.