Dashboard

Where to focus your FinOps efforts today.

Loading…

About

BigQuery FinOps Optimizer

v… | Released …

An enterprise-grade diagnostic and simulation suite for Google Cloud BigQuery costs. Analyzes historical telemetry, query workloads, and storage configurations across GCP organizations to maximize cost efficiency.

Release Notes Simulator GitHub

Global configuration

GCP Project Scopes, Required APIs & IAM Permission Guide

BigQuery FinOps Optimizer decouples query execution, slot reservation management, and workload target scopes so you can run organization-wide audits securely without needing administrative permissions across every single workload project.

1. Execution Project

The GCP project used to execute analysis queries against INFORMATION_SCHEMA views. All metadata scanning costs incurred by this tool are billed here.

Required GCP APIs:

bigquery.googleapis.com (Mandatory)
bigquerymigration.googleapis.com (AI Doctor)
cloudaicompanion.googleapis.com (AI Doctor)

IAM Roles & Permissions:

roles/bigquery.jobUser (runs queries)
roles/bigquery.dataViewer (reads results)

2. Admin Project

The central GCP project that owns your BigQuery Slot Commitments, Edition Reservations, and Autoscaler baseline/max limits.

Required GCP APIs:

bigqueryreservation.googleapis.com (Slots Optimizer)

IAM Roles & Permissions:

roles/bigquery.resourceViewer
or roles/bigquery.resourceAdmin

3. Monitored Scope (Focus Projects)

The set of target GCP projects analyzed. Leave empty to run an organization-wide audit across all projects in the GCP org.

Query Scope Mechanics:

Empty: Queries *_BY_ORGANIZATION views
Specified: Filters telemetry to focus list

IAM Roles & Permissions:

Org-level roles/bigquery.resourceViewer
or dataset read access on target projects

GCP environment

Execution Project ID Used for running the queries

Admin Project ID Used for fetching metadata

Region

Max Bytes Billed (GiB) Default: 800 GiB — increase for large orgs

Focus Projects Optional — leave empty for full org-wide analysis

Storage cost optimizer

Note: This analysis evaluates physical vs logical billing specifically for BASE TABLE storage (excluding materialized views and external tables).

Pricing & parameters

Active Logical GiB ($)

Long-term Logical GiB ($)

Active Physical GiB ($)

Long-term Physical GiB ($)

Time Travel Retention (Days)

Monthly savings ($)

Potential Monthly Savings $0.00

Datasets Analyzed 0

Optimization Opps 0

$0.0000

Storage Billing Arbitrage

Project	Dataset	Current Model	Recommended Model	Monthly savings ($)	Savings (%)	Action

Schema Optimizer

Partitioning and clustering recommendations from two complementary engines: Google Active Assist (compute-optimized) and our Static Schema Auditor (bytes-scanned-optimized).

Methodology & Scope: A Complementary Approach

Active Assist (Dynamic)

Optimizes for Compute (Slot-ms). Analyzes the last 30 days of query history to calculate the net compute savings of clustering/partitioning.

Methodology: Active Assist is designed to optimize for net compute efficiency. It may intentionally suppress recommendations for tables with heavy daily DML (inserts/updates) if estimated background maintenance costs outweigh the expected read savings.

Static Guardrail (Structural)

Optimizes for Storage & On-Demand Costs (Bytes Scanned). Performs a structural analysis of tables >1GB to enforce clustering/partitioning best practices.

Better Together: Complements Active Assist by serving as a structural safety net for tables undergoing heavy DML. Essential for preventing On-Demand cost spikes during migration.

Google Active Assist Recommendations

These partitioning and clustering recommendations are generated by Google's native AI engine based on your query history telemetry over the last 30 days.

Project	Dataset	Table	Recommendation	Suggested Columns	Partition Column	Est. On-Demand Savings	Est. Editions Savings

Proactive Migration Guardrails (Static Schema Auditor)

This module statically audits your database schemas to proactively detect massive, unclustered, or unpartitioned tables before they are queried and incur high costs.

Project	Dataset	Table	Rows	Logical Size	Partitioned	Clustered	Suggested Columns	Risk Status

Query cost & routing optimization

How it works

This analyzer profiles your historical BigQuery jobs to determine their most cost-effective billing model (On-Demand vs. Editions/Autoscaler). It is designed to act as an Advanced Workload Router, helping you identify structural "slot hogs" (better for On-Demand) and "data hogs" (better for Editions).

Pro-Tip for Spikey Workloads: For extremely sparse workloads with high peaks, it is often cheaper to move those specific projects back to On-Demand billing (paying per TB processed) instead of Editions. This eliminates the cost of idle slots during cool downs entirely. This analyzer is designed to identify exactly these candidates!

To identify the specific inefficiencies of individual queries, this tool evaluates each job in isolation. In reality, BigQuery Editions highly benefit from concurrency (sharing idle slots across many overlapping jobs). Because blending queries together hides individual bad actors, we use a Smart Job-Centric approach, applying advanced heuristics to approximate how an isolated job behaves against the BigQuery Autoscaler:

