Skip to content

v1.4.0

Choose a tag to compare

View all tags
@mbettan mbettan released this 08 Aug 00:54
· 17 commits to main since this release
v1.4.0
b91f008
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Verified
Learn about vigilant mode.

Release v1.4.0 — BigQuery FinOps Optimizer

This Pull Request merges all changes from branch 1.4.0 into main, introducing major architectural upgrades to Top Spenders Waste & Savings Attribution, Batch vs. Interactive Concurrency Prioritization, AI Doctor Dual-Engine SQL Optimization, BigQuery Client Pooling, and Universal CSV Export.

🚀 Key Highlights & Major Features

1. Top Spenders: Actual Billing Mode, Waste & Potential Savings Engine

  • True Billing Mode Classification: Queries are partitioned by their real execution mode using reservation_id IS NOT NULL (assigned slot reservation) vs. reservation_id IS NULL (multi-tenant on-demand). Spenders receive color-coded badges: Reservation ($\ge 80%$), On-Demand ($\le 20%$), or Mixed (displaying exact percentage and query count breakdown).
  • Waste & Non-Productive Spend Detection: Introduces exact dollarized waste tracking for capacity consumed with zero business output — failed/cancelled query slot-hours & on-demand bytes plus the 10 MiB minimum-billing floor overage. Waste is mathematically bounded as a strict subset of actual spend ($W \le A$).
  • Frequency-Ranked Primary Reservations: Utilizes APPROX_TOP_COUNT to extract and rank primary reservation names, surfaced via rich tooltips on the billing mode badge.
  • Actual Cost Attribution: Computes true dollar spend by combining actual on-demand billed bytes (total_bytes_billed for OD jobs) and active slot duration (total_slot_ms for reservation jobs) without cross-contamination.
  • Actionable Potential Savings: Evaluates actual_cost - min(est_on_demand, est_editions) to highlight high-ROI optimization candidates (such as heavy on-demand pipelines that belong on a reservation). When a user is already operating on the optimal pricing model, savings cleanly display as zero/dash (—).
  • Hypothetical Cost Accuracy (hypothetical_od_bytes): Uses logical bytes scanned (total_bytes_processed) for reservation queries (which record total_bytes_billed = 0), preventing false-positive recommendations that previously suggested 100% reservation workloads could run for "$0" on-demand.
  • Multi-Statement Script Attribution: Filters on (statement_type != 'SCRIPT' OR statement_type IS NULL) instead of parent_job_id IS NULL. This ensures child statements executed by orchestrators (Airflow/Cloud Composer, dbt, Stored Procedures) inherit their real reservation_id rather than being misattributed as on-demand by the parent script wrapper.
  • Data Transparency Tooltips: When processed data exceeds billed data (due to reservation queries billing 0 bytes), hovering over the Total Data Billed cell explains the exact breakdown used for hypothetical on-demand cost calculations.
  • Executive KPIs & Sorting: Refreshed KPI cards track Active Users Analyzed, Actual Total Spend, Wasted Spend, Potential Savings, and Total Slot Hours, with default DataTables ordering pinned to Actual Cost DESC.

2. Workload-Centric Interactive vs. Batch Priority Engine

  • Lineage-Driven Workload Aggregation: The Batch Candidates scan is now workload-centric rather than job-centric. Executions are aggregated into logical workloads using lineage labels (dbt_model, airflow_dag_id/dag_id, dataform, looker/tableau/dashboard_id, requestor), the scheduled_query_ job-ID prefix, and service-account identity.
  • Concurrency Optimization: Workloads are classified as UNDER_BATCHED (automated pipelines and heavy service-account DML burning the 100-query INTERACTIVE concurrency limit that live dashboards depend on) or OVER_BATCHED (human and BI connections stuck behind a >30s BATCH queue). BATCH and INTERACTIVE bill identically (same hardware, same slot-hour and per-byte pricing), turning every finding into a pure concurrency win with zero cost penalty.
  • Copy-Pasteable Remediation Snippets: Each row carries its detection reasons, a HIGH/LOW confidence grade derived from label provenance, and a ready-to-use remediation snippet for the detected tooling: dbt profiles.yml, Airflow BigQueryInsertJobOperator, or the Python SDK / bq CLI.
  • Graceful IAM Fallback: The scan probes JOBS_BY_ORGANIZATION with a dry run and transparently falls back to JOBS_BY_PROJECT when org-level IAM is unavailable. Excludes cache hits, script child jobs, and failed jobs.

3. AI Doctor & BigQuery Migration Compiler Enhancements

  • 1-Click SQL Translator Bridge: Added ⇄ Translator button with clipboard auto-copy integration, pre-populating queries directly into BigQuery Studio Interactive Translator without triggering Google Cloud Console routing errors.
  • DML Safety Guard Modernization: Expanded AST safety guard regex to accept AI-generated UPDATE, INSERT, DELETE, MERGE, and MATERIALIZED VIEW rewrites while maintaining strict blocks on destructive commands (DROP TABLE / TRUNCATE TABLE).
  • Multi-Region Location Routing: Updated SchemaFetcher DDL discovery queries and dry-run validators to explicitly pass location=params.location (e.g. us-east4), eliminating regional mismatch failures on INFORMATION_SCHEMA.TABLES.
  • Echo Suppression: Automatically detects when the Migration Compiler echoes SQL byte-identical to the input, clearing false "Migration API Config Applied" banners.

4. Enterprise Core & Performance Upgrades

  • BigQuery Client Pooling & ADC Caching: Replaced per-request client construction with a thread-safe, process-wide BigQuery client pool (get_bq_client) with LRU eviction and single-discovery Application Default Credentials (ADC) caching. Eliminates two metadata-server HTTP round-trips and TLS handshakes per API request on Cloud Run, and prevents ~1,000 metadata queries during HBO multi-project fan-outs.
  • Universal CSV Export: Injected automated Download CSV buttons across all DataTables views with UTF-8 BOM, HTML entity decoding, and RFC 4180 escaping.
  • Storage Hygiene TTL Auditor: Surfaced each dataset's default_time_travel_days alongside time-travel physical bytes from INFORMATION_SCHEMA.SCHEMATA_OPTIONS via batched UNION ALL queries across up to 50 projects.
  • Per-Table DML Abuse Detection: Aggregated by destination table with Active Days and Avg Inserts / Day columns with a refined threshold of 100 operations/day.
  • Snapshot Privacy & Value Scrubbing: Upgraded snapshot redaction to scan and scrub email-shaped values regardless of key name across object fields and string arrays.
  • Pricing & Unit Alignment: Centralized ON_DEMAND_USD_PER_TB and EDITIONS_SLOT_HR_RATE constants and aligned Active Assist conversions to decimal TB ($10^{12}$) matching the Google Cloud Recommender API format.

🧪 Testing & Verification

  • Full Automated Test Suite: 652 tests passing (645 passed, 7 skipped).
  • Sanitization: Zero company-proprietary identifiers (wiley, atypon, pericles) or PII across the codebase.
  • Regression Tests Added:
    • tests/test_bq_client_pool.py: Verified client pooling, LRU eviction, and process-wide ADC caching.
    • tests/test_ai_doctor.py: Verified DML query acceptance and location routing.
    • tests/test_cost_attribution.py: Verified idle reservation reconciliation and date range parsing.
Assets 2
Loading