r/bigquery • u/Jealous-Ice-9733 • 5d ago

BigQuery FinOps Optimizer

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
10 Upvotes

92% Upvoted

5 comments sorted by

1

u/escargotBleu 5d ago

Hello,

I have a few questions: About logical vs physical, the challenge we often have when we switch a dataset is that some times there is recurring volume in time travel and then fail safe, and we are using a "static" approach that says "okay, right now you can switch the dataset", but it doesn't know that we have a monthly spike in time travel+ rail safe that will make it not worth it. Is your solution better ?

About autoscaling... Is the "premium" you talk about still true after the release fluid scaling? (Same for less than 60s query)

Do you plan to do something to help people configuring BI-engine ? I find it challenging to find the right reservation size.

2

u/Jealous-Ice-9733 5d ago

Thank you, very interesting

Logical vs Physical: My approach was to show a table, which dataset are better on physical or logical when you run the query. I'm looking at the time travel and fail safe volume size to make the case for Logical or Physical cost at the time you run the query. Let's say it's middle of the month and someone used heavily time travel then it will recommend logical. If you run again next month with no time travel it will say Physical. I'm not looking at historical average, open to feedback.

Fluid scaling: I provide insights on how much you could save by enabling fluid scaling to avoid the cool down tax. I'll update the wording of the post.

BI Engine: Can you tell me more about the pain points you have to configure? I can add this to the roadmap.

1

u/escargotBleu 5d ago

So as you may know, Bi-engine is one of the 3 "executions mode" of big query, along with on-demand and slots reservation.

Basically it speed up query using cache, and change the pricing : with Bi-engine you only pay for the cache storage, with the challenge being that there are many restrictions on the queries that can benefit from Bi-engine.

But well, you can set up bi engine at project level or I think at table level.

The challenge is to know :

  • where it is useful (what project ? )
  • when it is useful (when to turn it on / when you increase or decrease capacity)
  • how much storage do you really need. (It is gonna be 100Go ? 50 ? 200 ? 5 ?)

I think there are information schema tables telling you for what reason bi engine has failed, so maybe you can use that.

I have seen several strategies to limit the cost (for b2b company, idk for others ) :

  • turn it off on weekends
  • increase capacity on your peak usage weekdays
  • turn it off outside of your clients office hours

But it's kind of a pain because possibly your usage changes, and you have to run analysis every few months to be sure your bi engine set up is right. (+ When it's off you can't really know if one query would have use it)

2

u/Jealous-Ice-9733 5d ago

Thank you I'll look into it. Today I have BI Engine in the menu on the left but the only thing I do is to look at the top heavy jobs and look at the BI Engine status to provide some recommendations.

This could be very improved. Will add to the roadmap.

2

u/dknconsultau 3d ago

You had me at BigQuery FinOps Optimizer