Top 25 Painful Data Migration Pitfalls You Wish You Had Learned Sooner | by Hanzala Qureshi | Sep, 2022

By Jessie Hobb On Sep 16, 2022

Lessons I wish I knew about before embarking on data migration journeys

“Life is like a box of chocolates; you never know what you’re gonna get” — if only Tom Hanks knew about the perils of Data Migration (DM). On a late Friday afternoon, receiving an urgent ping with the dreaded “reconciliation has failed again” will get your heart racing faster than your morning cardio.

If you have been involved in DM, you will know that this is one of the most complex projects undertaken by data teams. DM in and of itself is simple, i.e. I am moving data from one place to another. But the execution has many moving parts, from technical mappings to data quality and reconciliation numbers to target architecture. Getting all the elements on point is a tough job.

You are not migrating data, you are paying for years of technical debt

This article aims to help you with a checklist of pitfalls you should actively avoid with real-world examples, helping you succeed in your DM journey based on my learnings over the past decade.

It is divided into five sub-sections:

Data Migration Planning
Source and Target Architecture
Data Quality
Testing, testing and more testing
Don’t make these an afterthought

Let’s dive in!

1. Fail to plan, plan to fail

A successful DM has the backing of an intricate plan; there are many moving pieces and many source and target dependencies. Having a plan highlighting risks, assumptions, issues, and dependencies is the best start you could aim for in executing a successful outcome of a DM.

For example: in the plan to migrate data from store A to B, I would include these core sections; stakeholder engagement map, source and target data architecture, data profiling, test data coverage, data mapping documents, test strategy and execution plan, migration iteration/complete load activities.

2. Inexperienced team

Underestimating the monumental task ahead is a surefire way to fail your DM journey. Don’t skimp on the cost by getting a junior/inexperienced team to lead core workstreams. Invest in an experienced team that has been through DM previously and knows some of these pitfalls.

For example: when interviewing team members for the project, I will ask specific DM-related questions such as “what are the core risks to DM being successful?” or “what are the pros and cons of iterative vs full load DM?” or “how would you determine good test data coverage?”

3. Underestimating costs

If any project needs a contingency pot, it’s this. Many actors must be compensated: source system, infrastructure, data testing, architects, engineers, project managers, scrum masters, etc. Ensure with an intricate plan that you have accounted for the costs for all these tasks.

For example: when cost and budget plans are drawn up, I avoid using intuition-based estimated costs. For each actor involved, I would ask for a rough impact assessment based on known activities in the plan, add appropriate slippage risk contingency and then provide a budget submission for approval.

4. Looking for unicorns

You won’t find one subject matter expert (SME) that has answers to all the project questions. However, you will have many SMEs with various skill sets. It would be best if you made sure the right SMEs are talking to each other to help define the detailed level tasks and surfacing risks with huge impacts.

For example: when interviewing, hone in on the person’s core skill, and I would avoid making the same person wear many different hats in the team, as you’d want them to be proficient in their given workstream.

5. Lack of autonomy

Planning to run DM as one big project with all roads leading to a single decision-maker will cause frustration and slow you down. Organise a proper team structure and delegate autonomy to the right people with experience.

For example: when managing resources, I would provide guidance and support throughout their work as and when required; however, I would avoid looking over their shoulder constantly. Frequently, I would let them follow through on their decisions, allowing them to build their confidence.

6. No one knows the source system issues

In major migrations, source systems are old and archaic. They lack documentation; they have no lineage defined; they have no defined processes; there are no dictionaries. A handful of people know the system’s intricacies, and sometimes they sit behind bureaucratic processes. Account for this nuance in your plan (see 11.)

For example: I have seen customer core systems with incorrect customer personal information. These were incorrect for many years prior to the migration but never noticed, as no basic profiling was carried out. The incorrect elements such as email and first/last name resulted in many customer complaints; however, lack of documentation and a defined remediation process made them persist.

7. Infrastructure not available easily

I have lost count of the months it takes to set up a source and target environment for data transfer and testing. As part of your plan, having an open environment should be a critical dependency and a risk. You may need more than one environment to conduct different types of testing. Sadly, this pain continues even with newer Cloud environments.

