min read ·
January 13, 2020

Data Migrations: Migrate early, Migrate often

When doing a major update or replacing an existing system it is common that a data migration will need to be performed to move legacy data into the new system. Also in almost all cases, the shape of the data in the source and destination systems would not be the same.

Graphic illustrating a data transfer

Fabric recently completed a large data migration for one of our projects where we were replacing an off the shelf product, backed by a database, with a bespoke system written by Fabric and running in the cloud.

The following post outlines some of the lessons we learned when performing the migration.

Migrate Early

We realised early on that the complexity of the data migration was not well understood and posed a risk to the project. This was partially due to the complex and obfuscated structure of the data in the legacy system.

To mitigate this risk we decided to start migrating data from the old system into the new system as at the same time as it was being developed. This is probably the most invaluable lesson we learned.

It might be tempting to treat the data migration as the final step in a project that can be left until after development is complete. However, by working on the migration in parallel with development we found that it called attention to any data mismatches between the legacy and new systems, including missing and misnamed fields and in some cases data that would not even need to be migrated.

This also highlighted early on in the project invalid data in the legacy system both from a data integrity and business perspective and gave us the opportunity to either fix the data or in some cases make minor modifications to business rules in the new system.

In our project, the data validation in the legacy system was quite lax and the data quality, as a result, was very poor, but by migrating early it gave us the chance to start working through the issues and cleaning the data as we went.

By migrating early it also meant that the scale of the problem was identified early and we could allocate extra resources to it as we had completely underestimated the effort required. While on the subject of resources it also meant that we didn’t get to the end of development and then have a team of developers with little to work on except the data migration where they would have tripped over each other.

A question you might have is that if you are migrating data at the same time as development wouldn't there be some re-work required in the migration as the data structure in the new system is being developed?

We tried to keep the data migration one step behind the features being developed so that the data being migrated for that feature was reasonably stable before we migrated it. There was a little bit of catch up required at the end of the project to finish off the migration but not too much and this was performed during the final testing, bug fixing and hardening of the system.

Audit the Results

As mentioned the quality of the data in the legacy system we were working with was quite poor and in many cases, the decision on how to deal fixing the data required some input from a business person or users familiar with the old system.

So during development while the migration was taking place we wrote a number of reports (in fact just SQL queries) that listed all of the bad data. This allowed us to continue working on the migration but kept track of data to come back to and clean up.

These audit reports were continually run throughout the project and as the data migration improved the amount of bad data reduced until we achieved a clean data set prior to the final migration.

As far as cleaning up the data goes we employed three strategies:

  1. We fixed the data in the legacy system directly by users simply going into the system and fixing it
  2. We wrote SQL scripts to simply replace bad data with good values, and
  3. For complex data fixes that required some business logic to determine the correct data values, we wrote SQL functions to correct them.

As we were cleaning up the data in production as we went we had to take regular snapshots of this data (see below) so when it came to the final migration into production only a handful of records needed to be manually fixed.    

Migrate Often  

As we were doing the data migration at the same time as development, we treated it like any other feature or development task. What this means is we had stories for the migration tasks which were developed and tested locally before checking them in and going through the standard branching, pull request (PR) approval and merge process.

Further to this, once merged, the data migration task was included as part of the build from a snapshot of legacy production data. This data was then deployed to a test environment. This meant that testers as well as checking the new features as they were completed could test the current migrated data to ensure it was also correct.

Occasionally this testing would find flaws in the new system’s business logic that would not work with the migrated production data. These issues might not have been found or would have been found late in the project if testing had only been done with new data. 


In summary, we learned that there was a big advantage in working on the data migration in parallel with the development of features in the new system. We also found it important to follow the same development practices as the rest of the system.

Many of these are the same advantages you get working iteratively on an agile project such as identifying and mitigating risks early, being able to predict the effort required to complete the migration. However, by using migrated data throughout the development process it allowed us to find bugs in the migration and the new business logic as we went, rather than getting a shock at the end of the project.


Migrating from a legacy system?

Chris would love to hear about your experience and your challenges.

Tick (success) icon
Thanks for your message,
we will be in touch soon!
Oops! Something went wrong. Please try again later.