bigquery-cost-audit

BigQuery Cost Audit

When to Use

• Reviewing BigQuery query costs, failure patterns, or performance inefficiencies.
• Identifying which jobs, users, or projects are driving the highest spend.
• Preparing optimization recommendations for an engineering or cost-review meeting.
• Auditing governance: scheduled jobs, duplicated logic, or low-value recurring queries.

Goals

• Identify the main cost drivers by job, project, and user.
• Detect repeated waste patterns (full scans, failed retries, duplicated logic).
• Suggest realistic optimizations with estimated impact.
• Translate technical waste into business-language findings.

What to Inspect

Cost hotspots

-- Top 20 most expensive jobs in the past 7 days
SELECT
  job_id, user_email, query,
  total_bytes_processed / POW(1024, 4) AS tb_processed,
  ROUND(total_bytes_processed / POW(1024, 4) * 6.25, 2) AS estimated_cost_usd,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_processed DESC
LIMIT 20;

Repeated failures

SELECT
  error_result.reason, COUNT(*) AS failure_count, user_email,
  ANY_VALUE(query) AS sample_query
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND error_result IS NOT NULL
GROUP BY error_result.reason, user_email
ORDER BY failure_count DESC;

Missing partition pruning

Look for queries that scan full tables despite available partition columns:

• No WHERE filter on the partition column.
• _PARTITIONTIME or _PARTITIONDATE not in the filter.
• LIMIT used without a partition filter (does not reduce scan cost).

Missing clustering

Check high-scan queries that filter on non-clustered columns after partitioning is already in place.

Scheduled jobs with low value

-- Find scheduled queries with high scan volume (via Data Transfer Service run history)
-- Note: scheduled query metadata lives in region-specific transfer_run tables.
-- Substitute your project and region:
SELECT
  config.display_name,
  run.state,
  run.end_time,
  run.error_status
FROM `<project>.<region>.INFORMATION_SCHEMA.SCHEDULED_QUERY_RUNS` AS run
JOIN `<project>.<region>.INFORMATION_SCHEMA.SCHEDULED_QUERIES` AS config
  ON run.scheduled_query_id = config.scheduled_query_id
WHERE run.end_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
ORDER BY config.display_name, run.end_time DESC;
-- Then cross-reference with JOBS to find per-run bytes_processed.