The Spike Factor (Legacy): Editions traditionally charge a 1-minute minimum for provisioned slots. Short queries (< 60s) are mathematically penalized here to simulate this penalty.
Fluid Scaling (New): BigQuery now supports per-second billing without the 1-minute minimum floor. Check the "Simulate with Fluid Scaling" box to see how much you save on spiky workloads!
Autoscaler Granularity & Sharing: BigQuery scales in discrete blocks of 50 slots. We round simulated slot usage up to the nearest 50. Exception: Small queries using < 50 slots are assumed to seamlessly "pack" into your existing baseline capacity without triggering independent autoscaler events.
Routing Profiles: We categorize your recurring queries into actionable buckets. Use this to build a dynamic routing table—for example, automatically routing high-CPU/low-IO queries to an On-Demand billing project.

Constraints & Limitations

Not for Capacity Planning: Because we test jobs in isolation to find routing candidates, this tool does not calculate your total concurrent slot demand. Do not use this view to size your overall organization's baseline commitments.
Cost Drift: Query behavior can change over time based on data volume and parameters. Only route recurring queries that demonstrate stable, consistent savings in one specific model.

Pricing & parameters

On-Demand Rate ($/TB)

Editions Slot/Hr Rate ($)

Slot Step Size (Autoscaling)

Lookback Window (Days)

Min Job Size (MB)

Max Jobs to Analyze

Simulate with Fluid Scaling (No 60s Minimum)

Total On-Demand Cost $0.00

Total Editions Cost $0.00

Potential Savings $0.00

Compute Profile Simulator (IO vs CPU)

This scatter plot visualizes jobs based on their projected costs. Jobs above the dotted line are more expensive on Editions (CPU-heavy). Jobs below the dotted line are more expensive on On-Demand (IO-heavy).

Project level summaries

Project ID	On-Demand Cost ($)	Editions Cost ($)	Error Tax ($)	Savings ($)

Query Routing Optimizer & Cost Avoidance

Profiles query-level billing candidates. Workloads currently on On-Demand represent Potential Savings. Workloads already on Editions represent Realized Cost Avoided by your active reservations.

Hide Optimized Workloads Profile:

Project	Job ID	Current Model	Recommended Model	Profile	Savings / Cost Avoided ($)	Savings (%)	Action

Slots autoscaler optimizer

Understanding slot recommendations

BigQuery slots are virtual compute units used to execute jobs. This tool analyzes slot utilization at a high-resolution, second-by-second granularity using INFORMATION_SCHEMA.JOBS_TIMELINE to help you size reservations effectively. While Google Cloud's Slot Estimator provides recommendations based on hourly averages, this high-resolution approach identifies short-lived peaks to ensure you have enough capacity to avoid slot contention during bursty workloads. It provides the following metrics:

Autoscaler baseline: Represents the minimum number of slots that should always be allocated to guarantee capacity. It is calculated by taking the target percentile (e.g., P90) of the average slot usage across your selected window size.
Autoscaler max (P90/P99/Peak): Recommendations for your autoscaler's maximum size. By analyzing the absolute peak second within each window, it ensures you have enough capacity to avoid slot contention (queuing) during bursty workloads.
Merged reservation: Predicts the capacity requirements and efficiency gains if all reservations were consolidated into a single resource pool.

Note: All recommendations are rounded to the nearest 50 slots, matching BigQuery's scaling increments.

Scope Note: The chart shows all query usage across your organization (including On-Demand queries). However, the Recommendations table only analyzes jobs that ran within a reservation.

Analysis parameters

Lookback Window (Days)

Window Size (Minutes)

Resolution

Target Percentile (P) Used for baseline recommendation

Actual Provisioning Totals

This view answers two critical questions for capacity planning:

How much exposure did we have to unpredictable autoscaling costs?
How much did we overpay for baseline capacity not covered by commitments?

Descriptive: Based on configuration logs of what was actually provisioned and billed.

Why this matters: BigQuery Enterprise slot capacity is highly dynamic. Autoscaling fluctuates continuously based on workload, and capacity commitments can change at any time. To understand your true cost, we must calculate the exact "area under the curve" (time x slots) down to the second. This view helps you understand your compute overages and optimize commitments.

0.00

Total hours 0.00

Fluid Savings (Monthly) $0.00

Note: These metrics represent the compute capacity provisioned and billed by Google Cloud, not the actual slots consumed by your queries.

Actual provisioning ratio

What percentage of our compute hours were spent on autoscaling vs our baseline?

Descriptive: Shows the proportion of provisioned capacity types.

Current reservations

Configuration recommendations

Reservation ID	Recommendation	Action

How BigQuery reservations utilize idle and autoscaler slots

BigQuery's Reservation Predictability features allow you to control how your reservations scale beyond their baseline slot capacity using different scaling modes. These modes determine the order and types of additional slots (idle or autoscaler) that can be accessed, up to a configured max_slots limit.

`ALL_SLOTS` (Predictable Performance & Maximum Potential)

This mode aims to provide a balance of performance and cost-efficiency by utilizing all available slot sources. After the reservation's baseline slots (slot_capacity) are fully consumed, it will:

Utilize available idle slots from other reservations within the same administration project and edition.
If demand still exceeds capacity, engage the autoscaler to add slots, up to the reservation's configured max_slots.

Use Case: Good for workloads that can benefit from opportunistic use of idle capacity but also require the guarantee of autoscaling to meet peak demand up to a predictable limit.

`IDLE_SLOTS_ONLY` (Most Cost-Effective)

This mode prioritizes cost savings by only using free capacity. After baseline slots are used, it will:

Utilize available idle slots from other reservations.
It will not engage the autoscaler, even if demand exceeds the combined baseline and available idle slots. The maximum slots used will not exceed max_slots.

