BigQuery Partitioning and Clustering: Stop Paying for Full Table Scans

BigQuery’s on-demand pricing is simple: you pay for the bytes each query scans. The entire cost game is scanning fewer bytes. Partitioning and clustering are how you do that on large fact tables. Slots and reservations come later — after pruning works.

I treat a dry-run byte count the way I treat a Snowflake warehouse size: a number that belongs in the runbook, not a surprise in the invoice.

flowchart TD
  table[Fact table] --> part[Partition by date]
  part --> cluster[Cluster by customer_id]
  cluster --> prune[Query scans only matching blocks]
  prune --> bill[Bytes billed]

Partitioning: the coarse filter

A partitioned table is split by a column — almost always a date or timestamp. BigQuery then skips partitions that cannot match the filter.

create table analytics.events (
  event_id string,
  user_id string,
  event_type string,
  occurred_at timestamp
)
partition by date(occurred_at)
options (
  require_partition_filter = true,
  partition_expiration_days = 396
);

This query can scan one day instead of the whole table:

select count(*)
from analytics.events
where date(occurred_at) = '2026-08-11';

require_partition_filter = true is the option I turn on for every large table. A query with no partition filter fails instead of quietly scanning years. Analysts complain once. Finance complains every month if you skip it.

partition_expiration_days is lifecycle, not performance. Use it when the product actually allows dropping old events. Do not expire partitions a finance mart still needs.

Ingestion-time vs column partitions

You can partition on _PARTITIONDATE (ingestion time) or on a column. Column partitions match how people write WHERE occurred_at >= .... Ingestion-time partitions match “when did it land in BigQuery,” which is the wrong grain for late-arriving facts.

I use column partitions on event time unless the table is a raw dump where landing time is the contract.

Integer range partitions exist for id-shaped keys. They are easy to get wrong (range size vs skew). Prefer date partitions for facts unless you have a documented id-range access pattern.

Clustering: the fine filter

Within each partition, clustering sorts data by up to four columns so BigQuery can skip blocks that cannot match a filter or a join key.

create table analytics.events (
  event_id string,
  user_id string,
  event_type string,
  occurred_at timestamp
)
partition by date(occurred_at)
cluster by event_type, user_id
options (require_partition_filter = true);

Order matters. Put the column you filter or join on most first. event_type then user_id helps “all purchase events for a user on a day.” user_id then event_type helps “everything this user did today.” Pick from the query log, not from a guessing session.

Clustering is not a second partition key. It does not give you require_cluster_filter. It helps when the remaining scan after partition prune is still large. On a 2 GB/day table, clustering may not be worth thinking about. On a 2 TB/day table, it is.

BigQuery maintains clustering in the background. Wide, high- cardinality first columns work better than a unique event_id as the only cluster key (that is almost a random sort).

The mistakes that bypass pruning

These patterns turn a cheap query back into a full (or much larger) scan. The editor’s estimated bytes will tell you immediately.

-- BAD: function wrapping that BigQuery cannot prune on
where timestamp_trunc(occurred_at, day) = timestamp('2026-08-11')

-- GOOD: half-open range on the raw timestamp
where occurred_at >= '2026-08-11'
  and occurred_at <  '2026-08-12'

-- BAD: SELECT * reads every column (columnar storage)
select * from analytics.events
where date(occurred_at) = '2026-08-11';

-- GOOD: name what you need
select event_id, user_id
from analytics.events
where date(occurred_at) = '2026-08-11';

-- BAD: joining then filtering the partition on the outer query
-- after a subquery that selected all days
with all_days as (
  select * from analytics.events
)
select * from all_days
where date(occurred_at) = '2026-08-11';

Push the partition filter to the base table in the CTE. Some plans still prune; some do not. Dry-run both.

-- BAD: CAST / FORMAT on the partition column in WHERE
where format_date('%Y-%m-%d', date(occurred_at)) = '2026-08-11'

Looker, Hex, and “helpful” semantic layers love generating the bad forms. Review the SQL they emit, not the chart.

flowchart TD
  filter[Filter on event time] --> shape{Sargable?}
  shape -->|occurred_at greater-or-equal day AND less than next| prune[Partition prune]
  shape -->|FORMAT or TRUNC on the column| scan[Planner cannot prune]
  prune --> day[One day of bytes]
  scan --> all[Whole table in the estimate]

Wrap the partition column and you bought a full scan. Range-compare the raw timestamp and you did not.

-- Materialize today's pruned slice once. Point the hourly
-- dashboard at this table instead of re-scanning the fact.
create or replace table analytics.events_today
partition by date(occurred_at)
as
select event_id, user_id, event_type, occurred_at
from analytics.events
where occurred_at >= timestamp_trunc(current_timestamp(), day)
  and occurred_at < timestamp_add(
    timestamp_trunc(current_timestamp(), day),
    interval 1 day
  );

Dry-run the dashboard against events_today and against the base fact. If the bytes barely move, the BI tool is still wrapping the timestamp or selecting every column.

Verify cost before you run

In the UI, estimated bytes update as you type. From the CLI:

