governor-audit 0.7.12

Read-only BigQuery cost-audit tool - single-user, gcloud ADC only, no GCS / no GitHub / no dbt installation.

governor-audit

Read-only BigQuery cost-audit tool for single-user production audits. v0.5.4.

Posture: Single-user. gcloud ADC only. No GCS, no GitHub, no service-account JSON, no dbt installation, no shadow validation. The only thing it talks to over the network is BigQuery - and only to query INFORMATION_SCHEMA.JOBS_BY_PROJECT, INFORMATION_SCHEMA.COLUMNS, INFORMATION_SCHEMA.TABLE_STORAGE, and INFORMATION_SCHEMA.SCHEMATA_OPTIONS.

When to use this vs. the other governor packages

• governor-audit (this package): you have read access to a prod BigQuery project. You want a fast cost audit + detection findings without touching the dbt source code, running dbt, or setting up cloud infrastructure.
• governor-cli: you have the dbt project source on your machine and want to run dbt + propose fixes locally.
• governor-web: you operate the platform; you want shared infrastructure (GCS-backed manifests, GitHub PRs, scheduled syncs) for a team.

What you get

• Setup wizard - first run walks you through ADC sign-in (gcloud auth application-default login) → pick BigQuery project + region + lookback (defaults to 24 hours) → first scan kicks off in the background. You land on the configurations page with a spinner card and an auto-refresh, then auto-redirect to the dashboard once the scan completes.
• Permission-gated project picker - the picker auto-filters to projects where the active gcloud principal has both bigquery.jobs.listAll (for INFORMATION_SCHEMA.JOBS_BY_PROJECT) and bigquery.tables.list (for INFORMATION_SCHEMA.TABLE_STORAGE). Probes run in parallel via ProjectsClient.test_iam_permissions (~5–10s for 700-project orgs on first render) and cache per-principal in-process, so org admins aren't drowning in unauditable entries.
• Dashboard - Total / Build / Consumption / Flagged spend KPI cards, top-20 spenders bar chart (click a bar to open its top opportunity), and a Top Cost Drivers with Issues and Suggestions Found table with click-to-sort columns plus per-row issue and suggestion counts.
• Materialization (/recommendations/materialization) - two signal sources rendered as side-by-side sections. Audit-detected opportunities (top): the audit's own heuristic finds recurring expensive ad-hoc SELECT queries (≥ 5 executions, ≥ $5 total cost in the scan window) and proposes turning each into a CREATE MATERIALIZED VIEW. Every candidate passes a sqlglot AST safety check (no non-deterministic functions, no DML, no scripting) so the suggested SQL is something BigQuery will actually accept. BigQuery's recommendations (bottom): Google's own Materialized-View Recommender output surfaced verbatim, with paired performance / efficiency insights.
• Storage Billing (/recommendations/storage) - dataset-level recommendations to switch from LOGICAL to PHYSICAL storage billing. One card per dataset with monthly + annual savings, current-vs-physical cost comparison, compression ratio, per-table breakdown, and a copyable ALTER SCHEMA … SET OPTIONS (storage_billing_model = 'PHYSICAL') snippet. Sorted by monthly saving descending; only datasets where the switch would save more than the materiality threshold (~$10/month) appear.
• Detection engine - every enabled rule from governor_core.opportunities.rules runs against each cached job. Each detection candidate persists as its own Opportunity row, deduped by (rule_type, affected_table):
  • Issues (real cost / performance problems): slot_contention, join_explosion, partition_pruning, shuffle_spill, storage_billing_optimization. Also collect SQL-rewrite suggestions that fire on the same destination table and surface them inline as additional cards.
  • Suggestions (code-quality SQL rewrites): dead_cte, dead_column, dead_window_expression, unused_aggregation_output, redundant_order_by, unused_join, select_star, cross_join_unaggregated, self_join_anti_pattern. Suggestions also surface as standalone opportunities so models with no underlying issue still appear in /opportunities.