Use Case: Ideal for cost-sensitive workloads that can tolerate performance variability. Depends entirely on the availability of idle slots.

`AUTOSCALE_ONLY` (Dedicated Scaling)

This mode ensures that the reservation scales only with dedicated resources, without relying on shared idle capacity. After baseline slots are used, it will:

Engage the autoscaler to add slots, up to the reservation's configured max_slots.
It will not utilize any idle slots from other reservations.

Use Case: Suitable for workloads requiring consistent scaling behavior based purely on their own demand. Requires ignore_idle_slots to be set to true.

Prerequisite: To use any of these scaling modes with max_slots, "Reservation-based fairness" must be enabled on the administration project.

Tiered Baseline Recommendations

Select a risk profile to see the recommended baseline slots and estimated costs.

P80

Aggressive Savings

Low cost, higher risk of queuing during bursts.

0 slots

P95

Balanced

Optimal balance of cost and performance.

0 slots

Max

Performance

Zero queuing risk, highest cost.

0 slots

Sizing recommendations

Based on our real query history, what size should our reservations be?

Prescriptive: Suggested optimal capacity based on actual query workload demand.

Why are these recommendations higher than the official Google Cloud Slot Estimator?
The official GCP tool optimizes for cost based on your actual billed usage, which is artificially capped by your current max_slots limit. It only sees what was provisioned, not what was queued.
This FinOps Optimizer analyzes your unconstrained query demand to optimize for performance. The delta between the two tools represents the query queueing and performance degradation your users experience during bursts.

Reservation ID	Autoscaler baseline (P90)	Autoscaler max (P90)	Autoscaler max (P99)	Autoscaler max (Peak)

Slot usage by capacity (minute average)

Fluid scaling

Estimate savings from enabling Fluid Scaling (per-second billing).

Fluid Scaling: The FinOps Shift

BigQuery Fluid Scaling introduces true sub-minute elasticity to the autoscaler. By shifting from discrete one-minute billing increments to a continuous compute profiling model, you only pay for the exact duration of your workload execution, down to the second.

The Legacy Model: The 60-Second Floor

The Autoscale Penalty: Historically, scaling events incurred a mandatory 60-second minimum charge, meaning a lightning-fast 5-second dashboard query still billed you for a full minute of capacity.
Forced Consolidation: To mitigate this overhead, data platform teams were often forced to artificially pack unrelated, spiky workloads into shared reservations, sacrificing performance isolation.
Complex Engineering: Managing these "noisy neighbors" required significant FinOps engineering to constantly tune and rebalance slot limits across the organization.

The Modern Model: Frictionless Capacity

Micro-Billing: Capacity costs scale down instantly. If a complex aggregation resolves in 12 seconds, the financial meter stops at 12 seconds.
Decoupled Architecture: Organizations can freely provision dedicated, isolated reservations for individual business units without suffering financial penalties for idle time.
Granular FinOps: Spend becomes perfectly proportional to execution time, enabling hyper-accurate chargebacks and eliminating opaque "unused capacity" overhead.

Expected Impact: Industry-standard power tests show this architectural shift improving overall price-to-performance by up to 34%. The simulator below analyzes your organization's unique `JOBS_TIMELINE` data to calculate the exact financial recovery you'll see by enabling per-second billing.

Parameters

Lookback Window (Days)

Slot/Hr Rate ($)

Total Slot Hours Saved 0

Total Est. USD Saved (7d) $0

Total Extrapolated Monthly $0

Total Extrapolated Annual $0

Fluid scaling savings by capacity

Reservation ID	Status	Legacy Slot-Hrs	Fluid Slot-Hrs	Total Used Slot-Hrs	Recoverable Slot-Hrs	% Savings	$ Window	$ Monthly	$ Annual

Fluid scaling savings by pattern & reservation

Why analyze savings by query pattern?

While the aggregate numbers above show the total potential value of Fluid Scaling, this pattern-level breakdown is critical for actionability and risk mitigation:

Workload Isolation: If you are hesitant to enable Fluid Scaling globally, identify the specific patterns (e.g., Looker dashboards or dbt models) suffering from the 60-second cooldown penalty and isolate them into a dedicated reservation.
FinOps Accountability: Assign wasteful idle time directly to the engineering team or pipeline responsible for the query.
Catching Anti-Patterns: If a 200ms query runs continuously and wastes thousands of dollars in cooldown penalties, the right architectural fix might be moving that workload out of BigQuery entirely (e.g. to Cloud SQL).

Top 100 (query pattern × reservation) combinations ranked by estimated savings impact. The same pattern may appear once per reservation it runs on.

Pattern	Sample Job ID	Workload Type	Reservation	Job Count	Avg Duration (s)	Avg Slots/Job	Exposure Score	Indicative Savings (Window)

Edition Matrix Simulation

Simulation parameters

This simulator helps you find the most cost-effective baseline by balancing the cost of idle slots (waste) against the cost of autoscaling (bursts). It tests every baseline size and calculates the total bill for different commitment tiers. The turning point with the lowest 1-Year Commit cost will be highlighted.

Simulation Range

Pricing Tiers ($ per slot/hr)

Edition matrix simulation

How to Read This Simulation

This matrix simulates costs across different Baseline choices. For each baseline level, we calculate how much capacity would be covered by the fixed baseline vs. how much would spill over into variable Autoscale capacity.

In Buckets

Shows frequency distribution. E.g., how many minutes your total usage was within that specific slot range.

Utilization %

