FinOps Optimizer for BigQuery is an open-source tool that uses Gemini AI to analyze BigQuery queries,
detect SQL anti-patterns, and cut cloud costs — an enterprise-grade diagnostic & simulation
suite that dissects slot consumption, hunts wasteful patterns, and right-sizes your Editions capacity.
Deployed entirely inside your own laptop.
The full simulator is a desktop experience — open it on a larger screen for the complete console.
600+Tests Passing
25+Diagnostic Modules
org-wide · region-us · live
Monthly Waste Found$48,290 −31.4% spend
Cooldown Tax$6,112 Fluid Scaling ready
Slot Utilization · 24hp95 baseline · 100 slots
AI DoctorAI Doctor: Multi-Strategy ROI Engine prioritized 5 discovery modes across 7–90d org lookbacks
957 datasetsaudited in 42s
Gemini Insight7 anti-patterns detected
BigQuery FastAPI Python Gemini NumPy Cloud Run INFORMATION_SCHEMA BigQuery FastAPI Python Gemini NumPy Cloud Run INFORMATION_SCHEMA
Interactive Console
Behold the Scale of Savings
A simulated audit across 957 datasets and the top 500 queries of an enterprise
organization. Switch engines below.
Swipe for all 7 modules
Physical vs. logical storage billing — with the exact ALTER SCHEMA DDL to capture every
dollar.
—
Project
Dataset
Logical Cost
Physical Cost
Rec
Savings / mo
Job-level pricing arbitrage: which workloads belong on Editions slots and which belong
On-Demand.
Job ID
User
On-Demand
Editions
Profile
Delta
High-resolution slot utilization to find the perfect baseline and expose autoscale
spillover.
Recommended p95 baseline · 100 slots
Baseline coverageAutoscale spillover
Top offending query patterns causing inefficient autoscaler activations and the 60-second cooldown
tax.
Query Pattern
Project
Frequency
Avg Slot-hrs
Avg Dur (s)
Avg Bytes
Rec
Who is actually spending? Attribute real multi-engine spend across Reservations & On-Demand, track Wasted Spend from failed queries, and identify immediate savings.
Principal
Billing Mode
Queries
Data Billed
Slot Hours
Actual Spend
Waste
Potential Savings
Share
Vectorized Edition Matrix simulation — the optimal baseline vs. autoscale trade-off across
PAYG, 1-year and 3-year commitments.
In Buckets
Below Baseline
Autoscale
AS Cost
Baseline Cost
Total Monthly
Bucket
Min
Slots
Util %
Slot-hrs
Slot-mo
EE PAYG
PAYG
1 Yr
3 Yr
PAYG
1 Yr
3 Yr
Audited Spend$14,720last 30 days
Severity3 HIGH2 MED1 LOW6 queries diagnosed
Schema Coverage87%13/15 DDLs retrieved
Data Scanned2.4 TiBacross 6 queries
Job ID
User
Slot-ms
Severity
Original Cost
Original Query
Schema
Gemini Advice
Optimized SQL
job_etl_full_sc...
airflow-etl@company.com
48,720,000
HIGH
3.0 TiB~$296,868 / yr×14/day
SELECT *
FROM `prod-analytics.warehouse.events`
WHERE event_type = 'purchase'
ORDER BY created_at DESC
LIMIT 1000
2/2 DDLs ✓
🔴 Critical: Full Table Scan Without Partition Filter
Problem: This query scans the entire events table (3.1 TiB) without a partition filter on _PARTITIONDATE. Since this job runs 14 times per day, the annualized cost is ~$297K.
Anti-patterns detected:
SELECT * pulling all 47 columns when only 5 are used downstream
No partition pruning on a date-partitioned table
No clustering key alignment (event_type is a clustering column but appears only in WHERE)
Recommended fix:
Add WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) to prune partitions
SELECT
event_id,
user_id,
event_type,
revenue_usd,
created_at
FROM `prod-analytics.warehouse.events`
WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
AND event_type = 'purchase'
ORDER BY created_at DESC
LIMIT 1000
SELECT
d.dim_date,
p.product_name,
c.customer_segment,
SUM(f.revenue) AS total_revenue
FROM `prod-analytics.warehouse.fact_sales` f
CROSS JOIN `prod-analytics.warehouse.dim_dates` d
JOIN `prod-analytics.warehouse.dim_products` p ON f.product_id = p.product_id
JOIN `prod-analytics.warehouse.dim_customers` c ON f.customer_id = c.customer_id
GROUP BY 1, 2, 3
Problem: The CROSS JOIN with dim_dates creates a Cartesian product — every sales row is multiplied by every calendar date. With 1.5 TiB scanned across Editions (billed = $0), this consumes massive slot capacity.
Anti-patterns detected:
CROSS JOIN without a matching key (should use sale_date = dim_date)
No date filter on the fact table — scans entire history
Revenue is aggregated across an inflated row set, producing incorrect totals
Recommended fix:
Replace CROSS JOIN with a date-keyed JOIN or simply use the sale_date column directly
Add a WHERE clause for partition pruning
Estimated 99% slot reduction by eliminating the Cartesian explosion
SELECT
f.sale_date AS dim_date,
p.product_name,
c.customer_segment,
SUM(f.revenue) AS total_revenue
FROM `prod-analytics.warehouse.fact_sales` f
JOIN `prod-analytics.warehouse.dim_products` p ON f.product_id = p.product_id
JOIN `prod-analytics.warehouse.dim_customers` c ON f.customer_id = c.customer_id
WHERE f.sale_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2, 3
job_micro_cast...
looker-svc@company.com
1,250,000
HIGH
200 GiB~$11,726 / yr×8,760/yr
SELECT COUNT(*)
FROM `prod-analytics.logs.user_events`
WHERE CAST(event_timestamp AS STRING) LIKE '2026-07-28%'
1/1 DDLs ✓
🔴 High Frequency: 8,760 Executions with CAST Anti-Pattern
Problem: This query runs every hour (8,760×/year). The CAST(timestamp AS STRING) defeats partition pruning and forces a full column scan on every invocation.
Anti-patterns detected:
CAST(event_timestamp AS STRING) prevents the query optimizer from using the partition index
LIKE '2026-07-28%' is a string comparison when a direct timestamp range would be prunable
Recommended fix:
Replace CAST/LIKE with a native TIMESTAMP range comparison
Enables partition pruning: ~200 GiB → ~5 GiB per run
Estimated annual savings: ~$11K from scan reduction alone
SELECT COUNT(*)
FROM `prod-analytics.logs.user_events`
WHERE event_timestamp >= TIMESTAMP('2026-07-28')
AND event_timestamp < TIMESTAMP('2026-07-29')
job_ml_feature...
data-science@company.com
34,500,000
MEDIUM
512 GiB~$1,252 / yr
SELECT DISTINCT
u.user_id,
u.signup_date,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(o.amount) AS lifetime_value,
AVG(s.session_duration) AS avg_session,
MAX(e.last_login) AS recency
FROM `prod-analytics.core.users` u
LEFT JOIN `prod-analytics.core.orders` o ON u.user_id = o.user_id
LEFT JOIN `prod-analytics.core.sessions` s ON u.user_id = s.user_id
LEFT JOIN `prod-analytics.core.events` e ON u.user_id = e.user_id
GROUP BY 1, 2
3/4 DDLs ⚠
🟡 Memory Spill: Multi-Table Fan-Out Causing Shuffle to Disk
Problem: Four LEFT JOINs on the same key without pre-aggregation causes a fan-out explosion. BigQuery spilled 12.4 GiB to disk across 3 stages.
Anti-patterns detected:
SELECT DISTINCT combined with GROUP BY — redundant and expensive
Multi-way join without subquery pre-aggregation creates an O(n⁴) intermediate result
1 of 4 table schemas could not be retrieved (cross-project permission)
Recommended fix:
Pre-aggregate each dimension table in subqueries before joining
Remove the outer DISTINCT (the GROUP BY already deduplicates)
Estimated 80% memory reduction — eliminates disk spill entirely
SELECT
u.user_id,
u.signup_date,
COALESCE(o.order_count, 0) AS order_count,
COALESCE(o.lifetime_value, 0) AS lifetime_value,
COALESCE(s.avg_session, 0) AS avg_session,
e.recency AS recency
FROM `prod-analytics.core.users` u
LEFT JOIN (
SELECT user_id, COUNT(DISTINCT order_id) AS order_count, SUM(amount) AS lifetime_value
FROM `prod-analytics.core.orders`
GROUP BY 1
) o ON u.user_id = o.user_id
LEFT JOIN (
SELECT user_id, AVG(session_duration) AS avg_session
FROM `prod-analytics.core.sessions`
GROUP BY 1
) s ON u.user_id = s.user_id
LEFT JOIN (
SELECT user_id, MAX(last_login) AS recency
FROM `prod-analytics.core.events`
GROUP BY 1
) e ON u.user_id = e.user_id
SELECT
region,
product_category,
DATE_TRUNC(order_date, MONTH) AS month,
SUM(quantity * unit_price) AS revenue,
COUNT(DISTINCT customer_id) AS unique_customers
FROM `prod-analytics.sales.orders`
JOIN `prod-analytics.sales.products` USING (product_id)
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2, 3
Problem: This exact aggregation pattern (region × category × month) runs 4 times daily with identical GROUP BY output. Each run scans 307 GiB.
Anti-patterns detected:
Aggregation query with a static WHERE clause scanning 2+ years of history every time
No incremental processing — re-computes the full result on every run
Recommended fix:
Create a BigQuery Materialized View with enable_refresh = true and refresh_interval_minutes = 60
The MV would serve subsequent reads from its pre-aggregated cache at zero scan cost
Estimated 90% cost reduction (~$2K/year savings)
No rewrite — Materialized View recommended (see advice)
job_dashboard_...
analyst@company.com
2,100,000
LOW
10 GiB~$24 / yr
SELECT
COUNT(DISTINCT user_id) AS unique_users,
COUNT(*) AS total_events,
COUNTIF(event_type = 'conversion') AS conversions
FROM `prod-analytics.warehouse.events`
WHERE _PARTITIONDATE = CURRENT_DATE()
1/1 DDLs ✓
🟢 Minor: APPROX_COUNT_DISTINCT Opportunity
Problem:COUNT(DISTINCT user_id) on a high-cardinality column uses exact HyperLogLog computation. For a dashboard KPI, approximate counts are typically acceptable.
Recommendation:
Replace COUNT(DISTINCT user_id) with APPROX_COUNT_DISTINCT(user_id)
⚠️ Trade-off: ~1% accuracy reduction in exchange for ~50% memory savings
This is already a well-partitioned query with low total cost ($24/year) — optimization is optional
SELECT
APPROX_COUNT_DISTINCT(user_id) AS unique_users,
COUNT(*) AS total_events,
COUNTIF(event_type = 'conversion') AS conversions
FROM `prod-analytics.warehouse.events`
WHERE _PARTITIONDATE = CURRENT_DATE()
~1% accuracy trade-off
No queries match this filter.
Capabilities
Zero-Friction BigQuery Cost Optimization
Run the tool where your data lives. No third-party SaaS, no data exfiltration. One mission: eliminate waste.
✦ Flagship Capability
AI Doctor & SQL Anti-Pattern Diagnosis
Aggregates query executions across JOBS_BY_ORGANIZATION over 7–90 day lookback windows. Evaluates org-wide workloads using 5 Discovery Priority strategy modes, identifies CROSS JOIN explosions, wide-table full scans, unpruned partitions, and generates rewritten optimized SQL with estimated savings.
Powered by Gemini 3.6 Flash · Agent Platform native
5 Discovery Priority Strategies
⚖️ Balanced ROI Score💰 Cumulative Cost🔄 High Frequency💾 Memory RAM Spill⏱️ Total Slot Time
Vectorized NumPy engine modeling slot consumption across a full 730-hour billing month. Pinpoints optimal baseline recommendations (p80 Aggressive, p95 Balanced, Max Performance) across PAYG, 1-Year, and 3-Year commitments while exposing Fluid Scaling 60-second cooldown taxes.
Vectorized 730h Month ModelFluid Scaling Cooldown TaxBaseline vs. Autoscale Trade-OffOn-Demand vs. Editions Arbitrage
Storage Hygiene & Arbitrage
Detect datasets cheaper on physical storage with automated ALTER SCHEMA DDL. Audits tables where Time Travel exceeds live bytes and resolves per-dataset TTLs.
Physical vs. Logical ArbitrageTime Travel TTL Auditing (48h min)Storage Write API Candidate Tracker
✦ v1.4.3 Feature
Executive Assessment Report
Single-click automated sequential sweep across all diagnostic modules, generating a standalone executive HTML report with synthesized KPI scorecards, workload ROI rankings, prioritized roadmap, and print/PDF optimization.
Comprehensive Analysis SweepExecutive KPI ScorecardPrint & PDF Optimized
Enhanced
Hybrid Cost Attribution
Attribute blended Editions spend to individual projects with Lender Pays vs. Borrower Pays idle-slot models, Interactive vs. Batch priority, and waste tracking.
Lender vs. Borrower Pays ModelsInteractive vs. Batch PriorityTrue Reservation Billing Mix
Enhanced
From Findings to Action
Move from diagnosis to action instantly. Universal CSV export preserving active search/filters, 1-click BigQuery Console deep-links, and dynamic max_bytes_billed budget caps.
Universal CSV on Every TableConsole Deep-Links (Job/Dataset/Table)Dynamic Safety Budget Caps
HBO — Optimization Badges
Identifies exact engine optimizations applied to each query (Semi-Join Reduction, Join Commutation, Vectorization, Pushdown) via fault-isolated per-project fan-out.
Third-party FinOps SaaS vendors demand broad cross-account IAM roles, exposing your most
sensitive billing metadata. FinOps Optimizer is deployed entirely inside your local environment
or private cloud infrastructure. Your data never leaves your perimeter.
Zero Data Exfiltration
Deployed entirely inside your local environment or Cloud Run. No hidden telemetry, no phoning home, no third-party storage.
Keep Your IAM Keys
Never grant cross-project Service Account access to external vendors. You own the runtime.
Open Source Clarity
Inspect every line. Apache 2.0 licensed — you know exactly what executes against your warehouse.
600+ Security Tests
Rigorously tested, sanitized and hardened against credential leaks and SQL injection.
The Process
From BigQuery Audit to Execution
A streamlined loop designed for FinOps practitioners and Cloud administrators.
01
Connect
Authenticate via ADC or Service Account. Target your Organization Project ID and region.
02
Scan & Analyze
Pull metadata and compute costs against your negotiated regional pricing — on demand, never auto-polled.
03
Apply & Save
Review recommendations and generated DDL. Apply directly from the UI and start saving immediately.
04
Govern & Guard
Enforce billing caps, run migration guardrails, and keep the Query Doctor on continuous patrol.
Transparent Economics
Zero SaaS Tax. Model Your GCP Bill.
100% open source with zero license fees. Runs entirely within your Google Cloud perimeter. Model your exact BigQuery metadata scans, Cloud Run compute, and Agent Platform costs below.
Total Estimated Cost
$18.31
Estimated spend per month (30 runs)
BigQueryOn-Demand
$18.31
$6.25/TiB · ~$0.61 / run (30x/mo)
On BigQuery Editions? $0.00 incremental — see below
Cloud RunLocal ($0)
$0.00
Self-hosted / $0 compute
Agent PlatformOptional ($0)
$0.00
Deterministic Heuristics (0 AI)
Simulation Parameters
Organization Scale ProfileMedium
Est. Datasets
~250 datasets
Tables & Partitions
~5,000 (500k part.)
Daily Query Volume
~50k / day
Metadata Scanned
100 GiB / run
Cost Per Run (List)
~$0.61 / run
Projects
25 projects
Execution ModeRun Locally
Audit FrequencyDaily (30x)
Agent PlatformOff (0 - Heuristic Only)
Investigations
0 / sweep (Off)
Context Budget
0 tokens
Agent Cost / Sweep
$0.00 / sweep
Spend Proportion
BigQuery (100%)Cloud Run (0%)Agent Platform (0%)
Itemized Expense Breakdown
Itemized GCP Resource Costs per Run and Estimated Monthly Spend
FinOps Recommendation: High-frequency scans at this scale query significant metadata volume (~2.9 TiB/month). If your organization utilizes BigQuery Editions with Slot Reservations, queries consume existing idle reservation capacity with $0 on-demand fees.
BigQuery Editions & Slot Reservations
If your organization operates under BigQuery Editions (Standard, Enterprise, Enterprise Plus) with Slot Reservations, diagnostic metadata sweeps consume idle slots from your existing reservations with $0.00 incremental on-demand query byte charges.
Zero User Table Scans: Diagnostic sweeps only query INFORMATION_SCHEMA views. User datasets and table partitions are never scanned.
Pricing Baseline: Estimates modeled on US Region On-Demand list prices ($6.25/TiB). No free-tier deductions included. Rounded for display; monthly totals computed on unrounded run metrics.
Product Roadmap
Live from GitHub
Public Product Roadmap
Transparent, community-driven development for FinOps Optimizer. Track upcoming capabilities, active engineering, and recent releases.
bashgcloud run deploy bq-finops-optimizer --image gcr.io/$PROJECT/bq-finops
FAQ
Frequently Asked Questions
Everything you need to know about reducing BigQuery costs with AI-powered query optimization.
How does the AI Doctor optimize BigQuery queries?
The AI Doctor pulls real queries from INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION across your entire Google Cloud organization. It evaluates each query using five Discovery Priority strategy modes — Balanced ROI Score, Cumulative Cost, High Frequency, Memory RAM Spill, and Total Slot Time — then classifies anti-pattern severity with Gemini AI and generates rewritten optimized SQL with estimated cost savings.
Is FinOps Optimizer free?
Yes, it is fully open source under the Apache 2.0 license. There are zero SaaS fees — you only pay for your own BigQuery compute and Agent Platform usage. The tool runs entirely on your local machine or within your own Cloud Run environment.
Is FinOps Optimizer a Google Cloud product?
No. FinOps Optimizer is an independent, personal side project. It is not developed, maintained, supported, or endorsed by Google. BigQuery and Google Cloud are trademarks of Google LLC. This tool comes with no warranty, no SLA, and no guarantee of correctness or completeness. It is provided as-is under the Apache 2.0 license — use it at your own risk.
What BigQuery anti-patterns does it detect?
The tool detects common BigQuery anti-patterns including CROSS JOIN explosions, SELECT * on wide tables, CAST defeating partition pruning, missing clustering keys, unnecessary ORDER BY in subqueries, and redundant repeated queries. Gemini classifies each finding by severity (High, Medium, Low) and provides a rewritten optimized query with detailed reasoning.
Can I export BigQuery cost findings to CSV?
Yes. Every results table exports to CSV, and the export contains the full filtered result set — not just the rows visible on the current page. Job IDs, datasets and tables also deep-link straight into the BigQuery Console, so a finding goes from spreadsheet to console in one click.
How do I reduce BigQuery time travel storage costs?
The Storage Hygiene Auditor finds tables where time-travel storage exceeds live storage and shows each dataset's configured default_time_travel_days, so you can shorten the window only where it actually pays. It emits the matching DDL — ALTER SCHEMA `ds` SET OPTIONS(max_time_travel_hours = 48). BigQuery allows a minimum of 48 hours and a maximum of 168 hours (7 days).
How do I find pipelines that should use the BigQuery Storage Write API?
The DML Abuse Tracker aggregates high-frequency INSERT activity by destination table, reporting active days and average inserts per day. Tables sustaining hundreds of small INSERT statements per day are exactly the pipelines that should migrate to the BigQuery Storage Write API.
Does it work with BigQuery Editions?
Yes. The Editions Capacity Simulator models Standard, Enterprise, and Enterprise Plus editions with per-second billing, autoscaler simulation, and Fluid Scaling cooldown tax analysis. It recommends the optimal edition and slot capacity tier (p80, p95, max) for your workload — and calculates the exact On-Demand Equivalent Cost for each BigQuery Editions reservation.
Are you looking for beta testers and co-design partners?
Absolutely! We want to work with real users who are willing to test FinOps Optimizer in their own environments and share honest feedback. Whether you're a FinOps practitioner, a Cloud administrator, or a data engineering lead — we'd love to hear from you. Reach out directly at bettan.michael@gmail.com.
How can I contribute or help?
There are several ways to get involved:
Report bugs — open an issue on GitHub Issues. Please keep reports generic and never include PII, credentials, or sensitive information.
Request features — open a GitHub issue describing the use case you'd like to see supported.
Submit pull requests — if you've fixed a bug, improved performance, or added a feature, PRs are welcome.
Share private feedback — if you prefer to share feedback confidentially, reach out at bettan.michael@gmail.com.