# Bq Cost Auditor > Audit BigQuery SQL or a whole project for cost, then return a rewritten query and a projected byte reduction. Use when the user asks why a query is slow or expensive, mentions a BigQuery bill or slot contention, asks to optimize or tune SQL, asks about partitioning or clustering choices, or pastes a query and asks what is wrong with it. Also use before shipping any model that will run on a schedule. - Skill: `rk-chavali/bq-cost-auditor` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add rk-chavali/bq-cost-auditor` - Raw SKILL.md: https://api.skillmd.com/api/skills/rk-chavali/bq-cost-auditor/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: rk-chavali (https://skillmd.com/u/rk-chavali) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/rk-chavali/bq-cost-auditor --- # BigQuery cost auditor Read `references/bigquery-cost-heuristics.md` and `references/execution-model.md` before the first audit in a session. ## Run the audit, do not narrate it If the BigQuery MCP server or an authenticated `bq` CLI is available, gather the evidence yourself. A cost audit built on real bytes is a different artifact from one built on reading the SQL. **With the MCP server** (`execute_sql_readonly` only, never `execute_sql`): 1. `get_table_info` on every table the query touches. This gives you the partition column, the clustering columns, and the size. Do not ask the user for a schema you can read. 2. Run the three `INFORMATION_SCHEMA` queries from the heuristics reference through `execute_sql_readonly`. Real bytes, real run counts, real users. 3. Aggregate in SQL. Results are capped at 3,000 rows, so never `SELECT` raw job rows and count them yourself. **With the `bq` CLI**, add the one thing MCP cannot do, a dry run of the rewrite: ```bash bq query --use_legacy_sql=false --dry_run < rewritten.sql ``` That is the only way to get a before-and-after on a query that has never run. Without it, project from the historical bytes of the original and say that is what you did. **With neither**, emit the commands and label every number as unverified. State which of the three you used in your first line. "Read 14 days of job history through the MCP server" and "estimated from the SQL text" are different claims and the user deserves to know which one they are getting. ## When the user pastes a single query Do this in order and report in this order. 1. **State the grain and the driving table.** If you cannot tell what one row means, ask. Everything else depends on it. 2. **Find the partition column.** If the query filters on a partitioned column through a function (`DATE(ts)`, `CAST`, `FORMAT_TIMESTAMP`), that is finding number one, because it can defeat pruning. 3. **Count the columns actually consumed downstream.** Any `SELECT *` that feeds a narrower consumer is waste. 4. **Look for the four expensive shapes**: repeated CTEs, self joins used for dedupe, `LEFT JOIN` plus a not-null filter, and unconstrained wildcard scans. 5. **Rewrite the query in full.** Not a diff, not a snippet. Copy-paste ready. 6. **Quantify.** Give current bytes, projected bytes, percent saved, and the assumption. Prefer a real dry run of the rewrite. Second best is historical bytes for the original plus a stated projection method. Worst and last is an estimate from the SQL text, which you must label as such. ## When the user points you at a project Run the three queries in the heuristics reference: top spenders, repeated queries, large unpartitioned tables. Then rank findings by `bytes saved x runs per month`, not by how bad the SQL looks. A mildly sloppy query that runs every ten minutes beats a horrifying one that runs quarterly. ## Partitioning and clustering decisions Pick the partition column by how people filter, not by what looks like a date. - Event date column when queries ask about when the thing happened. - Ingestion time when queries ask about when we loaded it and the event date is unreliable. - Integer range when the natural filter is an id bucket, not a date. - No partition only when the table is under a few GB and stays that way. Cluster on up to four columns, most selective first, and only on columns that appear in `WHERE`, `JOIN`, or `GROUP BY`. Clustering a column nobody filters on costs write time and saves nothing. Recommend `require_partition_filter = true` on any table over 100 GB and say plainly that it will break existing unfiltered queries, which is the point. ## Output format ``` FINDING 1: Impact: Cause: Fix: FINDING 2: ... REWRITTEN QUERY PROJECTED Before: X TB per run, N runs per month After: Y TB per run Saved: Z percent Assumes: ``` ## Rules - Never call `execute_sql`. A cost audit is a read. If you find yourself wanting to materialize a test table to prove a rewrite is cheaper, emit the DDL and let the user run it. - Never claim a dollar amount without stating the price per TB and the edition. - Never recommend a reservation without asking about the ad hoc query workload, because moving analysts onto the same slots is how pipelines start missing SLAs. - If the honest answer is that the query is already fine, say that in one line and stop. A padded audit trains people to ignore audits.