Measures how efficiently that baseline is used. 100% means no slots wasted; lower percentages imply idle capacity.

Autoscale

The volume of compute (in hours or months) that exceeded the baseline and required dynamic scaling.

Total Monthly Cost

The final calculated sum of (Autoscale + Baseline) projected to a standard 730-hour billing month.

Tip: Look for the row that minimizes the Total Monthly Cost for your target commitment tier to find your optimal efficiency point.

Resolution Note: This simulation aggregates slot usage to 1-minute averages to balance performance and accuracy. This acts as a good approximation for Fluid Scaling, but may smooth out sub-minute spikes.

Recommendations

Commitment Type	Autoscale Slots	Recommended Baseline	Estimated Monthly Cost

Note: The table breaks down costs into Autoscale Cost and Baseline Cost, and sums them up into the Total Monthly Cost columns. Costs are projected to a standard 730-hour billing month.

In Buckets		Below Baseline		Autoscale		Autoscale Cost	Baseline Cost			Total Monthly Cost
Bucket	Minutes	Slots	Utilization %	Slot-Hours	Slot-Month	EE PAYG	EE PAYG	EE 1 Year	EE 3 Years	EE PAYG	EE 1 Year	EE 3 Years

Cost attribution

How cost attribution works

Google Cloud billing does not attribute BigQuery Editions costs to the specific projects that ran the queries. Following hybrid cost attribution model is helping with that:

We measure the exact slot-hours each project consumed and multiply it by the reservation's SKU rate (direct usage cost)
The difference between your total admin bill and the sum of direct costs is considered "waste" (e.g., idle baseline capacity). Depending on the waste rule selected, we either distribute this waste back to projects proportionally based on their usage share or we distribute this waste back to a specific project such as the original admin project for Central IT to cover the cost.

BigQuery autoscaling facts & waste drivers:

Idle Baseline Capacity: Paying for configured baseline slots that were not fully utilized by queries.
Cool Down Period: Autoscaled capacity is retained for at least 60 seconds after demand drops, incurring costs with 0 usage.
No Cross-Reservation Sharing: Autoscaled idle slots are not shared across different reservations.
Quantization: Scaling up happens in multiples of 50 slots, often allocating excess capacity.
Intermittent Penalties: High-frequency intermittent queries can result in continuous slot usage due to repeated cool downs.
Admin Project Isolation: Using separate Admin Projects prevents idle slot sharing between reservations.

Attribution rules & parameters

Waste Rule

Central IT Project

Borrowing Rule

Billing Month Start

Billing Month End

Reservations configuration

Reservation ID

SKU Rate ($)

Total Bill ($)

Attribution results

Project ID	Reservation ID	Direct Usage Cost ($)	Allocated Waste ($)	Total Cost Attribution ($)

Slot usage by project

Project ID	Total Slot Hours

Workload profiler

Optimizing for high-frequency, short queries

This tool identifies reservations experiencing a "continuous trickle" of small, frequent queries. Documentation and expert guidelines recommend specific strategies to optimize these workloads and avoid unnecessary autoscaler activation overhead.

Strategy A: Baseline Absorption & Sharing

Setting a Baseline > 0 is recommended to trade a small fixed cost for lower query latency. The slots are already allocated, allowing light interactive queries to start instantly without waking up the autoscaler.

Pro-Tip (Slot Sharing): To reduce waste, place multiple reservations on the same Admin Project. Idle baseline slots are automatically shared with other workloads!

Strategy B: Routing to On-Demand Pricing

Since these "trickle" queries usually scan very little data (low I/O) but are penalized by the 1-minute slot minimum on Editions, they are often much cheaper on the On-Demand model (which charges purely per TB processed).

Implementation: You can mix pricing models in your organization. Simply bind the projects running these small queries to a dedicated Billing Project configured for On-Demand.

Advanced Runtime: Short Query Optimizations

BigQuery's Advanced Runtime can automatically optimize short queries by consolidating execution into a single stage and skipping the shuffle layer. This reduces slot usage and latency dramatically.

Tip: To leverage this, keep query working sets small and result sizes small. Ensure you have enabled the Advanced Runtime for your project. Read more in the Google Cloud Blog.

How to Decide: The Math

Compare the cost of the job under both models: calculate the On-Demand cost based on total_bytes_billed and compare it against the Editions cost based on total_slot_ms (factoring in the 1-minute minimum for bursts). Use the Query cost optimizer tab in this tool to see these specific cost comparisons for individual jobs!

Profiler parameters

Uses global settings for Project, Region, and Lookback Window.

Workload profile warnings

Are we paying for idle slots due to a continuous trickle of small queries?

Prescriptive: Identifies reservations with high frequency of low-slot queries that might benefit from a fixed baseline.

Reservation ID	Total Flagged Hours	Peak Hourly Queries	Top Offending Projects	Recommendation

Temporal heatmap

Grid showing when the trickle happens across the week (Hour of Day vs Day of Week).

Hour	Sun	Mon	Tue	Wed	Thu	Fri	Sat

Top offending queries

Are these queries causing inefficient scale-ups? This table reveals the most frequent small queries that run during periods of high query counts but low slot usage. Look for queries with high frequency but very low (or 0 MB) data processed (like transactions or literal inserts).

Can they use Advanced Runtime? Check if they are marked as a "Candidate" to benefit from 1-stage execution.
Can they be batched? Frequent small inserts can often be combined into fewer, larger jobs to reduce overhead.
Should they be on On-Demand? If they process 0 MB, they are extremely cheap on the On-Demand model.

