{# 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 BigQuery itself flagged in INFORMATION_SCHEMA.JOBS - strictly the rule types whose evidence comes from query_info.performance_insights or query_info.optimization_details. Today that's four 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

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.

{# ── 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.
{# ── 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 by destination_table into a cohort (builds, consumers, dbt attribution, downstream node ids). Each cohort 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 cohort 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 cohort. 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 cohort 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 cohort set. 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.
{# ── 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 - whatever the last scan persisted is what tools return. If the 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 (24 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 issues + suggestions filtered by rule, dataset, workload, materialization-state; 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 audit DB 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.
{# ── 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 Origin line on the dashboard's Top Spenders bar-chart hover tooltip.
  • 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.

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