FinOps Optimizer — Assessment Report

Projectdemo-sandbox
Regionregion-us
Assessment Date2026-09-17 00:39 UTC
Lookback Window30-Day Analysis
Tool VersionFinOps Optimizer v1.4.4
LinksWebsite · GitHub

This document contains identifiable user data (email addresses). Handle accordingly.

Table of Contents

  1. 1. Executive Summary
  2. 2. Scope & Approach
  3. 3. Compute & Pricing Analysis
  4. 4. Query Efficiency Analysis
  5. 5. Storage & Lifecycle Analysis
  6. 6. Findings & Action Items
  7. 7. Appendix & Glossary

1. Executive Summary

◆
Project demo-sandbox (region region-us) processed 7,948 TiB in 30 days at an on-demand compute cost of $49.7K/mo, averaging 0 slots. The dominant cost driver is storage tco optimization. On-demand compute costs $49.7K/mo. Editions projection is $47.7K/mo (1.0× on-demand). Measured slot consumption: ~0 average slots, break-even at 1,134 slots (Enterprise $0.06/slot-hr). Quantified savings across all findings total $2.5K/mo (5% of in-scope spend, findings may overlap). Full quantification: §6.

Financial Baseline & Reconciliation

MeasureValueDerivation
Bytes billed (30d)7,948 TiBSUM(total_bytes_billed) from top_spenders
On-demand compute cost$49.7K/moTiB × $6.25/TiB ❌ Variance: 85.4%
Average slots0SUM(total_slot_ms)/3.6e6 ÷ 730h
Editions cost (3yr rate)$47.7K/moSlot simulation (3yr commit baseline + PAYG autoscale)
Storage cost$3.3K/moActive + long-term, by billing model
Total in-scope spend$52.9K/moCompute + storage
⚖
On-demand cost derives from SUM(total_bytes_billed) × $6.25/TiB. Every savings figure in this report is expressed as a percentage of one of these baselines.
$49.7K
On-Demand Cost/mo
$47.7K
Editions Cost/mo (3yr)
$2.5K
Modelled Savings/mo (may overlap)
16
Findings Triggered

Priority Actions

  1. ▲ Critical 81 ID-11: Storage TCO Optimization — $1.7K/mo (54% of storage)
  2. ● Medium 31 ID-19: Time-Travel Window Reduction Opportunity — $371.44/mo (12% of storage)
  3. ○ Info 8 ID-16: Long-Term Storage Discount Forfeiture — $45.07/mo (1% of storage)
⏱
This assessment reflects 30 days of INFORMATION_SCHEMA.JOBS data. Pricing rates are as of the assessment date and vary by region. Re-measure at 30/60/90 day intervals to track progress.

Estimates are non-binding modelling based on historical usage. Savings de-overlapped by lever — see §6.

2. Scope & Approach

This assessment evaluates BigQuery spend, architecture, and operational risk across four core FinOps dimensions using 30-day execution telemetry from INFORMATION_SCHEMA and Cloud Billing.

Analysis Dimensions

Dimension What We Evaluate Primary Telemetry & Signals Key Cost & FinOps Levers
Compute Model & Capacity Sizing Alignment between pricing tier (On-Demand vs. Editions) and actual slot consumption profile. total_slot_ms, baseline vs. peak slot ratios, idle slot availability. Edition selection (Standard/Enterprise), slot commitments, idle slot sharing across reservations.
Query Execution & Efficiency Query anti-patterns, redundant data scans, and compute inefficiencies. total_bytes_billed, column pruning rate, SELECT * frequency, 10 MB floor impact. Partitioning/clustering pruning, Materialized Views, BI Engine acceleration.
Storage Architecture & Lifecycle Economics Storage billing models, compression efficiencies, and backup overhead. Logical vs. physical byte ratios, table churn (>0.5), Time-Travel overhead. Physical storage billing adoption, 48h Time-Travel limit, zero-copy Table Snapshots, dataset TTLs.
Governance, Attribution & Risk Control Spend visibility, project guardrails, and uncontrolled cost exposure. Custom quota configurations, max_bytes_billed settings, reservation assignments. Execution vs. storage project decoupling, custom user/project quotas, budget alerts.

Detailed Dimension Breakdown

1

Compute Model & Capacity Sizing

Scope: Evaluates whether compute capacity matches workload patterns and if the pricing structure minimizes unit cost.

Focus Areas: Comparing On-Demand ($6.25/TiB) against Editions slot-hour pricing; tuning baseline versus autoscaling slot limits; locking in 1-year or 3-year commitments for sustained compound usage; and consolidating workloads under shared Admin Projects to leverage idle slot sharing.

2

Query Execution & Efficiency

Scope: Targets query execution anti-patterns that unnecessarily inflate scanned byte counts or slot consumption.

