{# How Governor Audit calculates every number it shows. Single-page explainer. Each section names the BigQuery source field, the formula, and the assumptions / limitations. 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. Read this once and you'll know exactly what each metric means — and what it doesn't.

{# ── 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.
{# ── Storage billing recommendation ─────────────────────────────── #}

Storage Optimization (logical → physical)

Source fields: INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE — specifically active_logical_bytes, long_term_logical_bytes, active_physical_bytes, long_term_physical_bytes per dataset.

BigQuery offers two storage billing models per dataset (you can't mix):

Logical (default)

Active: $0.02/GB/month

Long-term: $0.01/GB/month

Charges by uncompressed bytes. Cheaper for tables that don't compress well (heavily skewed JSON, free-text strings).

Physical

Active: $0.04/GB/month

Long-term: $0.02/GB/month

Charges by compressed bytes on disk. Cheaper when data compresses well (numeric columns, repeated values, partitioned tables).

The audit's calculation per dataset:

current_cost_monthly  = (active_logical / 2^30)  × $0.02
                      + (long_term_logical / 2^30) × $0.01

proposed_cost_monthly = (active_physical / 2^30) × $0.04
                      + (long_term_physical / 2^30) × $0.02

monthly_savings   = current_cost_monthly − proposed_cost_monthly
compression_ratio = logical_bytes / physical_bytes

Recommendation triggers when:

  • proposed_cost_monthly < current_cost_monthly (the move actually saves money)
  • monthly_savings ≥ $1 (sub-dollar savings are hidden as rounding noise on the dashboard)
  • Cards with savings ≥ $25/mo or ≥ 25% are highlighted with an emerald background as "significant"

Caveats

  • Prices are US multi-region. Regional pricing (EU, asia-*) is not applied — your actual savings may differ.
  • Time-travel and fail-safe physical bytes are charged at the active rate; active_physical_bytes already includes them, so the formula isn't double-counting.
  • Switching a dataset to physical is a one-way decision for the dataset's lifetime — you can't flip back. Read Google's docs before applying.
  • Reference: cloud.google.com/bigquery/pricing#storage
{# ── 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.

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 %}