Cutting a BigQuery Bill: Partitioning, Byte Limits, Materialized Views, and On-Demand vs Editions

The first BigQuery bill I was asked to fix was $41,000 in a month for a company with about 9 TB of data. Nine terabytes. They were scanning that dataset roughly 400 times over, every month, and nobody could tell me which query did it.

That is the normal shape of this problem. BigQuery is not expensive; it is unmetered at the point of use. An analyst writes a query, it returns in eight seconds, and the cost shows up thirty days later in a line item nobody owns. There is no slow query to complain about, which is exactly why it compounds.

So I do not start with tables. I start with the job history, because the bill is a ranked list and the top of that list is almost always shorter than anyone expects. In the $41k case, six scheduled queries and one Looker dashboard were 88 percent of the spend. Fixing them took two days.

This is the order I work in, the numbers I use to decide, and the controls I put in place afterward so the same thing cannot happen twice.

Find the money first

Every BigQuery project keeps a queryable job history. This is the single most useful query I know for this platform, and most teams have never run it.

-- Top 25 spenders over the last 30 days, with estimated on-demand
-- cost. Replace `region-us` with your dataset's region.
select
  coalesce(
    regexp_extract(query, r'/\* dbt_model: (\S+) \*/'),
    destination_table.table_id,
    user_email
  )                                            as owner_hint,
  count(*)                                     as runs,
  sum(total_bytes_billed) / pow(1024, 4)       as tib_billed,
  -- On-demand list price is per TiB scanned. Check the current
  -- rate for your region instead of trusting a hardcoded number.
  round(sum(total_bytes_billed) / pow(1024, 4) * 6.25, 2) as est_usd,
  sum(total_slot_ms) / 1000 / 3600             as slot_hours
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where creation_time >= timestamp_sub(current_timestamp(), interval 30 day)
  and job_type = 'QUERY'
  and state = 'DONE'
  and error_result is null
group by owner_hint
order by tib_billed desc
limit 25;

Two columns matter. total_bytes_billed is your on-demand invoice. total_slot_ms is what you would pay for under Editions. Keeping both in the same result set is how you answer the pricing-model question later without a second investigation.

Sort by tib_billed * runs and you will find the real villain, which is rarely the biggest single query. It is a 40 GB scan on a 15-minute dashboard refresh: 2.8 TB a day, from a chart three people look at.

Pruning is the fix; everything else is a discount

Once you know which queries cost money, almost every fix is the same fix: make them scan fewer bytes. Partition the fact table on event date, cluster on the column the top queries filter, and make sure the predicates are sargable. I wrote the mechanics up in the partitioning and clustering guide, so I will not repeat them here.

What I will repeat is the ordering, because teams get it backwards constantly. Buying slot capacity, adding materialized views, or turning on BI Engine before pruning works is paying a discounted rate for work that should not happen at all. You will save 30 percent on a query that could have been 95 percent smaller.

flowchart TD
  bill["High BigQuery bill"] --> top["Rank jobs by bytes billed"]
  top --> prune{"Do the top jobs prune?"}
  prune -->|"no"| fix["Partition, cluster, fix predicates"]
  fix --> cap
  prune -->|"yes"| cap["Set maximum_bytes_billed"]
  cap --> repeat{"Same query, same result, many times?"}
  repeat -->|"yes"| mv["Materialized view or scheduled table"]
  repeat -->|"no"| meter{"Steady spend above ~100 slots?"}
  mv --> meter
  meter -->|"yes"| editions["Editions with autoscale"]
  meter -->|"no"| ondemand["Stay on-demand"]

Fix the scan, cap the blast radius, cache the repeats, and only then argue about the pricing model.

The one exception I make: if the bill is actively on fire this week, set the byte caps first. They stop the bleeding in an hour, and pruning work takes days.

Byte limits: the control that fails closed

maximum_bytes_billed is the most underused feature in BigQuery. It kills a query that would exceed a byte threshold before it runs. Not a warning, not an alert after the fact — a hard failure with a clear error message.

Set it as a project-level default so nobody has to opt in:

-- Any query in this project that would scan more than 200 GB fails.
alter project `acme-analytics`
set options (
  default_query_job_maximum_bytes_billed = 200000000000
);

-- Per-run override for the handful of jobs that legitimately
-- need more, so the exception is explicit and reviewable.
set @@query_maximum_bytes_billed = 2000000000000;  -- 2 TB

From the CLI or a scheduler, it is a flag:

bq query \
  --use_legacy_sql=false \
  --maximum_bytes_billed=200000000000 \
  --dry_run \
  'select order_id from analytics.orders where order_date >= "2026-05-01"'

Pair it with a custom quota on daily bytes at the project and user level. The quota is the second net: even if someone raises the per-query limit, they cannot spend the whole month’s budget in an afternoon.