Focus Areas: Eliminating column-pruning failures (SELECT *); enforcing required partition filters (require_partition_filter); deploying Materialized Views for recurring aggregations; and assigning BI Engine capacity to eliminate scan costs on cached BI queries.

3

Storage Architecture & Lifecycle Economics

Scope: Optimizes dataset storage pricing models, table backup strategies, and lifecycle management.

Focus Areas: Migrating high-compression datasets to Physical Storage Billing; replacing full copy tables with zero-copy Table Snapshots; reducing Time-Travel retention from 7 days to 2 days on high-churn tables; and configuring automated partition and table expirations (TTLs).

4

Governance, Attribution & Risk Control

Scope: Establishes organizational controls to prevent runaway costs and enable spend attribution.

Focus Areas: Decoupling data storage projects from execution billing projects; applying project-level custom quotas and max_bytes_billed query flags; mapping reservation assignments to business units; and configuring programmatic budget alerts.

Data Sources & Assessment Limitations

  • Environment Scope: Target project demo-sandbox in region region-us, managed via administrative project demo-admin.
  • Primary Telemetry: region-us.INFORMATION_SCHEMA.JOBS analyzing 30 days of execution metadata (retained natively up to 180 days).
  • Reservation Attribution: Jobs billed under slot reservations report total_bytes_billed = 0 and are evaluated via slot-hour consumption (total_slot_ms).
  • Regional Isolation: Metadata is strictly region-scoped; jobs or datasets located outside region-us are excluded from this baseline.
  • Principal Aggregation: Top spender metrics are aggregated per service account or user principal; identical queries executed across multiple callers are attributed to the initiating principal identity.

3. Compute & Pricing Analysis

Pricing Model Comparison

ModelEstimated Monthly Cost (30-day baseline)Rate
On-Demand$49.7K$6.25/TiB
Editions (Projected)$47.7K$0.06/slot-hr

Break-Even Analysis

At $49.7K/mo on-demand, BigQuery Editions becomes cheaper only if sustained average slot consumption stays below:

TierRateBreak-Even (slots)
Enterprise PAYG$0.06/slot-hr1,134 slots
Enterprise 1yr commit$0.048/slot-hr1,418 slots
Enterprise 3yr commit$0.036/slot-hr1,890 slots
Standard PAYG$0.04/slot-hr1,701 slots

Measured average consumption: ~0 slots

➖ Comparable costs. Evaluate based on performance and governance needs.

Tiered Slot Recommendations

ReservationBalanced P95Autoscale MaxPerf. Max
default-or-on-demand86501610013400
default-pipeline80062005150
demo-admin:us-east4.res-default255054504550
demo-admin:us-east4.res-standard-sandbox105021001750

4. Query Efficiency Analysis

Top Spenders

UserBytes BilledCost (USD)QueriesBilling Model
gcp-prd-svc-composer-dags01@prd-data-platform.iam.gserviceaccount.com1,690.2 TB$12.5K142,500On-Demand
svc-looker-dashboard@prd-bi-serving.iam.gserviceaccount.com56.4 TB$11.6K345,000Reservation (slot-attributed)
dbt-cloud-runner@prd-analytics-core.iam.gserviceaccount.com1,495.7 TB$8.7K58,400On-Demand
svc-dataproc-worker@prd-mlops.iam.gserviceaccount.com0 B$6.9K31,200Reservation (slot-attributed)
danny.rand@mbettan.com1,055.5 TB$6.0K2,450On-Demand
svc-fivetran-loader@prd-raw-vault.iam.gserviceaccount.com58.7 TB$3.0K210,000Reservation (slot-attributed)
wade.wilson@mbettan.com527.8 TB$3.0K1,850On-Demand
svc-reverse-etl@prd-crm-sync.iam.gserviceaccount.com331.6 TB$3.0K94,000On-Demand
riri.williams@mbettan.com419.2 TB$2.8K4,120On-Demand
svc-kafka-stream-loader@prd-ingestion.iam.gserviceaccount.com3.5 TB$2.5K520,000Reservation (slot-attributed)
shuri.udaku@mbettan.com67.3 TB$2.2K3,200Reservation (slot-attributed)
svc-customer-portal-api@prd-backend.iam.gserviceaccount.com367.7 TB$2.2K185,000On-Demand
peter.parker@mbettan.com384.8 TB$2.2K1,420On-Demand
thor.odinson@mbettan.com34.6 TB$1.8K2,900Reservation (slot-attributed)
yondu.udonta@mbettan.com285.9 TB$1.6K980On-Demand
svc-dataform-compiler@prd-pipelines.iam.gserviceaccount.com129.5 TB$1.5K42,000On-Demand
svc-security-audit@prd-infosec.iam.gserviceaccount.com0 B$1.4K15,400Reservation (slot-attributed)
remy.lebeau@mbettan.com17.6 TB$1.4K3,800Reservation (slot-attributed)
svc-marketing-attribution@prd-growth.iam.gserviceaccount.com196.3 TB$1.2K28,000On-Demand
svc-tableau-connector@prd-reporting.iam.gserviceaccount.com12.8 TB$1.1K89,000Reservation (slot-attributed)
Showing top 20 of 50 — re-run this module in the app for full results.
Billing Model is inferred: if cost reconciles to bytes×$6.25/TiB ±15%, it is On-Demand. Service accounts with zero bytes billed but real cost are slot-attributed (Reservation).

