Case study · P—07
Cloudera Big Data ETL Offload Program
- Role
- Program lead · Big Data Management & Data Architecture Manager
- Period
- December 2019 – September 2020
- Organisation
- LC Waikiki
- Stack
- SQL Server, Cloudera, Apache Spark, Apache Hive, Apache Impala, Apache Sqoop, Apache Airflow, Parquet
- Status
- Completed
In short
200+ high-load ETL processes rebuilt as ELT on Spark, Hive and Impala on a 15-node Cloudera cluster: about 12x faster on average, up to 30x.
Context
LC Waikiki’s data warehouse was an 80 TB SQL Server warehouse with a star schema, designed the Kimball way. In this period the same role owned the Cloudera Hadoop platform, the SQL Server warehouse and the OLAP layers, and led a team of 8-12 engineers.
Role: led the ETL offload program, as Big Data Management & Data Architecture Manager.
Problem
The warehouse was struggling to refresh within its batch window.
Approach
- Proof of concept first: after a POC, the heaviest analytical workloads moved to a 15-node Cloudera cluster in the company’s own data centre.
- Rebuild, not lift: 200+ high-load ETL processes were rebuilt as ELT on Spark, Hive and Impala.
- Tooling: Sqoop for ingestion, Airflow for orchestration and Parquet as the storage format.
- Back to the warehouse: results were loaded back into the warehouse.
Architecture and technology
SQL Server → Sqoop → Cloudera (Spark, Hive, Impala on Parquet) → results loaded back into SQL Server, with Airflow orchestrating the flow.
Outcome
- Batch workloads about 12x faster on average and up to 30x on the heaviest jobs.
- SQL Server freed for OLTP and BI.
- Lower licensing costs and wider adoption of big data technology in the team.
- The technical baseline for the later BigQuery migration.
Lessons
- Offloading came before the full move. Heavy jobs left SQL Server in 2019–2020; the warehouse itself moved to BigQuery in 2023–2024, on the baseline this program set.
- The team’s skills changed with the platform: wider adoption of big data technology was one of the outcomes, not only speed.