Case study

How we turned a high-risk legacy data migration into a controlled transition

Building a reliable migration pipeline that protected data integrity, enabled gradual user migration, and maintained business continuity.

  • Healthcare
  • Legacy system modernisation
  • Data migration
  • Cloud migration
  • Database reverse engineering
  • ETL pipeline development
  • MS SQL to PostgreSQL migration
  • Cloud data migration
Client
NDA
Industry / Domain
Healthcare
LLI role
Legacy data migration strategy and implementation
Scope
Legacy database analysis, schema mapping, ETL pipeline development, file migration, data synchronisation

Overview

While continuing the development of the new version of the platform, we identified a critical risk that could significantly impact the overall success of the project - the migration of data from the legacy system to the new solution.

What initially seemed like a technical task revealed deeper complexity. The integrity, structure, and understanding of existing data became a key factor in ensuring a smooth transition and uninterrupted system operation.

The problem

Navigating unknowns in legacy data

The legacy system introduced substantial obstacles in the data migration process:

  • The database had a highly complex schema with little to no documentation
  • There was limited understanding of what specific data represented and how tables were related
  • The legacy and new systems were built on fundamentally different schemas, making direct one-to-one mapping impossible
  • The migration required handling differences between MS SQL (legacy) and PostgreSQL (new system)
  • The organisation lacked established processes, best practices, or frameworks for handling data migration at this scale

This created a high-risk scenario where incorrect assumptions or incomplete mappings could lead to data loss, inconsistency, or system instability.

Our approach

Bringing structure to uncertainty

To mitigate these risks, we focused on building a deep understanding of the existing system and creating a reliable migration process.

Reverse engineering the legacy database

  1. 01

    Analysed the existing schema to uncover data relationships and dependencies

  2. 02

    Reconstructed the meaning and structure of critical data entities

  3. 03

    Designed efficient mappings between the legacy and new system schemas

Building a robust ETL pipeline

  1. 01

    Developed a scalable ETL process using AWS Glue

  2. 02

    Enabled controlled extraction, transformation, and loading of data into the new system

  3. 03

    Incorporated the knowledge gained from reverse engineering to ensure accuracy and consistency

Handling file migration

  1. 01

    Successfully migrated files from on-premise or legacy drive storage to cloud-based storage

  2. 02

    Maintained full data consistency across systems throughout the process

The solution

We delivered a structured migration process combining:

  • Reverse engineering of the undocumented legacy database
  • Mapping between fundamentally different legacy and target schemas
  • Migration from MS SQL to PostgreSQL
  • A scalable ETL pipeline built with AWS Glue
  • Migration of legacy file storage to cloud-based storage
  • Continuous delta updates between the old and new platforms
  • A migration approach that supported gradual onboarding rather than a single disruptive cutover

The migration process was designed to support the continued development and operation of the new platform while users and data were progressively moved away from the legacy environment.

Technology stack

  • MS SQL

  • PostgreSQL

  • AWS Glue

  • ETL

  • Cloud-based file storage

  • Why these technologies?

    AWS Glue

    AWS Glue supported a structured and scalable extraction, transformation, and loading process between systems with different data models.

  • Why these technologies?

    PostgreSQL

    PostgreSQL formed part of the target platform architecture, while the migration process accounted for the structural differences between the new environment and the legacy MS SQL database.

The technology choices supported controlled migration, data consistency, and the ability to continue synchronising systems during the transition.

Results and impact

For users

  • Gradual migration to the new platform
  • Seamless onboarding to the new system
  • No downtime during the transition

For the organisation

  • Reduced risk associated with legacy system migration
  • Business continuity throughout the transition
  • A controlled path away from the legacy platform

For development and operations

  • Continuous delta updates kept both systems synchronised during migration
  • New functionality could continue to be developed and delivered
  • Migration work did not disrupt ongoing platform development
  • A previously uncertain migration process became structured and predictable

Why this case matters

Legacy modernisation often depends on much more than rebuilding an application. Existing data can become one of the largest sources of risk when its structure, meaning, and dependencies are poorly understood.

This case demonstrates LLI's ability to:

  • Identify migration risk before it becomes a blocker
  • Reverse engineer undocumented legacy environments
  • Translate complex legacy data into a new system architecture
  • Build reliable migration processes across different technologies
  • Sequence modernisation work without disrupting ongoing operations
  • Protect business continuity while legacy and new systems run in parallel

Value delivered: A high-risk legacy migration became a controlled, incremental transition that protected data integrity, supported uninterrupted development, and allowed users to move to the new platform without downtime.

More case studies

View all

Start the conversation

Build the right technology with the right engineering partner.

Tell us what you are planning, and our senior team will help you define the strongest way forward.

Talk to our team