Enterprise Data Warehouse Migration to Google BigQuery

Context

LC Waikiki’s enterprise data warehouse held 80 TB in 3,000+ tables, fed by 100+ source systems. It ran on premises, on SQL Server and a 15-node Cloudera cluster.

Cloud proofs of concept on GCP (BigQuery, Dataflow) and Azure (Synapse, Data Factory), run in 2019–2022, produced a FinOps and technical business case. That case became the basis of this migration.

Role: designed and led the migration, as Senior Data Engineering & Data Architecture Group Manager, leading an 18-person data organization through it.

Problem

The on-premises platform carried SQL Server and Cloudera licence and hardware costs.

Approach

• Target platform: BigQuery, with Dataflow, Dataform and Cloud Composer.
• Layers redesigned: Landing, Staging, Core and Data Mart, with versioning and CI/CD for every model.
• ETL/ELT optimised across 3,000+ tables with Dataflow and Dataform.
• Quality built in: data quality assertions were built into the pipelines rather than bolted on afterwards.
• Retirement: SQL Server and Cloudera licence and hardware costs retired.

Architecture and technology

• Source: SQL Server and a 15-node Cloudera cluster, on premises.
• Target: BigQuery; Dataflow and Dataform for pipelines and transformations; Cloud Composer (Apache Airflow) for orchestration.
• Layers: Landing → Staging → Core → Data Mart.
• Delivery: Git-based CI/CD and data quality assertions for every model.

Outcome

• $550K annual savings.
• 75% lower infrastructure cost.
• Workflows 7x faster.

Lessons

• The migration started from a business case. Proofs of concept on two clouds came first, and their FinOps and technical results set the direction.
• Data quality assertions went into the pipelines as they were rebuilt, not after the move.