Your first 10 BigQuery cost guardrails (before the bill, not after)

Every BigQuery horror story has the same shape. Somebody wrote a query, somebody scheduled it, and nobody found out what it cost until the invoice arrived four weeks later. By then it had run 720 times.

The fix is not "be careful". Careful does not survive contact with a new analyst, a connected BI tool, or an AI assistant generating SQL on demand. The fix is a set of controls that make expensive queries fail loudly instead of running quietly.

Here are ten, ordered by saving per hour of effort. The first four take under an hour combined and prevent the large majority of surprise bills.

The four that take an hour

1. Set maximum_bytes_billed as a project default

This is the highest-leverage control in BigQuery and the most consistently skipped. It converts a surprise bill into an error message.

Pick a ceiling slightly above your legitimate heaviest routine query. If your normal working queries scan 10–20 GB, set 100 GB. The analyst who accidentally writes a full-table SELECT * gets an error in two seconds rather than a $60 charge, and — importantly — learns something, because the error names the byte figure.

2. Custom quotas per user and per project

maximum_bytes_billed caps a single query. Custom quotas cap the daily total, which is what catches a loop, a misconfigured scheduler, or a BI tool refreshing far more often than anyone intended.

Set these in IAM & Admin → Quotas: Query usage per day at the project level, and Query usage per day per user. Numbers that work for a typical mid-sized setup: 2 TB/day per project, 500 GB/day per user. Both are adjustable, and hitting one produces a clear error rather than a bill.

The per-user quota matters more than people expect, because it isolates blast radius. One person's runaway notebook cannot consume the whole team's budget.

3. Require a partition filter on your largest tables

This makes it structurally impossible to scan a whole table by accident:

Now SELECT * FROM analytics.events fails with an explicit message telling the user to add a filter on event_date. Every query against your biggest table is forced to prune. This one option has probably saved more money than any other single line of BigQuery configuration.

4. A billing alert, at a threshold you will actually notice

Cloud Billing → Budgets & alerts. Set a monthly budget with alerts at 50%, 90% and 100%. Critically, route them to a channel a human reads — a shared inbox that nobody opens is the same as no alert.

Set the budget at roughly 1.3x your normal month. Too tight and you train yourself to ignore it; too loose and it fires after the damage is done. alerts to an inbox nobody opens are decoration

The five that take an afternoon

5. Partition and cluster

Partitioning splits a table into date-based segments so a filtered query reads only the segments it needs. Clustering sorts data within each partition so filters on the clustered columns prune further.

The rule of thumb: partition on the column in your WHERE clause; cluster on the columns in your JOIN and secondary filters. On a typical marketing events table, that is partition by event_date, cluster by (campaign_id, event_name).

6. Materialise what dashboards read

This is the single largest recurring saving in most setups. A dashboard tile that queries raw events costs its full scan on every refresh, for every viewer. The same tile reading a pre-aggregated daily table costs a rounding error.

7. Partition expiration

Storage is cheaper than query, which is why it gets ignored — but on a multi-terabyte events table it is a real monthly line, and 400-day-old raw events have essentially zero query value once you have rollups. Keep the aggregates forever; expire the raw.

8. Audit scheduled queries quarterly

Scheduled queries are where cost hides, because nobody watches something that already works. Query your own job history to find them:

Run this once a quarter. The top five rows are almost always where 80% of your spend is, and at least one of them is usually a report nobody reads any more.

9. A restricted service account for BI tools

Connected BI tools are a common source of runaway cost because they refresh on their own schedule and often ignore query caching. Give each one a dedicated service account with:

• Access only to your aggregate dataset, not raw tables.
• Its own Query usage per day per user quota.
• A conservative maximum_bytes_billed default.

Then a misconfigured refresh interval costs you a quota error instead of a month of scans.

Make cost visible before running

The other nine are limits. This one is culture, and it is what stops you needing the limits.

Every person writing SQL should see the byte estimate before pressing run. In the BigQuery console it is already there — the validator line in the top right, updating as you type. Most people never look at it, because nobody ever told them it was the price tag.

For programmatic access, the dry run gives the same figure for free:

bq query --dry_run --use_legacy_sql=false \
  'SELECT campaign_id, SUM(revenue) FROM analytics.events WHERE event_date >= "2026-08-01" GROUP BY 1'

Start here if time is short

Frequently asked questions

Will maximum_bytes_billed break legitimate queries?

Occasionally, and that is the point — you find out at the moment it happens, with a message naming the byte figure, rather than in an invoice. Set it above your heaviest routine query, and let people raise it per-session for the rare job that needs it.

Does query caching help?

Yes, substantially, and it is free — identical queries against unchanged tables return cached results at no cost for 24 hours. It fails silently though: any non-deterministic function (CURRENT_TIMESTAMP(), RAND()), any wildcard table, or any change to the underlying data invalidates the cache. Dashboards that inject a timestamp into every query defeat it entirely.

Should I switch to capacity (flat-rate) pricing?

It becomes attractive at high, steady volume — you buy slots and cost stops depending on bytes at all, which caps the downside of a badly-written query. But it also caps your throughput, so an expensive query slows everything else rather than costing more. Fix the guardrails first: many teams that were considering the switch find on-demand is fine once the rollups exist.

How do I find which queries cost the most historically?

INFORMATION_SCHEMA.JOBS_BY_PROJECT holds 180 days of job history including total_bytes_billed per job. The audit query above is the starting point; group by user_email alone for a per-person view.

Does LIMIT reduce cost?

No. LIMIT truncates the result after the scan is complete and paid for. The only things that reduce bytes are selecting fewer columns, filtering on a partition column, and reading from a smaller table.

One question to check the whole post landed. if this is easy, the guardrails worked

The summary

Ten controls, in order of return per hour:

1. maximum_bytes_billed as a project default — turns surprise bills into errors.
2. Custom daily quotas, per project and per user.
3. require_partition_filter on your largest tables.
4. A billing alert routed to a channel someone reads.
5. Partition and cluster every table over a gigabyte.
6. Materialise the aggregates dashboards read — usually the biggest recurring win.
7. Partition expiration on raw data.
8. Quarterly scheduled-query audit from INFORMATION_SCHEMA.
9. Restricted service accounts for BI tools.
10. Byte estimates visible before every run, for humans and for anything generating SQL.

The first four take an hour. Do those today, and the rest becomes optimisation rather than firefighting.