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:

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: <one line, the problem>
  Impact:   <bytes or percent, with the assumption>
  Cause:    <one sentence>
  Fix:      <what changes>

FINDING 2: ...

REWRITTEN QUERY
<full SQL>

PROJECTED
  Before: X TB per run, N runs per month
  After:  Y TB per run
  Saved:  Z percent
  Assumes: <the assumption>

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.