Query Optimization Issues

Abuse TypeUserBilled GBQuery Snippet
[SELECT * ABUSE]erik.lehnsherr@mbettan.com11,000SELECT * FROM `prd-core-data.analytics.fact_transactions` WHERE event_date > '2026-01-01'
[SELECT * ABUSE]valkyrie.brunnhilde@mbettan.com9,500SELECT * FROM `stg-core-data.warehouse.dim_customers` LEFT JOIN `stg-core-data.warehouse.dim_address...
[SELECT * ABUSE]hank.mccoy@mbettan.com6,900SELECT * FROM `prd-core-data.dw.fact_sessions` WHERE session_date BETWEEN '2026-01-01' AND '2026-06-...
[SELECT * ABUSE]monica.rambeau@mbettan.com5,800SELECT * FROM `stg-example-data.staging.orders_raw` ORDER BY created_at DESC LIMIT 1000
[SELECT * ABUSE]heimdall.gatekeeper@mbettan.com4,000SELECT * FROM `stg-core-data.warehouse.dim_customers` LEFT JOIN `stg-core-data.warehouse.dim_address...
[SELECT * ABUSE]luke.cage@mbettan.com4,000SELECT * FROM `stg-example-data.staging.orders_raw` ORDER BY created_at DESC LIMIT 1000
[SELECT * ABUSE]michelle.jones@mbettan.com3,900SELECT * FROM `prd-core-data.raw_events.clickstream_v2` WHERE _PARTITIONDATE = CURRENT_DATE()
[SELECT * ABUSE]kamala.khan@mbettan.com3,600SELECT * FROM `prd-core-data.logs.audit_log_v3` WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(),...
[SELECT * ABUSE]ben.grimm@mbettan.com3,300SELECT * FROM `prd-core-data.finance.gl_journal_entries` WHERE posting_date >= '2026-06-01'
[SELECT * ABUSE]clint.barton@mbettan.com3,300SELECT * FROM `stg-core-data.warehouse.dim_customers` LEFT JOIN `stg-core-data.warehouse.dim_address...
[SELECT * ABUSE]valkyrie.brunnhilde@mbettan.com3,200SELECT * FROM `stg-core-data.warehouse.dim_customers` LEFT JOIN `stg-core-data.warehouse.dim_address...
[SELECT * ABUSE]katy.chen@mbettan.com3,100SELECT * FROM `stg-core-data.marketing.campaign_impressions` WHERE campaign_id IN (101, 205, 307)
[SELECT * ABUSE]shang.chi@mbettan.com3,100SELECT * FROM `prd-core-data.analytics.fact_transactions` WHERE event_date > '2026-01-01'
[SELECT * ABUSE]matt.murdock@mbettan.com3,000SELECT * FROM `stg-example-data.staging.orders_raw` ORDER BY created_at DESC LIMIT 1000
[SELECT * ABUSE]hope.vandyne@mbettan.com3,000SELECT * FROM `prd-core-data.analytics.fact_transactions` WHERE event_date > '2026-01-01'
[SELECT * ABUSE]peter.quill@mbettan.com3,000SELECT * FROM `prd-core-data.analytics.fact_transactions` WHERE event_date > '2026-01-01'
[SELECT * ABUSE]marc.spector@mbettan.com2,800SELECT * FROM `stg-example-data.staging.orders_raw` ORDER BY created_at DESC LIMIT 1000
[SELECT * ABUSE]clint.barton@mbettan.com2,800SELECT * FROM `stg-core-data.marketing.campaign_impressions` WHERE campaign_id IN (101, 205, 307)
[SELECT * ABUSE]hope.vandyne@mbettan.com2,800SELECT * FROM `stg-example-data.staging.orders_raw` ORDER BY created_at DESC LIMIT 1000
[SELECT * ABUSE]bruce.banner@mbettan.com2,800SELECT * FROM `prd-core-data.logs.audit_log_v3` WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(),...
Showing top 20 of 326 — re-run this module in the app for full results.

Batch Candidates

WorkloadSlot HoursJob RunsPriority
analytics.fct_orders_daily186.44412BATCH
erp_fact_load142.09286BATCH
bi_scripts_exec_top_articles98.3194BATCH
etl-loader@demo-sandbox.iam.gserviceaccount.com76.851,204BATCH
Scheduled Query Pipeline41.22168BATCH
marketing_attribution22.6077BATCH
Looker Studio Dashboard18.943,120INTERACTIVE
ned.leeds@mbettan.com9.37486INTERACTIVE
Connected Sheets User3.18212INTERACTIVE

