May 28, 2020

All About Data Migration

Intro to Migration

If you operate a business, there is a good chance you rely on software to manage some aspects of your operation. Over time it is inevitable that you will want to upgrade your software or move to another vendor. Even more exciting, perhaps you are working with a custom software development firm to build an application tailored to your operational needs. Whatever the reason, part of the process will require migrating any existing data into the new software platform so that your business can pick up where you left off.

Depending on the age and throughput of your business, there is a good chance you have dozens or hundreds of individual data fields populated by thousands or even millions of records. Migrating all of that data to a new platform is an artform of its own. Most software vendors and custom software firms have a team dedicated to migrating data for new customers. With the guidance provided in this post, you can be prepared to work with a data migration vendor and execute a painless transition into your new application.

How Migration Works

Even small applications generally have at least 10-20 data tables with tens of thousands of records in them. It is almost never time or cost-efficient to transfer these records by hand. We also must consider the quality of the data; it will need to be transformed and sanitized to fit the new application architecture.

Your vendor can speed up the migration process by using automation. First, they will analyze the structure of your existing data. By understanding the purpose of each table and column, the vendor can then begin to create scripts designed to map the old data to the new software. These scripts will also be designed to sanitize the data and handle edge cases, such as missing or invalid records.

Your migration vendor will be an expert in automating data migration between systems. However, there will be situations where they need to look to you and your specific business needs to decide how to handle certain cases. For example, what should happen to data stored in the legacy system which doesn’t logically map to any part of the new system? Or how do we handle the situation where the new system requires some data to function that doesn’t exist in the legacy system?

In these cases, the vendor will collaborate with you and your team to solve these scenarios. To facilitate that collaboration, it is important that you assign a dedicated person from your team who will serve as the point of contact for the migration vendor.

Assigning a Point of Contact

Choosing the right point of contact from your team is critical for a smooth and successful migration process. Your point of contact will need to be able to answer any business or data questions that the vendor may have (or redirect those questions to the appropriate team member who can answer them). When choosing a point of contact, consider these criteria:

  • The point of contact should be a domain expert on the legacy application who understands the nuances of its functionality and the form of its data.
  • The point of contact should be responsive and regularly take the time to fully and accurately address the questions proposed by the vendor.
  • The point of contact should be someone who can make executive decisions regarding how data should be transformed or sanitized (or they should have access to the team members who can make those calls).

Since the point of contact will work with the migration vendor throughout the process, they are also the obvious choice to lead testing and verification efforts on the new system once you have transitioned over.

Identify Sensitive Information

To protect your customers, your partners, and your business, it is very important that you identify and call out any sensitive information stored in your data. This should be done BEFORE you transmit any of that data to your vendor. There are many types of sensitive data to consider and these often have consumer protections or regulations in place. Here are a few examples of sensitive information to alert your vendor of:

  • Personally Identifiable Information, such as Full Names, Addresses, Social Security Numbers, and Drivers Licenses. These are protected by state and federal laws.
  • Personal Health Information (PHI) (requires HIPAA compliance). Work with your vendor and compliance officer to make sure HIPAA guidelines are followed for any data transmissions.
  • Payment Information (requires PCI compliance). Consider using a dedicated third-party service such as to manage all transactions and payment information, removing that data from your own systems.

Your vendor should have a secure data transmission process in place. If you have any concerns, ask them how they are protecting your business’s and your customers’ data.

Transmitting Your Data

Once you’ve addressed any data privacy concerns, it’s time to give the vendor a copy of your data so that they can get to work. Now we need to consider where this data is actually stored.

Ideally, your legacy data is stored in a database which you can access, such as on a cloud-hosted or on-site server. If you have an IT person on your team, they should create a backup copy of the database and follow the vendor’s directions for transmitting that copy to them. In the event that you have a cloud-hosted server but no IT person, you may be able to provide the vendor with credentials so that they can access the server and retrieve their own database copy.

If your legacy software has a data export feature, this could be a simple way to extract a copy of your data. If you go this route, you will want to make sure that the export includes all the relevant data which you expect to migrate to the new system. It is very common for export features to only include a subset of data and not the entire dataset. If the export doesn’t have all the necessary data, you will need to fall back to the previously mentioned database copy option.