Why 0 MB? These queries are the perfect example of what the blog post calls "short" or "trickle" queries. They process no data but still consume slot time and trigger overhead. They are excellent candidates for Short Query Optimizations or for being routed to On-Demand pricing (where a 0 MB query costs nothing or at most the 10MB minimum charge, avoiding the 1-minute slot penalty on Editions).

Query Text	Project	Example Job ID	Frequency	Avg Slot Hours	Avg Duration (s)	Avg Bytes Processed	Recommendation

Top Spenders

Identify Top Resource Consumers

This view helps you identify which users are running the most queries, scanning the most data, and consuming the most slot hours. Use this information to optimize workloads, educate users, or allocate costs.

Note on Cost Attribution

Status: The current implementation uses a direct usage calculation (Data Billed × On-Demand Rate) and (Slot Hours × Editions Rate).

Complexity: In a shared reservation environment, attributing exact costs to users is hard because idle slots are shared and bursts are unpredictable. True cost might require a proportional allocation of reservation waste.

Profiler parameters

Uses global settings for Project, Region, and Lookback Window.

Active Users Analyzed 0

Actual Total Spend $0

Potential Savings $0

Wasted Spend $0 —

Total Slot Hours 0

Top spenders results

User Email	Identity	Billing Mode	Query Count	Total Data Billed	Total Slot Hours	Actual Cost	Waste	Est. On-Demand	Est. Editions	Potential Savings

History-Based Optimization (HBO) Analyzer

What is History-Based Optimization (HBO)?

BigQuery's History-Based Optimization (HBO) is a native feature — enabled by default since GA — that uses telemetry from completed executions of similar queries to automatically apply additional optimizations and improve query performance. HBO applies to all queries in BigQuery, including On-Demand pricing — it is not limited to reservations.

When you run a query repeatedly, BigQuery learns from each run and applies optimizations. These are split into two categories:

History-Based (requires prior runs)

Semi-join reduction — reduces shuffled data volume
Join commutation — swaps join sides for efficiency
Join pushdown — executes selective joins earlier
Parallelism adjustment — optimizes stage parallelism

Engine-Level (applied at runtime)

Enhanced vectorization — hardware-specific execution
Short query optimization — fast-tracks simple queries

Quantifying the Impact

This tool estimates HBO's impact by comparing each query's execution time against its historical average (avg_previous_execution_ms), filtering out jobs where data volume increased. Savings are projected to monthly slot-hours and dollars.

HBO Status: The status panel below checks each project's PROJECT_OPTIONS for the adaptive setting. If HBO has been disabled, you can re-enable it with the provided DDL.

HBO parameters

Uses global settings for Project, Region, and Lookback Window by default, or use the overrides below. (Project Scope Only)

Project ID Override Leave blank to use global setting

Lookback Window (Days) Leave blank to use global setting

Total Slot Hours Saved 0.00

Total Dollars Saved $0.00

Top Optimized Queries

Job ID	% Time Saved	New Elapsed (ms)	Original Elapsed (ms)	Optimizations Applied

Storage Hygiene & Time Travel Auditor

Why this matters

While the existing Storage Cost Optimizer tab handles the actual billing costs (should we pay for compressed or uncompressed data?), this Storage Hygiene Auditor tab handles architectural waste (are we burning money by leaving default settings on tables with high churn?).

For the BigQuery Admin:

Identify datasets with high churn rates where reducing the default 7-day Time Travel window to 2 days can instantly cut storage costs.

For the Data Engineer:

Detect tables that are fully dropped and recreated daily, highlighting opportunities to switch to incremental MERGE or partitioned updates.

Hygiene parameters

Uses global settings for Project and Region.

Total Current Size 0.00 GiB

Total Time Travel Size 0.00 GiB

Tables Analyzed 0

High Churn Tables 0

Potential Monthly Savings $0.00

Top Storage Waste Candidates

Project	Dataset	Table Name	Live Size (Physical GiB)	Time Travel Size (GiB)	Churn Ratio	Health Status

Architectural Anti-Pattern Scanner

Architectural Anti-Patterns

This scanner helps identify structural and pipeline inefficiencies that waste compute and storage costs.

DML & Ingestion Abuse

User Story: As a Data Architect, I want the app to detect jobs using the legacy tabledata.insertAll API or high-frequency SQL INSERTs, so I can migrate them to the Storage Write API and cut streaming costs by 50%.

Materialized View Waste

User Story: As a FinOps Manager, I want to identify Materialized Views (MVs) where the background refresh cost (Slots consumed) is actually higher than the compute saved during querying, so I can pause auto-refresh if it's wasting money.

Using LIMIT improves speed, but not cost

Applying a LIMIT clause to a SELECT * query does not affect the amount of data read. You are billed for reading all bytes in the entire table, and the query counts against your free tier quota.

Best practice: LIMIT speeds up performance, but doesn’t reduce costs. For data exploration, consider using BigQuery’s (free) table preview option instead.

Query Optimization Opportunities

This table identifies expensive queries (>100 GB scanned) using inefficient patterns. Each row shows the impact, root cause, and suggested fix.

User Email	Project ID	Job ID	Data Billed	Abuse Type	Estimated Waste ($)	Query Snippet	Suggested Fix

High-Frequency Ingestion Cost Auditor

Audits data ingestion pipelines for compute waste. Identifies service accounts running high-frequency SQL INSERT statements. Migrating these to the modern Storage Write API consumes zero query slots and cuts streaming costs by 50%.