Cost Attribution

ProjectReservationDirect CostTotal Attributed
pr-analytics-prodprod-reservation$12.5K$14.0K
pr-data-warehouseprod-reservation$8.5K$9.5K
sbx-sandbox-envdev-reservation$250.25$1.1K

5. Storage & Lifecycle Analysis

Storage Analysis

DatasetCurrent ModelRecommendedMonthly CostSavings
dataset_demo_038physicallogical$1.3K$814.44
dataset_demo_044physicallogical$230.98$127.75
dataset_demo_047physicallogical$225.55$114.55
dataset_demo_245physicallogical$121.43$71.38
dataset_demo_189logicalphysical$71.84$73.05
dataset_demo_213logicalphysical$57.15$47.03
dataset_demo_065logicalphysical$43.93$27.37
dataset_demo_080physicallogical$63.39$21.83
dataset_demo_181logicalphysical$55.66$24.83
dataset_demo_148logicalphysical$25.91$16.41
dataset_demo_199physicallogical$138.28$15.48
dataset_demo_211logicalphysical$50.39$19.34
dataset_demo_190logicalphysical$20.16$16.75
dataset_demo_188physicallogical$16.23$11.72
dataset_demo_233logicalphysical$21.85$11.55
dataset_demo_217logicalphysical$17.81$11.51
dataset_demo_124logicalphysical$23.18$10.09
dataset_demo_237logicalphysical$12.53$8.10
dataset_demo_198logicalphysical$8.65$5.33
dataset_demo_232logicalphysical$8.99$4.81
Showing top 20 of 234 — re-run this module in the app for full results.
442 datasets analyzed; 208 internal/duplicate datasets filtered. Showing 234 unique datasets.

Static Audit

TableDatasetRiskPartitionedClusteredSizeIssue
LND_PLAN_CURLND_ARCHIVE_HISTCritical RiskFalseFalse79.3 TB⚠️ Partition + require_filter
TBL_SNAPSHOT_CURLND_EVENT_CURCritical RiskFalseFalse76.6 TB⚠️ Partition + require_filter
PREP_REFERRAL_CURTBL_ASSETCritical RiskFalseFalse54.1 TB⚠️ Partition + require_filter
ROLLUP_REFUNDTBL_POSTINGCritical RiskFalseFalse45.8 TB⚠️ Partition + require_filter
TBL_CATEGORY_CURLND_EVENT_CURCritical RiskFalseFalse45.4 TB⚠️ Partition + require_filter
SRC_TERMINAL_V2LND_POSTING_HISTCritical RiskFalseFalse22.4 TB⚠️ Partition + require_filter
LND_CONTRACT_CURLND_ARCHIVE_HISTCritical RiskFalseFalse17.1 TB⚠️ Partition + require_filter
LND_TICKET_CURLND_ARCHIVE_HISTCritical RiskFalseFalse17.1 TB⚠️ Partition + require_filter
SRC_FORECAST_V2LND_METRIC_HISTCritical RiskFalseFalse16.5 TB⚠️ Partition + require_filter
SRC_HISTORY_V2LND_CONTROL_HISTCritical RiskFalseFalse15.4 TB⚠️ Partition + require_filter
EXP_ADDRESS_CURtbl_session_001Critical RiskFalseFalse12.4 TB⚠️ Partition + require_filter
FCT_TICKET_HISTTBL_ASSETCritical RiskFalseFalse12.2 TB⚠️ Partition + require_filter
FCT_CONTRACT_HISTTBL_ASSETCritical RiskFalseFalse12.1 TB⚠️ Partition + require_filter
FCT_CHANNEL_HISTTBL_ASSETCritical RiskFalseFalse12.0 TB⚠️ Partition + require_filter
FCT_OPPORTUNITY_HISTTBL_ASSETCritical RiskFalseFalse12.0 TB⚠️ Partition + require_filter
FCT_LEAD_HIST-01-1026TBL_ASSETCritical RiskFalseFalse12.0 TB⚠️ Partition + require_filter
SRC_VENDOR_V2LND_INVOICE_CURCritical RiskFalseFalse11.2 TB⚠️ Partition + require_filter
TBL_LOOKUP_V2CORE_CATALOG_HISTCritical RiskFalseFalse10.6 TB⚠️ Partition recommended
FEED_PRODUCT_DLYLND_USAGE_HISTCritical RiskFalseFalse10.5 TB⚠️ Partition recommended
PREP_NOTIFICATION_CURTBL_ASSETCritical RiskFalseFalse10.3 TB⚠️ Partition recommended
Showing top 20 of 121 — re-run this module in the app for full results.

Table Hygiene

