{# How Governor Audit calculates every number it shows. FAQ-style accordion - each metric is a collapsible
. The first ("Cost (USD)") is open by default; the rest are collapsed so readers can scan headings, then click into the section they care about. Pricing constants are verbatim from packages/core/src/governor_core/opportunities/rules/storage_pricing.py and gcp/clients.py so they stay in lockstep with what's actually computed. When the source code changes, this page should change with it. #} {% extends "layout.html" %} {% block content %}

Methodology

Where every number on this dashboard comes from. Source field, formula, caveats. Click a topic to expand.

{# Shared markup pattern for every section. Using
/ for the accordion (native browser support, keyboard-accessible, no JS). The chevron rotates 180° when open via group-open: utility. #}
{# ── Cost ───────────────────────────────────────────────────────── #}

Cost (USD)

Source field: INFORMATION_SCHEMA.JOBS_BY_PROJECT.total_bytes_billed

Formula: total_bytes_billed / 240 × $6.25 — on-demand pricing, US / EU multi-region, May 2026.

What it represents: what the query would have cost at BigQuery's published on-demand rate.

Caveats

  • On-demand pricing only. If your project uses BigQuery reservations (committed slots), this number is what the query would have cost on on-demand, not what you actually paid.
  • Pricing is hard-coded to $6.25/TiB. Regional variations (Asia, EU) are not applied. The audit doesn't track price changes - if Google updates pricing, this page must be updated.
  • BigQuery rounds total_bytes_billed up to 10 MB per query and 1 KB per table. Tiny queries always look like 10 MB.
  • Cached query results (where cache_hit = TRUE) have total_bytes_billed = 0 and therefore $0 cost - even though the query ran.
{# ── Slot time ──────────────────────────────────────────────────── #}

Slot time (hours / ms)

Source field: INFORMATION_SCHEMA.JOBS_BY_PROJECT.total_slot_ms

Formula: total_slot_ms / 3,600,000 for hours, displayed as-is for ms.

What a slot is: a unit of BigQuery compute capacity. One slot running for one second is one slot-second. Slot time measures compute intensity, not wall-clock time — a query that uses 100 parallel slots for 10 seconds consumes 1,000 slot-seconds even though the user waited only 10 seconds.

What it means for cost

  • On-demand pricing: slot time is informational, not billed. You pay per byte, not per slot-second.
  • BigQuery reservations: slot time IS the cost driver. You commit to N slots; each slot-second is a unit of your committed capacity.
  • The audit displays slot time regardless of your billing model so it doubles as a "performance density" signal.
{# ── Bytes processed ────────────────────────────────────────────── #}

Bytes processed

Source field: INFORMATION_SCHEMA.JOBS_BY_PROJECT.total_bytes_processed

What it represents: the bytes BigQuery scanned from storage to answer the query. Differs from total_bytes_billed because:

  • BigQuery rounds bytes_billed up to 10 MB per query and 1 KB per table; bytes_processed is the raw scan.
  • Cached results have bytes_billed = 0 but bytes_processed reflects the original scan.
  • LIMIT N doesn't reduce bytes_processed - BigQuery still scans the whole partition, then filters.
{# ── Issues vs Suggestions ──────────────────────────────────────── #}

Issues vs Suggestions

Issues are cost or correctness problems - slot contention, shuffle spill, unbounded scans, etc. The detection rule cites a specific signal in the BigQuery job's query_info.performance_insights block or in the query AST.

Suggestions are SQL rewrites that reduce work - SELECT * expansion to named columns, dead column removal, ORDER BY-without-LIMIT trimming, etc. Each one is a deterministic AST rewrite - no LLM, no guesswork. The diff you see is what would actually change.

Both feed the same opportunity row but they're separate counts on the table.

{# ── Workload classification ────────────────────────────────────── #}

Workload (build / consumption / other)

Heuristic classification from each job's statement_type and query body:

  • build - CREATE TABLE AS SELECT, MERGE, INSERT, UPDATE, DELETE. Queries that materialise data. Almost always dbt / scheduled pipeline cost.
  • consumption - read-only SELECT. BI tools, ad-hoc analysts, reverse-ETL exports.
  • other - DDL like CREATE VIEW, scripting, ambiguous statement types.

Used to split the dashboard's Total Spend into the Build / Consumption / Other strip so cost can be attributed to "warehouse build pipeline" vs "user querying" without manual labelling.

{# ── Optimisable spend headline ─────────────────────────────────── #}

Optimisable Spend (dashboard hero)

Formula: the sum of total_cost across every scanned job that hit at least one detection rule, deduplicated by job_id (so a job that fired three rules is counted once, not three times).

What it represents: the dollar value of queries Governor has flagged as having something to fix. It's not a savings projection - fixing every finding doesn't reduce this to zero, because some findings reduce a query's cost by 20%, not 100%.

The Flagged Jobs count below it is the same denominator without summing the cost - useful when one or two huge jobs dominate the dollar figure.

{# ── Scan window / period ───────────────────────────────────────── #}

Scan window

The audit pulls INFORMATION_SCHEMA.JOBS_BY_PROJECT for every job whose creation_time falls in [now − lookback_days, now]. The Period chip in the global header shows that window.

Three boundaries stack:

  • BigQuery itself retains INFORMATION_SCHEMA.JOBS for 180 days. Anything older is gone at the source.
  • Your configured lookback (1 / 7 / 30 / 180 days on the Configuration page) chooses the requested window inside that 180-day cap.
  • Each scan replaces the prior scan's data for the same (project, region). The audit doesn't append-merge into a running history - the cache always reflects exactly one window.

If you want long-running history, run wide-lookback scans (180d). If you want freshness, run narrow scans daily - but accept that yesterday's data is overwritten each morning.

{# ── Partition & Cluster recommendations (spec 151) ─────────────── #}

Partition & Cluster recommendations

What partition + cluster actually do: Partitioning physically splits a BigQuery table into separate slices on the value of a single column — almost always a DATE / TIMESTAMP — so a query filtering on WHERE event_date = '2026-05-12' reads only that one day's slice instead of scanning the whole table; this is the coarse-grained prune. Clustering sorts the rows within each partition by up to four additional columns (e.g. user_id, country) and stores them in small blocks, so a query filtering or joining on a cluster column tells BigQuery which blocks to skip — the fine-grained prune layered on top. Together, WHERE event_date = X AND user_id = Y first jumps to the right partition, then to the right blocks within it, reading only the matching rows.

How the recommender produces a pick: the recommender runs at the end of every scan and works entirely on already-cached data — zero extra BigQuery cost. It walks every job's SQL with sqlglot, attributes each column reference back to its source table via alias resolution, and buckets the references by usage type (equality filter, range filter, equi-join, group-by). For each (table, column) pair it accumulates a cost-weighted score = Σ (job.total_cost × pruning_share), where pruning_share is:

  • filter (equality / IN) → pruning_share 1.0 — full prune
  • range_filter (BETWEEN / >= / <) → 0.7 — partial prune
  • equi_join (JOIN ON) → 0.7 — partial prune via join key
  • group_by only → 0.5 — clustering helps, less than filtering

So a column that filters $5,000 worth of queries beats one that filters 100 cheap queries, regardless of count.

Partition pick (one per table): the top-scoring DATE / TIMESTAMP / DATETIME column whose filter frequency clears 70% of consumer queries — but only when the table is ≥ 100 GB and not already partitioned.

Cluster picks (up to four per table): the next top-4 columns (excluding the partition pick) whose combined filter+join frequency clears 50% of consumer queries, ordered most-selective-first because BigQuery prunes on the leading cluster column first. The table must be ≥ 10 GB and have ≥ 5 distinct consumer queries in the window before any pick is surfaced.

Apply SQL: the page generates the CREATE TABLE … PARTITION BY … CLUSTER BY … plus the copy-and-rename procedure (BigQuery doesn't support in-place partition changes), and a separate rollback script. Cluster-only changes on already-partitioned tables fall back to the lighter ALTER TABLE … SET OPTIONS.

BigQuery's second opinion: if the principal running the scan has roles/recommender.bigqueryPartitionClusterViewer, the recommender also pulls Google's own INFORMATION_SCHEMA.RECOMMENDATIONS + INSIGHTS and renders them side-by-side with an agreement / different-pick indicator. Without the grant, the page renders fine with just our analysis plus a small banner explaining what the role would unlock.

Deep analysis (optional): a per-column opt-in button runs APPROX_COUNT_DISTINCT + APPROX_TOP_COUNT against a candidate so you can judge cardinality + distribution before committing. The BigQuery dry-run cost is shown for explicit acknowledgement before any chargeable query runs.

Caveats & auto-surfaced warnings

  • IAM_IMPACT — fires on every partition change. The copy-and-rename apply procedure needs bigquery.tables.create and bigquery.tables.delete on the dataset, plus enough slot capacity to copy the full table.
  • WRITE_HEAVY — fires when more than 50 DML statements ran against the table in the scan window. Changing the partition or cluster scheme adds ongoing write overhead; verify with the owning team first.
  • ALREADY_PARTITIONED — informational, when the recommendation changes the partition column on an already-partitioned table. Downstream views or scheduled queries filtering on the current column will spike if they're not updated.
  • The recommender abstains entirely when the table has fewer than 5 distinct consumer jobs in the scan window — sparse signal produces false positives that cost more than missing recommendations.
  • The 10 GB / 100 GB thresholds mirror BigQuery's own and live in packages/audit/src/governor_audit/layout/recommender.py as module-level constants.
  • BigQuery doesn't support in-place partition-scheme changes — applying a partition change requires the copy-and-rename pattern surfaced in the Apply panel.
  • Cluster-only changes use ALTER TABLE … SET OPTIONS which is metadata-only; BigQuery re-clusters on next write.

Pricing constants live in packages/core/src/governor_core/opportunities/rules/storage_pricing.py and packages/core/src/governor_core/gcp/clients.py. If Google changes BigQuery pricing, those files are the single source of truth - and this page should be updated to match.

{% endblock %}