bigquery-cost-audit · diff
git:20260502.7e6497a to git:20260515.ff7c86b
75 added, 25 removed. Audit A to A.
---
name: bigquery-cost-audit
description: Analyze BigQuery usage, identify cost hotspots, repeated failures, and practical optimization opportunities.
---
# BigQuery Cost Audit
- Use this skill when reviewing BigQuery usage, query cost, failures, performance inefficiencies, or governance opportunities.
+ ## 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.
- - Identify the main cost drivers.
- - Detect repeated waste patterns.
- - Suggest realistic optimization opportunities.
- - Translate technical waste into business impact.
+ ## What to Inspect
- ## What to inspect
+ ### Cost hotspots
+ ```sql
+ -- 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;
+ ```
- - high-cost jobs
- - repeated scans
- - queries with poor partition pruning
- - missing clustering opportunities
- - repeated failures
- - low-value scheduled jobs
- - duplicated logic
- - joins or transformations likely causing excessive scan volume
- - jobs that should be cached, narrowed, or rewritten
+ ### Repeated failures
+ ```sql
+ 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;
+ ```
- ## Output format
+ ### 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).
- Return:
+ ### Missing clustering
+ Check high-scan queries that filter on non-clustered columns after partitioning is already in place.
- 1. top cost hotspots
- 2. recurring failure patterns
- 3. practical optimization opportunities
- 4. quick wins vs larger refactors
- 5. engineering summary
- 6. business summary
+ ### Scheduled jobs with low value
+ ```sql
+ -- 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.
+ ```
- ## Rules
+ ## Output Format
+ 1. **Top cost hotspots** — job ID, user, bytes scanned, estimated USD, query snippet.
+ 2. **Recurring failure patterns** — error reason, count, user, sample query.
+ 3. **Optimization opportunities**:
+ - Partition pruning gaps.
+ - Clustering candidates.
+ - Queries eligible for materialization or caching.
+ - Scheduled jobs that should be retired or narrowed.
+ 4. **Quick wins vs larger refactors** — flag which optimizations are a one-line WHERE clause fix vs a schema change.
+ 5. **Engineering summary** — technical root causes and remediation steps.
+ 6. **Business summary** — cost impact in plain language; approximate monthly savings per opportunity.
+
+ ## Rules
- Focus on practical opportunities, not theoretical micro-optimizations.
- Prefer changes that reduce cost without increasing operational fragility.
- Do not run destructive operations.
- - Do not edit code unless explicitly asked.
+ - Do not edit code or queries unless explicitly asked.
+ - Acknowledge uncertainty when cost estimates depend on assumptions about query frequency.