BigQuery Cost Predictor - ML-Powered Query Cost Prediction with BOOSTED_TREE

🚀 Select a Client Project to Analyze

💰 GCP Billing Breakdown

Detailed billing breakdown from GCP billing export table by service, SKU, and usage.

💡 BigQuery Cost Components:

Storage (80-90%): Data at rest

Analysis (10-15%): Query processing

Streaming (1-5%): Real-time ingestion

Why Query Analysis shows low costs: It only measures compute (analysis), not storage.

🔐 Required Access: We need BigQuery access to the project that contains the billing export table

Required roles: BigQuery Data Viewer + BigQuery Job User

⚡ One-time setup: Paste your billing table info once, we'll remember it forever

Example: project-399903.billing_table.gcp_billing_export_v1_0121F3_47AD14_59E88814

Billing Project ID:

Dataset ID:

Billing Table ID:

Service Filter:

Time Period:

Min Cost ($):

Result Limit:

📊 Billing Variance Analysis - Cost Changes Detection

0 significant changes detected

⚠️ Smart Alert System

Get instant notifications when costs spike unexpectedly

📧 Email for Alerts:

📱 Phone for SMS (optional):

🚨 Alert Threshold:

📊 Alert Frequency:

Service	SKU	Previous Cost	Current Cost	Difference ($)	% Change	Alert Level	Action
Run variance analysis to detect cost changes and waste patterns

📊 Table Storage Analysis - Storage Costs & Optimization

Analyze BigQuery table storage using INFORMATION_SCHEMA.TABLE_STORAGE

0 tables analyzed

Region:

Include Cost Forecasting:

Project	Dataset	Table	Logical (GB)	Physical (GB)	Time Travel (GB)	Logical Cost/Mo	Physical Cost/Mo	Potential Savings	Recommended Model	Efficiency
Run table storage analysis to see detailed storage breakdown

💰 BigQuery Cost Predictor

Predict query costs BEFORE execution to prevent bill shock

🚀 We will build model for you and predict based on BOOSTED_TREE_REGRESSOR

The first time will take ~ 16 minutes you we want to provide Accurate cost

Our main Goal is to give any company to predict query cost

So we need to use project as parameter that change based on company

✓ 🤖 BOOSTED_TREE_REGRESSOR REQUIRED

Only trained ML models provide accurate predictions (95% accuracy)

⚠️ No fallback predictions - Train your model first!

🚀 Setup Custom BOOSTED_TREE_REGRESSOR Model

🎯 The General Process

This is the general process we will implement for your company to predict query costs with high accuracy using machine learning.

PART 1: Create training table `{project}.Finops.train`

PART 2: BOOSTED_TREE_REGRESSOR model creation

PART 3: PREDICTION using the trained model

🏢 Project ID for Model Training (Company-specific):

We need to use project as parameter that changes based on company

PART 1: CREATE TABLE `{project}.Finops.train`

Extract historical query data from INFORMATION_SCHEMA

CREATE TABLE `{project}.Finops.train` AS
SELECT
  query,
  total_slot_ms / 3600000 as slots_hours -- Convert to hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= '2024-01-01'
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND total_slot_ms > 0

PART 2: BOOSTED_TREE_REGRESSOR

Create ML model (~16 minutes training time)

CREATE OR REPLACE MODEL `{project}.Finops.slots_prediction_model`
OPTIONS(
  model_type='BOOSTED_TREE_REGRESSOR',
  input_label_cols=['slots_hours']
) AS
SELECT
  query,
  slots_hours
FROM `{project}.Finops.train`

PART 3: PREDICTION

Use trained model to predict query costs

SELECT predicted_slots_hours
FROM ML.PREDICT(
  MODEL `{project}.Finops.slots_prediction_model`,
  (SELECT 'YOUR_QUERY_HERE' as query)
)

✅ Ready to Use!

Once the model is trained, you can predict costs for any query before execution using the UI below.

SQL Query:

📝 Sample Queries

⚡ BigQuery Query Executor

Execute any BigQuery query - works exactly like your test app

SQL Query

🔍 Analysis Results

Analysis Period:

Job Type:

Sort By:

Result Limit:

BigQuery Query:

SELECT * FROM INFORMATION_SCHEMA.JOBS_BY_PROJECT LIMIT 10;

💰 Discover Your Most Expensive Users and Queries

Discover which users and queries are costing you the most money. This helps you optimize your BigQuery usage and reduce costs!

Usage Start Time	Project	User	Job Type	Total Jobs	Query Jobs	Slots	Query Slots	Sample Query	Standard Cost	Enterprise Cost	Enterprise+ Cost
Select a client and analyze to see results

BigQuery Cost Analyzer

BigQuery Resource Editor

Welcome! Let's Add Your First Project

🚀 Select a Client Project to Analyze

💰 GCP Billing Breakdown

Cost by Service

Detailed Billing Breakdown

📊 Billing Variance Analysis - Cost Changes Detection

⚠️ Smart Alert System

📊 Table Storage Analysis - Storage Costs & Optimization

Total Tables

Logical Storage

Physical Storage

Potential Savings

Table Storage Details

💰 BigQuery Cost Predictor

🚀 Setup Custom BOOSTED_TREE_REGRESSOR Model

🎯 The General Process

📊 Cost Analysis

⚠️ Risk Factors

💡 Recommendations

📝 Sample Queries

⚡ BigQuery Query Executor

🔍 Analysis Results

BigQuery Query:

💰 Discover Your Most Expensive Users and Queries

🔗 Data Correlation Insights

Analysis Summary

Pick Hours – Heavy Slot Usage

🔍 Duplicate Logic Detection - Table Consolidation Opportunities

🗑️ Waste Detection - Query & ETL Optimization Opportunities

🪽 BQ Angel - Move Queries Between Reservations (Enhanced)

💰 Get alerts now on waste of money

🧪 Test Billing Variance Scenarios

📋 Test Results:

🪽 BQ Angel - Move Queries Between Reservations (Enhanced)

🤖 Claude AI Analysis

🗑️ OLD Waste Detection - DISABLED

🔍 OLD Duplicate Logic Detection - DISABLED

Billing Breakdown Guide

Required Permissions:

Query Optimization Guide

🚨 Problem Identified

❌ Current Query (Inefficient)

✅ Optimized Query (Recommended)

🔧 Step-by-Step Fix

💰 Cost Impact

💡 Additional Optimization Tips

Client Permission Instructions

⚙️ Alert Preferences

📧 Contact Information

📊 Email Alert Frequency

📱 SMS Alert Frequency