User Email	Project ID	Insert Count (Last 1D)	Wasted Slot Hours	Estimated Waste ($)	Recommendation

Materialized View Refresh Costs

This table shows the compute cost (slot hours) consumed by background refreshes for each Materialized View. High refresh costs with low query usage indicate waste.

Project ID	Dataset	MV Name	Refresh Count	Total Slot Hours

Data Skew Candidates

This table lists jobs where a single worker node took 10x longer than average to process data, indicating unbalanced joins or grouping keys.

How to Diagnose & Remediate Data Skew in BigQuery Console

🔍 What is Data Skew? Data skew occurs when data partitions unevenly across worker slots during a JOIN, GROUP BY, or window PARTITION BY operation. A single worker node becomes overwhelmed processing a "hot key" while other workers sit idle, causing query latency to spike and holding slots hostage.

🛠️ Step-by-Step Investigation in BigQuery Console:

Click the Inspect Stage button or Job ID link in the table to open the job in Google Cloud Console.
Navigate to the Execution details / Execution graph tab.
Locate the flagged Stage Name (e.g. S02: Join+Aggregate).
Compare Compute ms (Max vs Avg) and Records written (Max vs Avg) to pinpoint the bottleneck operator.

💡 Top 3 Remediation Techniques:

Filter NULL / Default Keys: NULL, "", or 'N/A' keys all hash to the same single worker. Add WHERE join_key IS NOT NULL before joining.
Key Salting (Heavy Entities): If a valid key (e.g., a massive tenant) causes the hotspot, append a random salt integer (e.g. MOD(FARM_FINGERPRINT(id), 10)) to split rows across 10 workers.
Partition Analytic Windows: Avoid unpartitioned OVER (ORDER BY ...) which forces all rows onto a single worker node; add appropriate PARTITION BY clauses.

Project ID	Job ID	User Email	Stage Name	Avg Compute (ms)	Max Compute (ms)	Skew Ratio	Action

Interactive vs. Batch Candidates

Analyzes workload concurrency profiles across automated pipelines, BI dashboards, and ad-hoc query streams to recommend optimal execution priority (BATCH vs INTERACTIVE).

Interactive vs. Batch Execution Mechanics & Guidance

⚡ Core Execution Differences: In BigQuery, INTERACTIVE queries are executed immediately against available slot capacity, subject to a strict hard limit of 100 concurrent interactive queries per project. When this limit is hit, additional queries fail with rate-limit errors. BATCH queries are queued and auto-started within minutes as slot capacity becomes available, auto-retrying gracefully without triggering immediate rate-limit failures.

💰 Hardware & Pricing Parity: Compute hardware, slot performance, and per-byte/slot-hour pricing are 100% identical between BATCH and INTERACTIVE queries. Running a pipeline in BATCH mode costs the exact same as INTERACTIVE, but protects your BI dashboards and human users from slot starvation during peak business hours.

💳 On-Demand Capacity Protection: For projects without edition reservations (pure On-Demand / Pay-Per-TB), BigQuery enforces a strict ceiling of 2,000 concurrent slots per project. Running heavy ETL pipelines concurrently in INTERACTIVE mode exhausts this 2,000-slot pool, causing BI dashboards and ad-hoc analysts to experience severe query queuing and degradation.

📋 Recommended Priority Matrix:

dbt / Airflow / Dataform / Scheduled Pipelines: Use BATCH priority (guarantees auto-retry and prevents dashboard slot starvation).
Looker / Tableau / Power BI Dashboards: Use INTERACTIVE priority (essential for sub-second UI responsiveness).
Heavy Service Account DML (MERGE / UPDATE): Use BATCH priority (isolated execution during idle capacity).

Workload / Target	Workload Type	Project ID	Runs	Slot Hours	% Interactive	Human Wait (s)	Finding	Remediation

Datasets Missing Expiration Policy

Temporary, dev, and sandbox datasets without expiration rules are one of the biggest sources of passive storage waste.

Project ID	Dataset ID	Status

Partitioned Tables Missing Filter Guardrail

If "Require Partition Filter" is off, users will accidentally run full-table scans on massive tables instead of filtering by date.

Project ID	Dataset ID	Table Name	Partition Type

Materialized View Rejections

This table lists queries from the last 30 days where a Materialized View was considered but rejected by the optimizer. Showing the 50 most recent.

User Email	Job ID	MV Name	Rejected Reason

Proactive Resource Warnings

This table lists queries from the last 30 days that triggered resource warnings (e.g., approaching limits). Showing the 50 most recent.

User Email	Job ID	Warning

BigQuery Execution Insights

Execution Performance Insights

BigQuery analyzes query execution stages and publishes performance telemetry. This dashboard identifies queries affected by performance bottlenecks, including slot contention, insufficient shuffle quota, and significant changes in input data volume.

Understanding Data Volume Anomalies

BigQuery identifies input data change insights by comparing the records read in the current execution against the historical average of equivalent runs (grouped by their normalized query hash).

Why are some increases so huge? If a query's history contains mostly dry-runs, tests on empty partitions, or tiny staging tables, the baseline average divisor is extremely low (e.g., 1 row). When the query runs on full production data, the relative difference escalates exponentially (e.g., a 20,000% increase is formatted as a +200x multiplier).

Parameters

Uses global settings by default, or use the overrides below. (Project Scope Only)