TableDatasetActive GBHealthChurn Ratio
table_product_tmpdataset_demo_0382,975.00High Churn/Recreate Detected0.72
table_snapshot_extdataset_demo_044392.57High Churn/Recreate Detected0.78
table_lookup_extdataset_demo_0030.00High Churn/Recreate Detected0.45
table_archive_extdataset_demo_0030.00High Churn/Recreate Detected0.88
table_order_tmpdataset_demo_0030.00High Churn/Recreate Detected0.68
table_history_extdataset_demo_0030.00High Churn/Recreate Detected0.32
table_customer_tmpdataset_demo_026456.28High Churn/Recreate Detected0.65
table_control_extdataset_demo_0030.00High Churn/Recreate Detected0.59
table_staging_extdataset_demo_0030.00High Churn/Recreate Detected0.83
SRC_REFERRAL_V2dataset_demo_2211,445.00Healthy0.11
REF_FULFILMENT_V2dataset_demo_199567.47High Churn/Recreate Detected0.75
CORE_BASKETdataset_demo_044127.50High Churn/Recreate Detected0.48
TBL_LOYALTYdataset_demo_078115.37High Churn/Recreate Detected0.92
REF_CARRIER_V2dataset_demo_0631,615.00Healthy0.02
SRC_NOTIFICATION_V2dataset_demo_222487.62High Churn/Recreate Detected0.36
REF_CHANNEL_V2dataset_demo_0632,295.00Healthy0.05

Time Travel Reduction

DatasetBillingCurrent Window (d)TT (GiB)Savings ($/mo)DDL
dataset_demo_003PHYSICAL74,200.50$120.01ALTER SCHEMA `prd-core-data.dataset_demo_003` SET OPTIONS(default_time_travel_hours = 48);
dataset_demo_038PHYSICAL73,100.00$88.57ALTER SCHEMA `prd-core-data.dataset_demo_038` SET OPTIONS(default_time_travel_hours = 48);
dataset_demo_012PHYSICAL71,800.00$51.43ALTER SCHEMA `stg-core-data.dataset_demo_012` SET OPTIONS(default_time_travel_hours = 48);
dataset_demo_009LOGICAL7900.00$0.00ALTER SCHEMA `dev-core-data.dataset_demo_009` SET OPTIONS(default_time_travel_hours = 48);
dataset_demo_044PHYSICAL72,500.00$71.43ALTER SCHEMA `prd-example-data.dataset_demo_044` SET OPTIONS(default_time_travel_hours = 48);
dataset_demo_027PHYSICAL71,400.00$40.00ALTER SCHEMA `stg-example-data.dataset_demo_027` SET OPTIONS(default_time_travel_hours = 48);

Shard Consolidation

Table PrefixDatasetShardsPhysical (GiB)Risk
events_dataset_demo_00318258,400.00HIGH — Table Rebuild
ga_sessions_dataset_demo_0387303,200.00HIGH — Table Rebuild
user_activity_dataset_demo_0123651,950.00HIGH — Table Rebuild
clickstream_dataset_demo_044960015,600.00HIGH — Table Rebuild
raw_imports_dataset_demo_009180540.00HIGH — Table Rebuild

6. Findings & Action Items

Findings Register

Sorted by deterministic score (descending). Score = (Impact + Risk) × Confidence × Effort × 5, capped at 100.

RefFindingPillarLeverScore PriorityImpact $/mo% of BaselineConf.EffortHorizon
ID-11Storage TCO OptimizationStorageRate81▲ Critical$1.7K54% of storageHLowM1
ID-19Time-Travel Window Reduction OpportunityStorageRate31● Medium$371.4412% of storageHLowM1
ID-16Long-Term Storage Discount ForfeitureStorageRate8○ Info$45.071% of storageMMediumQ1
ID-09Unpartitioned TablesStorageUsage6○ Info——HLowQ1
ID-10Stale / Orphan TablesStorageWaste6○ Info——HLowM1
ID-01Pricing Model OptimizationPricingRate5○ Info——HMediumM1
ID-20Date-Sharded Tables → Partitioned TablesStorageUsage5○ Info——HMediumQ1
ID-05Batch-Eligible WorkloadsQueryUsage5○ Info$35.940% of computeMLowM1
ID-03Micro-Query BatchingQueryUsage4○ Info$182.370% of computeMMediumQ1
ID-06DML Abuse PatternsQueryUsage4○ Info——MMediumQ1
ID-08Materialized View CandidatesQueryUsage4○ Info——MMediumQ1
ID-13HBO Optimization OpportunitiesQueryUsage4○ Info——MMediumM1
ID-1710 MB Minimum Billing Floor ImpactQueryUsage4○ Info$152.340% of computeMMediumQ1
ID-18Time-Travel & Fail-Safe Overhead on Physical BillingStorageRate3○ Info——LLowM1
ID-14Performance Resource WarningsQueryUsage2○ Info——LMediumQ1
ID-07Slot Skew / Hot SpotsQueryUsage2○ Info——LHighQ1