For example: when planning the migration activities, I would submit a request to the relevant team to get an environment set up as soon as possible. IT teams sometimes have a sandbox environment that could be used; however, ensure this is to the specification you need.

8. Not realising the extent of the technical debt

Technical debt is the best kind of debt; you don’t have to repay it, and there are no consequences! Sarcasm, of course.

Technical debt has been building up in the source system for years and decades; as part of planning, it is imperative to dedicate some time to understand this impact in your proceeding DM activities. Remember (3.) you may have to fork out some budget to address these debt items.

For example: most organisations keep a technical debt register; I usually start with that, then align the known problems to the activities on the plan and build a picture of dependencies/slippage technical debt can cost.

9. Over-ambitious data journey

Migrating data from source to target will be hard enough; not being able to correct your mistakes along the way will make it impossible. Ensure the set-up of the data journey goes through various staging environments, allowing you to reconcile data and fix issues without impacting the final destination.

For example: when procuring infrastructure (in 7). I would also provide a high-level logical architecture diagram showing multiple staging environments required before the target system is populated using the new data.

10. Deciding iterations and delivering big bang

Decide how you intend to migrate the data and set up the infrastructure accordingly. If the environment is for migrating data iteratively, ensure you genuinely iterate and not gather your releases to make a big bang.

For example: I have seen this numerous times, where earlier releases are delayed due to various issues, and the later releases are overloaded with “iterations”. The environment (7) is not set up for big bang DM; we do just that, disguised as a big iteration.

11. Expecting good quality data

Data Quality will be poor — always. I have never started a migration project with good quality data that I could lift and shift. Such an event is akin to pigs flying. Instead, invest time and effort with the source system SME to understand and improve the data quality before migrating it.

For example: I would start with known critical data tables and conduct simple profiling to see a number of Nulls, formatting, duplication etc., to help gauge the overall quality.

12. Not profiling a large enough sample of data

Data has trends; more data has more trends. Use a large sample of data to understand data quality issues hiding in plain sight. You will always have edge cases that can’t all be captured in the profiling exercise; hence be conscious in choosing your data sample.

For example: I had previously engaged SMEs and learnt when the business made significant changes in the source system; I included data from that particular period in the profiling exercise, which resulted in more DQ issues surfacing.

13. Not knowing your critical data attributes

Your target structure may have a limited number of attributes worth migrating. It would help if you understood what is critical upfront. 100% Data Quality is not a thing, so you must find a balance between what is critical and worth fixing and what isn’t.

For example: I ensure that the critical attributes are defined in two ways; firstly, the target system SME confirms technically which attributes are critical and secondly, the business end user establishes which attributes fulfil an end business goal.

14. Expecting data to have a single definition

When data mapping is carried out — we need to know the definition of the source data attribute, including its quality metrics. Organisations usually have under-invested in their data governance; hence this information is not readily available.

For example: Often, I would take the best guess on the definition and ensure business users validate it.

15. Not having a data remediation process

Finding data quality issues is one battle; remediating them is another. Especially if you have an archaic source system where changes take a long time, the risk of system failover is high. It will pay dividends having the right team (see 2.) and process onboarded to find a quick way of remediating data quality issues in the source.

For example: at the start of the project, I would map out an end-to-end remediation process for issues that surface throughout the DM journey. This process would help with DQ and other technical problems like batch failures and data corruption.

16. Not spending enough time on validation testing

Remember being asked if there is any slack in the plan? And then, miraculously, the testing effort is reduced to get the project over the line and take an acceptable level of risk. I don’t know if anyone ever defines what is acceptable. Testing should be the core of the DM journey, and validation is the centre of that core.

For example: The testing team in my project would carry out two types of validation, technical validation of the data ensuring it meets the data standards and functional validation, ensuring it meets the end business requirements.

17. No defined acceptance criteria

You can keep testing till the cows come home, but it could be a waste of time. Defining acceptance criteria ensures that end goals don’t keep changing. In addition, having acceptance criteria helps create boundaries, which helps plan the core tasks.

