Migration / Modernization

Large-Scale Legacy Data Warehouse Migration to Snowflake for Major Healthcare Foundation Trust

The Situation

One of a country's largest healthcare foundation trusts, operating five major hospitals with over 3 million patient contacts per year, needed to replace a costly legacy data warehouse with a modern Snowflake-based Secure Data Environment. The scale of the migration was significant and the deadline was hard — the legacy system had a fixed switch-off date that could not move. Key challenges included:

  • 164 billion rows of data across 400 databases, 100,000 tables, and five source systems to migrate
  • 40,000 to 50,000 SQL views written in Microsoft SQL Server syntax requiring translation to Snowflake SQL and rebuilding as dbt models
  • Strict healthcare data protection requirements governing every stage of the migration
  • A multi-party delivery structure involving InterWorks, a global systems integrator, and the client's own data team
  • No access to the actual data environment for a significant portion of the early project timeline

What We Did

Executed a Snowflake Rapid Start to stand up the foundational environment including storage, compute, user settings, and architecture

  • Designed and implemented a comprehensive RBAC governance framework including row-level security and Segregation of Duties documentation, built for reuse across similar healthcare clients
  • Built an AI-assisted SQL translation pipeline to automate bulk conversion of 40,000 to 50,000 MS SQL Server views into Snowflake-compatible SQL — all processing performed on plain text SQL scripts with no patient data involved, fully compliant with healthcare data protection requirements
  • Built approximately 2,000 individual dbt models to reconstruct the full data environment in Snowflake
  • Ingested and deduplicated 164 billion rows of data from the legacy environment
  • Designed a Longitudinal Patient Record architecture integrating data from an electronic patient record system, community, primary care, and regional sources
  • Built a custom Snowflake monitoring and cost governance package with automated alerts across all major cost drivers
  • Delivered the full migration on deadline

Results

  • Full migration of 164 billion rows and 100,000 tables completed on deadline
  • AI/ML-ready Secure Data Environment established, enabling future use cases including waiting list risk stratification, NLP on clinical notes, and cancer data extraction
  • Significant multi-year cost saving against the legacy platform
  • RBAC governance and data protection framework in place and designed for expansion across additional trusts in the regional health ecosystem
  • Executive endorsement from Chief Medical Officer and Chief Digital Information Officer level stakeholders

What This Unlocks

For large healthcare organizations facing legacy data warehouse migrations at scale, this engagement demonstrates a proven architecture and delivery model — including an AI-assisted SQL translation approach that made a migration of this volume feasible within a compressed timeline. The RBAC governance framework, Longitudinal Patient Record architecture, and dbt model templates are all reusable for similar programmes.

Team

Led by a public sector delivery lead responsible for overall programme governance and partner management, supported by a data engineer serving as technical lead on Snowflake infrastructure and dbt transformation, a principal consultant leading the AI-assisted SQL translation pipeline, and a strategic account executive managing stakeholder relationships.

Back to Snowflake Use Cases
Snowflake migrationdbthealthcarelegacy modernizationRBAC governanceAI-assisted migrationLongitudinal Patient RecordSQL translationEMEApublic sectorSecure Data Environment

Read more Snowflake Use Cases

Need help with Snowflake
planning, implementation or optimization?

Services Contact Us

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!