Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 37 ยท Security and Cost

Controlling Query Cost

Practical ways to cut and cap BigQuery spending: dry runs, maximum bytes billed, column selection, partition filters, caching, materialized views and monitoring.

On-demand BigQuery is wonderfully easy to start and just as easy to overspend on, because a single careless query can scan terabytes. Controlling cost has two parts. Reduce the bytes each query reads, and cap the damage when something goes wrong. This lesson gives you a toolkit for both, and ways to monitor where the money goes.

Everything that needs the real service is shown read-only, and the examples that can run use SQL and Python to model the estimates.

Know the cost before you run

The query validator. In the console, the editor shows "This query will process X when run" as you type. It is an estimate from table metadata and costs nothing.

Dry run. The same estimate is available from the CLI and the API, and does not execute anything:

Terminal
bq query --use_legacy_sql=false --dry_run \
  'SELECT user_id, event_name FROM shop.events WHERE DATE(event_ts) = "2026-03-02"'

In client libraries you set dry_run=True in the job configuration and read total_bytes_processed (see the Python client). Estimates are exact for partition pruning and an upper bound for clustered tables, because block pruning happens at run time.

Cap the cost: maximum bytes billed

Set a hard ceiling per query. If the query would process more than the limit, it fails immediately and charges nothing:

Terminal
bq query --use_legacy_sql=false --maximum_bytes_billed=10000000000 \
  'SELECT * FROM shop.events'

That limit is 10 GB (the number is in bytes). In SQL clients and the API the option is maximumBytesBilled. Put it in every scheduled query and every script that runs unattended.

Project- and user-level custom quotas cap total daily query bytes: "Query usage per day" and "Query usage per day per user" in the Quotas page. When a quota is hit, queries fail until the next day or until you raise it. Set budgets and alerts in Cloud Billing to email or trigger automation at spending thresholds; a budget alert does not stop spending by itself, so pair it with quotas for a hard stop.

Read fewer bytes

Because on-demand pricing follows bytes read, most savings come from reading less:

Read fewer bytes

Select columns Never SELECT * on big tables
Filter partitions Always WHERE on the date
Cluster keys Filter on clustered columns
Sample first TABLESAMPLE for exploring

Cost follows bytes read, not rows.

1 / 2

Concretely:

  • Select only needed columns. Storage is columnar, so every extra column adds its full size to the bill.
  • Filter on the partition column with constants, and on cluster columns, as in partitioned tables.
  • LIMIT is not a cost control. It only trims output. Use it with a partition filter, not instead of one.
  • Preview for free. The table Preview tab and bq head read sample rows without charge. TABLESAMPLE SYSTEM (1 PERCENT) reads about that fraction of a table's blocks while you explore.
  • Avoid repeated joins of huge tables; materialize the joined result if many reports need it.
  • Filter early and aggregate before joining when you can.
  • Use approximate functions such as APPROX_COUNT_DISTINCT when exact precision is not needed, since they use less memory and time.
SQL
SELECT country, COUNT(*) AS events
FROM shop.events TABLESAMPLE SYSTEM (5 PERCENT)
WHERE DATE(event_ts) BETWEEN '2026-03-01' AND '2026-03-07'
GROUP BY country;

Cached results and materialized views

BigQuery caches query results for about 24 hours per user, per query text, as long as the underlying tables have not changed. A repeat run is free and instant. Caching does not apply when the query uses non-deterministic functions such as CURRENT_TIMESTAMP(), or when you write results to a table you named.

A materialized view stores the precomputed result of an aggregation query and keeps it fresh, reading only changed base data. Queries on the base table can be rewritten automatically to use it:

SQL
CREATE MATERIALIZED VIEW shop.daily_revenue AS
SELECT DATE(order_ts) AS day, country, SUM(total) AS revenue, COUNT(*) AS orders
FROM shop.orders
GROUP BY day, country;

For dashboards that hit the same aggregation every minute, this turns repeated terabyte scans into small reads.

Estimating cost from a column list

A cost estimate is just column sizes added up for the partitions touched. The example prices a few versions of the same query with the example rate of 6.25 USD per TiB.

Python Price four versions of one query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Dropping the wide props column and adding a date filter cut the cost by several orders of magnitude, with no change in the answer to the question being asked.

Find the expensive queries

INFORMATION_SCHEMA.JOBS records every query with the bytes billed. Group it by user or label to see who spends the money:

SQL
SELECT user_email,
       COUNT(*) AS queries,
       ROUND(SUM(total_bytes_billed) / POW(1024, 4), 3) AS tib_billed
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 tib_billed DESC;

Add labels to jobs (team, pipeline) and group by them for chargeback. The same grouping logic runs on any table of job records.

SQL Which team spends the most?

Press Run (or Ctrl + Enter) to see the result here.

A cost checklist

  1. Dry run before running big queries.
  2. Set maximum_bytes_billed on automated jobs.
  3. Partition large tables, require a partition filter, and cluster by filter columns.
  4. Replace SELECT * with a column list.
  5. Cache or materialize repeated aggregations.
  6. Set quotas, budgets and alerts, and review INFORMATION_SCHEMA.JOBS weekly.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Does adding LIMIT 10 reduce the bytes billed on an unpartitioned table?

    LIMIT trims output rows; BigQuery still scans the referenced columns, so the bill does not drop.
  2. 2. What does maximum_bytes_billed do?

    It is a per-query ceiling; queries over it error out and are not charged.
  3. 3. Which makes repeated identical queries free for about 24 hours?

    Cached results are reused when the query and the underlying data are unchanged.
  4. 4. Why is SELECT * expensive in BigQuery?

    Each column is stored separately and billed by size, so reading all of them reads all that data.

Summary

  • Estimate with the validator or a dry run; billed bytes follow columns and partitions read.
  • Cap spending with maximum_bytes_billed, custom quotas, budgets and alerts.
  • Select needed columns, filter partitions and clusters, and sample while exploring.
  • Reuse work through the cache, materialized views and summary tables.
  • Review INFORMATION_SCHEMA.JOBS to find the expensive queries and teams.

Next: Loading Data into BigQuery