Data migration could be most time consuming and critical since it directly impacts Business. Imagine your salary being deposited in my account because of data issue 😉 Don’t worry but this depicts how critical Data is for business.
1) Every Data Migration is Unique
No matter how many times you do it, experience is going to be different. With every data migration, you need to update your checklist of learning to be referred for the next one. So, I would advise not to try to fit one pattern to all migrations. Keep your mind open and watch for those surprises.
2) Asking right questions
Asking right questions before you start thinking too deep is a way to reduce the pain later. Always start with some basic questions and move to medium and high complexity. You can ask these questions to yourself, Stakeholders or Architects. That’s how you learn. Later section lists some of these questions which would help you start.
3) Sign off on the Data Mapping
One of the most important activity you should be spending time on is – Data Mapping between Source and Target System. Identifying gaps between systems helps reduce data loss. Even after sign-off we miss a few things and it’s perfectly ok. But revise and revise till you get there.
4) Volume & Frequency of the Data Load
Is this one-time activity or would be repeated? How many records we are talking about? These questions will help you understand the right tools to be used. There are lot of automatons available to perform the complex migrations. Depending on size of the data you may need to think about ETL (Extract, Transform, Load) tools like Jitterbit, Informatica or Talend or schedule a batch job. Why? Ever tried opening extract of 300000 records in excel and performing VLOOKUP. You have your answer.
5) Validate the Data Load
We concentrate more on data load, and sometimes miss a point that we are equally responsible to validate data in Production. No matter what type of data you are migrating, setup ways to analyse. Validate that data has been correctly migrated. Create reports, validate the # of records for each type of load, perform mock data loads in full boxes prior to live.
The 5 pillars above helped me go through migration’s – simplest to most complex. Now what type of questions we should be asking. I have been keeping these questions as checklist and revise based on my learning. This comes handy every time you have a new requirement of Data Migration and need a quick estimation.
How to Estimate the efforts
When you need to estimate the efforts for the data load there are some factors which you should focus on. There are more but I am just listing few of them to get you started.
What type of data are we migrating?
Is it from Legacy system, part of Data Clean-up, Re-alignment of new regions, Dated exchange rates or anything else? The Source system helps you decide next course of action and analyse if you need any external Id to create a link between Source and Target records or are there additional records which needs to be created because of target system dependency.
What type of objects you need to Migrate data for?
Identify objects and their relationship. This helps you determine the correct order of execution. Objects may not be directly related, but sometimes are needed to be imported in certain order. Consider OpportunityLineItem – you need Pricebook with correct currency.
What are the Data Types of the Columns from CSV?
- Lookups/M-D Columns :Identify how many related object columns (lookups or M-D) you have. This has a greater impact on the Data migration efforts. More lookups mean more dependency on data. For each lookup, try different ways to identify the existing records from system. Not all clients have a fully functional duplicate management system and not on all objects. The cherry on the top is – objects which are M-D in target system. Now you also need to think about populating data in all such fields before actual migration.
Quick Tip: If you don’t know how VLOOKUP works in excel, high time to learn it. You will be needing it quite often.
- Date fields : Date & Datetime fields need special attention. The tool you are using to import data needs to be configured to follow the same time zone, else you might find incorrect timestamps. This actually happened with one of my projects when I just started my career. Data loader was configured to use the “IST” and the datetime fields were migrated to users with “EST” time zone. Dates were all messed up and we had to redo the migration.
Quick Tip : So, do consider the time zones while uploading data.
- Picklists & Multi-Selects : Picklists seems easy to import, right? Well, they can also hit you hard.
- Restricted Picklist = No : If values do not match records will still have these values and will also be added on your actual field as “Inactive”. But once user selects correct value, they no longer are available for selection. This creates confusing system behaviour.
- Restricted Picklist = Yes : Validate all values match the ones mentioned on field in Salesforce. Avoid the approach to remove it as “Restricted” and upload data. No doubt It will work, but not a best practice. Reason is point 1 above. Value being stored as temporary on record. Creates a lot of confusion for end users.
- Global Picklist : Check which objects share the same picklist. Adding new value would reflect in all related objects. Communicate this to Business and Architect as well.
- Record Types : Enable only on the record types which are relevant. While upload check if values are enabled on the record type you are using.
- Dependent Fields: Confirm if this field is controlling or dependent on another field. This might create issues with upload if dependency is not defined or is incorrect.
- Multi-Picklist :Confirm if you need to override existing values on records or need to append. If you need to append, extract and add the new values and then upload. Else you will be overwriting existing values in multi-picklist. e.g. Assume we have a multi-picklist field “Skills” and we need to scenario mentioned below. Then you need “Append” action to be performed. -Existing values: Apex, LWC , -New Values should be: Apex, LWC, JAVA
- Required fields : I am referring fields marked as required in Target system but were not required in Source system. Analyse which ones are required on FLS and which are on Page Layouts.
Key thing to remember here is: “Field Level” required flag acts on Database level while the “Page Layout” required flag acts on UI level. So, if field is required on Page Layout and you perform Data Load, no error will be thrown. But when users will try to edit records in Salesforce from UI, error will be displayed.
- Validation Rules on individual objects : You may not be able to figure out all the required ones but at-least try to identify the ones which will stop you. Deactivating Validation rules is not my personal favorite as we are defying the basic purpose of creating them – “Keep the Data Clean”. But there are some validation rules which might need to turn off. Check with leads or architect’s before you deactivate.
- Automations to be Disabled : List all the possible triggers and other automations you may need to deactivate for data load. Critical ones would be like sending emails. One instance where one of my team members performed data load and missed the step to deactivate workflow. Thousands of emails were triggered to end users. Now you can imagine what we were doing for next few days. Pay attention to integrations. Check if any of the fields might trigger integrations to the external system and do, we really need to trigger these. Volume of data might hit the callout limits, if not handled gracefully by integrations.
- Record Types : Identify the correct record types for the objects you need to import data for. Mapping incorrect record type would result in incorrect assessment of all the above points.
So, confirm before you proceed.
- Record Ownership : Who would be the owner for new records? Is that user Active in Salesforce and configured with correct permissions. Record ownership has direct impact on the visibility of records and affects reporting. You may need to decide whether you need immediate OWD (organisation Wide Defaults) re-calculation or Deferred Re-calculation to avoid record locking. So, add few checkpoints with specific business users to validate these points.
You should also prepare a “Data Migration Plan” listing all the activities and owners for each activity. This helps to track all tasks and reduces # of steps being missed. But don’t worry for now. Architects in your team usually take care of this. But it’s important to understand correct flow.
I hope these quick tips would help you understand the impact of the Data Migrations on Salesforce ecosystem. There is much more complexity, but these are ground rules and every time you perform Data Load you will be adding few more 🙂
Sample Data Migration Checklist:
Now you are geared up for your next Data Migration. So, all the best. Feel free to share your feedback and comments.