Case study
Healthcare data warehouse case study: migrating SQL Server reporting to Snowflake
ResultA SQL Server reporting warehouse was migrated to Snowflake through parallel running, reconciliation and report-owner sign-off.
- Client
- A US regional health system (anonymised)
- Industry
- Healthcare (hospitals and clinics)
- Service
- Data migration and data warehouse consulting: the dependency map, dual running, reconciliation, cutover report by report, and handover
- Type
- An on-premises SQL Server warehouse moved to Snowflake
- Status
- Delivered
We migrated a regional health system's reporting warehouse from its own data centre to Snowflake. The project covered source dependencies, transformations, access controls and the reports used by finance, quality and operations. Both platforms ran alongside each other while reports were reconciled and moved individually.
The starting point
The health system needed to move from a data centre that was scheduled to close. Its SQL Server warehouse depended on older ETL jobs and supported operational reporting as well as figures shared with outside organisations. Some transformation rules were not yet documented, and access grants had accumulated over time, so the migration needed more care than a direct table copy.
What we delivered
We mapped dependencies from query and job logs, rebuilt transformations with tests and mapped patient-level access before moving reports. Source feeds use change data capture where available and incremental extracts elsewhere. Daily comparisons record differences between the platforms and support investigation back to the underlying rule.
Rollout and handover
We built the new warehouse alongside the existing one and moved reports in stages. Each report required reconciled totals, permission checks and owner sign-off. Monitoring, alert routing and runbooks were handed to the health system's data team. Rollback remained available during cutover, and unused tables were handled through an owned archive process.
Where people remain in control
Report owners approve reconciled outputs and access checks before cutover. The data team owns ongoing operation and alert handling. A failed reconciliation holds the affected report while its cause is investigated.
Project outcome
The health system received a cloud reporting platform with tested transformations and documented operating procedures. Report-by-report reconciliation preserved the basis for comparing outputs during the move. Definition changes were kept separate from migration work so a difference in reported figures could be investigated rather than accepted as part of a redesign.
How performance is evaluated
| Measure | How it is measured | Comparison point |
|---|---|---|
| Reports moved | Reports reading the new warehouse with their owner's signature, over every report on the dependency map | The dependency map |
| Differences found | Each difference between the two warehouses, with the rule behind it once named | From the first day both run |
| Tables archived | Tables archived with a named owner's sign-off, over all tables on the old warehouse | The assessment |
| Query speed | p95 on each report's workload, replayed on both warehouses | The old warehouse, replayed |
| Age of the data | How old the newest row is when each report reads it, starting with the daily dashboards | The old warehouse's nightly load |
| Platform bill | Credits used each month, with the months both warehouses run shown on their own line | The envelope in the plan |
Technologies and tools
- SQL Server in the health system's data centre, until the quiet period ends
- Snowflake, in an account the health system owns
- Change data capture where a source supports it, incremental extracts elsewhere
- dbt, with each old job rewritten as a tested transformation
- Airflow, scheduling the loads and the daily comparison that logs every difference
- The health system's BI tool, its reports repointed one at a time
- Monitoring and alert routing, with the runbooks in the health system's repository
More case studies
All case studies- Case studyEthereum transactions, wallet activity and contract events, ready to query as they happenTransactions processed with few or no stops, and the indexed history retrieved quickly as it grows
- Case studyStudent services answers from a public university's own policiesA student services chatbot answers from approved university content and passes questions requiring staff judgement to the responsible office.
Bring us the warehouse you're preparing to move
Tell us which platform you're moving from, your target date and roughly how many reports rely on it. An engineer replies within two business days with what the assessment would map before any data moves.