Cloud Cost Forensics: 95% BigQuery Cost Reduction

The Swedish marketplace company experienced sudden, unexplained explosion in cloud analytics costs:

• Massive Cost Spike: Monthly analytics costs jumped from 400 EUR to 8,000 EUR—a 20-fold increase
• Unknown Origin: Unable to identify which application or service caused the cost explosion
• Complex Infrastructure: Multiple potential sources including 15+ backend services, 20+ cloud functions, and several data pipelines
• Limited Visibility: Cloud billing showed expensive table but not the queries or their sources
• No Query Attribution: Lack of logging made tracing queries to source applications impossible
• Budget Threat: Unexpected 7,600 EUR monthly cost threatening quarterly financial targets
• Investigation Paralysis: Internal team spent 3 weeks investigating without identifying root cause

Urgent cost reduction before month-end billing:

• Identify Culprit: Find specific service or application causing cost spike
• Understand Pattern: Determine what queries are executing and why they're expensive
• Immediate Reduction: Reduce costs by minimum 80% within 4 weeks
• Sustainable Optimization: Implement long-term efficient query patterns
• Future Prevention: Add monitoring and alerting catching cost spikes early
• Code Governance: Ensure all services properly tracked in version control with deployment pipelines

This became a forensic investigation across complex distributed infrastructure. Cloud billing identified the expensive table containing user behavior tracking data, showing billions of rows scanned daily. However, the critical question remained unanswered: which service was running these expensive queries? Investigation obstacles included disabled audit logging, distributed systems with services across multiple platforms, lack of centralized monitoring, and some legacy services deployed without proper documentation. The costs accumulated at 260 EUR daily making experimentation expensive.

Executed systematic six-week investigation and optimization:

Weeks 1-2: Investigation & Pattern Analysis

• Enabled audit logging to capture future query execution metadata
• Analyzed query patterns discovering repeating structure executing every 5 minutes
• Identified queries always filtering same column and selecting identical fields
• Calculated each query scanned full table costing approximately 10 EUR per execution
• Confirmed single query pattern responsible for over 90% of total costs

Weeks 2-3: Source Identification Detective Work

• Examined connection logs to map IP addresses to infrastructure pods
• Identified pod but discovered no corresponding repository in version control
• Found service deployed via manual commands 18 months prior
• Original developer left company without knowledge transfer
• Downloaded container image from cluster and reverse-engineered to extract application logic
• Discovered service feeding data to external analytics dashboard no longer in use

Weeks 4-5: Optimization Implementation

• Implemented table partitioning strategy reducing scan size by 200-fold
• Added clustering on frequently filtered column for additional efficiency
• Modified recovered queries to include time window restriction limiting data to last 30 days
• Combined optimizations reduced query cost from 10 EUR to 0.0025 EUR—a 4,000-fold improvement
• Recovered orphaned code into version control repository
• Established proper deployment pipeline

Week 6: Governance & Prevention

• Decided to decommission obsolete service entirely eliminating costs completely
• Implemented cost monitoring dashboard with multiple threshold alerts
• Created query cost estimation tool for developers
• Established code review checklist including optimization considerations
• Documented lessons learned and best practices for team
• Set up automated budget alerts preventing future blind spots