How BigQuery Pruning Actually Works: Partition Metadata, Block Statistics, and the Shapes That Defeat Both

I spent most of a day once on a query that was supposed to scan one day of a 90 TB events table. The predicate looked right. The table was partitioned on the right column. The editor’s estimate said 90 TB anyway, and maximum_bytes_billed killed the job before it started.

The predicate was where event_date = @run_date. A query parameter. BigQuery could not resolve the parameter value at planning time, so it could not prune partitions at planning time, so the estimate was the whole table. When I removed the byte cap and let it run, it billed 340 GB. The pruning worked fine. The estimate could not know that.

That gap between what BigQuery knows before it runs and what it discovers while running explains almost every confusing pruning result I have debugged. Partition pruning is a planning-time metadata operation. Block pruning is an execution-time statistics operation. They use different information, fire at different times, and are defeated by different mistakes.

If you have not set up partitioning and clustering yet, start with the partitioning and clustering guide and come back. This post is the layer underneath: what the engine is actually comparing, and why an apparently correct filter sometimes reads everything.

What BigQuery stores about your data

A BigQuery table is a set of Capacitor files in Colossus, plus metadata. Two levels of metadata matter for pruning.

Partition metadata is a catalog entry per partition: the partition key value (or range) and the byte count. There are at most a few thousand partitions for a well-designed table, so this is small and lives where the planner can read it instantly. This is what lets BigQuery tell you a byte estimate before executing anything.

Block statistics live inside the Capacitor files. Each column in each block carries min and max values, null counts, and dictionary information. There are millions of these. The planner does not read them all up front; workers consult them as they open blocks, and skip blocks whose min/max range cannot satisfy the predicate.

flowchart TD
  q["Query submitted"] --> plan["Planner reads partition metadata"]
  plan --> est["Byte estimate and maximum_bytes_billed check"]
  est --> exec["Workers open surviving partitions"]
  exec --> blocks["Read block min/max per column"]
  blocks --> skip["Skip blocks that cannot match"]
  skip --> bill["total_bytes_billed, always <= estimate"]

Partitions are pruned before the estimate exists. Blocks are pruned after, which is why the two numbers never match.

The practical consequence: the editor estimate reflects partition pruning only. Clustering never improves the estimate, only the final charge. Teams regularly conclude that clustering “did nothing” because they judged it by the number that structurally cannot see it.

Partition pruning is a planning-time constant fold

For BigQuery to eliminate a partition before execution, it must be able to evaluate your predicate against the partition key using only constants available at planning time. That is the whole rule, and everything else follows from it.

-- Prunes at planning time. Estimate drops. Constants on the right,
-- raw partition column on the left.
where event_ts >= timestamp '2026-05-01'
  and event_ts <  timestamp '2026-05-02'

-- Prunes at planning time. current_date() is folded before planning.
where event_date between current_date() - 7 and current_date()

-- Does NOT prune at planning time. The parameter value is not
-- known when the estimate is computed. It still prunes at runtime.
where event_date = @run_date

-- Does NOT prune. A correlated subquery must execute first.
where event_date = (select max(load_date) from ops.watermarks)

-- Does NOT prune, ever. The function output has no relationship
-- to the stored partition key that metadata can compare.
where format_date('%Y-%m', event_date) = '2026-05'

The middle two cases are the interesting ones because they behave differently from each other and from the last case. A query parameter gets dynamic pruning: the estimate is wrong but the bill is right. A subquery gets no partition pruning at all in most plans, because the planner cannot rewrite an arbitrary scalar subquery into a partition filter.

That distinction has an operational cost. If you use maximum_bytes_billed as a guardrail — and you should, as I argued in the BigQuery cost post — parameterized queries will trip it based on unpruned estimates. The fix is to interpolate the date into the SQL text at orchestration time rather than binding it as a parameter:

"""Interpolate the partition boundary into SQL text so BigQuery can
prune at planning time. Parameters defer pruning to runtime, which
breaks byte estimates and maximum_bytes_billed."""

from datetime import date
from google.cloud import bigquery

client = bigquery.Client()
run_date = date(2026, 5, 6)

# Validated by construction: run_date is a date object, formatted
# to ISO. Never interpolate untrusted strings into SQL.
sql = f"""
select customer_id, sum(amount_usd) as revenue
from analytics.fct_orders
where order_date >= date '{run_date.isoformat()}'
  and order_date <  date_add(date '{run_date.isoformat()}', interval 1 day)
group by customer_id
"""

job = client.query(
    sql,
    job_config=bigquery.QueryJobConfig(
        dry_run=True,
        use_query_cache=False,
        maximum_bytes_billed=200_000_000_000,
    ),
)
print(f"planning-time estimate: {job.total_bytes_processed / 1e9:.1f} GB")

