r/bigquery • u/Material-Log3282 • 6h ago

Conversational Agents are now GA in GCP Bigquery

Thumbnail
cloud.google.com
4 Upvotes
0 comments

r/bigquery • u/Jealous-Ice-9733 • 18h ago

BigQuery FinOps Optimizer

6 Upvotes

I've been working on a BigQuery FinOps suite that I've open-sourced and wanted to share it with the community (it is totally free)

If you run BigQuery at scale , you've probably dealt with some of these:

  • You're on Editions but have no idea if your baseline is right-sized, or if you're paying autoscaler premiums for nothing
  • Logical vs. physical storage billing — which datasets should switch, and what's the actual delta?
  • Cost attribution is broken because GCP dumps idle reservation costs into the admin project
  • "Cooldown tax" — 60-second billing minimums silently inflating your bill on high-frequency short queries
  • No easy way to see if HBO (History-Based Optimization) is actually saving you anything

What the tool does: It connects to your org-level INFORMATION_SCHEMA views and runs diagnostic queries across your entire GCP Organization. The output is an interactive dashboard with actionable findings — DDL you can copy-paste, capacity simulation matrices, cost attribution breakdowns, etc.

Modules

Category What it does
Storage Optimizer Audits every dataset for logical vs. physical billing — generates ALTER SCHEMA DDL for the ones where switching saves money
Capacity Planner NumPy-based hourly slot simulation over a 730-hour billing month. Outputs a tiered recommendation matrix (Aggressive / Balanced / Performance baselines)
Fluid Scaling Simulator Identifies reservations hit by the 60-second cooldown tax and simulates savings from true per-second billing
Cost Attribution Engine Proportionally distributes idle reservation waste back to user projects — solves the "admin project eats all the cost" problem
HBO Tracker Matches optimized vs. baseline query plans using normalized hashes. Tracks savings and warns about plans nearing 130-day expiry
AI Doctor The only module using GenAI — sends top queries to Gemini via BigQuery's native AI.GENERATE (zero infra: no CREATE MODEL, no connection, no dataset)
Anti-Pattern Linter Static SQL analysis — SELECT *, unclustered LIMITs, DML abuse, redundant MVs
+ 11 more BI Engine, Data Skew, Batch candidates, Governance, Top Spenders, etc.

Key design decisions

  • No AI hype: 17 out of 18 modules are pure SQL + Python analytics. The only GenAI module (AI Doctor) is clearly labeled and optional.
  • No infrastructure: It's a FastAPI app that uses your ADC credentials. No Terraform, no service accounts, no pub/sub. pip install and run.
  • Copy-paste DDL: Storage changes, fluid scaling configs, reservation adjustments — all generate ready-to-execute DDL statements.
  • Org-wide scope: Queries JOBS_BY_ORGANIZATION, TABLE_STORAGE_BY_ORGANIZATION, etc. — not just single-project views.
  • Focus Projects: Optional scoping to specific project IDs when you don't want to analyze the whole org.
  • Security: SQL injection prevention via _safe_ident(), Pydantic validation on every parameter, XSS escaping on all frontend rendering.

Give it a try

Quick start

git clone https://github.com/mbettan/bq-finops-optimizer.git
cd bq-finops-optimizer
pip install -r requirements.txt
AUTH_ENFORCED_UPSTREAM=true uvicorn src.main:app --reload --port 8080
4 comments