From a struggling MySQL monolith to a high-performance, predictable-cost cloud analytics ecosystem.
As a high-growth e-commerce platform scaled its operations, our data infrastructure hit a critical bottleneck. An aging, self-hosted MySQL 5.6 Data Warehouse orchestrated by Airflow was struggling to process advanced analytical workloads. It lacked modern features, and development was grinding to a halt.
Migrating to Google Cloud’s BigQuery (BQ) was the obvious technical evolution to serve the whole organization. It eliminated our compute constraints overnight; but it introduced a new, existential threat: unchecked query costs from widespread, unoptimized organizational access.
By applying an “Eliminate First” engineering mindset, restructuring our data models, enforcing querying discipline, operationalizing ML natively, and strategically negotiating GCP compute commitments, we successfully transitioned to a high-performance, predictable-cost ecosystem.
Here is the architectural blueprint of how we did it.
Phase 1: Architectural Paradigm & Modeling Shift
Moving to BigQuery required abandoning traditional relational logic. BQ is an exceptionally powerful engine, but it heavily penalizes legacy Star Schema designs through excessive data scanning and join overhead.
- Denormalization over Complexity: We rejected buzzword-heavy architectures — no Data Vault, no Data Mesh. Instead, we migrated the core business entities into flattened, immutable tables.
- Native BQ Data Types: Complex relationships were nested directly into the core records using BQ’s native JSON, ARRAY, and STRUCT columns. This design eliminated the need for highly detailed dimensional tables and costly downstream joins.
- Orchestration Elimination: Self-hosted Airflow was progressively deprecated for foundational transformations. Data pipelines were migrated directly into BigQuery Stored Procedures, significantly reducing external infrastructure overhead.
Phase 2: Organizational Maturity & The Kaizen Philosophy
With data now accessible to business users, the BI team, and executives via multiple visualization tools, usage became chaotic. Because BQ’s on-demand pricing bills by bytes scanned, this unoptimized access directly inflated the cloud bill.
We launched an internal engineering mentorship campaign focused on minimalism and production-ready querying:
- Strict Column Projection: An absolute ban on SELECT * in production code.
- Single-Scan Strategies: We trained analysts to scan massive datasets only once, utilizing window functions and CTEs to compute multiple metrics from a single pass.
- Sampling Execution: We enforced the use of BQ table sampling for exploratory and development analysis.
Workload Tiering: We applied strict trade-offs regarding where data is processed. Final static reports were dumped as flat files directly into Google Cloud Storage (GCS). If no semantic filtering was required by the end-user, the semantic layer was bypassed entirely to avoid recurring compute charges.
Phase 3: Operationalizing ML & CEM Pipelines
The newly flattened foundational models served as the absolute ground truth for all advanced analytics and Machine Learning use cases, built directly inside BQ using AutoML.
- Stock Forecasting: We built an end-to-end ML pipeline inside BQ. We optimized resource usage by defining custom cost functions and strictly isolating regressors.
- Customer Churn: We used an inverted training architecture. Instead of user-anchored data, the model was trained on order data. The target label was: “Will this order be followed by another order within 90 days from the same user?” The output was then pivoted back to user-level metrics to identify churn probability.
- Marketing (CEM): Marketing segmentations utilized Slowly Changing Dimensions (SCDs) updated dynamically in BQ. This ensured real-time engagement data without requiring us to re-process entire historical tables.
Code delivery was streamlined via a tightly parameterized CI/CD pipeline. Each module served a single business entity, allowing targeted in-place refills and backfills without executing the entire pipeline dependency graph.
Phase 4: Vendor Handling & Capacity Lock-in
Despite engineering optimization, the sheer volume of new users and use cases caused costs to spike again. Optimizing queries per terabyte was no longer mathematically viable on an On-Demand plan.
- Commercial Negotiation: We transitioned to BigQuery Enterprise with a 3-year commitment, securing a 60% reduction on the baseline quote.
- Slot Benchmarking Module: Enterprise BQ relies on capacity (Slots) rather than bytes scanned. To avoid over-provisioning, we built a custom BQ testing module that dynamically auto-scaled slot capacity up and down during peak workloads. By analyzing this usage telemetry, we identified the exact mathematical threshold for optimum slot capacity and locked it, enforcing a hard compute ceiling.
- Storage Optimization: We evaluated the physical vs. logical storage billing models natively available in BQ. By deliberately assigning specific datasets to logical storage billing, we achieved drastic baseline storage savings.
Phase 5: Technical Engine & Consumption Governance
With compute capacity locked into a finite slot pool, raw query performance now required aggressive low-level engine tuning. You cannot optimize query performance in a slot-based model without exceptional, deliberate choices over table mechanics.
- Table Mechanics: We deployed a strict matrix of partitioning (by date/time), clustering (by high-cardinality filters), and strategic sharding. BQ native Search Indexes were applied explicitly to text-heavy analytical columns.
- Housekeeping: We eliminated redundancy by utilizing in-memory session temp tables for intermediate transformations, and deployed Materialized Views with strictly calibrated refresh rates to keep the production environment pristine.
- Tactical Archiving: Heavy historical tables were sharded. When called back, users were required to explicitly pass wildcards, preventing accidental massive historical scans.
The Consumption Throttle (PowerBI) Our PowerBI deployment utilized DirectQuery, meaning every dashboard interaction pushed a query down to BQ. This saturated our slot capacity and throttled internal data pipelines.
To resolve this, we configured BigQuery BI Engine to process these interactive queries in-memory. Because we had previously established a highly efficient, denormalized data model, we only had to allocate BI Engine capacity to a specific subset of curated tables, entirely avoiding memory bloat and dashboard latency.
True cost optimization in the cloud is never just about writing better SQL. It is a continuous Kaizen loop that requires tearing down complex legacy models, forcing strict consumption discipline, leveraging native ML capabilities, and actively negotiating compute mechanics with the vendor. By tackling the problem across organizational maturity, technical architecture, and vendor management, we turned a volatile cloud bill into a predictable, highly-leveraged asset.
Eliminate First: How We Architected a 60% + BigQuery Cost Reduction was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
Source Credit: https://medium.com/google-cloud/eliminate-first-how-we-architected-a-60-bigquery-cost-reduction-be39dc46f3a8?source=rss—-e52cf94d98af—4
About the Author