For example: I use a standard set of criteria and tweak them as the project evolves. These criteria include “acceptable % of data quality”, “can users access the report/data in the same way in source and target”, or “can the data be loaded at the same time in target as it is in the source.”

18. Lack of early performance considerations

Performance testing is usually one of the project’s last activities, immediately becoming the next set of technical debt. After a long, arduous project, there is a minimal appetite to revisit design and coding decisions made early in the project. Changing them is expensive and time-consuming, putting that all-important “go-live” date at risk. Avoid this by ensuring performance is taken into account from the early onset.

For example: the performance testing team would be engaged from the start of the project to conduct short performance test cycles as data is migrated. This provides meaningful results and a threshold at which performance starts deteriorating.

19. Skipping test phases

Every test phase has a purpose; skipping or shortening them increases the risk of a failed DM delivery. System Integration & Regression testing come to mind for being heavily compromised with tight project deadlines. Ensure robust testing regiment is agreed upon and all the relevant teams understand the impact.

For example: each test phase would be defined and have a clear goal. I avoid adding test phases for the sake of it, as this leads to these phases being skipped when delivery timelines are under pressure.

20. Lack of a defined reconciliation framework

The aim of the game is to have the same set of data in the target that you started within the source. The best way to do this is to have a reconciliation framework that checks throughout your data journey that data has not gone missing.

For example: I would not carry out reconciliation at the source and target only; each checkpoint in between would have a reconciliation check to find the issues early and remediate them.

21. Not understanding the implications of Data Migration

Working with data and creating new services or architecture requires consulting with an organisation’s core teams. Engaging with the security team is essential to avoid unknown cyber risks. Engaging with the data privacy team is vital to treat personally identifiable data appropriately. Engaging with the data governance team is necessary to ensure the right policies are followed.

For example: at the start of the migration, I would put in a kickoff call and invite all impacted/known parties to talk through the initiative. Also, I would clarify with the relevant teams, in best ways to engage them to support this DM activity.

22. Lack of environment capacity planning

Will the environment stand the test of time, but even before, will the environment stand the test of the DM event? It is worth spending time with the relevant infrastructure teams to create a capacity plan to manage fluctuations in the data size over time. Of course, with cloud technologies, much of this could be automated.

For example: create a simple capacity plan, and add all the current data and attributes being migrated and their data size. I would then add a % increase in that data in line with business/data strategy and make sure capacity enhancement measures to remove redundant data are included in these calculations. In the end, you are left with a reasonable case scenario of capacity which can be used for planning.

23. Not investing in automation

Instead of executing each migration event manually, invest in automating the entire migration sequence; this includes automated data capturing, ingestion, testing, transferring and transforming.

For example: even if migration is a one-off event, I will ensure an automated code is developed to help run multiple migration events, including dress rehearsal. This also makes future executions simple.

24. Single-person dependencies

Migration takes a long time to execute; throughout the phases, key superstars start to shine. These individuals become single-person dependencies due to a lack of a knowledge-sharing culture.

For example, create a knowledge-sharing portal where each team member adds and tags information. Create a new joiner training pack including this information so they can be brought up to speed quickly.

25. Continuously changing variables

There are enough moving pieces in the DM journey; try and reduce constantly changing variables.

For example: reduce new changes in the source system, especially if the target system is supposed to be your end goal. Create a flexible target model to accommodate source testing and data quality changes: conduct DQ and validation tests to a limited scope of data attributes. Try to limit too many changes at once to avoid creating new dependencies and putting the overall goal at risk.

Conclusion

Wow — this was a long list. DM is one of the most challenging yet rewarding projects anyone could work on. This article should have covered 80% of known DM scenarios; of course, edge cases will not be captured here, so feel free to share them in your comments below.

If you like this kind of content, check out some of my other posts:

If you are not subscribed to Medium, consider subscribing using my referral link. It’s cheaper than Netflix and objectively a much better use of your time. If you use my link, I earn a small commission, and you get access to unlimited stories on Medium.

I also write regularly on Twitter; follow me here.