Salesforce Migration
Introduction
At Ankorstore, Salesforce is used as the CRM tool to contact brands and retailers that use the marketplace. The company needed move from an expensive Enterprise Salesforce billing plan to a new instance of Salesforce on a Professional billing plan.
This migration includes :
a migration of data between the two instances and
setting up new connections to ingest data to Salesforce
setting up new connections to ingest data from Salesforce
Problem Statement
The Salesforce plan to support a team of 500 people using Enterprise grade Salesforce services is an expensive solution for CRM tooling. It is an intelligent decision to migrate to a less expensive Salesforce tool to save costs every month. On a similar thinking, implementing the Salesforce migration internally is less expensive than hiring salesforce migration experts external to the organisation, given the data team has a good understanding of the implemented CRM tooling.
The migration plan is successful if :
historical data is moved from old SF instance to new SF instance
new connections can load SF data to BigQuery (using Stitch or Airflow) and load data from BigQuery (using Hightouch)
data schema of SF objects is updated and new columns are added or deleted
metadata from old instance is migrated for the teams that use Salesforce
measures to monitor the health of new SF instance are set up
Implementation
Salesforce data needs to be migrated in a specific order because some objects have mandatory relationships. The dependencies in these relationships dictate the order of data migration.
This is because every time data is added to Salesforce, Salesforce creates a new primary key for the data ingested. This means that data from previous Salesforce instance will have newly created primary keys, and the new ingested data cannot use to the previous key values anymore as foreign keys for other objects.
Example :
All Salesforce accounts have owners
All Salesforce opportunities are linked to accounts
So it is necessary to load users first, then accounts so that the account owners can be found in the users table
And then load the opportunities so that the accounts linked to each opportunity can be found in the accounts table
Migration Plan :
Preparation :
Migrate the metadata and configurations to new Salesforce instance
Select the data to migrate (Salesforce tables and list of columns for each object)
Map the data to new Salesforce IDs
When accounts are migrated, users should already be in the new Salesforce instance
Map the new user_id for each account owner
Migration :
Add a new sync between the warehouse (BigQuery) and Salesforce (we used Hightouch to do this)
Create a mapping table between the old Salesforce IDs and the new Salesforce IDs for each table that is ingested to the new Salesforce system
Ingest all historical data from old Salesforce instance to BigQuery (we use Airflow and/or Stitch to do this)
Transform the data in warehouse :
Use the mapping table to replace the old salesforce IDs with the new salesforce IDs.
Some IDs can be linked to more than one fields
An opportunity can be related to a user or an account or a case. So the old IDs in this column should be mapped to three mapping columns (polymorphic fields)
Test the new data by inserting it in a Sandbox environment to see how many rows of data are accepted or rejected. Set a threshold between 95 to 99% of data to be inserted in the Sandbox.
Once the data accepted in Salesforce and some data checks like duplicates, null checks, checks for valid data type and accepted data values are satisfactory, send the data to a new production Salesforce instance.
High level data migration diagram
Migration details
—> Order of insertion
Salesforce data needs to be migrated in a specific order because some objects have mandatory relationships. The dependencies in these relationships dictate the order of data migration.
For example, all Salesforce accounts have owners, and all Salesforce opportunities are linked to accounts. So it is necessary to load users first, then accounts so that the account owners can be found in the users table, and then the opportunities so that the accounts linked to each opportunity can be found in the accounts table.
—> Ingest Salesforce production data into BigQuery
We use Stitch to import all data from current Salesforce instance into BigQuery datalake.
—> Use dbt to create migration data
Using dbt, we create data tables for migration that can be ingested into the new Salesforce instances.
There are many complexities that need to be resolved to make the data ready for migration :
Complexity 1 :
Create a new column old Salesforce ID
For every salesforce object, we create a new column OldSfId__c which is the same as the record id (primary key) of the object.
We do this because during migration, the new salesforce instance will generate a new primary key for each row, so duplicating this column will help us preserve the original primary key.
We create a new column OldSfId__c for each object which contains the old primary key. Salesforce will create new primary keys during migration in the id column
Complexity 2 :
For each Salesforce object, replace the old foreign keys with new ones.
Some Salesforce objects contain relationships to other objects. For example, each account is linked to an account owner which is found in the user table. As the user id changes after migrating the user table, there is a need to establish relationship with the new user id in the account object too.`
We do this because during migration, the new salesforce instance will generate a new primary key for each row, so duplicating this column will help us preserve the original primary key.
Salesforce creates new primary keys in the id column and we create a column OldSfId__c to preserve the old primary keys. We also replace the foreign key (ownerid) with the new keys and preserve the old foreign keys (old_ownerid)
Using such matching tables, we replace the old foreign key (for example ownerid) with the new foreign key
Complexity 3 :
Replace inactive users in the data tables
Some users from the old Salesforce instance are not present anymore in the new Salesforce instance. We replace these inactive users with some active users for each object to migrate.
Complexity 4 :
Polymorphic relations
Sometimes, a column in a Salesforce object can have multiple foreign key relations, i.e., a single column can be related to more than one table.
In this case, the first three characters in the column value dictate which object the value is related to.
For example, a row in the email_message object can be related to a contact, case or lead. If the relationid column starts with 003, it is a contact. Prefix 005 denotes a user and 00Q denotes a lead.
For polymorphic columns, we do not replace the old foreign key with the new one. Instead, we create new columns to indicate which object the row relates to.
For polymorphic relations, we do not replace the old foreign keys with the new ones in dbt. We create additional columns to indicate which relation the foreign key comes from. In Hightouch, using these additional columns, we replace the old foreign key with the new one.
Migration detailed diagram
Objects without foreign relations are migrated during 3rd step. (salesforce_migration_* tables)
Objects with foreign key relations are migrated during 8th step. (salesforce_migration_* tables)
Objects with only active users are migrated during 10th step (salesforce_migration_active_* tables)
Results
Cost efficiency
From 150$ per user per month on Enterprise plan to 75$ per user per month on Professional plan
Reducing 50% of Salesforce cost directly from migration
Smooth migration from legacy Salesforce instance to new leaner Salesforce CRM in 2 months, comfortably before deadlines
Easy adoption of teams to new Salesforce system
96%+ of legacy data successfully migrated across all data objects.
Salesforce historical data cleaning