FileMaker to Salesforce migration, part 2: load with Bulk API 2.0 and external IDs, fix load errors, reconcile and cut over with a rollback plan

Note

FileMaker to Salesforce migration series - part 2 of 2. Part 1: plan, map, extract and transform - inventory, data model mapping, org preparation, Data API/ODBC extraction, staging transforms and container files. Part 2 (this post): load, reconcile and cut over - Bulk API 2.0 upserts with external IDs, load errors, reconciliation, cutover, rollback.

#The goal

Load the staged FileMaker data into Salesforce so that every record arrives once, children are linked to the right parents, and every row can be traced and reconciled. Then switch users over with a frozen source, a delta load and a tested way back.

This part assumes the work from part 1 is done:

• Every migrated object has a unique External ID field Legacy_Id__c, users are loaded and mapped for OwnerId, and automation has a migration bypass.
• Staging holds one validated CSV per object with Salesforce API names as headers, and parents referenced by external ID, for example Account__r.Legacy_Id__c.
• Container files are downloaded and ready to become Salesforce Files.

#Step 6: load with Bulk API 2.0 in dependency order

Load order: Users (mapping only) → Accounts → Contacts → custom parents → children → junction objects → files → notes and activities.

With the Salesforce CLI:

sf org login web --alias target --instance-url https://test.salesforce.com   # sandbox rehearsal
sf data upsert bulk --sobject Account --file accounts.csv \
  --external-id Legacy_Id__c --target-org target --wait 30
sf data upsert bulk --sobject Invoice__c --file invoices.csv \
  --external-id Legacy_Id__c --target-org target --wait 60

The CLI prints a job ID for each load. If you don't wait, check a job later with sf data upsert resume and that job ID. Download each job's failed records, either from Setup > Bulk Data Load Jobs or with the CLI's bulk results command (run sf data bulk results --help on your CLI version for its flags). Load them back into staging, fix, and rerun. Because it is an upsert on Legacy_Id__c, reruns update instead of creating duplicates.

Errors you will see, and what to do:

Tip

Keep any single parent under roughly 10,000 child records where you can (for example a catch-all "Unknown customer" account). Parents with more than that cause data skew, which makes lock errors and slow sharing recalculation much worse during the load and afterwards.

#Step 7: reconcile

Compare the source with Salesforce per object. Check more than counts:

sf data query --target-org target \
  --query "SELECT COUNT(Id) n, SUM(Amount__c) total FROM Invoice__c WHERE Legacy_Id__c != null"

-- staging side, same measures
SELECT COUNT(*), SUM(amount) FROM stg.invoices WHERE load_status = 'loaded';

• Counts per object = source rows - approved exclusions.
• Sums of money fields and counts per status match.
• Children per parent match for a sample of the 50 biggest parents.
• Random sample of 50 records per object checked field by field by a business user, with FileMaker open side by side.
• Every failed row is either fixed or signed off.

#Step 8: cut over

1. Rehearse the whole run in a sandbox at least once with production-sized data, and time it.
2. Freeze FileMaker: make the file read-only for users (privilege sets) at the agreed time.
3. Run a delta extract of rows created or modified since the rehearsal snapshot (FileMaker's modification timestamp field, if the solution has one; otherwise a full reload). Upsert the delta; external IDs make this safe.
4. Re-enable automation, recalculate roll-ups and sharing, run reconciliation, and get sign-off.
5. Keep FileMaker read-only for an agreed period as the reference copy, then archive the file and the staging database.

#Rollback plan

Because every migrated row has Legacy_Id__c, you can remove a bad load precisely. Export the Ids (SELECT Id FROM Invoice__c WHERE Legacy_Id__c != null) and run a bulk delete in reverse dependency order (children first). Hard delete skips the Recycle Bin but needs the Bulk API Hard Delete permission. Decide in advance who can approve it.

#Prevent problems next time

• Treat the mapping workbook (field-by-field, with transform rules and owner) as a deliverable, signed by the business before build.
• Re-run the full extract-transform-load at least twice in sandboxes. The second run finds the problems the first one fixed by hand.
• Keep Legacy_Id__c permanently. Support teams will need it for years to answer "where did this record come from?".

#Further reading

• In this series: Part 1 - inventory, data model mapping, extraction and transforms.
• Salesforce: Bulk API 2.0 Developer Guide, Salesforce CLI command reference (data commands), Best practices for deployments with large data volumes.