Project ID Override Leave blank to use global setting

Lookback Window (Days) Leave blank to use global setting

Slot Contention Jobs

Queries that experienced significant delays due to slot resource limitations.

Job ID	User Email	Project ID	Stage ID

Shuffle Quota Bottlenecks

Queries slowed down due to limits in BigQuery's memory-based shuffle storage quota.

Job ID	User Email	Project ID	Stage ID

Data Volume Anomalies

Queries with a dramatic increase in input data size relative to historical executions.

Job ID	User Email	Project ID	Data Increase

BI Engine ROI & Cache Miss Analyzer

BI Engine: Performance AND Cost Optimization

While usually viewed purely as a performance feature, BI Engine is a primary FinOps cost-saving tool.

Under On-Demand pricing, a full cache hit drops your bytes_billed to zero.
Under Editions pricing, acceleration reduces the total slot_ms consumed, saving money in your reservation.

The "Dashboard Doctor"

Dashboards are often slow or expensive because queries silently fail to use BI Engine (e.g., due to unsupported SQL functions). This tool parses the specific failure reasons in the logs to tell you exactly why your dashboards are scanning disk instead of RAM.

Note: The ROI calculation assumes On-Demand pricing of $6.25 per TB for the bytes saved.

Total Dollars Saved

$0.00

Full Acceleration Rate

0%

Analysis parameters

Uses global settings for Project and Region.

Top Jobs & BI Engine Status

User Email	Job ID	Processed (GiB)	Billed (GiB)	Mode	Failure Reasons

AI Doctor

How AI Doctor Optimizes Your BigQuery Workloads

AI Doctor combines Gemini LLM semantic auditing with BigQuery Migration Service API v2 to automatically discover and rewrite expensive, resource-hungry queries across your GCP organization.

BigQuery Migration API v2

Executes an autonomous 2-stage discovery & opt-in YAML pipeline (BigQuery2BigQuery_Translation) to rewrite CTEs, precompute subqueries, and convert string join functions.

Gemini LLM (Vertex AI)

Analyzes query telemetry against 100% grounded table DDL schemas from INFORMATION_SCHEMA to classify anti-pattern severity (HIGH, MEDIUM, LOW).

What AI Doctor Checks

✓ Query tuning — SELECT *, CROSS JOIN, ORDER BY w/o LIMIT, REGEXP_CONTAINS → LIKE, COUNT(DISTINCT) → APPROX_COUNT_DISTINCT
✓ Partition alignment — Detects queries that filter on the wrong column (not the partition key) and adds the correct partition predicate
✓ Clustering alignment — Flags queries not filtering/ordering on existing cluster keys
✓ Join optimization — Push WHERE filters before JOINs, flag suboptimal join order
✓ Require partition filter — Ensures rewritten SQL respects require_partition_filter = true
✓ Full SQL rewrite — Generates a complete, runnable optimized query (not just advice)
✓ Partition recommendations — Flags large unpartitioned tables (>1 GB) and recommends which column to PARTITION BY
✓ Cluster recommendations — Suggests CLUSTER BY columns based on the query's WHERE, JOIN, and GROUP BY patterns

Out of scope

✗ DDL execution — Recommends partition/cluster changes but does not generate or execute ALTER TABLE / CREATE TABLE DDL scripts
✗ Materialized View creation — Does not suggest creating MVs for repeated aggregations (use BI Optimizer tab)
✗ Storage billing model — Does not advise switching physical ↔ logical billing (use Storage Cost tab)
✗ Pipeline-specific advice — Advice is SQL-level; does not tailor to Looker LookML, dbt YAML, or Dataform configs

Dual-Engine AI Doctor Orchestration Pipeline

Input: Top Offending BigQuery SQL + Grounded Table DDLs

Scans INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION and auto-resolves schema types from INFORMATION_SCHEMA.TABLES

Pipeline 1: Gemini Semantic Model

Runs AI.GENERATE against schema context. Rewrites queries for column pruning, join order, and partition filters within OPTIMIZED_SQL_START...END.

Outputs severity score + semantic advice

Pipeline 2: Migration Service Compiler

Autonomous 2-Stage compiler pass. Synthesizes optimizer.config.yaml to perform AST rewrites (CTE materialization, REGEXP to LIKE).

Deterministic AST optimization & dry-run

Merge & Echo Suppression Gate

If optimized SQL is identical to original, echo is cleanly suppressed (—). Validated rewrites display with interactive copy & diff.

Pipeline 1: Gemini Semantic Model (AI.GENERATE)

Gemini evaluates the query against table partition keys and cluster fields. When a structural query rewrite is possible, it encloses the code in OPTIMIZED_SQL_START ... OPTIMIZED_SQL_END.

Why it may not produce SQL:

• Architectural / Operational Advice: Many queries cannot be rewritten without altering business logic (e.g. missing partition filters where Gemini cannot guess the intended date range, or queries needing table partitioning changes). In these cases, Gemini provides text recommendations without generating speculative SQL.
• Token Truncation Guard: If a generated query exceeds token limits and the closing OPTIMIZED_SQL_END marker is missing, the backend discards the partial SQL to prevent invalid syntax.

Pipeline 2: BigQuery Migration Service API

Executes Google's official BigQuery-to-BigQuery compiler with an autonomous 2-pass workflow (Pass 1 diagnostic discovery $\rightarrow$ Pass 2 config synthesis & execution).

Compiler Rewrites & Safety Behaviors:

