r/bigquery • u/Jealous-Ice-9733 • 17h ago
BigQuery FinOps Optimizer
7
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 installand 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
- 🔗 GitHub: github.com/mbettan/bq-finops-optimizer
- 🎮 Live Simulator (no GCP needed): Simulator Demo
- 📄 Website: mbettan.github.io/bq-finops-optimizer
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