5 Common Data Migration Mistakes

Data migration is a process of extracting data from one or more sources, transforming that data, and loading it into a target system. Sometimes this process ‘Extract Transform Load (ETL)’ is done manually using spreadsheets and direct data entry in target applications for small data set but generally an ETL tool or custom programs developed for migration are used for transferring data from source to target system.

Data migration requirements are created mainly due to two scenarios; first, changes in the existing business process due to strategic alignment or merger with another organisation. Second, automation of business processes by implementing a new IT application or an upgrade of an existing IT application. In both scenarios existing data set need to be transferred from Sources to Target carefully so that data does not lose its business state and next step in the business process can be executed in Target system. For example, any open order transferred from Source system must be active and available for next business process step.

Considering this Data migration is commonly seen as a complex task in large or medium sized IT Programmes and generally it is left until the start of the Build phase. Also, Data Migration is perceived as a technical activity however in my opinion it should be considered as a business activity for three main reasons,

i) Data migration tasks get least time & priority from Business resources.
ii) A definition of extraction criteria and business rules requires business understanding.
iii) Resolution of data quality issues and reconciliation of business data can only be done by business resources

Therefore I suggest Data migration should be led by business and supported by the delivery team rather pushing Data migration towards Delivery teams with minimum business involvement. Due to this, programmes often have an impact on their budget or timelines or both.

According to a report by Bloor, 38% of data migration projects run over time or budget. The Gartner report states that 83% of all data migration projects either fail outright or suffer significant cost overruns and /or delays. Therefore undertaking data migration for an enterprise solution may seem like a daunting prospect. Learning to recognise that and avoiding these five common data migration mistakes can help you stay on time with your deployment plan and keep costs low.

1. Not enough time planned for data migration
Companies don’t spend enough time in planning for data migration activity as it is considered a task to move data from Source to Target. However data migration needs more time devoted to planning migration strategy & approach in order to answer questions like, how many years of historical data need to be migrated? Can this be avoided by keeping legacy systems and providing read-only access to business? What is the quality of data and can it be fixed in source or during the transformation process? If not, then would it be efficient to fix the data quality in target system after migration is completed? How many test cycles should be planned for migration and reconciliation? What is the failover plan in case migration is only partially completed?

These are some of the key questions which must be addressed during migration planning phase. This due diligence would help in delivering a better plan and in avoiding migration issues.

2. Lack of business engagement from the beginning
Business sees data migration as a technical activity managed by the delivery team therefore it is assumed that the role of business is to answer queries raised by the migration team. However it is seen that a migration project mainly suffers for two reasons,
a) In most cases data extraction criteria is not signed-off with business till the end and leads to scope creep. For example, earlier business wanted to migrate last year active customers only but now it is changed for last 3 years
b) Business lack of trust in Data migration process because they have absolutely no idea, first, how data is prepared and second, how data migration process works (i.e. sequence of activities). Therefore business hesitates to provide sign-off on data on time which leads to delays.

3. Data quality is left for data migration workstream
Poor data quality is generally acknowledged as a common issue affecting deployment schedules in major programmes. There are two simple reasons,
a) Data quality is often assumed to be a responsibility of the migration team
b) Setting unrealistic expectations to achieve 100% accuracy for all data items

Data quality control should always start with setting up a realistic agreement with Business and an appropriate allocation of business resources along with technology support.

4. Data migration testing is mixed as part of the Application testing
Application testing strategy covers the functional and non-functional aspects of the application on the manufactured data set. However data migration testing requires a different approach.

Data migration testing should always be done in a separate environment to stabilise migration issues, otherwise it could easily be confused as an application issue. This mix-up would impact on the issue resolution time which could result in delaying testing cycles and thus ultimately affecting the Go-Live date.

5. Unclear approach for post migration reconciliation and decommissioning Data reconciliation is generally interpreted as source vs target data verification of some sample records. For this reasons there is no formal step-by-step approach defined for post migration reconciliation which I have observed caused stress on Go-Live day.

To avoid such situation it is important to categorised data reconciliation in two-steps,
Step i) Technical reconciliation is performed by migration team to match the records counts, field count, table count and data fields matching to identify missing or truncated values.
Step ii) Business/Financial reconciliation is performed by business resources and mainly focuses on verifying account balances, output of key reports for selected top key Customers.

It is important for the migration team to agree on a decision for the legacy application after go-live of new application. This agreement plays an important role in defining migration strategy.

Above are few of the top common Data migration mistakes which I have seen repeated in many IT Programmes. These mistakes can be avoided by taking right steps. For example, if Project team spend sufficient time in planning for Data migration activity and ensuring business users are engaged from the beginning. Also making sure the roles & responsibilities are clearly understood by business & technical team for the data migration process. I hope you find this article interesting and if you feel I have missed any common data migration mistake then please post your comments.

About Author
Sanjay Kumar is an Enterprise Architect with over 14+ years of experience in delivering data architecture solutions for large IT programmes. He specialises in Data migration and led major ERP and bespoke data migration Programmes in different industries and sectors. He has worked in diversified roles with major IT delivery companies and two of the Big4 Consulting firms Deloitte, Ernst & Young. Views described in this article are solely owned by Sanjay Kumar.