DEV Community

Richard C
Richard C

Posted on Edited on Originally published at ailstudio.hashnode.dev AI-assisted

How to Audit Contact–Account Parents After a Salesforce Migration with SOQL

#salesforce #soql #datamigration

By Richard C · AIL Studio · 7x certified Salesforce professional

Disclosure: I make and sell RecordBridge, a browser tool that compares Salesforce CSV exports and flags Contacts or Opportunities on the wrong Account. This article teaches a native Salesforce check with SOQL, Data Loader, and a report. There are two optional links to my products: a free Guide and RecordBridge (US$49). I have a vested interest in both.

This is the Salesforce-native version of the wrong-parent check: everything runs in SOQL, Data Loader, and a standard report, so you never rebuild Account lookups from flat files. A post-migration audit that only checks row counts or runs basic CSV diff tools over Contact fields will pass a Contact that now hangs off the wrong Account, because the error lives in the parent link, not in the Contact's own values.

You will prove your External ID keys are unique, export each Contact with its parent Account's stable key in one child-to-parent relationship query, optionally mirror those columns in a Contacts & Accounts report, then compare source and target on that key.

Prefer pandas or Google Sheets formulas? See How to catch wrong-parent Contacts after a Salesforce migration.

What "wrong parent" means

Record Ids almost always change between a source org and a target org. A Contact whose AccountId was 001...AAA in the source may correctly become 001...ZZZ in the target. Comparing Id strings tells you nothing.

You need a stable key that identifies the same Account in both orgs — typically an External ID custom field. Call it External_Key__c below; substitute your real API name.

The check:

  1. For each Contact in the source, resolve its Account's External_Key__c.
  2. Find the same Contact in the target by the Contact's own stable key.
  3. Resolve that Contact's Account's External_Key__c in the target.
  4. If the two Account keys differ, the Contact has the wrong parent.

Same Contact fields. Different company. That is what basic "did every cell match?" diff tools miss.

Prerequisites

  • Read access to Account and Contact in both orgs (or in before/after sandboxes that still hold the data).
  • A stable key on Account and on Contact that is populated on both sides. No shared key? Stop here; fuzzy name matching is a different project.
  • Data Loader (or another SOQL export tool), or Salesforce Reports with CSV export.
  • Permission to create or edit a report in the target org if you use the report path.

All sample names and keys below are example data. Emails use the reserved example.invalid domain.

1. Confirm the keys are safe to look up

Before you trust any join, prove the Account key is unique on each side. In Developer Console, VS Code with Salesforce Extensions, or Workbench, run:

SELECT External_Key__c, COUNT(Id) cnt
FROM Account
WHERE External_Key__c != null
GROUP BY External_Key__c
HAVING COUNT(Id) > 1
Enter fullscreen mode Exit fullscreen mode

Zero rows means every non-blank Account key is unique. Repeat for Contact:

SELECT External_Key__c, COUNT(Id) cnt
FROM Contact
WHERE External_Key__c != null
GROUP BY External_Key__c
HAVING COUNT(Id) > 1
Enter fullscreen mode Exit fullscreen mode

Also count blanks you must treat as unresolved, not as matches. These are two separate queries; run them one at a time:

SELECT COUNT() FROM Account WHERE External_Key__c = null
SELECT COUNT() FROM Contact WHERE External_Key__c = null OR AccountId = null
Enter fullscreen mode Exit fullscreen mode

If duplicates or blanks are widespread, fix the keys first. Parent checks on dirty keys invent findings.

Salesforce documents child-to-parent relationship queries in the SOQL relationship query guide. You will use that pattern next.

2. Export Contacts with the parent Account key in one SOQL

Data Loader can export related parent fields with a child-to-parent relationship query. Official tip from the Data Loader export guide: relationship field names are case-sensitive (Account.Name works; ACCOUNT.NAME does not). Nested parent-to-child subqueries are not supported in Data Loader, so start from Contact, not Account.

Run this export in the source org (Export, object Contact):

SELECT Id,
       External_Key__c,
       LastName,
       Email,
       AccountId,
       Account.Name,
       Account.External_Key__c
FROM Contact
WHERE External_Key__c != null
Enter fullscreen mode Exit fullscreen mode

Save as source-contacts-with-parent.csv. Repeat the exact same query in the target org; save as target-contacts-with-parent.csv.

You now have, on every Contact row, the parent Account's stable key without a separate Account join in your spreadsheet. That is the main advantage of doing this inside Salesforce instead of rebuilding lookups from four flat files.

