Cutting BigQuery Costs: Partitioning, Clustering, and Bytes Scanned

You are billed for bytes read, not rows returned

The single most important thing to understand about BigQuery's on-demand pricing is that the size of your result has nothing to do with your bill. A query returning one number can scan a terabyte and charge you for a terabyte.

select count(*)
from events
where event_date = '2025-08-01';

One row out. If events isn't partitioned, that's a full scan of every byte in the event_date column — and if it's stored as a string rather than a date, potentially far worse.

Everything below is a variation on one goal: read fewer bytes.

Callout: Before running anything expensive, check the estimate. The BigQuery console shows a bytes-processed estimate in the top right before you run. From the CLI, bq query --dry_run gives you the same number without executing. Make reading it a habit.

Columns are stored separately

BigQuery is columnar. Each column lives in its own set of blocks, so a query reads only the columns it names. That single fact makes select * the most expensive habit in the language.

-- reads every column in the table
select * from events where user_id = 42;
 
-- reads two
select user_id, event_name from events where user_id = 42;

On a wide events table — a hundred columns, most of them unused — that difference is routinely 20× or more. It costs nothing to write the column list, and you were going to need only three of them anyway.

This is also why select * except(...) is worth knowing when you genuinely need most columns:

select * except(raw_payload, debug_json)
from events;

The two heaviest columns in an events table are usually a JSON blob and a raw payload. Excluding them often cuts the scan in half.

Partitioning: skip whole days

Partitioning splits a table into segments by a date or integer column. When a query filters on that column, BigQuery skips entire partitions without reading them — this is called pruning, and it's the biggest single lever available.

create table analytics.events (
  event_timestamp timestamp,
  user_id int64,
  event_name string,
  revenue numeric
)
partition by date(event_timestamp)
cluster by event_name, user_id;

Now a query filtering on the partition column reads one day instead of three years:

select event_name, count(*)
from analytics.events
where date(event_timestamp) between '2025-08-01' and '2025-08-07'
group by event_name;

Pruning is fragile, and breaking it is easy. The filter must be on the partitioning column, in a form the engine can evaluate before reading. These all silently defeat it:

-- function applied to the partition column in a way that hides the range
where format_timestamp('%Y-%m', event_timestamp) = '2025-08'
 
-- comparing against a subquery result
where date(event_timestamp) = (select max(load_date) from control_table)
 
-- the partition column buried in an OR with a non-partition condition
where date(event_timestamp) = '2025-08-01' or user_id = 42

The subquery case is the one that bites teams in production, because it looks so reasonable. Resolve the value first and inject it as a literal, or use a scripting variable:

declare target_date date default (select max(load_date) from control_table);
 
select count(*)
from analytics.events
where date(event_timestamp) = target_date;

You can force the issue for everyone by setting require_partition_filter on the table, which rejects any query that doesn't filter on the partition column. On a large shared table this is one of the highest-value settings available — it converts an expensive mistake into an error message.

Clustering: skip blocks within a partition

Clustering sorts data inside each partition by the columns you choose, and records the value range of each block. A filter on a clustered column lets BigQuery skip blocks whose ranges can't match.

Order matters and works left to right, exactly like a composite index. With cluster by event_name, user_id:

• filtering on event_name — full benefit
• filtering on event_name and user_id — full benefit
• filtering on user_id alone — little or no benefit

So cluster by the column you filter on most often, first. Up to four columns are allowed, and pick ones with high cardinality — clustering on a boolean accomplishes nothing.

Unlike partitioning, clustering gives no up-front estimate. The console's estimate ignores block pruning, so a clustered query frequently bills less than predicted. Check total_bytes_billed in the job statistics afterward to see what actually happened.

Filter before you join

Join order and filter placement matter more here than in a typical row-store, because every byte read is billed.

-- reads all of both tables, then discards most of it
select u.country, count(*)
from events e
join users u on u.id = e.user_id
where date(e.event_timestamp) = '2025-08-01';

Push the filter down so the join operates on a fraction of the data:

with todays_events as (
  select user_id
  from events
  where date(event_timestamp) = '2025-08-01'
)
select u.country, count(*)
from todays_events e
join users u on u.id = e.user_id
group by u.country;

Same answer, and the scan is bounded by one partition plus the two columns of users that are actually needed.

Stop re-reading the same raw data

The most expensive pattern in most warehouses isn't a single bad query — it's fifty dashboards each aggregating the same raw events table every morning.

The fix is to aggregate once and let everything read the small table:

create or replace table analytics.daily_event_summary
partition by event_date as
select
  date(event_timestamp) as event_date,
  event_name,
  count(*) as event_count,
  count(distinct user_id) as unique_users,
  sum(revenue) as revenue
from analytics.events
where date(event_timestamp) >= date_sub(current_date(), interval 90 day)
group by event_date, event_name;

A dashboard hitting that summary reads megabytes instead of terabytes. For simpler cases, a materialized view maintains itself incrementally and BigQuery will transparently route eligible queries to it.

Find out where the money actually goes

Don't guess. INFORMATION_SCHEMA records every job, so you can rank your spend directly:

select
  user_email,
  count(*) as jobs,
  round(sum(total_bytes_billed) / pow(1024, 4), 2) as tib_billed
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where creation_time >= timestamp_sub(current_timestamp(), interval 30 day)
  and job_type = 'QUERY'
group by user_email
order by tib_billed desc
limit 20;

Swap user_email for query to find the specific statements responsible. In nearly every project I've looked at, a small handful of scheduled queries account for most of the bill — and they're usually the easiest things to fix, because nobody is watching them run.

The order to do this in

1. Cap the blast radius. Set a maximum bytes billed on queries and configure project-level quotas so a single mistake can't run away.
2. Measure. Query INFORMATION_SCHEMA and rank by bytes billed.
3. Partition the big tables and turn on require_partition_filter.
4. Cluster on whatever those top queries filter by.
5. Kill select * in scheduled queries and BI tool connections.
6. Pre-aggregate anything read repeatedly.

Steps 1 through 3 typically account for most of the savings. The rest is refinement — worth doing, but only after the table layout is right, because no amount of query tuning compensates for an unpartitioned table.