← Work

Oracle to Amazon Redshift - Kisumu County

County Government of Kisumu · 2019 – 2021

A 150TB Oracle warehouse running since 2009, hit with a 100x workload spike from a new ERP rollout. Redesigned for Redshift, migrated with zero downtime, query times cut by two-thirds.

The county's Oracle warehouse had been running since 2009, starting at around 120GB. By the time the ERP for cashless revenue collection went live across 400+ collectors, the cluster was handling roughly 100x the workload it was sized for. ETL windows started slipping. Before committing to a migration, a PoC against the most critical schemas turned up four things that changed the approach.

Most of the transformation logic had no canonical form. Business rules lived in stored procedures that had been modified in-place for years by people who were no longer there. Reconstructing what the code was supposed to do from execution patterns and report outputs took longer than any other part of the project.

The source was also still changing. The ERP rollout was not finished; tables were being altered and new feeds added throughout the migration window. Treating the source as stable was not an option.

Oracle and Redshift have different physical assumptions. Oracle is row-based. Redshift is columnar MPP with node-local storage. Moving the schema as-is would have reproduced the join bottlenecks on a different platform. The schema needed to be redesigned around Redshift's distribution model, not transplanted.

Oracle-specific SQL was throughout the codebase. CONNECT BY, ROWNUM, Oracle date arithmetic, UDFs with no Redshift equivalent. Each required manual rewriting.

Schema remodelling

The warehouse had large, interdependent fact and dimension tables. Analysing the longest-running joins and assigning distribution keys to maximise co-located joins (where both sides of a join sit on the same node) cut cross-node I/O on the queries that had been causing the most pain.

Compound sort keys on date and administrative unit let Redshift skip large data blocks on the biggest fact tables. Most analytical queries filtered on those two dimensions, so the keys paid off on almost every scan.

Compression encodings were applied after running ANALYZE COMPRESSION on a representative sample rather than guessing upfront.

The original schema used Type 1 SCDs with straight overwrites. As a result, the warehouse had no historical state. Kisumu hosts a dense concentration of health research institutions and the only Level 5 hospital in the western region. Type 2 SCDs were introduced on key dimension tables to preserve point-in-time accuracy, which also unlocked ML forecasting workloads downstream.

Migration architecture

Infrastructure Active / outcome Main flow CDC / transform Orchestration Oracle (on-premises) Live transactional source initial load + CDC AWS DMS Full load then change data capture S3 (raw) Landing zone Redshift (raw) Source schema replica transform + test dbt Modelling, testing, transformation Airflow Orchestrates pipeline Redshift (curated) Consumed by reports and processes Cutover: two-week parallel run Oracle (live) Old ETL still writing Redshift (curated) Rebuilding on every run Validation Outputs compared across critical reports discrepancies Logged + resolved before re-validation validated Cutover Traffic redirected to Redshift Oracle decommissioned Migration complete

County operations run continuously. DMS performed the initial full load into S3, then switched to CDC, keeping Redshift within minutes of Oracle throughout. Airflow ran the pipeline. dbt built and tested the production reporting layer on every run; a failing test blocked the DAG from completing.

Cutover used a Data Freeze Date. Oracle and Redshift ran in parallel for two weeks while county staff validated outputs across all critical reports. Once validation passed, processes were redirected and Oracle was decommissioned.

Key decisions

Decision Chosen Why
Replication AWS DMS with CDC Kept Redshift within minutes of Oracle throughout the migration window.
Transformation dbt Undocumented stored procedures ported into version-controlled SQL models. Test failures blocked the pipeline.
Orchestration Airflow DAG-based scheduling with retry handling across the full pipeline.
Historical accuracy Type 2 SCDs Type 1 overwrites were silently losing state. Covered in schema remodelling above.

Outcomes

Query execution time cut by two-thirds on the highest-traffic reports. Data refresh dropped from 48 hours to under 3 hours. TCO down roughly 30% against continuing the hardware procurement cycle. Zero downtime at cutover.