Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Reduce BigQuery query cost

Snowflake, BigQuery & Databricks · BigQuery

Reduce BigQuery query cost

Hardwarehouses-25
scenariobigquerycostoptimizationbytes-scanned

Question

A team's BigQuery queries scan terabytes daily. How do you bring costs down?

Solution

BigQuery on-demand cost is driven by bytes scanned, so the work is to scan fewer bytes. Start by finding which queries scan the most, and fix those first.

Step 1: measure

SELECT user_email, query, total_bytes_billed / POW(1024, 4) AS tib_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
ORDER BY total_bytes_billed DESC
LIMIT 50;

Usually a handful of queries, often scheduled dashboards or an unfiltered job, account for most of the cost. Group by query text, user and project to see repeated offenders.

Step 2: reduce what each query reads

  • Select only the columns needed. BigQuery is columnar, so SELECT * bills for every column. Selecting five columns out of fifty cuts bytes sharply.
  • Partition by date, and filter on the partition column. Turn on require_partition_filter so nobody can forget it.
  • Cluster on the columns used in filters and joins.
  • LIMIT does not reduce the bytes billed. The engine still reads the data first. To look at sample data, preview the table in the console (free) or use TABLESAMPLE.
  • Query a smaller table. Build daily summary tables or a materialized view, and point dashboards at them, instead of recomputing from raw events every refresh.

Step 3: change how repeated work happens

  • Materialize expensive intermediate results once, rather than recomputing them in every query.
  • Use materialized views for repeated aggregations, and BI Engine to serve dashboards from memory.
  • Rely on the query result cache: identical queries on unchanged data are free for 24 hours.

Step 4: guardrails

Set a maximum bytes billed per query, set project or user level quotas for custom daily limits, and add budget alerts. For new users, an INFORMATION_SCHEMA report each week keeps the habit alive.

Step 5: reconsider the pricing model

If the cost is high but steady, compare it with editions. Capacity pricing turns a per-byte bill into a predictable slot bill, so wasteful queries cost time instead of money. It does not remove the need to scan less.

PreviousNext