Run that with the literal and with a parameter, and compare the two estimates. The difference is the pruning the planner could and could not see.

Block pruning is a min/max comparison, so sortedness is everything

Clustering physically sorts rows within each partition by your cluster columns, then writes blocks. Pruning works because each block’s min and max for a column bound what it can contain.

The consequence is that clustering effectiveness is entirely about how tightly your cluster column values cluster into blocks. Two tables with identical cluster keys can prune completely differently depending on cardinality and insert pattern.

create table analytics.fct_events (
  event_id    string,
  customer_id string,
  event_type  string,
  region      string,
  event_ts    timestamp
)
partition by date(event_ts)
-- Order is a prefix rule. This layout prunes well for:
--   region = 'eu-west'
--   region = 'eu-west' and event_type = 'purchase'
-- It prunes poorly for:
--   event_type = 'purchase'          (no leading column filter)
--   customer_id = 'c_8813'           (third position, weak)
cluster by region, event_type, customer_id
options (require_partition_filter = true);

Clustering is a prefix index, like a composite B-tree. Filtering on the second column without the first gives you almost nothing, because every block’s event_type range spans most values once blocks are sorted by region first.

Three failure patterns I see repeatedly:

A unique or near-unique leading column — event_id, a UUID, a hash — makes clustering worthless. Sorting by a random value means every block’s min/max spans the whole key space, so nothing can be skipped. This is not “weak clustering,” it is zero clustering with a maintenance cost.

A very low cardinality leading column — a two-value is_deleted flag — halves your scan at best. That is a real win on a 40 TB table and a rounding error on a 40 GB one.

Small frequent writes leave newly arrived data unsorted until background reclustering catches up. Streaming ingestion into a clustered table means the last few hours prune worse than the rest. Check the ratio before concluding your keys are wrong:

-- How much of each table is still awaiting reclustering.
select
  table_name,
  round(sum(total_logical_bytes) / pow(1024, 3), 1)  as total_gib,
  round(sum(active_logical_bytes) / pow(1024, 3), 1) as active_gib,
  clustering_ordinal_position
