Global Data Platform Migration

Project Overview

A global insurance group with operations in 23 countries had a 15-year-old Oracle data warehouse that was costing $4M/year in licences and taking 14 hours to run nightly batch jobs. Business teams were making decisions on 24-hour-old data. New analytics use cases were taking 6–9 months to implement.

We were brought in as the fractional CTO team to architect and execute a full migration to a modern cloud data stack — with a hard requirement of zero downtime and zero data loss.

The Challenge

A 15TB Oracle warehouse accumulated 15 years of tribal knowledge: stored procedures with undocumented business logic, reports referencing deprecated schemas, ETL jobs written in COBOL.

Key risks:

• Silent breakage — migrated data that looked correct but contained subtle transformation errors
• Downtime — any outage would prevent claims processing across 23 countries
• Political resistance — 12 teams owned different sections of the warehouse, each with their own priorities and concerns

Architecture

Migration Strategy: We used a strangler fig pattern — standing up the new Snowflake stack in parallel, migrating table-by-table with automated reconciliation, and only cutting over when parity was confirmed.

Reconciliation Engine: We built a custom Python reconciliation tool that ran daily comparisons between Oracle and Snowflake outputs for every report, flagging any discrepancies above a 0.001% threshold.

dbt Transformation Layer: All 400+ Oracle stored procedures were reverse-engineered, documented, and rewritten as dbt models with unit tests. Business logic was made explicit and version-controlled for the first time.

Cutover: Each country's reporting was migrated in a 2-hour cutover window on a Sunday, with an automated rollback plan ready.

Results

• Zero downtime across all 23 country deployments
• Nightly batch runtime reduced from 14 hours to 47 minutes
• Oracle licence costs eliminated: $4M/year saved
• New analytics use cases now deliverable in days instead of months