Data Migration Best Practices

Data migration is a normal and common part of working with Salesforce. most companies have different systems used for different purposes. Keeping track of all this data can be cumbersome and challenging. This is one reason many companies are looking to integrate or replace systems and move to as much of a centralized system as possible. As part of this, Data migration becomes a major discussion.

When moving data between systems we have pay careful attention to how we move it, where each piece of data gets loaded to, and what needs to be updated. Even in spreadsheets almost all business data is in some sort of relational setup and those relationships are important to maintain. How do we work with moving this data around and keep the relationships in tact?

Here are some tips and best practices I have found that help with moving data. While these are designed around the Salesforce Platform, the concepts can apply to any data migration project with any system.

ID your Data
The first point to bring up is marking your data with ID's. No matter what your data currently sits on whether that be Excel or some other system, always have an ID related to each record. This will go a long way in helping keep sense of what data belongs where as we move into the relationships.

This ID must be absolutely Unique for each record you are working with (at least within each table). I would strongly suggest the ID also be case sensitive as this helps avoid confusion and relationships.

Data Mapping
Identify where in the new system your data will go. In the Salesforce world, this means identifying which objects and fields your data will be imported into. For other systems this might simply be the tables you are importing into.

Import Template(s)
When setting up your data for import it is helpful to have or create an import template. For the Salesforce Platform, this is an excel spreadsheet saved as a csv. The top row defines what fields we will be putting data into within a specified object (one spreadsheet for each object). Having this template can assist you in creating your data map and getting your data into the correct fields.

With Salesforce, if you use the field names in your template it will help as most import tools can automatically pull the import mapping based on the field names if they match.

Data Loading Processes
When it comes tie to load the data, a really goo approach if possible is to run the loading processes with "Upsert" functions. This means that the system looks at your ID field (Which would be mapped to the External ID field created earlier), if a record is found that matches the ID of the record being loaded, it simply updates the existing record. If no match is found, a new record is created.

By using this command you can run multiple iterations of a data load process without as much worry about creating duplicates and causing yourself a nightmare in data de-junking later on.

Loading with Errors
It would be surprising if you were to load your data and have 100% success on the first run. Don't get discouraged, data migration is a time consuming processes and this is where much of the time can be spent. Load the data and then work off the error files. Sort the files by errors and take the rest of the processes one error at a time. Work on 1 error, once that is fixed and the data is loaded, move on to the next error and continue the process until all or at least most of the data is loaded (depending on your situation, leaving some data unloaded may be acceptable).