• Issues & Suggestions workspace (/opportunities) - listing aggregated to one row per affected_table (one entry per object, not per rule). Surfaces query/job-level rule findings - the lighter analysis tier next to the dataset-level Storage Billing page and the lineage-aware Partition & Cluster page. Filter row: Search, Dataset, Author, Issue (issue rule types only), binary Workload (Build / Consumption), binary Suggestions (With / Without), binary Partition/Cluster (With / Without). The Issue column shows the first rule that fired on the table as a chip plus a +N overflow indicator when more than one rule applies; the full list lives in the chip's tooltip. Improvement-only rows show "-" in the Issue column. Click any row to open the detail page.
• Job history view (/jobs/{project.dataset.table}) - every cached execution of a recurring job rolled up into one page. Cost-trend line chart (one point per UTC day with ≥ 1 execution; days without are skipped) with markers showing when the query body changed mid-window. Query versions section lists each distinct query body chronologically with a unified diff against the previous one. Available improvements surface only the LATEST version's suggestions; older versions get a resolved chip when the latest version no longer triggers a rule, and a new in this version chip when a later version regresses. Drill into a single execution via any chart point. See spec 146.
• Opportunity detail - one consistent layout regardless of whether the row is an issue or a standalone suggestion. Header strip with N issues / N suggestions pills + author + dataset, then a 4-up stat strip (Issues found with rule names listed, Cost (this execution), Bytes Processed, Slot Time), a collapsible Cost trend for this table chart (linking out to /jobs/{table} for the full cohort view), an Issues found section with one bordered card per issue rule paired side-by-side with its Why this fired / Recommended fix description, an Available improvements section with each deterministic SQL rewrite as a tabbed Diff / Original SQL card (rendered from governor_core.solutions.templates), and finally Evidence + BigQuery query lookup panels at the bottom. No severity scores anywhere - the audit dropped them as noise.
• Settings - three rule pages plus account / appearance / LLM:
  • Issues (/admin/settings/issues) - toggle the four query-side issue rules (slot_contention, join_explosion, partition_pruning, shuffle_spill).
  • Suggestions (/admin/settings/suggestions) - toggle the suggestion rules.
  • Storage Billing (/admin/settings/storage-billing) - toggle the storage_billing_optimization rule and tweak its pricing parameters.
  • Plus Account (gcloud principal + ADC probe), Appearance (light / dark / system), AI / LLM (Gemini API key, optional - reviewer code lands in a future release).
  • Audit owns its own enable defaults: every rule is on unless you've explicitly toggled it off. The cloud catalog's per-rule defaults are intentionally ignored here.