from `region-us`.INFORMATION_SCHEMA.COLUMNS c
join `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE t using (table_schema, table_name)
where c.clustering_ordinal_position is not null
  and table_schema = 'analytics'
group by table_name, clustering_ordinal_position
order by table_name, clustering_ordinal_position;

The shapes that silently defeat both

Here is the list I check against when a query that should prune does not. Every one of these has cost a client real money.

A CTE that selects the table without the filter. The planner often pushes predicates down through a simple CTE, but not through one with a window function, an aggregate, or a qualify. Once a window function sits between the base table and your filter, the window has to be computed over all rows, so all rows load.

-- Defeated: the window must see every row before the outer filter
-- can be applied, so every partition loads.
with ranked as (
  select *, row_number() over (
    partition by customer_id order by event_ts desc
  ) as rn
  from analytics.fct_events
)
select * from ranked
where date(event_ts) = '2026-05-06' and rn = 1;

-- Fixed: filter inside, before the window.
with scoped as (
  select event_id, customer_id, event_type, event_ts
  from analytics.fct_events
  where event_ts >= timestamp '2026-05-06'
    and event_ts <  timestamp '2026-05-07'
),
ranked as (
  select *, row_number() over (
    partition by customer_id order by event_ts desc
  ) as rn
  from scoped
)
select * from ranked where rn = 1;

That transformation is the single highest-value rewrite I know for BigQuery. Push the partition predicate to the innermost scan of every CTE that touches a large fact table, even when it looks redundant. The window functions post has more on the pattern itself; the pruning consequence is what makes it a cost issue rather than a style one.

A join that you expected to prune the fact table. Filtering dim_customers.country = 'DE' does not prune fct_orders by default. BigQuery can build a runtime filter from the small side of a broadcast join and push it into the fact scan, but it fires opportunistically and only helps block pruning, never partition pruning. If you need the fact table pruned by date, put the date predicate on the fact table yourself.

SELECT * past a pruning fix. Partition pruning controls how many rows load; column selection controls how wide each row is. You can prune perfectly and still scan 2 TB because the table has a 1.4 KB JSON column nobody reads. These are independent multipliers.

A view that hides the partition column. A view exposing event_month computed from event_ts looks convenient and makes partition pruning impossible for every consumer, because their filters land on a derived expression. Expose the raw partition column in every view over a large fact table.

OR across a partition predicate and something else. where event_date = '2026-05-06' or customer_id = 'c_1' cannot prune, because rows satisfying the second branch may live in any partition. Split into a union all.

Verifying pruning instead of assuming it

Three signals, in increasing order of trustworthiness.

The editor byte estimate is instant and reflects partition pruning only. Use it to catch gross mistakes like a wrapped predicate.

INFORMATION_SCHEMA.JOBS_BY_PROJECT.total_bytes_processed is ground truth after the fact, and includes block pruning. This is the number your invoice comes from.

-- Did that model actually prune? Compare bytes processed against
-- the table's total size for the same query over several runs.
select
  job_id,
  creation_time,
  round(total_bytes_processed / pow(1024, 3), 2) as gib_processed,
  round(total_slot_ms / 1000.0, 1)              as slot_seconds,
  cache_hit
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where creation_time >= timestamp_sub(current_timestamp(), interval 2 day)
  and query like '%fct_events%'
  and cache_hit = false
order by creation_time desc
limit 20;

The query plan in the job details shows records read per stage. If the first stage reads 90 billion records on a table with 400 million rows per day, you did not prune, regardless of what the predicate looks like. I trust the record count over my own reading of the SQL every time.

Where teams get this wrong

Judging clustering by the byte estimate. The estimate structurally cannot see block pruning. Compare actual total_bytes_processed before and after, with cache disabled.

Binding the partition date as a query parameter and then setting tight byte caps. These two practices are individually good and mutually incompatible. Interpolate the date into SQL text for jobs that must respect a byte limit.

Clustering on a high-cardinality unique key. A UUID leading cluster column gives you reclustering costs and no pruning. Cluster on what you filter, ordered by how often you filter it.

Assuming dbt incremental models prune. An incremental model’s merge or insert statement prunes only if the predicate on the target table references the partition column with a literal. Configure partition_by and an explicit incremental_predicates clause; see dbt incremental models in production for the configuration details.

Adding a cluster key every time a query is slow. Four cluster columns is the limit, each rewrite costs money, and the fourth column contributes almost nothing. Pick from a month of job history and leave it alone.

Treating require_partition_filter as sufficient. It forces a filter on the partition column. It does not force a sargable one. where format_date('%Y-%m', event_date) = '2026-05' satisfies the requirement and scans everything.

FAQ

Why does my byte estimate differ from what I was billed?

Because they measure different pruning. The estimate is computed from partition metadata before execution. The bill reflects block statistics consulted during execution, which can only reduce the number. An estimate much larger than the bill usually means clustering or dynamic pruning did real work.

Does clustering help joins?

Sometimes, through runtime filter pushdown: BigQuery builds a filter from the small side of a broadcast join and applies it to block statistics on the large side. It is opportunistic, not guaranteed, and it never prunes partitions. Design for it as a bonus rather than a plan.

Should I partition and cluster on the same column?

No. Partition by date, cluster by the dimension you filter within a date. Clustering on the partition column adds nothing, since all rows in a partition already share that value.

How many partitions is too many?

The hard limit is 10,000 per table, and you want far fewer. Daily partitions over three years is about 1,100, which is comfortable. Hourly partitions over three years is 26,000 and will not create. If you need finer granularity than daily, partition by day and cluster by hour.

Does BigQuery prune on nested or repeated fields?

Column-level pruning works on nested fields — reading order.items.sku does not read sibling fields. But you cannot cluster on a repeated field, and a filter inside an UNNEST runs after the array loads. Flatten hot access paths into top-level columns if they drive expensive filters.

Why did my table stop pruning after a backfill?

A backfill that rewrites partitions leaves them unclustered until background reclustering completes, and it resets the 90-day long-term storage clock. Give it a day before measuring, and check whether the backfill wrote one huge partition where you expected many.

What this means for your pipelines

Write your predicates for the planner, not for readability. The partition column bare on one side of a comparison, literals or constant-foldable expressions on the other, pushed to the innermost scan of every CTE. That is three rules, and they cover the large majority of pruning failures I have investigated. Make them a review checklist item on any model that touches a table above a terabyte.

Then instrument the thing you actually care about. Pull total_bytes_processed for your top twenty models weekly and diff it against last week. A model whose scan volume jumps 40x overnight has almost always had a view refactored underneath it, a parameter introduced, or a window function moved above a filter. None of those show up in a code review as a performance change, and all of them show up in the job history immediately.

The mental model worth keeping: BigQuery is not deciding how hard to work on your query. It is deciding how much data it is allowed to skip, using only what it can prove from metadata. Every shape that obscures the relationship between your predicate and the stored column takes that proof away, and the engine falls back to the only safe answer, which is reading everything.