So what about a worst-case scenario where you don’t have direct access to the database or don’t know where the data is hosted? In that case, you will need to coordinate with the legacy software vendor and your data migration vendor to locate and export your data.

Data Prioritization and Sanitization

Now that the vendor has a copy of your data, you will likely need to make some decisions around the priority of data and how it is sanitized. We’ve mentioned data sanitization, but what is that? Well, depending on the age of your legacy application, it may not have had validations on the data fields. For example, maybe it is possible to enter the text “not a date” in a date field. Newer software systems which execute lots of business logic on data require strict validations on that data to make sure it is proper and valid for calculations. In a system like that, it would crash if we tried to migrate “hello world” into a date column in the database. So the process of sanitizing data allows us to clean up and correct any invalid or malformed data.

Let’s look at a more realistic example. Here I have a date entry which was mistyped in a non-validated field: 11/12/32019. A human could probably look at that and know how to correct the date, but it would be incredibly difficult to create a script which could automatically correct typos like this. So one option is to have a person vet these errors and fix them by hand, but what if we had 3 million records and 20,000 of them have invalid dates? Now you must decide if it’s worth the extra time and cost to have a human manually correct all 20,000 records. If this is on a date field you don’t typically rely on, it might not be worth the effort. In that case, you could choose to remove the invalid date entries, or give them a safe default value.

We’ve looked at one example, but in real world systems you will likely have dozens of date, phone number, address, tax rate, and other fields which expect very specific types of data. In a legacy system, it’s not uncommon to have invalid data interspersed throughout your records. Have you ever seen an employee use an address field to store notes? Or how about storing credit card details in a notes field? It’s more common than you think, and each instance of invalid data must be sanitized before the data can be migrated into the new system. Sometimes it just makes more sense to throw away the bad data. That’s a decision you will have to make with your vendor if and when it comes up.

Transitioning to the New Application

We’re almost to the point where you can use your news system! The vendor has been at it for a few weeks perfecting the migration scripts: they are cleaning up invalid data, filling in some missing content, and running tests on the new system. It’s time to create a plan for transitioning off of your legacy system and onto the new one.

First, we must consider the fact that the data copy provided to the vendor has grown stale as you’ve continued to conduct business on the legacy system while they built the migration scripts. Once you’ve settled on a transition date with the vendor, you will want to send them a fresh database copy as close to that transition as possible. This will allow you to start using the new software with all of the latest business records and transactions intact.

Since you likely can’t cease business operations during the transition, it is inevitable that some transactions won’t make it in the migration. During the transition and training period, you may have to duplicate work into both the legacy software and the new application until you have every employee switched onto the new platform. As long as you prepare your employees for this part of the transition, you can avoid any lost data or delayed business.

Have an audit plan in place with your business domain experts so that they can review your data within the new application. You should expect to spot-check entries throughout the app to make sure all data critical to your business operations were correctly migrated over.

Finally, it is a good idea to keep the legacy system available for a few months after the transition. You may find yourself needing to log back into the legacy system a few times to reference old records or double-check migrated data. It is incredibly helpful to have the legacy system available as a fallback in case you discover any issues with your new system or migrated data.


And that’s it! With proper planning, appropriate role assignment, and a good migration vendor partner, you can minimize the stress inherent to transitioning to a new software platform. By keeping these items in mind and communicating with your vendor, you can have a very clean and efficient migration of any data.

The Great Migration

Embarking on a data migration project is no small feat, no matter the transition circumstances. Be sure you have a technology partner on your side to be certain it’s a smooth, communicative experience—with little disruption to your ongoing business processes.

Are you preparing for a data migration project soon? We have completed hundreds of successful data migrations and are ready to help. Give us a call at 559 560 3300 or email us at [email protected] Want to learn more about custom software in general? Click here.

Corey Shuman is the Director of Software Engineering, overseeing software delivery and development processes at Shift3. He tinkers with robots, builds GPS systems from scratch and makes one helluva spicy mixed drink.