bq query --dry_run --use_legacy_sql=false \
  'select event_id from analytics.events
   where date(occurred_at) = "2026-08-11"'

On-demand list price is published per TiB scanned (region and edition matter — read the current pricing page, do not tattoo a number from memory onto a contract). The runbook math is:

bytes_from_dry_run × runs_per_month × your_rate

A dashboard that re-scans a terabyte-class unpruned table every hour is a budget incident, not a “BI is expensive” story. Partition the table, fix the filter, then see if the dashboard still matters.

-- What actually ran (INFORMATION_SCHEMA, region qualifier required)
select
  creation_time,
  user_email,
  total_bytes_billed,
  total_slot_ms,
  destination_table.table_id,
  query
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where creation_time > timestamp_sub(current_timestamp(), interval 7 day)
  and statement_type = 'SELECT'
order by total_bytes_billed desc
limit 20;

Optimize the jobs at the top of that list. Do not cluster a table nobody queries.

Writes that keep pruning honest

-- Load / query write into a partitioned table
create table analytics.events_daily
partition by dt
cluster by event_type
as
select
  date(occurred_at) as dt,
  event_id,
  user_id,
  event_type,
  occurred_at
from analytics.events_landing
where date(occurred_at) = @run_date;

Scheduled queries and Dataform / dbt models should always filter @run_date (or the dbt var) on the partition column. A daily job that select * from an unfiltered three-year source is how “incremental” becomes a full scan with extra steps.

-- MERGE with a partition filter on the target
merge analytics.orders t
using analytics.orders_stage s
  on t.order_id = s.order_id
 and t.order_date = s.order_date
when matched then update set amount = s.amount
when not matched then insert row;

Put the partition column in the ON clause or a t.order_date = @day predicate so BigQuery does not consider every partition a merge candidate.

Clustering vs partition explosion

Do not partition by user_id because “we always filter by user.” You will create a mess of tiny partitions and hit partition-count limits. Partition by date (or a coarse integer range). Cluster by user_id.

If almost every query is “one user, all time,” you may want a second table clustered (or even grouped) for that access path — not 400 million partitions.

When on-demand is the wrong meter

If the team’s monthly scan bill is large after pruning is fixed, look at editions / slot commitments. Buying capacity so unpruned SELECT * can run all day is paying twice for the same mistake.

Capacity also does not fix a dashboard that scans a clustered table with a function on the cluster column. Dry-run still matters; the unit just becomes slots instead of bytes.

Pitfalls

• Partitioning by a high-cardinality timestamp at microsecond grain. Use date() or timestamp_trunc(..., hour) as the partition expression if you must, not raw micros.
• Assuming clustering helps SELECT * with no filter. It does not skip columns. It skips row blocks.
• Changing cluster columns weekly. Each rewrite has a cost. Pick from a month of JOBS_BY_PROJECT, then leave it.
• Streaming inserts + immediate “why isn’t it clustered?” Clustering catches up. Do not page on a five-minute-old stream.
• Copying a table with bq cp and losing options. Recreate partition / cluster / require_partition_filter explicitly.

When NOT to bother

• Tables that stay small (hundreds of MB). Partitioning adds ceremony for no scan win. A clustered tiny dim is optional.
• Workloads that always need the full history in one query (some ML training extracts). Still cluster if joins filter; do not pretend a partition filter exists.
• You are on a flat-rate reservation and the pain is slot contention, not bytes. Pruning still helps slots, but your next lever might be the query itself or a materialized table.

FAQ

Why did estimated bytes stay huge after I added WHERE date(...)? The predicate is probably wrapped (FORMAT, TIMESTAMP_TRUNC on the raw column, or a CAST). Pruning needs a sargable range on the partition expression. Dry-run until the bytes drop.

Is clustering a second partition key? No. It sorts blocks inside a partition. There is no require_cluster_filter. It helps after the date prune still leaves a large scan.

Should I partition by user_id? Almost never. You will hit partition-count limits and tiny-file pain. Partition by date. Cluster by user_id.

Does require_partition_filter slow queries? It fails queries that forgot a partition predicate. That is the point. Analysts complain once. Finance complains every month if you skip it.

Will a reservation fix an unpruned dashboard? It changes the meter from bytes to slots. The dashboard still scans the table. Fix the filter, then talk about editions.

I streamed rows in five minutes ago. Why is clustering “wrong”? Clustering catches up in the background. Do not page on a brand-new stream. Check again after the table has settled.

Production checklist

• Large facts: partition on a date/timestamp column, not ingestion time, unless landing time is the grain.
• require_partition_filter = true on those tables.
• Cluster columns match the top filters/joins in JOBS_BY_PROJECT.
• dbt / scheduled SQL includes a sargable partition predicate.
• Dashboards: dry-run bytes × refresh rate written down.
• SELECT * banned on fact tables in code review.
• Partition expiration only where legal / product agrees.
• A monthly look at the heaviest jobs — not a one-time migration.

Partition first, cluster second, then argue about slots. Most BigQuery bills I have been asked to “look at” were a missing WHERE on occurred_at and a BI tool that selected every column. The features above exist so that mistake fails closed.