Consolidated Savings Summary

FindingLeverPriorityEst. Savings/moConf.EffortHorizon
ID-11: Storage TCO OptimizationRate▲ Critical$1.7KHighLowM1
ID-19: Time-Travel Window Reduction OpportunityRate● Medium$371.44HighLowM1
ID-16: Long-Term Storage Discount ForfeitureRate○ Info$45.07MediumMediumQ1
ID-03: Micro-Query Batching †Usage○ Info$182.37MediumMediumQ1
ID-17: 10 MB Minimum Billing Floor Impact †Usage○ Info$152.34MediumMediumQ1
ID-05: Batch-Eligible WorkloadsUsage○ Info$35.94MediumLowM1
Sum total (may overlap)$2.5K
⚖
Rate and Usage savings compound multiplicatively, not additively. Findings sharing an affected-resource set are counted once at the larger estimate.
⚖
† Findings marked with † share the same underlying waste (billing-floor micro-queries). Do not sum their savings independently.

Detailed Findings

ID-11 81 ▲ Critical Storage Rate Effort: Low Conf: High M1

Storage TCO Optimization

CONDITIONLogical-to-physical storage ratio exceeds 2× on some datasets, indicating potential savings from physical billing.
EVIDENCE442 datasets with savings potential totalling $1.7K/mo. Top: dataset_demo_038 ($814.44); dataset_demo_038 ($159.43); dataset_demo_044 ($127.75)
CONSEQUENCE$1.7K/mo (54% of storage baseline)
RECOMMENDATIONSwitch eligible datasets to physical storage billing model. Note: reduce max_time_travel_hours to 48h first to minimize time-travel charges. There is a 14-day cooldown between billing-model changes.
Documentation →
ID-19 31 ● Medium Storage Rate Effort: Low Conf: High M1

Time-Travel Window Reduction Opportunity

