Find Your Most Expensive Queries with INFORMATION_SCHEMA.JOBS

Every query, load, copy, and export BigQuery runs is recorded in the INFORMATION_SCHEMA.JOBS views with the fields that matter for cost: bytes billed, slot-milliseconds, cache hits, the user or service account, the SQL text, and the labels on the job. That makes a cost audit a set of SQL queries rather than a billing-export project. Below are the queries we run in the first hour of every engagement, in the order we run them, followed by how to turn the results into a dashboard that keeps the findings from decaying.

Which view to use

View	Scope	Needs
JOBS_BY_USER	Your own jobs in the project	Nothing extra
JOBS_BY_PROJECT (alias JOBS)	All jobs in one project	bigquery.jobs.listAll on the project
JOBS_BY_FOLDER	All projects under a folder	bigquery.jobs.listAll on the folder
JOBS_BY_ORGANIZATION	Everything	bigquery.jobs.listAll at org level

All views are regional: qualify them as `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT (or your region). They retain 180 days of history and are partitioned by creation_time — always filter on it or you will pay to scan the whole retention window to audit your spend.

The cost math for on-demand pricing is total_bytes_billed / POW(1024, 4) * price_per_TiB; for Editions it is total_slot_ms / 1000 / 3600 * price_per_slot_hour. Substitute the rate for your region and edition.

1. Top users by bytes billed

DECLARE price_per_tib FLOAT64 DEFAULT 6.25; -- replace with your on-demand rate

SELECT
  user_email,
  COUNT(*) AS jobs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 2) AS tib_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * price_per_tib, 2) AS est_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
GROUP BY user_email
ORDER BY tib_billed DESC
LIMIT 25;

Service accounts belonging to BI tools and schedulers usually dominate this list. That is expected; the question is whether their queries are efficient, which the next queries answer.

2. The most expensive individual queries

Group by a hash of the normalised SQL so repeated runs of the same statement roll up:

SELECT
  FARM_FINGERPRINT(REGEXP_REPLACE(query, r'\s+', ' ')) AS query_hash,
  ANY_VALUE(SUBSTR(query, 1, 300)) AS sample_sql,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 3), 1) AS gib_total,
  ROUND(AVG(total_bytes_billed) / POW(1024, 3), 1) AS gib_per_run,
  ROUND(SUM(total_slot_ms) / 1000 / 3600, 1) AS slot_hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY' AND state = 'DONE'
GROUP BY query_hash
ORDER BY gib_total DESC
LIMIT 20;

A query that is cheap per run but runs 10,000 times a month is the classic dashboard-refresh problem; a query that is expensive per run and runs daily is the classic missing-partition-filter problem. Both show up here.

3. Slot-hour hotspots by hour of day

For Editions customers, what matters is when slot demand peaks, because that drives the autoscaler:

SELECT
  EXTRACT(HOUR FROM creation_time AT TIME ZONE 'America/Chicago') AS hour_local,
  ROUND(SUM(total_slot_ms) / 1000 / 3600, 1) AS slot_hours,
  COUNT(*) AS jobs
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
  AND job_type = 'QUERY'
GROUP BY hour_local
ORDER BY hour_local;

Pair this with INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT for per-second slot usage when you need to size a reservation baseline precisely.

4. Cache-hit rate

Result-cache hits are free. A low hit rate on a dashboard workload means queries are being written in a way that defeats the cache — CURRENT_TIMESTAMP() in the SQL, non-deterministic functions, or tables that are streamed into continuously:

SELECT
  user_email,
  COUNTIF(cache_hit) AS cached,
  COUNT(*) AS total,
  ROUND(100 * COUNTIF(cache_hit) / COUNT(*), 1) AS cache_hit_pct
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'
GROUP BY user_email
HAVING total > 100
ORDER BY cache_hit_pct ASC;

5. Scheduled-query waste

Scheduled queries are tagged in the job's labels. Find the ones that rebuild the same bytes every run:

SELECT
  (SELECT value FROM UNNEST(labels) WHERE key = 'data_source_id') AS data_source,
  destination_table.dataset_id,
  destination_table.table_id,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 3), 1) AS gib_total,
  ROUND(AVG(total_bytes_billed) / POW(1024, 3), 1) AS gib_per_run
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND EXISTS (SELECT 1 FROM UNNEST(labels) WHERE key = 'data_source_id')
GROUP BY 1, 2, 3
ORDER BY gib_total DESC;

Anything that runs hourly and scans a full table every time is a candidate for an incremental rewrite or a materialised view. Also look for destination_table values nobody reads — cross-reference against INFORMATION_SCHEMA.TABLE_STORAGE and the jobs that reference the table.

6. Queries that should have used a partition filter

referenced_tables lists what each job touched; combining it with the query text finds scans of partitioned tables without a filter on the partition column:

SELECT
  job_id, user_email,
  ROUND(total_bytes_billed / POW(1024, 3), 1) AS gib,
  SUBSTR(query, 1, 200) AS sample_sql
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(referenced_tables) AS t
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND t.table_id = 'events'
  AND NOT REGEXP_CONTAINS(LOWER(query), r'event_ts|_partitiontime|_partitiondate')
ORDER BY gib DESC
LIMIT 20;

The durable fix is require_partition_filter = TRUE on the table, which turns this report into an empty list.

Turning it into a dashboard

Findings decay unless someone looks every week. The setup we leave behind:

1. A scheduled query that appends the previous day's JOBS_BY_ORGANIZATION rows — with the SQL text truncated and sensitive literals stripped — into a finops.jobs_daily table partitioned by creation_time. This also preserves history beyond the 180-day window.
2. A view that joins that table to a small finops.team_map table mapping user_email and project to a team, so cost can be attributed.
3. A Looker Studio report on the view with four pages: spend by team over time, top 20 queries this week, cache-hit rate by workload, and reservation utilisation (from RESERVATION_TIMELINE) for Editions customers.
4. A budget alert at the billing-account level and a custom quota on QueryUsagePerUserPerDay so one person cannot consume a month's budget in an afternoon.

None of this requires anything beyond BigQuery and Looker Studio, and the whole thing can be stood up in a day. If you would rather we do it, it is the first deliverable of our cost optimization service; contact us to get started.