Example data (two orgs, one Contact):

Org Contact External_Key__c LastName Email Account.Name Account.External_Key__c
Source CT-001 Renée Example renee@example.invalid Aster Labs AC-001
Target CT-001 Renée Example renee@example.invalid Blue Orchard AC-002

LastName and Email match. Parent Account key does not (AC-001 vs AC-002). Flag as WRONG_PARENT.

If you also need Account-only coverage (Accounts with no Contacts, or key hygiene), export Accounts separately:

SELECT Id, Name, External_Key__c FROM Account
Enter fullscreen mode Exit fullscreen mode

3. Optional: the same columns from a Contacts & Accounts report

If your admins live in Reports more than in Data Loader, build the same view there.

  1. Create a report with the Contacts & Accounts report type (or a custom report type that starts from Contact and includes Account).
  2. Add columns: Contact External Key, Account External Key, Contact Name (or Last Name), Account Name, Email.
  3. Filter out rows where Contact External Key or Account External Key is blank.
  4. Run once in the source context (or from a source backup org) and once in the target. Export each run as CSV.

You still compare outside Salesforce. The report only replaces the SOQL export. It does not compare two orgs by itself.

4. Compare source and target on the Contact key

With both CSVs open (Sheets, Excel, or a small script), for every Contact External_Key__c present on both sides:

Contact key Source Account key Target Account key Result
CT-001 AC-001 AC-002 WRONG_PARENT
CT-002 AC-003 AC-003 PARENT_MATCH

Rules that keep the audit honest:

  • Match Contacts on the Contact stable key, never on Salesforce Id.
  • Compare Account stable keys, never AccountId.
  • Blank or missing parent key → UNRESOLVED, not a match. Do not hide #N/A with a default.
  • Contact only on one side → missing/extra record, a separate finding from wrong-parent.
  • Normalize case and trim spaces on keys before comparing; tools differ on case sensitivity.

Worked result on the example rows: 1 wrong-parent Contact, 1 parent match, 0 LastName/Email differences.

5. Spot-check inside the target org with SOQL

After you have a shortlist of suspect Contact keys, confirm them live in the target before you change data:

SELECT Id,
       External_Key__c,
       LastName,
       Email,
       Account.Name,
       Account.External_Key__c
FROM Contact
WHERE External_Key__c IN ('CT-001')
Enter fullscreen mode Exit fullscreen mode

Open the Contact, open the Account, and verify the business expectation (which company should own this person). The export flags the mismatch; a human decides the fix. This article stops at detection. It is not a mass-update script.

The same parent pattern works for Opportunity → Account if your migration moved deals too. Swap Contact for Opportunity and use the Opportunity stable key. Data Loader still starts from the child object when you want Account.External_Key__c on the row.

Honest limits

  • No stable External ID (or other shared key) on both sides → this method does not apply.
  • Comparing Account.Name alone is fragile (renames, duplicates, punctuation). Prefer the External ID.
  • Data Loader does not support nested parent-to-child subqueries, and polymorphic relationship queries (for example TYPEOF on fields like WhoId) may not export cleanly depending on your tool and API version; keeping exports flat with parent-dot fields avoids both.
  • A clean parent check on Contacts does not certify the whole migration. Field value drifts, sharing, and record types are separate passes.
  • Example figures and the Renée / Aster / Blue Orchard rows are labeled example data.

Wrap-up

You can catch wrong-parent Contacts with tools you already have: a uniqueness query on the External ID, a Contact export that includes Account.External_Key__c, an optional Contacts & Accounts report, and a key-to-key compare between source and target. The relationship is the thing to test; matching names and emails is not enough.

For a worked example, the free RecordBridge Guide has four small sample CSVs and a README that walk through spotting a wrong-parent Contact with spreadsheet lookups. Free RecordBridge Guide.

If you would rather load before-and-after Account, Contact, and Opportunity CSVs in a desktop browser and get a findings CSV — including wrong-parent links, missing/extra rows, and field diffs — RecordBridge is US$49 one-time. Nothing installs on Salesforce. Nothing connects to your org. Comparisons stay in the browser page's memory. It needs the same shared stable key this article uses; if you do not have one, do not buy it for that case. Built-in sample files (example data) produce 7 findings with all three objects selected, including one wrong-parent Contact.

Disclosure (again): I am the maker of RecordBridge at AIL Studio. The SOQL and report steps above stand on their own; the product links are optional.

RecordBridge by AIL Studio is not affiliated with Salesforce. Salesforce is a trademark of Salesforce, Inc.

Top comments (0)