Controlling BigQuery costs: dry runs, byte limits, quotas and partition filters

The ways to cap what BigQuery queries can cost. Dry runs, maximum bytes billed, require_partition_filter, custom daily quotas, reservation limits and billing budgets, and what each one does and doesn't stop.

BigQuery has no single “budget limit” setting. Cost is controlled at five different levels, and each one catches a different mistake. A dry run shows what one query will cost. Maximum bytes billed stops one query from costing more than a number you set. require_partition_filter stops queries that forget to filter a big table. Custom quotas cap a day’s bytes for a project or a user. Reservation limits cap slots when you are on capacity pricing. Billing budgets only send alerts.

The first four apply to on-demand pricing, where cost follows bytes read (see How a query is billed).

Dry runs

A dry run validates a query and reports how many bytes it would process, without running it. Querylab.io shows the estimate in the status bar as you type (see Cost limits). The BigQuery console shows the same number (“This query will process 623.1 KiB when run”), bq query --dry_run prints it, and the API takes a dryRun flag on the job.

SELECT word
FROM `bigquery-public-data.samples.shakespeare`

A few things about the number:

• It is a plan, not a bill. Billed bytes are rounded up to a MiB and have a 10 MiB minimum for each table.
• For clustered tables it is an upper bound. The real run can read less.
• It ignores the query cache. A query whose result is cached still shows its full size, though the real run bills 0.
• For tables protected by row-level security, a dry run always returns 0 bytes. Google does this so a dry run can’t be used to learn about hidden rows.

Maximum bytes billed

You can give a query a ceiling. If BigQuery estimates that the query will bill more than that, the query fails before it starts and costs nothing. In bq it is a flag, and in the API it is maximumBytesBilled on the job configuration:

bq query --use_legacy_sql=false --maximum_bytes_billed=1000000 \
  'SELECT word FROM `bigquery-public-data.samples.shakespeare`'

We ran this against the Shakespeare sample. It failed before running, with:

Query exceeded limit for bytes billed: 1000000. 10485760 or higher required.

The message names the trap. The limit is checked against billed bytes, which include the 10 MiB minimum per table, not against the dry-run estimate. This query processes only 1.3 MB, but any query that reads column data is billed at least 10,485,760 bytes. A limit below that fails every such query. The rounding is easy to reproduce:

WITH jobs AS (
  SELECT 'one row of a tiny table' AS query, 55 AS bytes_processed
  UNION ALL SELECT 'shakespeare, one column', 1332943
  UNION ALL SELECT 'a 23,300,000-byte read', 23300000
)
SELECT
  query,
  bytes_processed,
  GREATEST(DIV(bytes_processed + 1048575, 1048576), 10) * 1048576 AS bytes_billed
FROM jobs
ORDER BY bytes_processed

Set the limit from billed bytes, not from what the estimate shows. For clustered tables the check uses the upper-bound estimate, so a query can fail the limit even though its real billed bytes would have fit.

The limit is per query. It doesn’t stop someone from running a thousand queries that each stay under it.

Require a partition filter

The most common big mistake on a large table is a query with no date filter. A table option rejects those queries:

CREATE TABLE sales.orders (
  order_id INT64,
  order_date DATE,
  amount NUMERIC
)
PARTITION BY order_date
OPTIONS (require_partition_filter = TRUE);

-- on an existing table
ALTER TABLE sales.orders SET OPTIONS (require_partition_filter = TRUE);

These statements need a dataset of your own, so they are not run on this page. We tested the option on a small partitioned table. A query with no filter failed:

Cannot query over table 'mydataset.orders' without a filter over
column(s) 'order_date' that can be used for partition elimination

That includes SELECT COUNT(*). The same message appeared in a dry run, so you find out before you run anything. What counts as a filter is narrower than you might expect:

The option makes sure the filter is on the partition column. It does not make sure the filter is narrow. order_date IS NOT NULL satisfies it and still scans the whole table, so it protects against forgetting, not against a careless filter. Partitioning covers how filters prune partitions.

Custom quotas

A custom quota is a daily limit on query bytes. You set one for the project, one for each user (a service account counts as a user), or both. On-demand pricing has no other way to cap a day’s total spend. You set them on the Quotas & System Limits page of the Cloud console, under the BigQuery API, as “Query usage per day” and “Query usage per day per user”. Values are in TiB. You need the Quota Administrator role (roles/servicemanagement.quotaAdmin). A lower quota takes effect within a few minutes.

The project quota already has a default: 200 TiB a day on on-demand pricing, about $1,250 at the US price. The per-user default is unlimited. For most projects 200 TiB is far above normal use, so it only stops a true runaway; set your own, lower number.

When a quota runs out, queries fail with a usageQuotaExceeded error:

Custom quota exceeded: Your usage exceeded the custom quota for
QueryUsagePerDay, which is set by your administrator.

The per-user version says QueryUsagePerUserPerDay. A project quota is shared by everyone in the project. Google’s example has a project limit of 50 TB a day and a user limit of 10 TB. Once one service account has used 10 TB, it is locked out for the day, while everyone else carries on. When the project total reaches 50 TB, BigQuery stops working for every user in that project.

That last point is a risk of quotas themselves. A single runaway job can use the whole day’s budget and stop your dashboards. A quota is a safety net for a project, not a budget for a team. You can run the query from another project that has access to your datasets and no quota.

Capacity pricing limits slots

On capacity pricing, the cap is slots instead of bytes. A reservation has a maximum size, and BigQuery never scales above it. See Reservations. Project caps can also set an approximate limit on the slots, or on the concurrent queries, that one project takes from a shared reservation.

A slot cap protects the bill. Inside it, a query that needs more compute doesn’t cost more, it runs longer. Use the cap that matches the model you pay by.

Budgets and alerts only warn

Cloud Billing budgets track spend against an amount and email you at thresholds you choose. They don’t stop anything. A runaway query keeps running after the alert. For an actual stop, use a custom quota or maximum bytes billed.

Find what is costing money

INFORMATION_SCHEMA.JOBS records the bytes billed by every job. This query ranks users by their on-demand cost over 30 days at $6.25 per TiB:

SELECT
  user_email,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY user_email
ORDER BY usd DESC
LIMIT 10;

The statement_type != 'SCRIPT' filter matters. A script’s parent job reports the bytes of its child statements, and counting both double-counts them. Change region-us to your location. Your bill will still differ from this total by the free tier, by any discounts, and by ML queries that bill at a different rate. Querylab.io’s query history lists every job in the project with its cost, and the project’s SQL templates include Cost by User and Top Expensive Queries recipes (see DDL operations).

Habits that cost nothing

• Name columns instead of using SELECT *. Use SELECT * EXCEPT (...) when you want almost everything.
• Don’t use LIMIT to save money on an unclustered table. It doesn’t change what is read. On a clustered table it can, because the scan can stop early.
• Look at a table with its Preview tab, in Querylab.io (see Table information) or the BigQuery console, or with bq head or the tabledata.list API. These reads are free and use no quota.
• If several queries share an expensive step, write that step to a table once and query the table.
• Cancel jobs you no longer need, but a cancelled query can still be billed up to its full cost.
• A query that fails is not billed.

Querylab.io can also warn or block a query before it runs when the estimate is over a limit you set. See Cost limits.