• Supported Transformations: Materializes repeated CTEs (REWRITE_CTE_TO_TEMP_TABLE), converts regex to wildcards (REGEXP_CONTAINS_TO_LIKE), precomputes scalar subqueries (PRECOMPUTE_INDEPENDENT_SUBSELECTS), and prunes set joins (ADD_DISTINCT_TO_SUBQUERY_IN_SET_COMPARISON).
• Echo Suppression: If the compiler finds no applicable rules (or returns the SQL unchanged), echoing is suppressed so identical queries aren't falsely advertised as optimizations.
• Fail-Open Resilience: If bigquerymigration.googleapis.com is not enabled on a project (404), the translation is skipped gracefully without interrupting Gemini's advice.

Discovery Priority Modes

⚖️ Balanced ROI Score

Multi-factor score ranking candidates by Cost + Frequency + Slot Time + RAM Spill.

💰 Cumulative Cost ($)

Ranks workload templates by total bytes billed ($ impact) across all executions.

🔄 High Frequency (BI)

Targets dashboard micro-offenders executing repeatedly (COUNT(*) > 1).

💾 Memory RAM Spill

Filters for queries spilling intermediate shuffle data from RAM to disk.

⏱️ Total Slot Time

Focuses on heavy compute queries consuming the highest aggregate CPU slot milliseconds.

Required Roles (on the execution project)

Migration Editor

roles/bigquerymigration.editor

SQL optimization workflows (create, get, delete)

BigQuery User

roles/bigquery.user

$0.00 dry-run byte validation

Vertex AI User

roles/aiplatform.user

Gemini-powered recommendations

This analysis sends table schemas (DDLs) and SQL query text. Row-level data is NEVER sent or stored.

AI Analysis Parameters

Audits your most expensive queries (Top 5, 10, or 20) based on slot usage. The system dynamically over-fetches the highest-cost jobs from BigQuery, automatically filtering out queries containing deleted tables or system views that lack sufficient schema context, ensuring you only receive the precise number of valid, actionable recommendations you requested. Gemini then audits the final list for performance anti-patterns, hiding perfectly optimized queries.

Analyze Top N Queries:

Discovery Priority:

⚖️ Ranks candidates by Cost + Run Frequency + Slot Time + RAM Spill.

Lookback Window:

Gemini Model:

Top Inefficient Queries

A semantic, serverless audit of your most resource-hungry SQL queries. The system analyzes raw query telemetry, retrieves referenced table schemas (DDLs) from the metadata catalog, and leverages Gemini to highlight performance anti-patterns with tailored refactoring advice.

Job ID	User Email	Total Slot Hours	Severity	Original Cost	Query SQL	Schema Coverage	AI Optimization Advice	Optimized Query

Assessment Reports

⚡

No Assessments Yet

Click Generate Report to run an analysis sweep and build a comprehensive FinOps assessment. Each report is saved here for reference.

Top Optimization Opportunities

Top Projects by Cost

Recent Anomalies

About

BigQuery FinOps Optimizer

Global configuration

1. Execution Project

2. Admin Project

3. Monitored Scope (Focus Projects)

GCP environment

Storage cost optimizer

Pricing & parameters

Organization Status

Storage Billing Arbitrage

Schema Optimizer

Methodology & Scope: A Complementary Approach

Active Assist (Dynamic)

Static Guardrail (Structural)

Google Active Assist Recommendations

Proactive Migration Guardrails (Static Schema Auditor)

Query cost & routing optimization

How it works

Constraints & Limitations

Pricing & parameters

Compute Profile Simulator (IO vs CPU)

Project level summaries

Query Routing Optimizer & Cost Avoidance

Slots autoscaler optimizer

Understanding slot recommendations

Analysis parameters

Actual Provisioning Totals

Actual provisioning ratio

Current reservations

Configuration recommendations

How BigQuery reservations utilize idle and autoscaler slots

ALL_SLOTS (Predictable Performance & Maximum Potential)

IDLE_SLOTS_ONLY (Most Cost-Effective)

AUTOSCALE_ONLY (Dedicated Scaling)

Tiered Baseline Recommendations

Aggressive Savings

Balanced

Performance

Sizing recommendations

Slot usage by capacity (minute average)

Fluid scaling

Fluid Scaling: The FinOps Shift

The Legacy Model: The 60-Second Floor

The Modern Model: Frictionless Capacity

Fluid Scaling Configuration Status

Parameters

Fluid scaling savings by capacity

Fluid scaling savings by pattern & reservation

Why analyze savings by query pattern?

Edition Matrix Simulation

Simulation parameters

Simulation Range

Pricing Tiers ($ per slot/hr)

Edition matrix simulation

How to Read This Simulation

Recommendations

Cost attribution

How cost attribution works

Attribution rules & parameters

Reservations configuration

Attribution results

Slot usage by project

Workload profiler

Optimizing for high-frequency, short queries

Strategy A: Baseline Absorption & Sharing

Strategy B: Routing to On-Demand Pricing

Advanced Runtime: Short Query Optimizations

How to Decide: The Math

Profiler parameters

Workload profile warnings

Temporal heatmap

Top offending queries

Top Spenders

Identify Top Resource Consumers

Note on Cost Attribution

Profiler parameters

`ALL_SLOTS` (Predictable Performance & Maximum Potential)

`IDLE_SLOTS_ONLY` (Most Cost-Effective)

`AUTOSCALE_ONLY` (Dedicated Scaling)