How to Diagnose High BigQuery Costs: A 3-Step Audit

Search for “reduce BigQuery costs” and you’ll get every result sharing the same advice:

1. partition your tables,
2. cluster your tables,
3. add materialized views.

That advice isn’t wrong — it’s just that you can’t fix a problem without first diagnosing it.

Some possible reasons why your costs are high:

• A scheduled query keeps running when it’s no longer needed
• A dashboard with monthly stats keeps refreshing every 5 minutes
• A table is partitioned on a column that no one filters on

The key here is to: diagnose before you fix.

Here’s a three-step audit that does it, using BigQuery’s own job metadata.

How BigQuery Actually Charges

In 2026, Google Cloud’s on-demand pricing charges $6.25 per TiB scanned in US multi-region — for bytes processed, not rows returned (Google Cloud, BigQuery pricing, 2026).

A query that returns ten rows can still cost real money if it scans an unpartitioned 500GB table to find them.

Here’s what that looks like in dollars:

• Scanning 500GB against an unpartitioned table: about $3.13 at the on-demand rate
• The same query against a table partitioned and clustered to scan 5GB instead: about $0.03

$3.13 → $0.03 is a ~104x reduction in cost (about 99% cheaper), because the scan volume drops from 500GB to 5GB — a 100x reduction in bytes touched.

The difference is driven entirely by how much data the query touches, not by what it returns.

If you’re on capacity-based pricing instead — billed per slot-hour rather than per byte scanned — the numbers look like this:

• Standard edition: about $0.04 per slot-hour, no commitment required
• Enterprise edition: $0.06 pay-as-you-go, $0.048 with a 1-year commitment, $0.038 with a 3-year commitment

(all figures: Google Cloud, BigQuery pricing, 2026)

Whichever pricing model you’re on, the same problem applies: nothing about a query’s output tells you what it cost to run. You have to look at what it scanned.

That’s what the audit below does.

Step 1: Find Your Top Cost Drivers

INFORMATION_SCHEMA.JOBS logs every job you’ve run — including bytes billed — for the past 180 days (Google Cloud, INFORMATION_SCHEMA.JOBS docs, 2026). Querying it costs nothing and takes about 30 minutes.

Run this against the last 30 days:

SELECT
    user_email,
    query,
    SUM(total_bytes_billed) / POW(10, 12) AS tb_billed,
    COUNT(*) AS run_count
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND statement_type != 'SCRIPT'
GROUP BY user_email, query
ORDER BY tb_billed DESC
LIMIT 20;

Two details matter here:

• The query is region-qualified (region-us) — INFORMATION_SCHEMA.JOBS views are scoped to a region, and the query must run in a location that matches it.
• statement_type != 'SCRIPT' excludes scripted queries, which would otherwise double-count child jobs under their parent script.

Read the results for two patterns:

• A single expensive query — one job, one large scan — is usually a one-off, easy to fix once you’ve found it.
• A repeated query with a high run count — the same query showing up dozens or hundreds of times — is almost always a scheduled job or a dashboard. It’s usually where the real money is.

Chase the repeated-query pattern first. Fixing a query that runs once a day for a year fixes 365 future scans, not one.

One billing detail worth knowing before you compare numbers: BigQuery applies a 10MB minimum billing per query, even if the actual scan is smaller (Google Cloud, INFORMATION_SCHEMA.JOBS docs, 2026). It rarely matters at scale, but it explains why a trivial query never shows as $0.

Step 2: check partitioning and clustering against real query patterns

Partitioning only saves money if your queries actually filter on the partition column. A table partitioned by load_date does nothing for a query filtering on event_date or customer_id — the query still has to scan every partition to find matching rows.

For each expensive query surfaced in Step 1, check the table’s partition and cluster columns:

SELECT table_name, column_name, is_partitioning_column, clustering_ordinal_position
FROM `your_dataset`.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'your_table';

Compare that against the query’s WHERE clause. If the columns don’t line up, you’ve found a mismatch — not a missing-partition problem, but a wrong-partition problem. Cross-check BigQuery’s own partition and cluster recommendations for the table as a second signal, but don’t stop there — the recommendation engine doesn’t know which queries your team actually runs most.

The most common mismatch isn’t a missing partition — it’s the wrong one. A table partitioned by ingestion date, while every real-world query filters on a business date (order date, event date), gets none of the benefit partitioning is supposed to provide. Repartitioning to match how the table is actually queried, not how it was loaded, is usually the single largest fix available once you’ve found it.

Step 3: find scheduled queries and dashboards nobody owns

Scheduled queries and BI refreshes are the most common source of invisible cost. They run in the background on a timer, and unlike an analyst’s ad-hoc query, nobody’s watching the bill when they fire.

Cross-reference the repeated queries you found in Step 1 against your scheduler:

SELECT
    job_id,
    query,
    SUM(total_bytes_billed) / POW(10, 12) AS tb_billed,
    COUNT(*) AS run_count
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND job_type = 'QUERY'
    AND query LIKE '%scheduled_query%' -- adjust to match your scheduler's naming
GROUP BY job_id, query
ORDER BY tb_billed DESC;

Then check each hit against your scheduler — Cloud Scheduler, dbt Cloud jobs, Airflow DAGs, or your BI tool’s refresh settings — and ask two questions: does anyone still use this, and does it need to run this often? If nobody’s using it, disable it. If it’s a dashboard refreshing every five minutes that gets checked twice a month, drop the frequency or add a WHERE clause that limits how much it scans on each run.

Isn’t it a little strange that the most expensive queries in most projects are the ones nobody’s actively watching? That’s exactly why this step tends to find more than Step 2 does.

How often to re-run this audit

Run it monthly. New leaks show up the same way the old ones did: a new dashboard gets built, a new scheduled job gets added, or a table grows past the point where its original partitioning still fits how it’s queried.

Turn the Step 1 query into its own scheduled job, and a one-time audit becomes a recurring check that costs nothing to run.

Frequently asked questions

How much can this actually save? It depends entirely on how much waste exists before you look. Using verified on-demand pricing, a query scanning 500GB unpartitioned costs about $3.13; the same query against a table partitioned and clustered to scan roughly 5GB costs about $0.03 — a real, calculable difference at $6.25/TiB (Google Cloud, BigQuery pricing, 2026). Industry write-ups commonly cite 50-80% overall bill reductions from partitioning and clustering fixes, though that figure isn’t tied to a single published benchmark and varies by workload.

Does this replace partitioning and clustering advice? No. The audit tells you where to apply it. Partitioning, clustering, and materialized views are still the fix — this just stops you from applying them to tables that were never the problem.

What’s the difference between INFORMATION_SCHEMA.JOBS and Cloud Audit Logs? INFORMATION_SCHEMA.JOBS is free, fast to query, and retains job history for 180 days (Google Cloud, INFORMATION_SCHEMA.JOBS docs, 2026) — enough for this audit. Cloud Audit Logs capture richer detail, like caller IP addresses and full request metadata, but require separate log sink setup. Use audit logs for security or compliance auditing, not routine cost checks.

Can I automate this? Yes. All three queries here are meant to run on a schedule, not be typed by hand every time you want a cost check.

Run this against your own project

The three queries above are the manual version of this audit. We’re building a starter repo that wraps them into a scheduled job with cost attribution by team, table, and query — it isn’t public yet, but if you want to run this audit yourself in the meantime, the queries above are everything you need to start.