7 Steps to a Successful Data Migration

Almost all companies we talk to are planning some kind of data migration. They are either driven by the businesses, who are realizing that business intelligence is now a competitive deployment necessity, or they are driven by IT infrastructure leaders, who are pushing to move away from legacy systems that contain data messes, deliver lackluster performance, or represent an opportunity to reduce the on prem storage and infrastructure footprint.

However, given the complexity of today's enterprise, performing an ETL/ELT data migration is a daunting task for any organization to undertake. Over 80% of these projects fail, and a surprisingly high number of organizations don’t proactively work to reduce project risk, scope creep and budget overages. Some of the key reasons for this state of affairs is as follows:

  • Sheer volume of data, and an ever-increasing high rate of ingestion
  • Poor data quality, which is often processed and stored from many sources and systems
  • Metadata and development code accumulated over many years (especially where multiple mergers have taken place), which present surprises during the migration process.
  • Data can come from legacy data sources that wherein data modification is done by manual or batch processes, or it can come from highly dynamic, schemaless datastores like NoSQL databases, Hadoop or blob storage. Combining legacy structured and schemaless data requires additional steps in the transformation process.
  • Inadequate tools for data mapping and transformation, resulting in greater manual effort and lengthier timelines
  • Ineffective scoping
  • Failure to complete a deep pre-project assessment to identify all the skeletons in the closet (i.e., legacy code, legacy data sources) and either address them or leave them out of scope


For those organizations that do understand the risks, upon trying to mitigate them, they find that there is an enormous cost to re-write years of legacy data integrations/code, consolidating multiple tools an platforms proves to be difficult, getting users to move takes a lot longer than one would expect, and the collective cost and time to market for the project brings ROI seriously into question. However, despite this, we all know that those who embark on these data migration challenges will disrupt, and those who don’t will be disrupted by the competition.

We’ve found that the following strategies can ultimately reduce timeline and cost by 5x. 

1. Goals, Goals, Goals

You can either set out to migrate everything (and risk boiling the ocean - we know where that ends up), or you can start with key business (not IT) objectives. As simple as this is, it immediately eliminates scope because you will be able to identify migrations that are actually not necessary to achieve specific business goals.

2. Measure Twice, Cut Once

Given the stated business goals, take an inventory of all relevant artifacts to be migrated, build source of truth documentation and conduct an analysis. For example, create a list of all objects and cross references, analyze and document mapping details; determine the number and nature of data transformations required; provide mapping cross references; assess functions, SQL statements and transformation expressions; identify SQL programs, dynamic SQL and referenced objects and create a program-Object cross reference.




Show Me A Sample


 3. Assess Complexity

Once the above analysis is complete, group the artifacts and summarize migration complexity into four buckets: Low, Medium, Complex, and Very Complex. Provide detailed explanations as to why each set of artifacts is categorized as such. Data quality and transformation requirements should be a prominent consideration in the groupings. Then, seek second and third opinions to make sure you are not underestimating a particular area. Find colleagues or partners who have migrated similar artifacts to paint a clear picture of where there will be complexity and unexpected challenges.

 4. Run Project Analytics

Since you now have quantitative facts on what must be migrated, and a solid assessment of complexity for each, you can apply standard industry metrics to determine resources, cost and timeline required for the migration.

 5. Descope

The price tag will probably be higher than expected (and if not, perhaps you can move forward!). Also remember that, statistically, projects that are broken out into smaller parts have a 7x likelihood of success, compared to “big bang” waterfall projects. With this in mind, go back and try to narrow the list of business goals for the migration and reduce the migration effort, particularly where there is high complexity and low payback. Determine where you can exclude data volumes, sources, etc.

 6. Build Support

Now that you’ve got clear business goals (and, presumably, executives who are behind these goals), an accurate level of effort assessment and budget, and timeline, build support for your initiative. If you are in IT, I’d recommend that you have the executives who stand to benefit from the migration make the case, and you be there to support the findings and requirements.

Don’t try to convince people to do a project, and then own the risks and potential for ROI on your own. It took your organization a long time for this state of affairs to develop, and its going to take a village to address the challenges and prepare the organization to compete in the digital age.


Help Me With This


 7. Proactively Automate, Pre-Project

Once you’ve got support for your project, I’d recommend a strategy around data cleansing, data transformation and conversion, using as much automation as possible. There are many things you can do to convert and prepare data before the migration to avoid load failures, downtime and other unexpected consequences .