How I Reduced Cloud Costs by 70%

A deep dive into architecting an automated BigQuery Health Checkup & Query Optimization Framework that scans code repositories, analyzes table metadata, and enforces cost-saving rules to cut analytics spending by 70%.

The Cost Optimization Challenge

At petabyte scale, uncontrolled BigQuery usage leads to massive, invisible cost spikes. Rather than manually chasing down inefficient queries after the bill arrived, I proposed and architected an automated GBQ Query Optimization Framework. This proactive system analyzes all derived-table queries stored in our Bitbucket repositories to evaluate query efficiency, table usage, partitioning, and overall data design.

Automated Discovery & Inventory

To ensure complete coverage, the framework begins at the source code level:

• Source Code Discovery: Reads direct SQL queries and procedure call statements straight from the Bitbucket repository.
• Dynamic Procedure Resolution: For stored procedures, the backend queries INFORMATION_SCHEMA.ROUTINES to fetch the underlying definitions, parsing the actual logic instead of just the wrapper.
• Metadata Cataloging: Python scripts analyze the parsed SQL blocks to extract project IDs, dataset IDs, referenced tables, existing partitioning/clustering keys, and record counts into a centralized metadata catalog.

The 5 Core Optimization Rules

Using the cataloged metadata and INFORMATION_SCHEMA.JOBS, the framework programmatically flags unoptimized workflows across multiple dimensions:

• 1. Frequently Queried Non-Partitioned Tables: Flags non-partitioned tables larger than 50 GB that are queried heavily (> 20 times in 30 days).
• 2. Suboptimal Partition Keys: Detects tables that are partitioned, but whose partition keys (like date fields) are consistently ignored in the WHERE clauses of downstream queries.
• 3. Large Non-Partitioned Tables: Catches legacy flat tables exceeding 100 GB that urgently require partitioning.
• 4. Unfiltered Large Table Scans: Identifies queries processing massive volumes of data (e.g., > 10 TB processed) resulting in high slot_minutes without utilizing a WHERE clause.
• 5. Sharding Recommendations: Proactively flags tables approaching BigQuery limits—marking tables > 4TB as "Critical" and > 1TB as "Consider Sharding" to maintain performance.

Proactive Alerting & Governance

To make the system hands-off, I built an automated alerting engine. A master Health Check Config Table stores all rules and thresholds alongside recipient email lists. A Google Cloud Scheduler triggers a Python-based Cloud Function hourly or daily, which executes the active rules against the BigQuery metrics.

If any unoptimized queries or bloated tables are detected, the system uses the SendGrid API to instantly dispatch an HTML alert report directly to the data owners, including the specific SQL failing the check and the recommended remediation.

The Final Impact

By shifting from reactive cost-cutting to agile, automated metadata-driven governance, we achieved a 70% reduction in BigQuery costs. The programmatic alignment of Bitbucket code with BigQuery execution logs not only slashed cloud spend but drastically reduced the manual tuning workload for the database engineering team.