The cultural effect matters more than the technical one. Once an unpruned query fails immediately with “would process 4.1 TB, limit is 200 GB,” analysts learn to check the dry-run estimate. That lesson does not happen from a monthly cost review meeting.

Materialized views, honestly

Materialized views in BigQuery are genuinely good and routinely misapplied. The engine maintains them incrementally, and — this is the part people miss — it can rewrite queries against the base table to hit the view automatically, without the query author knowing.

-- Pre-aggregate the shape your dashboards actually ask for.
create materialized view analytics.mv_daily_revenue
partition by order_day
cluster by country_code
options (
  -- Allow up to 30 minutes of staleness so the view is not
  -- refreshed on every single write to the base table.
  max_staleness = interval 30 minute,
  allow_non_incremental_definition = false
)
as
select
  date(order_ts)        as order_day,
  country_code,
  count(*)              as orders,
  sum(amount_usd)       as revenue_usd,
  -- APPROX_COUNT_DISTINCT is materializable; COUNT(DISTINCT) is not.
  approx_count_distinct(customer_id) as customers
from analytics.fct_orders
group by order_day, country_code;

The economics are simple and unforgiving. You pay for the refresh work every time the base table changes, and you pay for the view’s storage. A materialized view pays off when the read-to-write ratio is high. If the base table is appended to every five minutes and the dashboard is read twice a day, the view costs more than the queries it replaces.

Restrictions to know before you plan around them: no COUNT(DISTINCT), no window functions, no UNION ALL, limited outer join support, and only one base table for incremental refresh. When you hit those walls, a scheduled query writing to a partitioned table is the answer. It is less elegant, refreshes on your schedule, and has no restrictions at all. I use scheduled tables more often than materialized views, and I have never regretted it.

BI Engine sits alongside this. It is a reserved in-memory cache priced per GB-hour, and on a dashboard hitting the same small aggregate table all day it is very cheap relative to the scans it eliminates. It does nothing for a 4 TB ad-hoc query, so do not buy it as a general remedy.

On-demand vs Editions: the actual crossover

On-demand charges per TiB scanned. Editions charge per slot-hour with autoscaling, plus optional baseline slots you can commit to for a discount.

The comparison people attempt — “which is cheaper?” — is unanswerable in the abstract because the two models bill different things. What is answerable is: given last month’s actual jobs, which meter would have produced a smaller number? You already have both columns from the query above.

"""Compare last month's on-demand bill against an Editions estimate
using real slot consumption from INFORMATION_SCHEMA."""

from google.cloud import bigquery

client = bigquery.Client()

ON_DEMAND_PER_TIB = 6.25    # verify against current regional pricing
SLOT_HOUR_ENTERPRISE = 0.06  # pay-as-you-go Enterprise, per slot-hour
HOURS_IN_MONTH = 730

rows = client.query("""
select
  sum(total_bytes_billed) / pow(1024, 4) as tib_billed,
  sum(total_slot_ms) / 1000 / 3600       as slot_hours
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where creation_time >= timestamp_sub(current_timestamp(), interval 30 day)
  and job_type = 'QUERY' and state = 'DONE'
""").result()

r = next(iter(rows))
on_demand = r.tib_billed * ON_DEMAND_PER_TIB
editions = r.slot_hours * SLOT_HOUR_ENTERPRISE

# Average concurrent slots tells you the baseline to commit to.
avg_slots = r.slot_hours / HOURS_IN_MONTH

print(f"on-demand:        ${on_demand:,.0f}")
print(f"editions (autoscale only): ${editions:,.0f}")
print(f"average concurrent slots:  {avg_slots:,.0f}")

The rough threshold I have landed on across several accounts: below the equivalent of about 100 baseline slots of steady consumption, stay on-demand. Above it, Editions with a modest baseline commitment plus autoscale usually wins, and the win grows with how flat your usage curve is.

The nuance that decides real cases is the shape of the load. Editions rewards steady utilization; a three-hour nightly batch and nothing else wastes committed baseline slots for 21 hours a day. On-demand rewards spiky, unpredictable work. If your profile is “quiet all day, enormous at 2 a.m.,” on-demand’s per-byte model is doing you a favor.

Reservations also buy you something cost models miss: predictability and workload isolation. Put the batch pipeline in one reservation and interactive BI in another, and a runaway backfill stops competing with the CFO’s dashboard. On-demand gives you a shared, invisible slot pool and no way to prioritize. Some teams switch to Editions for that reason alone and treat the cost outcome as neutral.

The storage line nobody reads

Compute gets all the attention, but on mature accounts storage creeps toward a quarter of the bill. Three levers, all cheap:

-- 1. Physical (compressed) billing instead of logical (uncompressed).
--    Typically 40-60% cheaper on columnar data that compresses well.
alter schema analytics
set options (storage_billing_model = 'PHYSICAL');

-- 2. Expire partitions you are legally and operationally allowed to drop.
alter table analytics.fct_events
set options (partition_expiration_days = 730);

