Oracle to Snowflake platform migration

Moving a multi-entity reporting platform off a legacy warehouse, including stored-procedure conversion and a redesigned layered architecture.

Scope

A dozen operating entities, each with its own source conventions, feeding a shared reporting layer. The legacy platform had accumulated a decade of stored procedures, temp tables, and dated backup copies with no clear ownership.

Approach

Inventory before migration. The first deliverable was not a pipeline, it was a classification of every object in the source database — separating live objects from orchestration temp tables, backups, and dated copies. Migrating without that means faithfully reproducing a decade of accumulated debt.

Layered rebuild rather than lift-and-shift. Landing, conforming, and serving layers, with the serving layer rebuilt atomically rather than patched in place. Atomic rebuild trades some compute for a property that matters more: readers never see a half-updated snapshot.

Incremental serving tables over materialized views. The serving layer needed joins and unions that the materialized-view implementation would not accept. Incremental dynamic tables handled the same workload with fewer restrictions, at the cost of having to reason carefully about refresh semantics.

A representative bug

A serving table refresh started failing with a numeric conversion error. The cause was two source systems typing the same join key differently — text on one side, numeric on the other — with empty strings present in the text version. The fix was normalising the key at the boundary: trim, treat empty as null, then attempt numeric conversion in a way that yields null rather than raising on bad input. The broader lesson was that join keys deserve explicit type contracts at layer boundaries instead of relying on implicit coercion.