T: +44 (0) 203 287 4191


How to avoid data migration headaches 

Data Migration is one of the riskiest and most time consuming elements of most of the system implementation projects we get involved in. 

But what is Data Migration and why is it so difficult?   

In simple terms Data Migration is the process of transferring data from one system to another, typically as part of system upgrades or implementations.  Data migrations can also be used to consolidate data from multiple sources to one platform which allows organizations to analyse data more effectively. 

Data migrations can be complex and challenging; Some of the challenges include: 

  • Data inconsistencies 
  • Incomplete data 
  • System downtime 
  • Lack of knowledge of the core systems; both of the source system where we are migrating from and the target / destination system where we are migrating to) 
  • Business process re-engineering 
  • Timelines 
  • Resourcing


As part of any migration process the goals are to ensure data accuracy, completeness and consistency in the new system whilst minimising downtime and disruptions to operations.  With careful planning, co-ordination and testing, we can make a smooth transition with minimal disruption to business operations. 

6 Steps involved in the data migration process include:

Step1: Planning and Assessment

This step includes identifying the data to be migrated, understanding the structure and any dependencies of the data, documenting success criteria, planning the migration process and assessing resource capacity. 

A Data Migration Strategy document allows us to record the data requirements; the sources of data; the migration scope; priorities; and the success criteria.  It is crucial that stakeholders from various areas of the business are involved to ensure there is alignment with business objectives.   

Not all data should or needs to be migrated;  Alongside this, there may be some data that will not be migrated as part of the project due to the complexity of the differing structures, but may be input manually post implementation. 

As part of this step, we need to look at the quality and structure of the source data to identify any inconsistencies.  It is useful if there are people involved on the project team that understand how the data might have been recorded in the source system so they can share their knowledge.  This may identify areas for data cleansing – where we rectify data errors, duplicates and inconsistencies to improve the data quality.  Data cleansing can be carried out prior to extracting the data, as part of the transformation process or indeed as an exercise post go-live. 

Planning data migration timelines, budgets, and resource allocation is crucial for project success.  This includes estimating the time and effort required for data extraction, transformation, and loading tasks (known as ETL), as well as budgeting for necessary tools and expertise.  Often there is more than one cycle of data migration as we identify additional mapping rules. Data cleansing also means more accurate data.  

Step 2: Data Extraction

When extracting data from the source system we need to ensure data integrity and accuracy.  We also need to consider the mechanisms of how we undertake the extraction and which tools we use to do this. 

Depending on volumes and complexity of the data, it may be that only a small set of data is extracted.  It is useful to take timings as this may impact the timeline for live implementation. 

It is also important to understand where the extraction should take place and the mechanism for transferring files;  We believe that the data should reside in the Client’s domain at all times and certainly do not advocate sending of confidential information via email! 

Rules for extraction should be documented e.g. only open Client records and no closed Client records.  Obviously sometimes these rules change so a tool that allows you to easily change the extract routine is invaluable.   

Whilst Excel can be really useful in data migrations, we’d recommend a dedicated data analytics tool like Alteryx (available from Solitaire Consulting partner, Continuum). 

Step 3: Data Transformation

Data structures or formats tend to differ between source and target systems, so this step is about converting data formats and standardising data fields to enable the data to be loaded into the target system. 

There is usually an element of data cleansing, particularly where text fields need to be mapped to fields containing drop down lists. Data cleansing will usually be done in the source system prior to migration, but can also be done in data staging areas or post implementation. 

It is important to document any such transformations / data cleansing tasks and to make sure people are aware, especially those testing the data – you now potentially have a source system that has data that will not necessarily be what is loaded into the target system. 

Step 4: Data Loading

We’ve extracted our data; we’ve transformed the data; we now need to load the transformed data into the target system ensuring we are maintaining the integrity and accuracy of the data. 

How we load this data is often determined by the vendor of the target system. They may provide tools, such as the Configuration Packages in TrustQuay Online or NavOne, that allow the Client to load data, or the Vendor may provide spreadsheet templates to complete and they load the data. In other cases the vendor may have dedicated scripts / programs to extract and load directly. This may seem ideal but with little ability to transform or cleanse the data as part of the process the outcome can be less accurate.   

Just like the extraction stage, data loading can be incomplete so it is important to document any partial data loads. Testers will need to know what has been loaded so they can focus testing on those areas. 

Step 5: Validation and Testing

This step is about verifying that the migrated data is accurate, complete and consistent with the original data.  This often involves record counts, sample validation checks and testing to ensure data meets quality and integrity criteria.  It is also important to undertake testing of any new  / changes to business processes that have been introduced as a result of the new target system. 

Whilst some of the data can be automatically validated (e.g. record counts, field matching etc), it is vital that there is testing done by the users as part of the User Acceptance Testing process especially in relation to processes. You can read more about this in our recent blog “Testing from the front lines” by my colleague Corrado Anderson. 

Step 6: Deployment

And finally, providing the migrated data into the production environment ready for operational use.   

It’s all very well having successfully completed the migration and it’s ready for use on Monday morning, but the end-users need to have been trained on the new ERP system’s functionality and processes so they can actually use the new system!  Operational processes may have also needed to be modified and these also need to be communicated. 

There may also have been some tasks that were identified for post go-live resolution and it is important that these are planned, prioritised and communicated 

Establishing data governance policies and procedures helps maintain data quality, security, and compliance in the new ERP system. Regular data audits, performance monitoring, and data management practices ensure the continued integrity and relevance of data post-migration. 

So yes, data migrations can be complex and challenging; but with careful planning, co-ordination and testing we can minimize the pains and hopefully get you using your new migrated data quickly. 

Demistifying Data Migrations Event

If you are Jersey-based, this is for you…

Want to know more on how to make data migration simple to ensure a smooth transition in your organisation??

Join us as we demystify data migrations with Tracey Watson and Paul Every

This event will cover key aspects of data migrations, including planning, analysis, data mapping, integrity checks, and testing strategies. Gain valuable insights to minimize risks and ensure a smooth transition for your organization.

Monday 25th March, Kingsman Offices, Castle Street, from 12.30pm to 2pm, book your place now for this complimentary lunch & learn session

Leave a Reply

Your email address will not be published. Required fields are marked *