Part 1: Salesforce Migrations Without the Migraines
Nobody becomes a data engineer dreaming about CRM migrations. Like group projects in high school, they’re often disorganized, poorly planned, and rushed. Yet sooner or later, you’ll be asked to move your company’s data into a CRM like Salesforce.
The hard part isn’t copying rows from point A to point B. It’s the sheer amount of detail. Management rarely grasps it, and for most of the team this is their first migration, so what seems obvious to an experienced developer gets completely overlooked.
I’ve lived through a few of these projects, some run by outside consultants and some by a swarm of project managers. They all land somewhere between epic dumpster fire and a mild throbbing in your forehead. This checklist is the process I wish I’d had on my first one. It’s built around one simple idea. Keep every mapping decision in a table and generate everything else from it. If you do that, every change the business throws at you becomes a re-run instead of a rewrite.
The checklist at a glance
1. Pull the schema from your current database.
2. Pull the schema from Salesforce.
3. Map your columns in a translation table.
4. Clean up picklists.
5. Build the staging tables dynamically with Spark.
6. Validate emails, phone numbers, and URLs.
7. Load the data into Salesforce.
8. Test, adjust, and upsert.
This article covers Steps 1–4, mapping your data. Part 2, The Salesforce Migration Pitfalls Nobody Warns You About, covers Steps 5–8: staging, validating, and loading it.
What you’ll need
The examples use Microsoft Fabric, but the approach works on any platform that has access to Spark. Here’s the stack:
• A Spark notebook. The code uses Fabric’s notebookutils and lakehouse paths. On Databricks, swap in dbutils.secrets.get() and your own storage paths.
• A secret store. The examples read credentials from Azure Key Vault.
• A Salesforce connected app with the OAuth client-credentials flow enabled and a “run as” integration user that can read and write every object you’re migrating.
• Python libraries: requests, pandas, and openpyxl (for reading and writing Excel files).
• A pipeline tool, such as the Data Factory pipelines in Fabric, AWS Glue, or Apache Airflow.
• Access to your source database with permission to read its system tables.
Step 1: Pull the schema from your current database
Start by collecting every table and column in your existing CRM database. If it runs on SQL Server (or another database that supports INFORMATION_SCHEMA), this query does the job:
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
Export the results. You’ll compare them to Salesforce in Step 3.
Step 2: Pull the schema from Salesforce
Next, collect the Salesforce schema. You’ll need a connected app with administrative access to the org as well as the client ID and client secret.
Authenticate
The first function retrieves an access token using the OAuth 2.0 client-credentials flow. The example below runs in a Microsoft Fabric notebook and reads the credentials from Azure Key Vault. Swap in your own secret store if you’re on a different platform, and never hard-code secrets in a notebook.
import requests
import pandas as pd
KEY_VAULT_URI = "https://<your-key-vault>.vault.azure.net/"
TOKEN_URL = "https://your_company.my.salesforce.com/services/oauth2/token"
API_VERSION = "v61.0"
TIMEOUT_SECONDS = 120
def get_access_token():
"""Authenticate to Salesforce with the OAuth 2.0 client-credentials flow."""
get_secret = notebookutils.credentials.getSecret
client_id = get_secret(KEY_VAULT_URI, "salesforce-client-id")
client_secret = get_secret(KEY_VAULT_URI, "salesforce-client-secret")
token_response = requests.post(
TOKEN_URL,
data={
"grant_type": "client_credentials",
"client_id": client_id,
"client_secret": client_secret,
},
timeout=TIMEOUT_SECONDS,
)
if not token_response.ok:
raise RuntimeError(
f"Salesforce authentication failed "
f"({token_response.status_code}): {token_response.text}"
)
token_data = token_response.json()
access_token = token_data["access_token"]
instance_url = token_data["instance_url"]
print(f"Authenticated to instance: {instance_url}")
return access_token, instance_url
ACCESS_TOKEN, INSTANCE_URL = get_access_token()
HEADERS = {"Authorization": f"Bearer {ACCESS_TOKEN}"}
QUERY_URL = f"{INSTANCE_URL}/services/data/{API_VERSION}/query"
Two small helpers for SOQL queries
Every query in this article goes through the same REST endpoint, so wrap it in two small helpers. run_soql() returns every record from a query and follows Salesforce’s pagination, since large result sets come back in batches. count_records() returns just the number of records in an object.
def run_soql(soql_query):
"""Run a SOQL query and return every record, following pagination."""
response = requests.get(
QUERY_URL, headers=HEADERS, params={"q": soql_query}, timeout=TIMEOUT_SECONDS
)
response.raise_for_status()
result_page = response.json()
records = result_page.get("records", [])
while not result_page.get("done", True):
next_page_url = f"{INSTANCE_URL}{result_page['nextRecordsUrl']}"
response = requests.get(next_page_url, headers=HEADERS, timeout=TIMEOUT_SECONDS)
response.raise_for_status()
result_page = response.json()
records.extend(result_page.get("records", []))
return records
def count_records(object_name):
"""Return the number of records in one Salesforce object."""
response = requests.get(
QUERY_URL,
headers=HEADERS,
params={"q": f"SELECT COUNT() FROM {object_name}"},
timeout=TIMEOUT_SECONDS,
)
response.raise_for_status()
return response.json()["totalSize"]
Find the objects that actually hold data
Salesforce calls its tables objects, and the EntityDefinition object stores information about all of them. A typical org has hundreds of objects. To eliminate some of the clutter, I keep only the objects that contain at least one record.
One caution: counting records sends one API request per object, and every request counts against your org’s daily API limit. Counting a few hundred objects once is fine, but don’t put this loop in a pipeline that runs every hour. If you already know which objects you’re migrating, set CORE_OBJECTS_ONLY = True to count just the core objects.
CORE_OBJECTS_ONLY = False
CORE_OBJECTS = ["Account", "Contact", "Lead", "Opportunity", "Event", "Task"]
entity_records = run_soql("""
SELECT QualifiedApiName, DeveloperName, Label
FROM EntityDefinition
WHERE IsQueryable = true
""")
entity_df = pd.DataFrame(entity_records).drop(columns=["attributes"], errors="ignore")
if CORE_OBJECTS_ONLY:
object_names = CORE_OBJECTS
else:
object_names = entity_df["QualifiedApiName"].tolist()
object_row_counts = []
for object_name in object_names:
try:
row_count = count_records(object_name)
except requests.HTTPError:
row_count = 0 # Some system objects can't be counted without a filter.
object_row_counts.append([object_name, row_count])
table_row_df = pd.DataFrame(object_row_counts, columns=["table_name", "row_count"])
used_table_df = table_row_df[table_row_df["row_count"] > 0]
Even at large companies, a handful of standard objects do most of the work. If you’re coming from another CRM, these are the ones you’ll most likely need to line up with:
Salesforce object | What it holds | Typical legacy source |
Account | Companies and organizations | Customers, companies, clients |
Contact | People tied to an account | Contacts, customer contacts |
Lead | Unqualified prospects | Prospects, web inquiries |
Opportunity | Deals in the pipeline | Deals, quotes, sales orders |
Event | Calendar items (meetings, calls) | Appointments, meetings |
Task | To-dos and logged activities | Activities, follow-ups, notes |
Build a data dictionary
Now pull the field-level schema for every object you kept. Each row records the object, the field’s API name, its data type, and its label.
data_dictionary = []
# Swap used_table_df for table_row_df if you want to scan every object.
for table_name in used_table_df["table_name"]:
field_records = run_soql(f"""
SELECT QualifiedApiName, DataType, Label
FROM FieldDefinition
WHERE EntityDefinition.QualifiedApiName = '{table_name}'
""")
for field_record in field_records:
data_dictionary.append([
table_name,
field_record["QualifiedApiName"],
field_record["DataType"],
field_record["Label"],
])
data_dictionary_df = pd.DataFrame(
data_dictionary,
columns=["table_name", "column_name", "data_type", "label"],
)
Convert Salesforce types to Spark types
When you display data_dictionary_df, you’ll notice the data types don’t look like a typical T-SQL schema. The goal is to convert Salesforce types to standard ones, for example Checkbox to a Boolean or Email to a string. Since Spark will likely be the intermediate layer where you manipulate the data, this script strips off the size and converts each type to its Spark SQL equivalent.
data_type_conversion = [
["Auto Number", "STRING"],
["BitVector", "BINARY"],
["Checkbox", "BOOLEAN"],
["Currency", "DECIMAL(18,2)"],
["Date", "DATE"],
["Date/Time", "TIMESTAMP"],
["Email", "STRING"],
["External Lookup", "STRING"],
["Formula", "STRING"],
["Hierarchy", "STRING"],
["IP Address", "STRING"],
["Long Text Area", "STRING"],
["Lookup", "STRING"],
["Master-Detail", "STRING"],
["Name", "STRING"],
["Number", "DOUBLE"],
["Percent", "DOUBLE"],
["Phone", "STRING"],
["Picklist", "STRING"],
["Record Type", "STRING"],
["Roll-Up Summary", "DOUBLE"],
["Text", "STRING"],
["Text Area", "STRING"],
["Time", "STRING"], # Spark has no time-only type, so keep it as HH:mm:ss text.
["URL", "STRING"],
["Address", (
"STRUCT<street: STRING, city: STRING, state: STRING, postalCode: STRING, "
"country: STRING, stateCode: STRING, countryCode: STRING, "
"latitude: DOUBLE, longitude: DOUBLE, geocodeAccuracy: STRING>"
)],
]
spark_type_by_salesforce_type = dict(data_type_conversion)
# "Text(255)" -> "Text", "Lookup(User)" -> "Lookup"
data_dictionary_df["data_type_base"] = (
data_dictionary_df["data_type"]
.astype(str)
.str.split("(")
.str[0]
.str.strip()
)
data_dictionary_df["spark_data_type"] = (
data_dictionary_df["data_type_base"].map(spark_type_by_salesforce_type)
)
# Anything left unmapped needs a decision before you move on.
unmapped_fields_df = data_dictionary_df[data_dictionary_df["spark_data_type"].isna()]
print(unmapped_fields_df["data_type_base"].unique())
You may notice that many types become strings. Your source database almost certainly has no email, phone, or URL data type, so those values arrive as plain text. Salesforce, however, does validate them, so you’ll need to check your data against the right regex patterns before loading it. That validation happens in Part 2.
Step 3: Map your columns in a translation table
Now line up your current CRM columns with the Salesforce fields. An AI assistant like Claude can handle the obvious matches, such as first_name, last_name, and phone_number. But every business is different, and you’ll almost certainly need fields that don’t come out of the box. Custom fields in Salesforce are easy to spot because their API names end in __c.
Take the source schema from Step 1, line it up with the Salesforce data dictionary from Step 2, and save the consolidated version to an Excel file. This becomes your translation table, the single source of truth that drives the dynamic queries later on.
source_table | source_column | sf_table | sf_column | spark_data_type |
dbo.customers | customer_id | Account | Legacy_Id__c | STRING |
dbo.customers | company_name | Account | Name | STRING |
dbo.customers | industry | Account | Industry | STRING |
dbo.contacts | first_name | Contact | FirstName | STRING |
dbo.contacts | email_address | Contact | STRING | |
dbo.contacts | region | Contact | Region__c | STRING |
Notice the Legacy_Id__c field. Create an External ID field like this on every object you migrate, and map your old primary key to it. It’s how you’ll trace a Salesforce record back to its source, and it’s what makes the upserts in Step 8 (Part 2) possible. If you plan to map record owners, add an owner_email helper row as well (covered in the pitfalls section of Part 2).
Expect the business to change its mind. They’ll rename columns, add new ones, and rethink entire objects. The translation table keeps everyone organized, and because your code reads from it, adjusting to a change means editing a row rather than rewriting a query.
Also expect the business to make a slew of changes a week or two before launch. They often oversimplify their early tests and stubbornly wait until the bitter end to do any real testing. That’s when these translation tables really earn their keep.
Step 4: Clean up your picklists
A CRM migration is usually, at least in part, a chance to fix the governance problems of the old system. It’s a great time to turn free-text fields into drop-down picklists. But when a column has hundreds of unique values that need to be whittled down to 10 or 20 choices, the task can be daunting.
If the business has already started building picklists in Salesforce, pull every active value with the Describe API:
def get_picklist_values(object_name):
"""Return [object, field, value, label] for each active picklist value."""
describe_url = (
f"{INSTANCE_URL}/services/data/{API_VERSION}"
f"/sobjects/{object_name}/describe"
)
response = requests.get(describe_url, headers=HEADERS, timeout=TIMEOUT_SECONDS)
response.raise_for_status()
picklist_rows = []
for field in response.json()["fields"]:
if field["type"] in ("picklist", "multipicklist"):
for picklist_option in field["picklistValues"]:
if picklist_option["active"]:
picklist_rows.append([
object_name,
field["name"],
picklist_option["value"],
picklist_option["label"],
])
return picklist_rows
all_picklist_rows = []
for object_name in used_table_df["table_name"]:
all_picklist_rows.extend(get_picklist_values(object_name))
salesforce_picklist_df = pd.DataFrame(
all_picklist_rows,
columns=["sf_table", "sf_column", "picklist_value", "picklist_label"],
)
salesforce_picklist_df.to_excel(
"/lakehouse/default/Files/salesforce_pick_list.xlsx", index=False
)
Then pull the distinct values from the matching columns in your current CRM. This is where the translation table really pays off, because it already tells you which source column feeds each picklist field.
SELECT 'Account' AS sf_table,
'Industry' AS sf_column,
industry AS crm_value,
COUNT(*) AS row_count
FROM dbo.customers
GROUP BY industry
ORDER BY row_count DESC;
Next, let Claude take the first pass at matching. Here’s a sample prompt:
Attached is salesforce_pick_list.xlsx, which lists every Salesforce picklist field and its available choices, and crm_options.xlsx, which contains the current values from our CRM. Match each CRM value to the most likely Salesforce choice. Return one table with these columns: sf_table, sf_column, crm_value, picklist_value, and confidence_score. Score each match from 1 to 5, where 5 means highly confident and 1 means little confidence. If no reasonable match exists, leave picklist_value blank and score it 1.
The bottom line
None of this is glamorous. You haven’t moved a single record yet, and the business is probably already asking when they’ll see data in Salesforce. But this groundwork is what separates a migration you control from one that controls you.
You now have four tables: your source schema, a Salesforce data dictionary, a translation table, and a picklist translation table. Every mapping decision lives somewhere the business can see and edit instead of being buried in your code. So when they rename a column two weeks before launch (and they will), you’ll update a row instead of rewriting a query.
In Part 2, The Salesforce Migration Pitfalls Nobody Warns You About, we’ll put those tables to work. We’ll generate the staging tables with Spark, validate emails and phone numbers, load everything into Salesforce, and dodge the problems that turn go-live weekend into a 2:30 a.m. fire drill.
Click here to see Part 2. Have a migration war story? Share it in the comments.
Comments