{# How Governor Audit calculates every number it shows. FAQ-style accordion - each metric is a collapsible
. The first ("Local state and project switching") 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 in Governor Audit comes from: source field, formula, ranking logic, and 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. #}

Foundations - what every number means

{# ── Local state / project switching ────────────────────────────── #}

Local state and project switching

Config file: ~/.governor-audit/config.json stores the active project, region, billing project, scan defaults, AI settings, and the multi-project registry. The top-level gcp_project_id / bigquery_region pair is the project currently selected in the header switcher.

State database: ~/.governor-audit/state.db stores cached jobs, opportunities, rollups, storage metrics, layout recommendations, slot-demand snapshots, and scan history. Rows are scoped by a deterministic config_id derived from (project, region), so multiple project scans can live in the same DB without mixing.

Switching project: changing the header project switch updates the active project in config.json. Dashboard, Cost Analysis, Issues & Suggestions, Partition & Cluster, Slot Capacity, and Storage Billing all read only rows matching that active (project, region).

Re-scanning: a new scan replaces the cached jobs, opportunities, and rollups for that same (project, region). It does not overwrite scans for other registered projects. ScanRun rows stay as history so the Configuration page can show what happened.

{# ── 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 projects: bytes-based pricing as above. Reservation projects (committed slots): the audit detects the billing posture from each job's reservation_id and instead ranks Total Spend and the cost drivers by slot-time - the real cost driver on a reservation - priced at the Standard $0.04/slot-hour rate (spec 162). A "slot-time priced" badge appears on the dashboard when this basis is active, so the headline ranking reflects what your committed capacity actually goes to rather than a bytes figure you never 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.
{# ── 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.

{# ── 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 cached data for the same (project, region). Other registered projects remain in state.db under their own config_id.

If you want long-running job context, run wide-lookback scans (180d). If you want freshness, run narrow scans daily. The ScanRun table keeps scan metadata, but the detailed jobs/opportunities cache reflects the latest scan window for the active project.

Headline rollups - the dashboard's hero numbers

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

Optimisable Spend (dashboard hero)

Formula: the sum of observed total_cost for logical jobs that have at least one issue or suggestion in the active scan window. A job or logical object that fired multiple rules is counted once.

What it represents: the amount of scanned spend attached to something Governor thinks is worth reviewing. It is not a savings projection, because the audit cannot know the exact safe savings of a SQL rewrite, schedule change, reservation change, or materialization decision.

The Flagged Jobs count below it is the same concept without summing cost. The dense cost-driver table and filters live on Cost Analysis; the dashboard keeps only the executive summary plus the top five expensive jobs and top five critical issues.

{# ── Issues vs Suggestions ──────────────────────────────────────── #}

Issues vs Suggestions

Issues are cost or performance problems tied to actual executions in INFORMATION_SCHEMA.JOBS. Most issue rules come from BigQuery's own query_info.performance_insights / query_info.optimization_details; audit-local issue rules are allowed only when the evidence is still execution-derived and directly actionable. Today that's five rules:

  • slot_contention - query spent significant time waiting for slot capacity
  • shuffle_spill - intermediate stage spilled to disk
  • partition_pruning - query scanned a partitioned table without filtering on the partition column
  • join_explosion - join cardinality blew up beyond what the query author likely intended
  • expensive_full_scan - audit-local detection for scalar aggregate reads that scan a large table without a narrowing filter

Suggestions are everything else - standalone opportunities the audit itself reasons about, not signals BigQuery handed us. Three classes today:

  • SQL rewrites - deterministic AST rewrites (SELECT * expansion, dead column / CTE removal, ORDER BY-without-LIMIT trimming, etc.). No LLM; the diff you see is what would actually change.
  • Materialization recommendations - materialization_candidate (audit heuristic) and bq_materialization_recommendation (Google's MV Recommender pass-through). See the Materialization section below.
  • Storage billing - storage_billing_optimization, dataset-level LOGICAL → PHYSICAL switch recommendations.

Both feed the same Opportunity row shape but render as separate columns on /opportunities (Issue / Suggestions). The membership lists live in packages/audit/src/governor_audit/scan/categorization.py.

{# ── Operational severity ──────────────────────────────────────── #}

Operational severity score

Scale: severity is 0-100. Buckets are Low 0-19, Medium 20-49, High 50-79, and Critical 80-100.

What it is now: an operational priority signal, not an estimated-savings score. Audit does not claim it can calculate exact savings for a query rewrite, materialization change, reservation review, or schedule move.

Formula shape:

severity =
  issue-type base score
  + frequency points
  + workload points
  + detector-evidence points
  • Issue type gives the base score. Join explosion and shuffle spill start higher than broad-scan warnings because they usually indicate more direct query-shape problems.
  • Frequency is intentionally large. A build problem that happens 50 times in the window should rank above a one-off ad-hoc query, even when the one-off query was expensive.
  • Workload gives build jobs extra weight because recurring pipeline issues are usually owned, repeatable, and fixable. Consumption issues still appear, but one-off consumption does not dominate the dashboard.
  • Detector evidence uses rule-specific fields: queue ratio / queue seconds for slot contention, multiplication ratio and output rows for join explosion, shuffle spill flags, partition skew, slot-hours, and bytes scanned.

What is excluded: job_cost_usd, expected_savings, and hard-coded savings percentages do not move the severity score. Cost is still displayed and used as a dashboard tie-breaker, but it is observed spend, not guessed savings.

Dashboard priority: the Top 5 Critical Issues To Solve panel first selects recurring build issues, then ranks by observed cost, run count, and operational severity. One-off consumption findings belong in Cost Analysis unless there are no recurring issues to show.

{# ── Origin attribution (spec 156, v0.9.0) ─────────────────────── #}

Origin (BI / orchestrator / CDC attribution)

Every cached BigQuery job is classified at scan time into one of ~21 origin tools by reading the labels BigQuery's INFORMATION_SCHEMA.JOBS_BY_PROJECT already exposes. The result is the origin_tool column on every cached job and feeds:

  • The Spend by origin tile on the dashboard - one bar per tool, sorted by total cost.
  • The Origin column + multi-select filter on /opportunities (sits between Workload and Query Cost).
  • The Origin card on every opportunity-detail and job-detail page, with the per-tool metadata that was extracted (Looker dashboard id, Fivetran connector id, Airflow DAG / task ids, etc.) plus a classifier_signal field naming the exact rule that fired.
  • The Source filter and table column on Cost Analysis.
  • The cost_by_origin MCP tool (per-tool cost / job_count / share-of-total).

No new BigQuery API calls; no new IAM grants needed. Classification is a pure-function lookup against four signal sources (labels, job_id prefix, query-prefix comment, user_email pattern). Code lives in scan/origin.py; the matcher table is data-driven so new tools land as one-line additions.

Tool families detected (v0.9.0):

BI / Analytics

  • looker - any of looker-context-id / -instance-slug / -history-slug labels
  • looker_studio - requestor=looker_studio
  • hex - hex-app-id / hex-project-id labels
  • mode - mode-app / mode-user-id labels (or -- Mode Analytics: query prefix)

Transformation / orchestration

  • dbt - dbt_invocation_id / dbt_orchestrator labels (or /* {"app": "dbt" query prefix)
  • airflow - airflow-dag-id / airflow-task-id labels set by BigQueryOperator 2.x+
  • dataform - dataform_* job_id prefix
  • bq_scheduled_query - requestor=cloud_dts + transfer_type=SCHEDULED_QUERY
  • dataflow - beam_job_* job_id prefix or goog-dataflow-provided-template-name label

CDC / ELT ingestion

  • fivetran - fivetran-connector-id / fivetran-destination-id labels, or fivetran_* job_id prefix
  • airbyte - airbyte-connection-id / -source-name / -destination-name labels (Cloud + OSS share this prefix)
  • stitch - stitch-data@… service-account email
  • hightouch - hightouch-sync-id / -model-id labels
  • census - census-sync-id / -model-id labels, or census@… email
  • rivery - /* Rivery query prefix
  • matillion - -- Matillion / /* Matillion query prefix
  • dlt - dlt_pipeline_name / dlt_source_name labels (dlthub Python ELT)
  • bq_data_transfer - requestor=cloud_dts with a non-scheduled-query transfer_type (e.g. Google Ads, Salesforce native connectors)
  • cdc_generic_load - fallback: any job_type=LOAD that didn't match a specific ELT rule above. Catches Storage Write API, custom Python ingest scripts, Singer taps invoked directly. Better than ad_hoc because LOAD spend is operationally distinct.

Residual

  • sql_workbench - -- DataGrip / -- TablePlus / /* DBeaver query prefixes from desktop IDEs
  • ad_hoc - direct console / API / bq CLI queries with no recognisable label. Not an error condition - on a healthy warehouse 30-50 % of jobs legitimately land here.

Classification precedence (first match wins):

  1. Label key - highest confidence; survives query rewriting and proxies. The classifier checks an ordered list of well-known keys per tool.
  2. Job-ID prefix - set by the issuing service (fivetran_*, dataform_*, beam_job_*).
  3. Query-prefix comment - older versions of some tools embed a marker (/* {"app": "dbt"..., -- Mode Analytics:). Less reliable; can be stripped by query rewriters and SQL proxies.
  4. User-email pattern - last-resort for tools with stable service-account naming (Stitch, Census, Hightouch).
  5. LOAD-type fallback - any job_type = LOAD that didn't match above lands in cdc_generic_load.
  6. Residual - ad_hoc.

Storage shape:

Pre-classified at scan time into two columns on bigquery_jobs: origin_tool (indexed VARCHAR(32)) and origin_metadata (loose JSON; carries the per-tool extracted keys plus classifier_signal). The raw labels array is not persisted - on a 5M-job warehouse that would cost ~1 GB just for label JSON; the pre-classified shape is ~50 bytes/row.

v0.9.0 limitations

  • Tableau, Power BI, Sigma, Metabase are not detected. Their queries land in ad_hoc until we collect real production fixtures.
  • No human-readable name resolution. The metadata shows raw IDs (looker_dashboard_id: abc123, fivetran_connector_id: salesforce_prod). A future patch will add an optional ~/.governor-audit/looker_dashboards.csv / fivetran_connectors.csv lookup file.
  • Cross-tool double-counting. A view queried by both Looker and a Hex notebook contributes to both origins' totals. Deduplication is a deferred follow-up.
  • Synthetic v1 fixtures. The classifier ships with fixtures derived from public vendor docs. If your warehouse has a tool that lands in ad_hoc when it shouldn't, the classifier_signal field on the job-detail page tells you which rule fired (e.g. fallback:none_matched). File an issue with an anonymised bq show --format=prettyjson of one job and we'll patch the matcher table in the next patch release.

Recommendation engines - actionable output

{# ── Slot reservation right-sizing (spec 157, v0.10.0) ──────────── #}

Slot reservation right-sizing

The Slot Capacity page computes your effective slot demand over the scan window and compares it to your current BigQuery billing posture. The output is one of five recommendation kinds with a copy-paste ALTER RESERVATION (or CREATE RESERVATION) SQL snippet.

Demand curve formula

For each cached BigQuery job with start_time / end_time / total_slot_ms: 

effective_slots(job) = job.total_slot_ms / job.duration_ms

slots_at_minute_m = SUM(
  effective_slots(job)
  FOR every job WHERE start_time <= minute_m_end AND end_time > minute_m_start
)

Worked example: a query consuming 60,000 slot-ms in a 60-second (60,000ms) execution averaged 1 slot. Aggregating across concurrent jobs gives the per-minute effective slot count.

From the dense per-minute series we compute avg / p50 / p95 / p99 / peak / idle_fraction. p95 is the headroom anchor in both the reduce and upgrade branches.

Billing-posture detection

We read INFORMATION_SCHEMA.JOBS_BY_PROJECT.reservation_id on each cached job:

  • reservation_id IS NULL → on-demand.
  • A single non-null reservation_id covering >= 80% of the in-window jobs → "primary reservation".
  • Anything else → mixed posture (recommendation defaults to no_change).

When the audit principal also has bigquery.reservations.get on the admin project, we read INFORMATION_SCHEMA.RESERVATIONS.slot_capacity for the exact current size. Without this grant the recommendation card renders a fillable input the user types into; the savings calc is computed client-side from the typed value.

Decision table

Condition Recommendation Target slots
On-demand AND monthly spend > $500 consider_reservation ceil(p95 × 1.2)
Reservation R observed AND p95 < 0.7 × R reduce ceil(p95 × 1.2)
Reservation R observed AND p95 > 0.95 × R upgrade ceil(p95 × 1.3)
Reservation observed AND idle_fraction > 0.7 split ceil(avg × 1.2) (steady-state floor)
None of the above no_change -

Pricing constants

Slot pricing (US/EU multi-region, as of May 2026): Standard $0.04/slot-hour, Enterprise $0.06/slot-hour, Enterprise Plus $0.10/slot-hour. Per-month: multiply by ~730.5. The recommender defaults to Standard since it's the most common starting point; users on Enterprise / Enterprise Plus can multiply the savings figure by 1.5 / 2.5 respectively. Annual commits are ~50% cheaper than monthly - quoted figures here are monthly.

Where this surfaces

  • The Slot Capacity page - demand chart + recommendation card + copy-paste SQL.
  • The slot_reservation_recommendation MCP tool - returns the same numbers (without the per-minute series) so chat clients can answer "Are we right-sized on slots?".

v1 limitations

  • Single region per scan. Multi-region tenants run separate audits per region.
  • Single dominant reservation. When workload genuinely splits between two pools (e.g. bi-pool + etl-pool), v1 picks the most-populated one. Per-pool cards are a follow-up.
  • Auto-scaling reservations (slot_capacity_min / _max) are treated as if they were the static _max. A v2 spec will model the auto-scaler.
  • Edition switching not modelled. Standard → Enterprise → Enterprise Plus requires per-edition feature-vs-cost analysis; out of scope.
  • Idle slot reclaim not modelled. BigQuery's scheduler gives idle capacity from other reservations in the same admin project; the split card may therefore be slightly over-conservative.
  • 180-day INFORMATION_SCHEMA retention caps the longest meaningful scan window. Seasonality outside that window can't be measured.
{# ── 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.
{# ── Materialization recommendations (spec 154) ─────────────────── #}

Materialization recommendations

What this surfaces: for every table the audit has BigQuery activity on, pick the right persistence strategy. Materialization in BigQuery / dbt isn't binary - the same logical model can be stored five different ways with very different cost / freshness trade-offs. This recommender picks one strategy per table and explains the evidence.

Where it appears: as a Suggestion-class Opportunity row on /opportunities (look for the Materialization column), and on the opportunity detail page as a card with a strategy pill. The recommended_strategy field on the row's evidence_snapshot is the discriminator.

Strategies the audit detects today:

  • ephemeral - inline the SQL as a CTE in downstream models. Fires when: the table is a dbt model (identified by /* {"app": "dbt", ...} */ in the build SQL), has no non-dbt consumers, has ≤ 2 distinct downstream dbt models, and individual builds cost < $1 on average. The bet: inlining is cheaper than persisting when the model is tiny and read in few places.
  • incremental - switch to materialized: incremental so rebuilds process only the delta. Fires when: the representative build SQL contains a high-water-mark date filter pattern (WHERE col >= TIMESTAMP/DATE(...), {{ '{' }}{{ '{' }} is_incremental() {{ '}' }}{{ '}' }}, _PARTITIONTIME >= ...) AND total build cost in the scan window is ≥ $5. The bet: the filter already names the delta - turning the full-scan rebuild into an incremental one is mechanical.
  • table - persist the result as a physical table. Fires when: the table is rebuilt ≤ 7 times AND read ≥ 10 times AND total read cost is ≥ $5 in the scan window. The bet: each rebuild fans out into many reads; one persistence saves the per-read source scan.
  • materialized_view - wrap as a CREATE MATERIALIZED VIEW. Fires when: the same ad-hoc SELECT (no destination_table) runs ≥ 5 times for a total of ≥ $5 and an sqlglot AST safety check confirms BigQuery would accept the body. The card shows the generated CREATE MATERIALIZED VIEW SQL with a Copy button. This is the BI-dashboard re-run pattern.

Strategy currently deferred: view - converting an over-rebuilt table to a view when the source churns faster than the rebuild cadence. Needs a per-source change-rate signal the audit doesn't yet measure; tracked as an open follow-up.

How the dispatcher picks: at scan time, every cached job is grouped into a logical object group by destination_table (builds, consumers, dbt attribution, downstream node ids). Each group is fed to the fitters in priority order - ephemeral → incremental → table - and the first non-None match wins. The ad-hoc MV pass runs separately on SELECT-only jobs with no destination, because those don't fit the destination-table shape.

The Materialization column on /opportunities: after both materialization passes finish, a cross-link phase sets Opportunity.materialization_suggested = TRUE on each materialization candidate's OWN row (identity-only - the candidate's affected_table is the destination_table being recommended). That's what the column reads. A row showing Yes means "this row IS a materialization candidate". Click into the row to see the strategy-aware reasoning card.

BigQuery's second opinion: when the scan principal has the recommender IAM grant, the audit also pulls Google's INFORMATION_SCHEMA.RECOMMENDATIONS filtered to google.bigquery.materializedview.Recommender and persists each row as a separate Opportunity with opportunity_type = "bq_materialization_recommendation". They render in the same Materialization section as the audit's own recommendations, distinguished by a "BigQuery Recommender" pill. Without the grant the section just doesn't include them.

Caveats & what this doesn't (yet) measure

  • No source-change-rate signal. Three of the four fired strategies (view, MV, table) depend on how fast the upstream changes. The audit assumes slow-changing sources today; verify before applying when sources update frequently.
  • Ephemeral is risky on hidden consumers. The audit only sees consumers that ran inside the scan window. A BI tool reading the table monthly won't be visible during a 7-day scan; ephemeral would break it. The card surfaces this caveat inline.
  • Incremental fitter is regex-based. It pattern-matches the filter; it doesn't verify the filter expression is monotonic over the merge key. Late-arriving rows that update existing partitions will be missed unless the user picks an appropriate merge strategy. The card calls this out.
  • MV fitter passes an sqlglot AST safety check first. Non-deterministic functions (CURRENT_TIMESTAMP, RAND, GENERATE_UUID, etc.), DML / DDL nodes, and scripting commands are hard-blocked - the candidate is dropped rather than surfaced with a warning. Multi-dataset reads emit a soft caveat but don't block.
  • Thresholds (count, cost, downstream-count, average-build-cost ceiling) live as module-level constants in packages/audit/src/governor_audit/materialization/strategies.py and audit_heuristic.py. Settings UI to tune them is an open follow-up.
  • The dispatcher picks one strategy per logical object group. If two strategies plausibly fit, ephemeral wins over incremental wins over table by priority order. This is intentional - users want one recommendation per model, not a buffet.
{# ── Unused destination tables (spec 155) ────────────────────────── #}

Unused destination tables

What this surfaces: tables that have at least one build in the scan window AND no other query reads from them. The recommendation is to review whether the table is still needed; common causes are decommissioned BI dashboards, dead dbt models, or pre-production tables that never got a consumer wired up.

Where it appears: as a Suggestion-class Opportunity row on /opportunities (look for the Unused column), and on the opportunity detail page as its own card (separate from the Materialization section). The opportunity_type is unused_destination_table.

Detection heuristic:

  • The logical object group has at least one build job in the scan window (statement_type in CREATE_TABLE / CREATE_TABLE_AS_SELECT / CREATE_OR_REPLACE_TABLE / INSERT / MERGE / CREATE_VIEW / CREATE_MATERIALIZED_VIEW with this table as destination).
  • No external consumer: no job in the scan window has this table in its referenced_tables, EXCEPT for self-references. An incremental INSERT ... SELECT FROM <self> reads its own max-key to compute the delta; those self-referencing jobs are filtered out before the consumer check.
  • The destination_table does not match the audit's own shadow-validation pattern __governor_<hex>_shadow$ (spec 110). dbt __dbt_tmp and generic _shadow / _temp / _staging are NOT in the skip list and will appear as candidates; widen the skip list in a follow-up if they're noisy.

No cost floor. Every orphan is flagged regardless of build cost; the recommendation is "review", not "act", so users can decide which orphans matter.

Universal caveat

  • Scan-window blindness. The audit only sees consumers that ran INSIDE the scan window. A BI tool reading the table monthly is invisible to a 7-day scan. If you suspect external consumers exist, widen the scan window before acting. Same hedge as the ephemeral materialization fitter.
  • The audit cannot quantify storage savings yet; that requires pulling INFORMATION_SCHEMA.TABLES and joining against the logical object groups. Deferred to a follow-up spec.
  • Truly idle tables (zero builds AND zero reads in window) are NOT detected here - the audit doesn't know they exist without INFORMATION_SCHEMA.TABLES. Deferred to a follow-up spec.

Surfaces & tools - how to consume the analysis

{# ── Dashboard vs Cost Analysis ─────────────────────────────────── #}

Dashboard vs Cost Analysis

Dashboard: the executive scan summary. It shows total spend, build vs consumption spend, flagged jobs, bytes scanned, slot time, spend by origin/author, Top 5 Expensive Jobs, and Top 5 Critical Issues To Solve. It is intentionally compact so the first screen answers "where should I look first?"

Cost Analysis: the investigation workspace for all cost drivers. It owns the sortable/filterable table, dataset/source/workload/finding filters, and the deeper "what is costing money?" view. One-off consumption queries, clean high-cost jobs, and high-cost ad-hoc reads belong here rather than in the dashboard's critical-issues panel.

Issues & Suggestions: the fix workflow. It groups findings by object, opens directly to the opportunity detail page, and shows current issues, SQL suggestions, materialization cards, cost trend, historical issue context, and technical evidence in one place.

{# ── MCP server (spec 152) ───────────────────────────────────────── #}

MCP server (agent-facing surface)

What this is: a local Model Context Protocol server that exposes the audit's cached scan data as a set of read-only tools an external MCP client (Claude Desktop, IDE plugins, custom agents) can call. The server runs on demand via governor-audit mcp; nothing extra is started when you run governor-audit start for the web UI.

Data source: the same SQLite DB at ~/.governor-audit/state.db the web UI reads. No separate cache, no fan-out queries to BigQuery at tool-call time - tools read the rows scoped to the active (project, region). If that project's scan is empty or stale, MCP tool responses will be too; re-run governor-audit scan from the web UI or CLI first.

Tool surface (27 today): grouped roughly by the same sections the web UI exposes:

  • Jobs & cost - list / filter cached BigQuery jobs by date, project, dataset, user, statement type, error state; pull the full row for a single job_id; aggregate cost by dataset, author, statement type, day.
  • Opportunities - list the same object-grouped Issues & Suggestions rows the web UI shows, including criticality_score, issue_types, suggestion_types, cost, and run count; pull the evidence_snapshot for a single opportunity.
  • Storage billing - per-table byte counts, per-dataset billing model, the LOGICAL → PHYSICAL recommendation list.
  • Layout recommendations - partition + cluster picks from the spec-151 recommender, plus the BQ Recommender second opinion when persisted.
  • Materialization - the per-table strategy picks from the spec-154 dispatcher, plus the orphan rows from spec 155.
  • Scan metadata - which (project, region) was scanned, scan history rows, scan-config introspection.

Read-only by construction: tool handlers receive a SQLAlchemy Session that is never passed into any write path. The session is built via the same governor_audit.db.session.build_engine() helper the web UI uses; nothing in packages/audit/src/governor_audit/mcp/ imports .commit() or any write-side service. This is enforced by a static-import test in tests/unit/mcp/ that grep-fails the build if a write call sneaks in.

Caveats

  • No auth. The server speaks MCP over stdio; trust boundary is "anything that can run a local subprocess as you". Treat it like any CLI tool, not a network service.
  • Tool responses are JSON-serialized SQLAlchemy rows. The schema can change between audit minor versions; pin governor-audit in the MCP client's launcher config if you depend on a specific shape.
  • No streaming. Large result sets (long job lists) are returned in one chunk; agents should pass narrow filters rather than asking for "all jobs".
{# ── Chat / Audit AI (spec 153) ──────────────────────────────────── #}

Audit AI (chat)

What this is: an in-app chat surface at /chat that lets you ask natural-language questions over the audit's cached scan - "which dbt models cost the most this week?", "find tables with no partition that scanned over 100 GB", "summarise the materialization recommendations for the analytics dataset". The backend is the same Gemini SDK the cloud package uses; the data it has access to is the same SQLite DB the web UI and MCP server read.

Configuration: chat is opt-in. You must paste a Gemini API key on Settings → AI / LLM first; without one, the /chat link still renders but shows a setup prompt instead of the conversation surface. The key is persisted to ~/.governor-audit/config.json (mode 0600, atomic write) - same store as the rest of the audit config.

Per-session cost cap: the LLM-cost guard is configurable on the same settings page. The cap is in USD and tracks the running token cost of the conversation; when the threshold is reached, the next send is rejected with an explanatory error rather than silently continuing. The cap resets per browser session (reload the page to start fresh).

Where conversation history lives: browser-local only. Messages are persisted to localStorage; there is no chat-messages table on disk. Closing the tab keeps history; clearing browser storage wipes it; visiting from a different browser starts fresh. This is deliberate - the audit is a single-user tool and chats can contain table names / SQL the user wouldn't want shipped into a separate persistence layer.

What the model can see: the same read-only, active-project audit DB rows the MCP server reads. Tool-call wiring is unified - chat shares the MCP tool implementations under the hood, so what the chat agent can ask is exactly the subset listed in the MCP section above. No tool gives it write access, no tool lets it run new BigQuery jobs.

Caveats

  • Cost lives with the user. Your Gemini API key, your bill - the audit doesn't proxy or pool. The per-session cap is a guardrail, not a limit Google enforces.
  • The model only sees what the audit's read-only tools return. It cannot run ad-hoc BigQuery SQL, can't peek at uncached job_ids, and won't make recommendations based on data outside the scan window.
  • The chat surface is loopback-only, same as the rest of the FastAPI app. Your API key and conversation never leave your machine except via the Gemini API call itself.
  • No conversation export today. If you want to keep a specific transcript, copy it from the page before clearing - browser storage is the single source of truth.

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