Amazon Pharmacy · 2024
Snowflake to Redshift Migration
A $1.4M infrastructure decision with zero reporting downtime.
The Problem
Amazon Pharmacy's analytics stack had grown organically on Snowflake — workloads, pipelines, and reporting dependencies had accumulated without a unified ownership model. The platform cost approximately $1.4M annually. Moving to Redshift would align with AWS-native tooling the organization already operated at scale. The risk: dozens of downstream reports, dashboards, and automated pipelines depended on the existing setup. A failed migration meant broken dashboards for pharmacy leadership.
Reflection
“A migration of this scale is fundamentally a change management exercise. The technical work was complex but tractable — Redshift SQL is close enough to Snowflake that most queries needed minimal changes. The harder challenge was getting three teams to agree on deprecation schedules and new ownership models while keeping existing pipelines running. Clear documentation, staged timelines, and explicit sign-offs at each stage made that possible.”
Approach
Audit before touching anything
I catalogued every Snowflake query, pipeline, and table touched in the prior six months. Unused objects were flagged for deprecation before migration began — this reduced the migration surface by approximately 30% and eliminated carrying dead weight into the new system. The audit also exposed four critical pipelines with no documented owner, which would have become incidents had they failed silently post-migration.
Migrate in layers, not in one shot
I sequenced the migration in four stages: raw ingestion tables first (least downstream impact), then transformed and aggregated tables, then ETL orchestration jobs, then reporting surfaces last. Each layer was validated with data quality checks before the next began. Rollback procedures were written and tested at each stage — not as a formality, but as a forcing function to understand failure modes before they happened in production.
Redesign ETL ownership simultaneously
The migration wasn't a lift-and-shift. I used it as an opportunity to establish pipeline ownership standards that hadn't existed on Snowflake: every job got an owner, an SLA, a failure alert, and a dependency map. This was the highest-leverage change in the project — it improved reliability not by changing the technology, but by establishing accountability.
Architecture
Phased migration: audit → redesign → rebuild → validate. Each layer signed off before the next began.
Impact