Part 2: The Salesforce Migration Pitfalls Nobody Warns You About
It’s 2:30 a.m. on go-live weekend. The load you tested three times just finished, and Salesforce is reporting thousands of failed records. Half your Accounts belong to a sales rep who left the company two years ago. Every Opportunity claims it was created today. And somewhere, an automated welcome email just went out to your entire customer list.
None of that is a mapping problem. Your translation tables can be perfect, and the load can still go sideways.
In Part 1, we did the groundwork: pulling both schemas, mapping every column in a translation table, and cleaning up the picklists. This article covers the other half. We’ll stage, validate, and load the data, and tackle the pitfalls that turn go-live weekend into the scene above.
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.
Part 1 covered Steps 1–4. This article picks up at Step 5. The pitfalls section comes right before the load in Step 7, because that’s when you need it.
The table-driven migration flow. Everything downstream is generated from the translation tables, so fixes loop back to the tables, not the code.
Before you start
This article uses the same Microsoft Fabric setup as Part 1. The code also reuses three things from Part 1, so run those cells first in the same notebook:
the get_access_token(), run_soql(), and count_records() helpers, plus the ACCESS_TOKEN, HEADERS, INSTANCE_URL, API_VERSION, and TIMEOUT_SECONDS values they use
data_dictionary_df, the Salesforce data dictionary
your translation table and picklist translation table, saved to the lakehouse
Step 5: Build the staging tables dynamically with Spark
Save the results from Step 4 in a separate picklist translation table and let the business team review and adjust it. Have them start with the low-confidence scores.
Now it’s time for the Spark magic. Because every mapping decision lives in a table, the code that builds your Salesforce-ready data never hard-codes a column name. This script reads both translation tables and writes one Spark SQL query per Salesforce object. Every picklist column gets a LEFT JOIN to the picklist translation table, which swaps the raw CRM value for its approved picklist value. The results are saved to your lakehouse. It assumes each Salesforce object is fed by a single source table.
translation_df = pd.read_excel("/lakehouse/default/Files/translation_table.xlsx")
picklist_columns_df = spark.sql("""
SELECT DISTINCT sf_table, sf_column
FROM pick_list_translation
""").toPandas()
def build_staging_query(sf_table):
"""Generate the SQL that builds one Salesforce object's staging table."""
table_mapping = translation_df[translation_df["sf_table"] == sf_table]
source_table = table_mapping["source_table"].iloc[0]
table_picklists = picklist_columns_df[picklist_columns_df["sf_table"] == sf_table]
picklist_columns = set(table_picklists["sf_column"])
select_columns = []
picklist_joins = []
for mapping_row in table_mapping.itertuples():
source_value = (
f"CAST(src.`{mapping_row.source_column}` AS {mapping_row.spark_data_type})"
)
if mapping_row.sf_column in picklist_columns:
join_alias = f"picklist_{len(picklist_joins) + 1}"
picklist_joins.append(
f"LEFT JOIN pick_list_translation AS {join_alias}\n"
f" ON {join_alias}.sf_table = '{sf_table}'\n"
f" AND {join_alias}.sf_column = '{mapping_row.sf_column}'\n"
f" AND {join_alias}.crm_value = src.`{mapping_row.source_column}`"
)
source_value = f"{join_alias}.picklist_value"
select_columns.append(f"{source_value} AS `{mapping_row.sf_column}`")
select_list = ",\n ".join(select_columns)
join_list = "\n".join(picklist_joins)
return f"SELECT {select_list}\nFROM {source_table} AS src\n{join_list}"
for sf_table in translation_df["sf_table"].unique():
staging_query = build_staging_query(sf_table)
print(staging_query)
spark.sql(staging_query).write.mode("overwrite").saveAsTable(
f"sf_stage_{sf_table.lower()}"
)
The script prints every query before running it, so you can see exactly what it does. For the sample translation table from Part 1, the Account query looks like this:
SELECT CAST(src.`customer_id` AS STRING) AS `Legacy_Id__c`,
CAST(src.`company_name` AS STRING) AS `Name`,
picklist_1.picklist_value AS `Industry`
FROM dbo.customers AS src
LEFT JOIN pick_list_translation AS picklist_1
ON picklist_1.sf_table = 'Account'
AND picklist_1.sf_column = 'Industry'
AND picklist_1.crm_value = src.`industry`
If something looks wrong, paste the query into a SQL cell and debug it there.
Any CRM value without an approved match becomes NULL, which is exactly what you want for a restricted picklist. Salesforce would reject it anyway. Just make sure each crm_value appears only once per column in the picklist translation table. A duplicate would match twice and duplicate the record.
Step 6: Validate emails, phone numbers, and URLs
Once the staging tables are built, check the email, phone, URL, and postal code fields against the right regex patterns, and make sure no text is too long for its field. Here are some reasonable starting points:
Salesforce type | Max length | Starting regex (adjust to your data) |
80 | ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$ | |
Phone (US) | 40 | ^\+?1?[\s.-]?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$ |
URL | 255 | ^(https?://)?([\w-]+\.)+[\w-]{2,}(/\S*)?$ |
Postal code (US) | 20 | ^\d{5}(-\d{4})?$ |
I won’t go deep into data-quality techniques here, but if you want an easy way to find outliers, see Stop Fixing Your Users’ Sloppy Excel Files.
The validation code runs in four short parts. The first part sets up the patterns and three small helpers that every check shares:
KEY_COLUMN = "Legacy_Id__c"
REGEX_BY_TYPE = {
"Email": r"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$",
"Phone": r"^\+?1?[\s.-]?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$",
"URL": r"^(https?://)?([\w-]+\.)+[\w-]{2,}(/\S*)?$",
}
POSTAL_CODE_PATTERN = r"^\d{5}(-\d{4})?$" # US ZIP codes. Adjust for other countries.
MAX_LENGTH_BY_TYPE = {"Email": 80, "Phone": 40}
SIZED_TYPES = ["Text", "Text Area", "Long Text Area", "URL"]
error_queries = []
def get_stage_table(table_name, column_name):
"""Return the staging table to check, or None if the column wasn't staged."""
stage_table = f"sf_stage_{table_name.lower()}"
if not spark.catalog.tableExists(stage_table):
return None
staged_columns = spark.table(stage_table).columns
if KEY_COLUMN not in staged_columns or column_name not in staged_columns:
return None
return stage_table
def to_sql_pattern(regex_pattern):
"""Spark SQL treats a backslash as an escape character, so double each one."""
return regex_pattern.replace("\\", "\\\\")
def build_error_query(stage_table, table_name, column_name, failure_condition):
"""Return a query that lists every staged value that fails one check."""
return f"""
SELECT '{table_name}' AS sf_table,
'{column_name}' AS sf_column,
CAST(`{KEY_COLUMN}` AS STRING) AS legacy_id,
CAST(`{column_name}` AS STRING) AS bad_value
FROM {stage_table}
WHERE `{column_name}` IS NOT NULL
AND {failure_condition}
"""
The second part checks every email, phone, and URL field against its regex pattern. Postal codes are stored as plain Text fields in Salesforce, so they’re found by name instead of by type:
fields_to_check = data_dictionary_df[
data_dictionary_df["data_type_base"].isin(list(REGEX_BY_TYPE))
]
for field_to_check in fields_to_check.itertuples():
stage_table = get_stage_table(field_to_check.table_name, field_to_check.column_name)
if stage_table is None:
continue
sql_pattern = to_sql_pattern(REGEX_BY_TYPE[field_to_check.data_type_base])
error_queries.append(build_error_query(
stage_table,
field_to_check.table_name,
field_to_check.column_name,
f"NOT `{field_to_check.column_name}` RLIKE '{sql_pattern}'",
))
postal_code_fields = data_dictionary_df[
data_dictionary_df["column_name"].str.endswith("PostalCode")
]
for postal_code_field in postal_code_fields.itertuples():
stage_table = get_stage_table(
postal_code_field.table_name, postal_code_field.column_name
)
if stage_table is None:
continue
sql_pattern = to_sql_pattern(POSTAL_CODE_PATTERN)
error_queries.append(build_error_query(
stage_table,
postal_code_field.table_name,
postal_code_field.column_name,
f"NOT `{postal_code_field.column_name}` RLIKE '{sql_pattern}'",
))
The third part catches text that’s too long. A value longer than the field allows makes the whole record fail, and the limits are already in your data dictionary. Text(255) means 255 characters, and Email and Phone fields have fixed limits of 80 and 40:
length_checks_df = data_dictionary_df[
data_dictionary_df["data_type_base"].isin(SIZED_TYPES + list(MAX_LENGTH_BY_TYPE))
].copy()
# "Text(255)" -> 255. Email and Phone fall back to their fixed limits.
length_checks_df["max_length"] = (
length_checks_df["data_type"]
.astype(str)
.str.extract(r"\((\d+)\)$")[0]
.astype(float)
.fillna(length_checks_df["data_type_base"].map(MAX_LENGTH_BY_TYPE))
)
length_checks_df = length_checks_df.dropna(subset=["max_length"])
for length_check in length_checks_df.itertuples():
stage_table = get_stage_table(length_check.table_name, length_check.column_name)
if stage_table is None:
continue
error_queries.append(build_error_query(
stage_table,
length_check.table_name,
length_check.column_name,
f"LENGTH(`{length_check.column_name}`) > {int(length_check.max_length)}",
))
The last part combines every check into one query and saves the failures to the error_log table. If nothing fails, it drops the old log, so you never review errors left over from an earlier run:
if error_queries:
error_log_query = "\nUNION ALL\n".join(error_queries)
spark.sql(error_log_query).write.mode("overwrite").saveAsTable("error_log")
else:
spark.sql("DROP TABLE IF EXISTS error_log")
print("No validation errors found.")
Start your review with a summary, so you can see which fields have the most problems:
SELECT sf_table,
sf_column,
COUNT(*) AS error_count
FROM error_log
GROUP BY sf_table, sf_column
ORDER BY error_count DESC;
The linked article exports a highlighted Excel file, but I don’t recommend this approach for a large migration, since the file can quickly become too large to open. Query the error_log table instead. The business can then fix the bad records in the source system or agree to load them as NULL.
The pitfalls nobody warns you about
Steps 5 and 6 get your data shaped correctly. But a perfectly shaped dataset can still blow up on load day. These are the issues that cause the 2:30 a.m. scene above, so handle them before you press go.
Record owners. Every Account, Contact, Lead, and Opportunity needs an OwnerId, and your legacy system doesn’t know Salesforce user IDs. Map the old owner’s email address to a helper column (owner_email) in the translation table, then swap it for the matching Salesforce user. Anything without a match falls back to a default owner, such as your integration user.
DEFAULT_OWNER_ID = "005000000000000AAA" # Replace with your integration user's ID.
user_records = run_soql("SELECT Id, Email FROM User WHERE IsActive = true")
users_df = pd.DataFrame(user_records)[["Id", "Email"]]
users_df["owner_email"] = users_df["Email"].str.lower()
users_df = users_df.drop_duplicates(subset="owner_email")
users_spark_df = spark.createDataFrame(users_df[["Id", "owner_email"]])
users_spark_df.createOrReplaceTempView("salesforce_users")
account_columns = [
column_name
for column_name in spark.table("sf_stage_account").columns
if column_name not in ("owner_email", "OwnerId")
]
account_select_list = ", ".join(
f"stage.`{column_name}`" for column_name in account_columns
)
spark.sql(f"""
SELECT {account_select_list},
COALESCE(sf_user.Id, '{DEFAULT_OWNER_ID}') AS OwnerId
FROM sf_stage_account AS stage
LEFT JOIN salesforce_users AS sf_user
ON LOWER(stage.owner_email) = sf_user.owner_email
""").write.mode("overwrite").saveAsTable("sf_load_account")
Original created dates. By default, every migrated record looks like it was created on load day, which wrecks reporting on deal age and customer tenure. To keep the original CreatedDate and CreatedById, turn on Enable “Set Audit Fields upon Record Creation” in Setup and grant that permission to your integration user. These fields can only be set when a record is inserted, never on an update, so get them right on the first load.
Validation rules, flows, and triggers. Automation built for users typing one record at a time will fire on every migrated row. That means slow loads, failed records, and sometimes a wave of unwanted emails. A common pattern is a custom “Bypass Automation” permission that your rules and flows check for, assigned only to the integration user. In sandboxes, also set email deliverability to System email only so test loads never reach real customers.
Duplicate rules. Salesforce’s duplicate rules can block inserts that look like existing records. Remove duplicates in your Spark staging tables first, so the load isn’t the first place they’re caught.
Time zones. Salesforce stores every date/time in UTC. If your source stores local time, convert it during staging with to_utc_timestamp(created_at, 'America/Chicago'), or every timestamp will be off by several hours.
Text that’s too long. A value longer than the field allows makes the whole record fail. The length checks in Step 6 catch these before the load, using the limits from your data dictionary.
Record types. If an object has more than one record type, every row needs the right RecordTypeId. Add a column for it to your translation table and look up the IDs from the RecordType object by DeveloperName.
Files and attachments. Documents, images, and notes are their own workstream. They load into ContentVersion and are much heavier than regular records, so plan and time them separately.
Step 7: Load the data into Salesforce
With clean staging tables in hand, use a tool like Data Factory in Microsoft Fabric, Azure Data Factory, or AWS Glue to copy the data directly into Salesforce.
Load in dependency order
A child record can’t point to a parent that doesn’t exist yet, so load parents first. A typical order looks like this:
Order | Object | Depends on |
1 | User | None (create or activate users first) |
2 | Account | User (owner) |
3 | Contact | Account |
4 | Lead | User (owner) |
5 | Opportunity | Account |
6 | OpportunityContactRole | Opportunity, Contact |
7 | Event and Task | Contact, Account, Opportunity, or Lead |
Link records by External ID, not Salesforce ID
You don’t know a parent’s Salesforce ID until after it’s loaded, but you do know its legacy ID. Salesforce lets you set a lookup by referencing the parent’s External ID with relationship notation. To link a Contact to its Account, map the legacy account ID to Account.Legacy_Id__c instead of AccountId. For a custom lookup, the relationship name ends in __r, for example Region__r.Legacy_Id__c. This avoids an extra round trip to look up Salesforce IDs between loads.
Use upsert and the Bulk API
In your copy activity, set the Salesforce sink’s write behavior to Upsert and the External ID field to Legacy_Id__c. For anything beyond a few thousand records, make sure the connector uses the Bulk API, which loads data in large batches.
Before the production load, run the full pipeline into a full-copy sandbox at least once. It’s the only way to find out how long the real load will take, and which validation rules and automations will fight you along the way.
Step 8: Test, adjust, and upsert
We all know the process is never this easy. You’ll find issues in every round of testing. The good news is that you rarely need to touch the code. Update the translation table, re-run Steps 5 and 6, and upsert the results on the Legacy_Id__c External ID. Records that already exist get updated, new ones get inserted, and nothing gets duplicated.
Most load tools support upsert natively. To fix a single record by hand, the REST API works too:
def upsert_record(sf_table, legacy_id, field_values):
"""Insert or update one record, matched on its Legacy_Id__c External ID."""
upsert_url = (
f"{INSTANCE_URL}/services/data/{API_VERSION}"
f"/sobjects/{sf_table}/Legacy_Id__c/{legacy_id}"
)
response = requests.patch(
upsert_url, headers=HEADERS, json=field_values, timeout=TIMEOUT_SECONDS
)
response.raise_for_status()
return response.status_code # 201 = created, 204 = updated
upsert_record("Contact", "C-10042", {"Email": "jane.doe@example.com"})
The bottom line
Your next CRM migration won’t be easy, but if you follow this playbook, you may only need a few Advil along the way. Most of your coworkers will be newbies at this, so expect the requirements to keep moving under their feet.
You can’t foresee every problem, but across both parts of this series, you’ve built a process that keeps you in control. Your schema, column mappings, and picklist decisions live in tables the business can see and edit. Your validation catches bad data before Salesforce does. And because every load is an upsert on Legacy_Id__c, every change becomes a quick re-run instead of an emergency rewrite. That’s the difference between a calm go-live weekend and the 2:30 a.m. scene this article opened with.
This series doesn’t cover cutover planning, integrations with other systems, or user training. Each deserves its own article.
If you’re frustrated, exhausted, and ready to throw in the towel, congratulations. You’re one step closer to becoming a migration champion. With each project, you’ll pick up simple tricks that make the next migration a little less painful.
Missed the first half? Start with Part 1. Have a migration war story or a better regex? Share it in the comments.
Comments