BigQuery Cost Control: How to Let People Ask Questions Without a Surprise Bill

BigQuery bills on-demand queries by the bytes scanned, not the rows returned, which is why one careless question can cost more than a month of dashboards. The fix is three controls, and they take an afternoon: partition and cluster your big tables so queries read a slice instead of everything, set maximum bytes billed so a runaway query fails instead of bills, and set custom quotas per user and per project so a bad day has a ceiling. Do those and you can let people ask their own questions without watching the invoice.

Why BigQuery bills the way it does

The mental model that causes overspend is thinking of BigQuery like Postgres, where a query returning ten rows is cheap. BigQuery does not care what you got back. It cares what it had to read to find it.

Because storage is columnar, the price of a query is roughly the size of the columns it touched across the partitions it scanned. That has two consequences people learn the expensive way. SELECT * is genuinely costly, because it reads every column including the ones nobody wanted. And LIMIT 10 saves you nothing at all: the limit applies after the scan, so the same terabyte gets read and billed either way.

This is also why the same question can cost wildly different amounts depending on how it is written. A filter on a partition column reads one day. The identical filter on an unpartitioned column reads the whole table.

The three controls that actually cap spend

They stack, and they cover different failure modes. Partitioning makes the normal case cheap. Maximum bytes billed catches the pathological single query. Quotas cap the aggregate day. Skipping any one of them leaves a real hole: a well-partitioned warehouse can still be drained by someone running the same expensive query in a loop.

Partition and cluster the tables people actually query

This is the highest-leverage change and it is usually a one-time job on a handful of tables. Partition on the column people filter by, which is almost always a date. Once events is partitioned by day, "how many signups last week" reads seven partitions instead of three years of history.

Clustering goes a level further, sorting data within each partition by columns you filter or group on, like customer_id or country. Filters on clustered columns let BigQuery skip blocks inside the partition.

Then turn on require partition filter on those tables. It rejects any query that does not filter on the partition column, which converts the most common expensive mistake into an error message. That single setting prevents more overspend than most cost dashboards do, because it stops the bytes from being read rather than reporting on them afterward.

How do I stop a single query from costing thousands?

Set maximum bytes billed. It is a per-query (or per-job) limit: if BigQuery's estimate exceeds the number you set, the query fails immediately and costs nothing instead of scanning a warehouse and billing for it. You can set it in the console, on a job config through the API, or as a session default.

Pick a number by asking what a legitimate question should cost. If your normal query reads a few gigabytes, a cap of 100GB leaves room for genuinely big work while making a full-table cross join impossible. The failure message is a much better teacher than a line item discovered three weeks later.

Pair it with a dry run, which BigQuery supports natively: it returns the byte estimate without executing anything. Any tool worth using should be able to tell you what a query will scan before it runs it.

Should I use flat-rate or on-demand pricing?

On-demand bills per byte scanned and has no floor, so it is the right default for spiky, unpredictable, ad-hoc usage. Capacity-based pricing (reserved slots) bills for compute you hold whether or not you use it, which makes cost predictable but sets a monthly floor.

The honest rule: stay on demand until your monthly on-demand spend is consistently high and predictable enough that reserved capacity is cheaper, then switch. Buying slots to control cost when your usage is low and lumpy generally means paying a fixed price for the privilege of not thinking about it. If cloud spend across services is the broader concern, that is a cloud cost problem worth watching across the whole account, not just a BigQuery setting.

Does an AI data analyst make BigQuery more expensive?

It can, if it is careless, and this is a fair thing to ask before you put a plain-English box in front of a warehouse that bills per byte. The risk is real: more people asking more questions means more queries, and a tool that writes SELECT * against an unpartitioned table will cost you money at machine speed.

What makes the difference is whether the tool writes queries the way a competent analyst would. Agentsql filters on partition columns when they exist, selects the columns the question needs instead of everything, and shows you the exact SQL for every answer, so an expensive query is visible before it becomes a habit rather than after it becomes an invoice. Combined with maximum bytes billed and a per-user quota, the ceiling is set by your controls, not by anyone's good intentions.

It is also worth weighing what the questions replace. A question that gets answered in ten seconds for a few cents of scan was previously a ticket that cost an analyst half an hour. Scanning is cheap; analyst time is not. The goal is not zero query spend, it is spend that maps to answers people actually needed. We broke down the wider math in what an AI data analyst actually costs.

Why is my BigQuery bill so high?

In our experience it is almost always one of five things, in rough order of frequency.

1. Unpartitioned large tables. Every query reads everything. Fix the table, not the queries.
2. SELECT * in saved queries and dashboards. Especially ones on a schedule, quietly rescanning every column every hour.
3. Scheduled queries nobody uses anymore. The dashboard was for a launch in March. It still refreshes every 30 minutes.
4. Views over views. A tidy-looking query against a view that expands into a full scan of three underlying tables.
5. Dashboard auto-refresh. One dashboard, ten tiles, refreshing every five minutes, is over a hundred thousand queries a month.

Notice how few of those are humans asking too many questions. The recurring machine costs are usually larger than the ad-hoc human ones, which is exactly backwards from where the anxiety usually goes. Check INFORMATION_SCHEMA.JOBS to see where your bytes actually went before you restrict anyone.

A setup you can finish this week

1. Query INFORMATION_SCHEMA.JOBS for the last 30 days, sorted by total bytes billed. Find your real top ten.
2. Partition and cluster the tables behind them, then enable require partition filter.
3. Set maximum bytes billed as a default for interactive and tool-driven queries.
4. Set custom quotas per user and per project so the worst possible day has a known ceiling.
5. Delete the scheduled queries nobody reads. This is usually the single biggest line item.
6. Then connect people to ask BigQuery questions in plain English and let them work.

The point of the controls is not to ration curiosity. It is to make the cheap path the default one, so you can stop rationing it.