BigQuery Cost Optimization: Find and Fix Expensive Queries

A BigQuery bill can rise while the number of people using a dashboard stays unchanged. A reporting job starts reading a longer history, an incremental transformation becomes a full refresh, or a dashboard runs the same expensive query more often. The first question is not which discount to buy. It is which work changed, and whether that work is necessary.

This guide focuses on query compute costs. Storage, ingestion and other BigQuery features need separate review. For the wider account, start with the Google Cloud FinOps guide.

Establish which meter you are trying to reduce

BigQuery offers on-demand compute, charged by data processed, and capacity compute, charged for slot capacity over time. The distinction changes how savings appear: fewer billed bytes can reduce on-demand charges, while faster queries do not automatically reduce a capacity commitment already purchased. Check the project's actual assignment and the applicable BigQuery pricing model before estimating savings.

Choose a representative review period that includes normal reporting and scheduled batch work. Record the billing model, project, location, workload owner and relevant reporting deadlines. Keep currency, credits and invoice adjustments consistent between comparisons.

A useful baseline includes both cost and output. For a reporting pipeline, track cost per completed refresh, freshness and failure rate. For interactive analytics, track cost per useful query alongside response time. Lower spend caused by missing reports is not an improvement.

Find the jobs responsible for the change

Use job metadata to identify a manageable investigation list. The following read-only query returns the largest successful query jobs by billed bytes from the last seven days. It uses the selected project's US location. Replace the region qualifier and execution location together for workloads elsewhere.

Google documents BigQuery User and BigQuery Resource Viewer as roles for accessing this view. Follow your organization's least-privilege policy. Excluding parent SCRIPT rows avoids double counting their child jobs. Metadata can be incomplete for some protected workloads; this is an investigation aid, not an invoice calculation. See the JOBS view reference.

SELECT
  job_id,
  creation_time,
  user_email,
  ROUND(total_bytes_billed / POW(1024, 4), 3) AS billed_tib,
  ROUND(total_slot_ms / 1000, 1) AS slot_seconds,
  cache_hit
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
  AND (statement_type IS NULL OR statement_type <> 'SCRIPT')
ORDER BY total_bytes_billed DESC
LIMIT 25;

For capacity workloads, investigate slot consumption and reservation utilization rather than treating billed bytes as cash spend. The query deliberately omits query text; inspect sensitive SQL only in an authorized environment. Running metadata queries can itself incur charges.

Review both individual jobs and repetition. A moderately expensive query scheduled every few minutes may matter more than the largest one-off analysis. Ask the owner what consumes the result, how fresh it must be and whether the job still has a customer. Do not remove unfamiliar work just because its purpose is undocumented.

Reduce the data each query needs

Start with projection: select only the columns the result requires. Returning ten rows is different from reading little data. In particular, LIMIT is not a dependable cost control for non-clustered tables. A dry run estimates bytes without executing the query, and maximum bytes billed can reject an on-demand query whose estimate exceeds its limit. These controls are explained in Google's cost-control guidance.

Next, inspect time filters. A partitioned table only helps when the query can prune irrelevant partitions. A qualifying filter on the partition column can restrict the scan; a dynamic expression that depends on another table may not. Google's partition-query documentation describes the differences.

For an illustrative daily revenue report, ask whether the query really needs every event since launch. If the business question concerns the previous day, test a bounded date filter against the agreed reporting timezone. Verify totals against a trusted reference before replacing the scheduled job. Late-arriving events and corrections may require a small reprocessing window, not a full historical scan.

Clustering is useful when frequently filtered columns allow BigQuery to skip storage blocks. Column order and actual filter patterns matter; it is not a substitute for understanding the workload. Pre-execution estimates for clustered tables also have limitations because the blocks read are determined during execution. See clustered table behavior.

Fix repetition before buying more capacity

Compare the schedule with the decision the report supports. An hourly management report may not benefit from a refresh every minute. Conversely, slowing a fraud-monitoring feed to save compute could create a much larger business risk.

Consider an aggregate table when several consumers repeatedly compute the same result. Document its grain, refresh interval, ownership and correction process. Estimate the cost of maintaining it as well as reading it. An extra data product that nobody maintains can become more expensive than the query it replaced.

For transformations, separate new or changed records from historical backfills where the data model permits. Test recovery after a missed run and the treatment of updates to older records. The cheaper design must still produce complete, correct data.

Choose controls that match the workload

Do not choose on-demand versus capacity using one universal monthly data threshold. Compare representative workload behavior, concurrency requirements, applicable regional rates and existing contractual obligations. Use the official compute cost guidance to distinguish quota controls from reservation controls.

Prove the saving in a controlled comparison

Pick one recurring workload with a known owner. Record its baseline, make one change and compare equivalent periods. Keep the input volume, reporting scope and result correctness visible in the review.

Suppose an illustrative query moves from scanning 2 TiB per run to 0.2 TiB while returning the same approved result. That is a 90% reduction in scanned data for that query, not a 90% reduction in the BigQuery invoice. Capacity, storage, other queries and pricing adjustments still matter. State the denominator whenever a percentage is presented.

Close the review with a named owner, the change, its evidence and an alert condition for recurrence. The cloud cost anomaly playbook explains how to route unexpected changes back to the responsible team.

Where to investigate next

If query scans are under control but the wider bill keeps rising, inspect Cloud Run idle costs and Google Cloud network transfer separately. Different services need different evidence.

CloudForge's Google Cloud cost optimization consulting connects that technical investigation with an accountable implementation plan: which workload to change, what could break, who approves it and how the result will be measured.