Data Migration to Salesforce – Challenges, Tools, Cleanup, Quality Measurements and Best Practices
Data migrations play a critical role in Quote-to-Cash (QTC or Q2C) transformation projects.
As a leading CRM platform, Salesforce is often integral to the front end of QTC processes, supporting account and opportunity management as well as quoting/CPQ functionalities. As Salesforce expands beyond its original CRM role with modules such as order management, customer support, and billing, the scope of data migrations extends beyond traditional records like accounts, leads, and contacts.
Compared to relational databases, migrating data to Salesforce introduces unique technical challenges, including its schema structure and API-based operations, which often require specialized tools. These challenges and tools are explored in the following sections.
Data Cleanup
Ideally, data should be cleaned before it is imported into Salesforce. However, in certain situations, it might be more practical to perform data cleanup after the import. Some common reasons include:
- Ease of Cleanup: Depending on the data sources, it may be simpler or more efficient to clean data directly within Salesforce using tools such as DemandTools or others.
- Consolidating Data from Multiple Sources: When data is pulled from various sources, using an intermediate staging database might not always be necessary. Importing all data into Salesforce and performing cleanup there can streamline the process.
- Time Constraints: When meeting a go-live deadline is prioritized over achieving fully accurate data, cleanup tasks can be deferred to post-import stages.
- Different Data Models: Source data models might not align perfectly with Salesforce’s target data structure. In such cases, additional updates and transformations are often needed after the data is imported.
A hybrid approach can also be effective: clean the data to a reasonable extent at the source, migrate it, and then perform the remaining cleanup directly in Salesforce. This strategy balances accuracy with efficiency and allows teams to adapt to specific project needs.
Progress and Data Quality Monitoring
Data migrations are iterative processes that require consistent monitoring to ensure accuracy and progress. Migration scorecards are an effective tool for this purpose, providing a clear comparison of row counts and data consistency between source and destination systems. For example:
| Object | Source Count | Target (Salesforce) Count | Difference |
|---|---|---|---|
| Accounts | 100 | 99 | 1 |
| Contacts | 789 | 260 | 529 |
| … |
For large-scale projects, scorecards often include additional details such as:
- Per-load Snapshots: Capturing progress and data quality at each stage of the migration.
- Field-level Breakdowns: Analyzing specific fields (columns) within objects (rows) for discrepancies.
- Summaries: Including aggregated metrics like billed monthly revenue or the size of the sales pipeline.
- Error Rates: Monitoring the percentage of records with errors, anomalies, or inconsistencies.
- Other Metrics: As necessary to suit project-specific goals or challenges.
Establishing success criteria An essential question when designing scorecards is: What defines success in a data migration? Key considerations include:
By addressing these questions early, teams can align on realistic goals and ensure that the data migration meets business needs effectively. |
Salesforce Data Mapping Challenges
When migrating data to Salesforce, several platform-specific technical considerations must be addressed to ensure a successful migration:
| Consideration | Description |
|---|---|
| Object Ids/Primary Keys | Each row in Salesforce is identified by a unique object id. The ids are always auto-generated and cannot be set by users. As a result, the ids change between subsequent data loads and cannot be used as primary key identifiers for migration. Possible solutions to the problem include intermediate tables mapping source data to object ids or using source primary keys as external ids in the Salesforce model. |
| Relations/Foreign Keys | Salesforce object relationships are based on object ids. As a result, migrating relationships from source data will require mapping involving intermediate tables or external ids. |
| Record Types | Salesforce record types enable creation of sub-classes of objects with different layouts, action menus, workflows, etc. For example, Customer, Partner and Vendor types can be defined for the account object. As a result, separate source tables may need to be consolidated into a single Salesforce object during migration. |
Salesforce Data Upload Tools
Salesforce does not provide direct database access; instead, data must be uploaded using specialized tools. These tools can be categorized as follows:
| Tool Type | Examples |
|---|---|
| Data Loaders | Data loaders are import/export tools and can source data from CSV files as well as databases:
|
| Salesforce as a Database | These tools map Salesforce internal storage to SQL databases:
Using these tools, data loading can be easily implemented in SQL. |
| ETL connectors | ETL (Extract, Transform, Load) tools such as Microsoft SSIS have native or third-party connectors to access Salesforce data. |
| Custom Tools | For complex migration logic or highly specialized use cases, custom-developed tools may be required. These tools are tailored to meet unique requirements and can be used alongside or in place of standard tools. |
Implementing Data Migration
It is important to build the migration process so that:
- The process is automated and can be run multiple times.
- Data is loaded incrementally.
- Records are updated rather than removed and re-created.
- Data can be uploaded to multiple Salesforce instances.
- A full sandbox is used for development and testing.
- A staging database is used for cleanup and data transformation:
Source System(s) → Staging Database → Transform & Cleanup → Upload to Salesforce
Conclusions
Data migrations to Salesforce can vary widely in complexity, ranging from straightforward data uploads to large-scale, iterative projects involving automation, staging databases, performance scorecards, and more.
This complexity increases significantly when Salesforce is used beyond its core CRM functions, incorporating modules such as order management, customer support, and other advanced capabilities.
At Nextian, we bring extensive experience in Salesforce data migrations and cleanups, backed by a proven track record of successful projects.
Contact us today to find out how we can help you!