-- 3. Find what is actually large before optimizing the wrong table.
select
  table_name,
  sum(total_logical_bytes)  / pow(1024, 4) as logical_tib,
  sum(total_physical_bytes) / pow(1024, 4) as physical_tib,
  safe_divide(sum(total_logical_bytes), sum(total_physical_bytes)) as compression_x
from `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
where table_schema = 'analytics'
group by table_name
order by logical_tib desc
limit 20;

Run the compression ratio query before flipping the billing model. Physical billing has a higher per-GB rate but bills the compressed size, so it wins whenever your compression ratio exceeds roughly 2.5x. Event tables with repeated strings compress 8x or better and are an obvious yes. Tables already storing compressed blobs may not be, and the switch has a 14-day lock-in per dataset.

Long-term storage discounting is automatic after 90 days without modification, which is a good reason not to run a pipeline that rewrites historical partitions for no reason. I have seen an “idempotent” daily job rebuild three years of partitions every night, resetting the 90-day clock on the entire table and doubling the storage bill silently.

Pitfalls

Optimizing tables instead of queries. Nobody’s bill is spread evenly. Rank the jobs, fix the top twenty, and ignore the tail. A week spent clustering a table that appears in no expensive query saves nothing.

Trusting query runtime as a cost signal. BigQuery will scan 2 TB in nine seconds and charge you for all of it. Runtime tells you about slot availability, not spend. Read bytes billed.

SELECT * in a staging model. Columnar storage means you pay per column read, and a wide SELECT * at the top of a chain makes every downstream model carry the width. This is the most expensive line of SQL in most warehouses — see the SQL anti-patterns post for the rest of that family.

Cache-dependent cost estimates. Repeating a query returns from cache and reports zero bytes billed, which makes a fix look more effective than it is. Use --dry_run or disable cache when measuring.

Buying Editions to avoid fixing SQL. Capacity pricing changes the unit, not the work. An unpruned dashboard on a reservation just starves your batch jobs of slots instead of generating a byte charge. Same disease, different symptom.

Streaming inserts as an afterthought. The Storage Write API and legacy streaming inserts are billed separately from queries, per GB ingested. High-volume event streams can quietly exceed the query line. Batch-load where freshness allows it.

FAQ

Why is my bill high when my dataset is small?

Because you are billed on bytes scanned, not bytes stored. A 100 GB table queried 300 times a day with no partition filter is 30 TB of scan per day. Dataset size sets the ceiling for a single query; query volume sets the bill.

Does clustering reduce bytes billed or just improve speed?

It reduces bytes billed, when the filter matches the cluster columns. BigQuery skips blocks it can prove cannot match, and you are not charged for skipped blocks. The catch is that the estimate shown before execution cannot account for block-level pruning, so the pre-run number will look pessimistic and the final charge will be lower.

Should I use materialized views or scheduled queries?

Scheduled queries writing to partitioned tables, unless you specifically want automatic query rewrite against the base table. Scheduled tables have no expression restrictions, refresh on a schedule you control, and are far easier to reason about when the numbers look wrong at 8 a.m.

How do I attribute cost to a team?

Inject a comment with the model or team name into every generated query and parse it out of JOBS_BY_PROJECT, which is what the regexp_extract in the first query does. Labels on jobs work too and are cleaner if your tooling sets them. Without one of these, attribution collapses to service-account email and you learn nothing.

Is BI Engine worth it?

If you have dashboards repeatedly hitting the same aggregate tables under a few hundred GB, yes — the reservation cost is small next to the scans it removes. It is not a fix for large ad-hoc queries or for unpruned fact tables, because it only caches what fits.

Can I set a hard monthly spending cap?

Not a true cap that stops billing, but custom quotas on daily bytes processed per project and per user come close, and maximum_bytes_billed prevents any single query from being the incident. Budget alerts in Cloud Billing are notifications, not limits — do not treat them as a control.

What this means for your pipelines

Treat the cost of a query as part of its definition. Every scheduled query and dbt model in your repo should have a known dry-run byte count and a known run frequency, and the product of those two belongs in the code review, not in a monthly finance escalation. The teams that stay out of trouble are the ones where “how many bytes does this scan” is as routine a question as “does this return the right rows.”

Then set the limits so the answer cannot be catastrophic. Project default maximum_bytes_billed, require_partition_filter on every large fact table, and custom daily quotas turn cost overruns into failed queries someone fixes the same morning. This is the same philosophy as auto-suspending an idle warehouse, which I argued at length in the Snowflake cost guide — the platform differs, the failure mode does not.

Only after all of that is the pricing-model question interesting. Pull your real slot-hours and bytes billed, run both meters against the same month, and switch if the numbers say so. Most teams who ask me whether they should move to Editions are actually asking whether they can stop caring about pruning. They cannot, and the reservation will make that clear within a week.