CONDITIONDatasets with the default 7-day time-travel window can often be safely reduced to 2 days, reclaiming significant physical storage on high-churn tables.
EVIDENCE6 datasets with reducible time-travel windows, total savings $371.44/mo. Top: dataset_demo_003 ($120.01; dataset_demo_038 ($88.57; dataset_demo_044 ($71.43
CONSEQUENCE$371.44/mo (12% of storage baseline)
RECOMMENDATIONReduce max_time_travel_hours to 48h on eligible datasets via ALTER SCHEMA DDL. Review each dataset's recovery requirements before applying.
Documentation →
ID-16 8 ○ Info Storage Rate Effort: Medium Conf: Medium Q1

Long-Term Storage Discount Forfeiture

CONDITIONTables with high churn ratios (>0.5) reset the 90-day timer for long-term storage pricing, doubling the rate from $0.01 to $0.02/GiB/mo.
EVIDENCE5 tables with churn >0.5 totalling 4,507 GB. LTS penalty: ~$45.07/mo. Top: table_product_tmp (2,975 GB, churn 0.72); REF_FULFILMENT_V2 (567 GB, churn 0.75); table_customer_tmp (456 GB, churn 0.65)
CONSEQUENCE$45.07/mo (1% of storage baseline)
RECOMMENDATIONInvestigate high-churn tables: are the UPDATE/MERGE operations necessary? Consider append-only patterns with periodic compaction.
Documentation →
ID-09 6 ○ Info Storage Usage Effort: Low Conf: High Q1

Unpartitioned Tables

CONDITIONActive Assist recommends partitioning for one or more large tables.
EVIDENCE121 unpartitioned tables totalling 811.4 TB (121 also unclustered). Top: LND_PLAN_CUR (79.3 TB); TBL_SNAPSHOT_CUR (76.6 TB); PREP_REFERRAL_CUR (54.1 TB); ROLLUP_REFUND (45.8 TB); TBL_CATEGORY_CUR (45.4 TB)
AFFECTEDLND_PLAN_CUR, TBL_SNAPSHOT_CUR, PREP_REFERRAL_CUR, ROLLUP_REFUND, TBL_CATEGORY_CUR
RECOMMENDATIONPartition tables by ingestion time or a date/timestamp column. Enforce require_partition_filter = true on large tables to prevent full-table scans.
Documentation →
ID-10 6 ○ Info Storage Waste Effort: Low Conf: High M1

Stale / Orphan Tables

CONDITIONTables with zero reads in the past 90+ days may be candidates for archival or deletion.
RECOMMENDATIONSet defaultTableExpirationMs at dataset level. For compliance data, archive to GCS Coldline/Archive tier.
Documentation →
ID-01 5 ○ Info Pricing Rate Effort: Medium Conf: High M1

Pricing Model Optimization

CONDITIONOn-demand compute costs $49.7K/mo. Editions projection is $47.7K/mo (1.0× on-demand). Measured slot consumption: ~0 average slots, break-even at 1,134 slots (Enterprise $0.06/slot-hr).
RECOMMENDATIONRemain on On-Demand pricing. Editions becomes cheaper only when sustained average slot consumption drops below 1,134 slots (Enterprise) or 1,701 slots (Standard). Focus savings on demand-side optimization (see query findings below).
Documentation →
ID-20 5 ○ Info Storage Usage Effort: Medium Conf: High Q1

Date-Sharded Tables → Partitioned Tables

CONDITIONDate-sharded table families (e.g. events_20240101, events_20240102, …) bypass partition pruning, require wildcard queries, and inflate metadata overhead compared to a single ingestion-time partitioned table.
EVIDENCE5 date-sharded table families with 12,700 total shards (29,690.0 GiB). Top: clickstream__* (9600 shards); events__* (1825 shards); ga_sessions__* (730 shards)
RECOMMENDATIONConsolidate each sharded family into a single partitioned table using bq cp --append or a scheduled MERGE pipeline. Retain the shards briefly for rollback, then delete.
Documentation →
ID-05 5 ○ Info Query Usage Effort: Low Conf: Medium M1

Batch-Eligible Workloads

CONDITIONSome workloads are candidates for batch scheduling, reducing peak slot usage.
EVIDENCE9 batch candidates, 599 total slot-hours (≈$35.94/mo at Editions rates). Top: analytics.fct_orders_daily (186 slot-hrs); erp_fact_load (142 slot-hrs); bi_scripts_exec_top_articles (98 slot-hrs)
CONSEQUENCE$35.94/mo (0% of compute baseline)
RECOMMENDATIONSchedule non-urgent queries using BigQuery batch priority or Cloud Scheduler.
Documentation →
ID-03 4 ○ Info Query Usage Effort: Medium Conf: Medium Q1

Micro-Query Batching

CONDITIONA high rate of very small queries suggests potential for batching or consolidation.
EVIDENCETop micro-query sources: svc-monitoring-probe@ops-sre.iam.gserviceaccount.com (640,000 queries); svc-kafka-stream-loader@prd-ingestion.iam.gserviceaccount.com (520,000 queries); svc-looker-dashboard@prd-bi-serving.iam.gserviceaccount.com (345,000 queries). Billing floor waste: ~29.2 TB / $182.37/mo.
CONSEQUENCE$182.37/mo (0% of compute baseline)
AFFECTEDsvc-monitoring-probe@ops-sre.iam.gserviceaccount.com, svc-kafka-stream-loader@prd-ingestion.iam.gserviceaccount.com, svc-looker-dashboard@prd-bi-serving.iam.gserviceaccount.com, svc-datadog-metrics-probe@ops-monitoring.iam.gserviceaccount.com, svc-fivetran-loader@prd-raw-vault.iam.gserviceaccount.com
RECOMMENDATIONBatch micro-queries using scripting, scheduled queries, or multi-statement transactions.
Documentation →
ID-06 4 ○ Info Query Usage Effort: Medium Conf: Medium Q1

DML Abuse Patterns

CONDITIONFrequent single-row DML operations detected, which are inefficient in BigQuery.
RECOMMENDATIONBatch DML operations using MERGE or streaming inserts.
Documentation →
ID-08 4 ○ Info Query Usage Effort: Medium Conf: Medium Q1

Materialized View Candidates

CONDITIONRepeated aggregation patterns detected that would benefit from materialized views.
RECOMMENDATIONCreate materialized views for frequently-run aggregation queries.
Documentation →
ID-13 4 ○ Info Query Usage Effort: Medium Conf: Medium M1

HBO Optimization Opportunities

CONDITIONHistory-based optimization identifies queries that could benefit from optimized execution plans.
RECOMMENDATIONReview HBO recommendations and enable for qualifying workloads.
Documentation →
ID-17 4 ○ Info Query Usage Effort: Medium Conf: Medium Q1

10 MB Minimum Billing Floor Impact

CONDITIONBigQuery bills a minimum of 10 MB per table referenced per query. High-frequency micro-queries accumulate significant billing-floor waste.
EVIDENCE8 principals with >100K queries. 2,437,500 queries × 10 MB floor ≈ 24.4 TB of pure billing-floor waste (≈$152.34/mo). Top: svc-monitoring-probe@ops-sre.iam.gserviceaccount.com (640,000 queries); svc-kafka-stream-loader@prd-ingestion.iam.gserviceaccount.com (520,000 queries); svc-looker-dashboard@prd-bi-serving.iam.gserviceaccount.com (345,000 queries)
CONSEQUENCE$152.34/mo (0% of compute baseline)
RECOMMENDATIONConsolidate micro-queries via multi-statement transactions, MERGE, or batch pipelines.
Documentation →
ID-18 3 ○ Info Storage Rate Effort: Low Conf: Low M1

Time-Travel & Fail-Safe Overhead on Physical Billing

CONDITIONPhysical storage billing charges for time-travel (up to 7d) and fail-safe (7d) bytes. High-churn datasets on physical billing may negate the compression savings.
EVIDENCE1 datasets recommended for physical billing have high-churn tables. Time-travel/fail-safe bytes may negate savings. Affected: dataset_demo_038
RECOMMENDATIONReduce max_time_travel_hours to 48h on high-churn datasets before switching to physical billing. Note the 14-day cooldown between billing model changes.
Documentation →
ID-14 2 ○ Info Query Usage Effort: Medium Conf: Low Q1

Performance Resource Warnings

CONDITIONResource warnings indicate queries that spill to disk or exceed memory targets.
RECOMMENDATIONOptimise flagged queries to reduce memory pressure and avoid spill-to-disk.
Documentation →
ID-07 2 ○ Info Query Usage Effort: High Conf: Low Q1

Slot Skew / Hot Spots

CONDITIONSome queries exhibit significant data skew, causing uneven slot utilization.
RECOMMENDATIONInvestigate partition pruning, clustering, and query restructuring.
Documentation →

Checks Passed / Not Applicable

RefCheckStatus
ID-02Capacity Ceiling Risk✅ Passed
ID-04SELECT * Anti-Patterns✅ Passed
ID-12Cost Guardrails Missing✅ Passed
ID-15Manual Backup Tables → Zero-Copy Snapshots✅ Passed

Implementation Roadmap

Week 1
Quick wins: Configure custom quotas, set max_bytes_billed on critical jobs.
Month 1
Demand reduction: ID-19: Time-Travel Window Reduction Opportunity, ID-10: Stale / Orphan Tables, ID-01: Pricing Model Optimization, ID-05: Batch-Eligible Workloads, ID-13: HBO Optimization Opportunities, ID-18: Time-Travel & Fail-Safe Overhead on Physical Billing, ID-11: Storage TCO Optimization
Quarter 1
Structural optimizations: ID-16: Long-Term Storage Discount Forfeiture, ID-09: Unpartitioned Tables, ID-20: Date-Sharded Tables → Partitioned Tables, ID-03: Micro-Query Batching, ID-06: DML Abuse Patterns, ID-08: Materialized View Candidates, ID-17: 10 MB Minimum Billing Floor Impact, ID-14: Performance Resource Warnings, ID-07: Slot Skew / Hot Spots
Ongoing
Re-measure: Track KPIs (TiB billed/mo, $/TiB, avg slots). Reassess pricing model at 30/60/90 day intervals.

7. Appendix & Glossary

Glossary of Terms

TermDefinition
SlotUnit of BigQuery compute capacity (~0.5 vCPU + RAM). Queries consume slots proportional to data scanned and complexity.
Slot-hour1 slot sustained for 1 hour. The billing unit for Editions pricing.
Bytes billedData scanned after partition/column pruning, rounded up to a 10 MB minimum per table referenced.
On-DemandPay-per-query at $6.25/TiB billed. No commitment; 2,000 concurrent slot cap per project.
EditionsSlot-based pricing. Enterprise: $0.06/slot-hr (PAYG), $0.048 (1yr), $0.036 (3yr). Standard: $0.04/slot-hr. Autoscale + baseline split.
Logical billingStorage charged on uncompressed logical size. Default for new datasets.
Physical billingStorage charged on compressed physical size + time-travel + fail-safe bytes. Often cheaper for compressed data.
Churn ratioFraction of table bytes rewritten per period via DML. High churn resets the 90-day long-term storage discount timer.

Scoring Methodology

Each finding is assigned a deterministic score using the formula:

Score = (Impact + Risk) × Confidence × Effort × 5, capped at 100

Priority bands: Critical ≥ 75, High ≥ 50, Medium ≥ 25, Low ≥ 10, Info < 10.

Report Generation

This assessment was generated by FinOps Optimizer v1.4.4. All telemetry data was queried directly from BigQuery INFORMATION_SCHEMA views for project demo-sandbox in region region-us.

AI / LLM Usage: Vertex AI (Gemini) was utilized exclusively for the semantic query rewrites in Section 4 (AI Doctor). All other calculations, financial projections, rule-based heuristics, and scorecards were computed deterministically by local algorithms.

Disclaimer

Not a Google Product: This tool is an independent, personal open-source project (Apache 2.0) and is not affiliated with, endorsed by, or supported by Google.

Cost Modeling & Verification: All costs and savings figures presented in this assessment are mathematical projections calculated using regional Google Cloud public list prices ($6.25/TiB on-demand, $0.06/slot-hour Editions baseline). They do not reflect enterprise contractual discounts, Custom Pricing Agreements (CPAs), or Committed Use Discounts (CUDs). Always verify findings against the Google Cloud Billing Console prior to making architectural or contractual commitments.

Execution & Safety: Provided "AS IS", without warranty of any kind. All recommended DDL queries, partition modifications, and table alterations should be thoroughly tested and validated in non-production environments first.