How do you stop one careless BigQuery query from billing terabytes?
answer
- one ceiling per job is not enough
- a daily cap for the project or user
- make the table demand its filter
- labels turn a bill into an owner
- changing billing model bounds the downside
basics
~20 sLayer controls: maximum_bytes_billed fails an oversized job before it reads data, custom query-usage-per-day quotas cap a project or user for the day, require_partition_filter blocks unfiltered scans of big tables, and INFORMATION_SCHEMA attributes what still slips through.
solid answer
~50 sNo single knob is enough, so the answer is a ladder. **Per job**: `maximum_bytes_billed` — BigQuery fails the query before reading data if it would exceed the ceiling, and you are not billed. Set it as a default in every client and scheduled job. **Per project or user per day**: a custom query-usage-per-day quota stops runaway automation after the daily budget, resetting the next day. **Per table**: `require_partition_filter` on large partitioned tables makes an unfiltered scan an error rather than an invoice. **Before the query exists**: dry runs in CI on generated SQL, and review of views that hide `SELECT *`. **After the fact**: `INFORMATION_SCHEMA.JOBS` ordered by `total_bytes_billed`, grouped by `user_email` or job labels, to find whose dashboard is the problem. Billing budgets and alerts still matter, but they are lagging — they tell you it happened, not that it must not.
code
sql · 3 lines-- Make an unfiltered scan an error instead of an invoice
ALTER TABLE `proj.ds.events`
SET OPTIONS (require_partition_filter = TRUE);go deeper
Recall the two controls you can set yourself: a maximum-bytes-billed ceiling on your query, and reading the dry-run estimate before you run anything large.
Explain each control's scope — per job, per project per day, per table — and that a per-job ceiling does nothing about a thousand jobs at the ceiling.
Design the layered scheme: defaults baked into shared clients, require_partition_filter on event tables, quotas per environment, labels for attribution, and a routine that reviews the top spenders.
Own the policy: what spend is bounded by controls versus by billing model, what limits sandbox versus production carry, and who is accountable when the guardrails fire.
## Why guardrails, not discipline On-demand BigQuery has an unusual property: a single line of SQL typed into a console can read petabytes and produce a bill nobody approved. Training helps, but the queries that cost the most are usually machine-generated — a BI tool refreshing an unfiltered view, a scheduled model that quietly widened, a notebook loop. The engineering answer is a layered set of limits where each layer catches what the one before missed. ## Layer 1 — the per-job ceiling `maximum_bytes_billed` is the most direct control. It exists as `--maximum_bytes_billed` in `bq`, as `maximumBytesBilled` in the query job configuration, and as a query setting in the console. ```bash bq query --use_legacy_sql=false --maximum_bytes_billed=50000000000 \ 'SELECT user_id FROM `proj.ds.events` WHERE event_date = "2026-08-01"' ``` If BigQuery determines the query would process more than the limit, the job fails immediately with an error and nothing is billed. The important operational move is not setting it occasionally but making it a **default in the shared client wrapper**, so every service, scheduled job and notebook inherits a sane ceiling and has to opt out deliberately for a genuinely large job. ## Layer 2 — the daily budget A per-job ceiling does not stop a thousand jobs at the ceiling. Custom quotas on query usage per day cap the total bytes a project — or a single user within a project — can process in a day. Once exceeded, further queries fail until the quota resets on the next daily cycle. This is the control that turns an infinite-loop notebook or a misconfigured scheduler from a five-figure event into an annoyance, and it is the one most teams forget until after their first incident. Set it per environment: a generous production number, a tight one for sandbox projects. ## Layer 3 — the table's own contract On a large partitioned table, `require_partition_filter` makes any query that does not filter on the partitioning column fail. It converts "the analyst forgot the date filter" from the most expensive query of the month into an error message that names the problem. It is the highest-value single setting on any event-scale table, and it costs nothing. ## Layer 4 — before the query is written Dry runs are free, so generated SQL can be estimated in CI and a pull request failed when a model would scan more than an agreed threshold. Review views carefully: a logical view defined as `SELECT *` inlines the full-width scan into every caller, and a wide view over a partitioned table without a filter is a cost trap with a friendly name. Materialized views, scheduled aggregates and BI Engine reduce the repeated cost of dashboards rather than capping it. ## Layer 5 — attribution after the fact When something did cost money, `INFORMATION_SCHEMA.JOBS` answers who and what: ```sql SELECT user_email, SUM(total_bytes_billed) / POW(1024, 4) AS tb, COUNT(*) AS jobs FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND job_type = 'QUERY' GROUP BY user_email ORDER BY tb DESC; ``` Adding labels to jobs from each pipeline or BI tool makes the grouping meaningful beyond service accounts — otherwise every expensive query is attributed to one shared identity and you learn nothing. Cloud Billing budgets and alerts belong here too, but understand what they are: lagging detection with a delay, useful for noticing a trend, useless for preventing a single bad query. ## Layer 6 — change the billing model The structural fix is capacity pricing. Inside a reservation, an unfiltered scan consumes slot time and slows its neighbours; it does not generate an unbounded charge. Organizations with many analysts and weak query hygiene sometimes move to slots as much for the bounded downside as for the price. The trade is that you now manage queueing and workload isolation instead of bytes. ## Putting it together A credible answer names at least three layers and distinguishes preventive from detective controls. The preventive ones — `maximum_bytes_billed`, daily quotas, `require_partition_filter`, capacity pricing — stop the spend. The detective ones — dry runs in review, INFORMATION_SCHEMA attribution, billing alerts — tell you where to aim the preventive ones next. A team relying only on alerts is describing how they find out, not how they stop it.
- What exactly happens when a query exceeds maximum_bytes_billed?BigQuery determines the query would process more bytes than the limit and fails the job before reading data. You get an error naming the limit, and nothing is billed. It is a hard stop, not a truncation — the query does not run partially and does not return a reduced result set.
- Why aren't Cloud Billing budget alerts sufficient on their own?They are detective, not preventive, and they arrive after usage is recorded — often hours later, by which time an expensive query has finished. Use them to notice trends and to catch what the hard controls miss, but the spend has to be bounded by per-job ceilings and daily quotas.
- How do you attribute cost when everything runs as one service account?Attach labels to jobs — per pipeline, per model, per dashboard — and group `INFORMATION_SCHEMA.JOBS` by label rather than `user_email`. Without labels, every job from a BI tool or orchestrator collapses onto one identity and you can see that money was spent but not which asset spent it.
saying these in an interview costs you the question
- Relies only on billing alerts to control spend
- Thinks maximum_bytes_billed truncates the query instead of failing it
- Believes LIMIT is a cost guardrail
- Forgets daily quotas, so repeated jobs bypass the per-job cap
- Blames analysts rather than adding an enforceable control