BigQuery bills that triple overnight, and the six controls that stop it
On-demand BigQuery charges for bytes scanned, so one badly written dashboard can cost more than your compute. Here is how we cap it without slowing anyone down.
BigQuery is the service most likely to produce a surprise invoice in Google Cloud, and the reason is structural: on-demand pricing charges for bytes scanned, and nothing in the default setup stops a SELECT * against a 40TB table from running forty times a day because someone put it behind a dashboard refresh.
The good news is that every control you need exists, is free, and takes a day to put in place.
1. Find out who is scanning what, today
INFORMATION_SCHEMA.JOBS_BY_PROJECT has every query, its user, its bytes billed and its duration. One query over the last 30 days, grouped by user and by referenced table, tells you where the money goes. Almost always the answer is concentrated: two or three recurring queries account for most of it, and they are usually a BI tool refreshing on a schedule.
Put that query in a scheduled report that lands in a channel weekly. Visibility alone changes behaviour more than any policy.
2. Partition and cluster the big tables
This is the biggest single lever and it is a schema change, not a query change. A table partitioned by ingestion date means a query with a date filter scans one day instead of three years. Clustering on the columns people filter by — customer ID, region, event type — cuts it further.
Then turn on require_partition_filter on the partitioned tables. A query without a date predicate fails instead of scanning everything. Teams grumble for a week and then their queries get faster.
3. Custom quotas, per project and per user
Console setting, no code: a daily bytes-scanned quota per project and per user. Set the per-user quota at something like five times a normal analyst's daily usage. It will not bother anyone doing normal work and it turns a runaway loop from a four-figure invoice into an error message.
This is the control that most teams have never enabled, and it is the one that would have prevented every BigQuery bill shock we have been called in about.
4. Materialise what is queried repeatedly
A dashboard that recomputes the same aggregate every fifteen minutes should be reading a materialised view or a scheduled aggregate table, not the raw events. The pattern: raw partitioned table, a scheduled query that writes daily aggregates, dashboards pointed exclusively at the aggregates. Scan volume typically drops by two orders of magnitude.
Also check BI Engine for the dashboards that stay hot — it caches at a fixed monthly price and takes the repeated scans off the on-demand meter entirely.
5. Decide on-demand versus editions, with real numbers
Editions (capacity-based pricing) buys slots rather than bytes. The crossover depends entirely on your pattern:
- Spiky, unpredictable, low total volume — stay on demand. You pay nothing when nobody queries.
- Steady daily pipelines with a predictable floor — Editions with autoscaling and a baseline of reserved slots is usually cheaper, and it caps the worst case, which is often worth more than the average saving.
Model it from the JOBS table before committing, and note that the two can coexist: reservations for the ETL project, on-demand for the ad hoc analysis project.
6. Storage, which people forget entirely
Storage is the quiet half of the bill. Three things: long-term storage pricing kicks in automatically after 90 days without modification (so avoid pointless rewrites of old partitions), partition expiration deletes data you have no reason to keep, and table snapshots are cheaper than full copies for the "keep a version before the migration" case.
Check for duplicated datasets while you are there. Every estate has three copies of the events table from three different migration attempts.
What good looks like
A month after this work, the typical result is 50 to 70 percent off the BigQuery line with no reduction in what anyone can do, and — more importantly — a hard ceiling so the next surprise cannot happen. We treat it as part of the cost work in a cloud engagement, the same way the five layers work on AWS.
What to do this week
Run the JOBS_BY_PROJECT query for the last 30 days grouped by user and sorted by bytes billed. Then set a per-user daily quota. The first tells you the story, the second means the story cannot get worse while you fix it.