Case Study: 58% Reduction in BigQuery Costs

Company Profile

• Industry: E-commerce / Online Retail
• Location: Argentina, Chile, Colombia
• Data volume: 500 GB/day (events, transactions, inventory)
• BI users: 45 analysts, 120 dashboard users
• Platform: Google Cloud (BigQuery, Looker, Cloud Composer)

Initial Challenges

1. Uncontrolled costs: Analysts ran full-scan queries on partitioned tables, generating bills of $15,000+ per month in BigQuery. Each ad-hoc query cost between $30-$80.
2. Metric inconsistency: Each team (Marketing, Finance, Operations) defined "Revenue" differently. We found 7 different definitions of the key business metric.
3. Degraded performance: Executive dashboards took 30-45 seconds to load. Users ran the same query multiple times thinking it had failed.
4. Lack of governance: There was no control over who accessed what data. Junior analysts had full access to production tables without auditing.

Solution Architecture

We implemented a 5-layer architecture with integrated FinOps:

1. Optimized Ingestion Layer:
   • Cloud Functions for real-time events (partitioned by hour)
   • Airflow in Cloud Composer for batch ETL (nightly execution)
   • Reduced re-ingestion: from 3 times/day to 1 time/day for historical data
2. DBT Transformation Layer:
   • Incremental models for large tables (only processes new data)
   • Automatic partitioning by date
   • Data quality testing (100+ automated tests)
3. Semantic Layer in Looker:
   • 15 certified base Views (single source of truth for metrics)
   • 8 Optimized explores with selective joins
   • PDTs for pre-aggregation of executive dashboards
   • Datagroups with intelligent refresh (4h for metrics, 24h for dimensions)
4. Presentation Layer:
   • 5 executive dashboards (refresh every 4h via PDT cache)
   • 20 operational dashboards (on-demand refresh)
   • Automatic alerts for critical KPIs
5. FinOps Monitoring with AI:
   • AI agent that analyzes queries every hour
   • Automatic alerts when a query exceeds $5
   • FinOps dashboard for cost tracking by team

Quantifiable Results

Lessons Learned

• Partitioning is non-negotiable: All large tables must be partitioned from day 1. Migrating later is expensive and complex.
• Smart PDTs: Do not create PDTs for everything. Only for frequent and expensive queries. Use sql_trigger instead of max_cache_age when possible.
• Continuous education: Analysts need to understand how BigQuery costs work. We implemented monthly training sessions with real cases.
• Governance from the start: Row-level security in Looker from day 1. It is much harder to implement it later.
• Proactive monitoring: The AI agent paid for its development in the first month by detecting 3 queries that were costing $2,000/month without anyone noticing.