• Scan query preview - the configurations page shows the exact INFORMATION_SCHEMA SQL the next scan will run, with resolved timestamps, on a Jobs / Columns / Storage tabbed panel and a copy button.
• Scan history - every scan run logged with timestamp, status (running / succeeded / failed), project, region, lookback, and job count. Failed scans carry a tooltip with the failure reason.
• Background scans - clicking Run scan on the configurations page (or submitting the setup wizard) kicks off the scan in a daemon thread and lands you back on the page immediately with a spinner card and a 5-second meta-refresh until completion. Storage and column syncs are wrapped in outer guards: if INFORMATION_SCHEMA.TABLE_STORAGE is empty or the principal lacks bigquery.tables.list for that step, the scan logs a warning and continues - the dashboard storage panel just renders empty.
• Optimised scan pipeline (spec 147) - the three independent INFORMATION_SCHEMA queries (JOBS_BY_PROJECT, COLUMNS, TABLE_STORAGE + SCHEMATA_OPTIONS) run concurrently in a 3-thread pool so total network wall-time is the slowest of the three rather than their sum. Detection wraps the rule loop in a sqlglot AST cache so identical query bodies (the common case for recurring dbt rebuilds - every job sharing a query_hashes.normalized_literals) parse exactly once across all rules instead of once per (rule, job). Every scan emits a scan timing: jobs_fetch=… columns_fetch=… storage_fetch=… persist=… detection=… total=… INFO line on completion so the operator can see where the time went.
• Recommended physical layout card (spec 148) - a new card on the opportunity detail page suggests partition + cluster keys per table, derived from how the table is actually queried in the cached scan window. The recommender joins three signals: lineage (now populated on the synthetic manifest from BigQueryJob.referenced_tables), column-usage (every cached query's WHERE / JOIN / GROUP-BY parsed via the spec-147 AST cache), and schema/storage. No SQL modification: the card is informational only - it names the recommended column and surfaces the evidence (e.g. "92% of consumer queries filter on event_date"), the user applies the change manually. The recommender abstains under low confidence (< 5 consumer jobs, no dominant filter column, table below 1 GB materiality, or already partitioned) - picking the wrong partition key actively harms performance, so a missing card is preferred to a confident-wrong one.

Quickstart

gcloud auth application-default login
uv tool install governor-audit
governor-audit start
# open http://localhost:8765 - first run goes through the setup wizard

The wizard saves config + auto-runs your first scan; you don't need to call init or scan manually unless you prefer the CLI.

CLI alternatives:

governor-audit init --project prod-warehouse-123 --region us
governor-audit scan                # uses config defaults (24-hour lookback)
governor-audit scan --days 7       # override the lookback for this run
governor-audit status              # connection / cache snapshot
governor-audit reset -y            # wipe cached scan data; keep config
governor-audit reset-config -y     # drop config.json so next start re-enters setup
governor-audit stop                # terminate the managed web server

See the spec quickstarts for the full first-audit walkthrough:

• spec 141 quickstart - original audit MVP
• spec 144 - query-only rule catalog and synthetic manifest
• spec 145 - TABLE_STORAGE ingestion + dashboard storage panel
• spec 146 - /jobs/{table} cohort view with cost-trend chart and per-version detection
• spec 147 - parallel INFORMATION_SCHEMA fetch, shared sqlglot AST cache, phase-by-phase timing

Architecture

• Storage: SQLite at ~/.governor-audit/state.db via governor_core.db.sqlite_compat. The connect-time hook bumps SQLITE_LIMIT_VARIABLE_NUMBER from the default 999 to 100_000 so WHERE col IN (?, ?, …) queries past the cap (busy 24h scans routinely exceed 999 jobs) don't trip OperationalError: too many SQL variables. Persisted shapes:
  • BigQueryJob - raw INFORMATION_SCHEMA rows.
  • TableColumnMetadata - column lists for SELECT * expansion.
  • TableStorageMetric - per-table byte counts + per-dataset billing model - feeds the storage-billing rule.
  • Opportunity - detection findings.
  • ScanRun (audit-only) - every scan attempt with status / timing / project / region / lookback. Drives the Scan history table and the auto-refresh state.
  • AuditOpportunityMetadata (audit-only) - dbt attribution + query hash sidecar tied to Opportunity.
• Scan replaces previous data per (project, region): each new scan wipes the prior scan's BigQueryJob / Opportunity / TableColumnMetadata / TableStorageMetric rows for the same config_id before persisting fresh ones. ScanRun history is preserved (it's the audit log).
• Project switching: (project, region) hashes to a deterministic config_id (governor_audit.scan.sentinels.config_id_for). Every dashboard / opportunities / job-detail read is scoped to the active config_id, so switching projects in the configurations form shows only the new project's data.
• Auth: gcloud Application Default Credentials only - google.auth.default(). No service-account JSON. No browser OAuth. The configurations route additionally probes bigquery.jobs.listAll + bigquery.tables.list per project via Resource Manager's test_iam_permissions and only lists projects where both are granted; results cache per-principal in-process so the probe runs once per session.
• Workload classification: manifest-free heuristic - dbt-originated CTAS / MERGE / INSERT / UPDATE / DELETE → build; non-dbt SELECT → consumption; ambiguous → other. Driven by the /* {"app": "dbt" comment-prefix the dbt-bigquery adapter prepends.
• Synthetic manifest (spec 144): audit reuses every governor_core rule unmodified by building a synthetic dbt-shaped manifest from BigQueryJob rows. Each row becomes a model node keyed by destination table; CTAS / MERGE wrappers are stripped so manifest-driven analyzers see the inner SELECT. The same pattern feeds governor_core.solutions.templates for deterministic before/after SQL diffs.
• Non-fatal storage step: INFORMATION_SCHEMA.TABLE_STORAGE requires bigquery.tables.list (granted by roles/bigquery.metadataViewer or roles/bigquery.dataViewer), which is a separate role from the roles/bigquery.resourceViewer that powers the cost scan. The picker filter normally drops projects missing it, but the orchestrator also wraps sync_table_storage and sync_table_columns in an outer try/except: any failure (empty result, permission denied, region typo, network blip) logs a warning and continues - the rest of the scan still completes and the dashboard storage panel renders empty.
• Config migrations: ~/.governor-audit/config.json carries a schema_version and runs forward-only migrations on every load (currently v1 → v7). Each migration is a no-op when the payload is already at-or-above its target. The current migration set drops the deprecated v1 manifest field, resets stale per-rule overrides, force-enables suggestion rules, and adds the optional billing_project_id field. governor-audit reset-config is the escape hatch when you want a totally fresh config.
• Loopback only: the FastAPI app rejects any request whose Host: header isn't a localhost variant. Not a public service.

IAM for cross-project billing (spec 150)

By default, the BigQuery scan job runs in (and is billed to) the analysed project - the one named by gcp_project_id. Operators who don't want production projects paying for their own metadata queries can route the cost to a separate billing project by setting billing_project_id (via the onboarding wizard's Step 2, the /admin/configurations form, governor-audit init --billing-project, or by editing ~/.governor-audit/config.json directly).

When billing_project_id differs from gcp_project_id, the ADC identity needs both:

1. On the billing project - bigquery.jobs.create (granted by roles/bigquery.jobUser), so the scan job can be created there.
2. On the analysed project - bigquery.resourceViewer (or a custom role granting bigquery.jobs.listAll), so the scan can read region-<X>.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

Example grants (replace the principal and project IDs):

# Billing project - run jobs there
gcloud projects add-iam-policy-binding tooling-acct \
  --member="user:[e-mail]" \
  --role="roles/bigquery.jobUser"

# Analysed project - read its job history
gcloud projects add-iam-policy-binding prod-warehouse-123 \
  --member="user:[e-mail]" \
  --role="roles/bigquery.resourceViewer"

There is no preflight check - governor-audit surfaces BigQuery's own 403 error if either grant is missing. The wizard, the configurations form, and governor-audit init's tail output all repeat the IAM requirements so admins see them at configuration time.

See specs/150-audit-billing-project/quickstart.md for the end-to-end walkthrough.

Versioning

governor-audit ships on its own version track, decoupled from the cloud bundle (governor-core / governor-web / governor-cli / governor-bq). Audit v0.5.x and cloud v0.7.x coexist. See scripts/release-audit.sh for the release flow.

License

MIT.