How to check record counts and parent relationships after a Salesforce load

AI-Assisted

By Richard C, AIL Studio

Spreadsheet formulas were tested in LibreOffice Calc 25.2.3.2, not in Google Sheets, Excel, or a Salesforce org. No Salesforce org was queried to write this article.

A finished Data Loader or Bulk API job can still be short on rows, or it can accept every child while some parent lookups stay blank and one parent ends up with fewer children than the source file. This walkthrough reconciles source-to-target counts per object using the job’s own success and error files, then checks whether parents resolved: how many children have a blank parent lookup, and whether each parent has the same number of children in the source and in the target.

It does not check whether a weekly Data Export downloaded every zip or attachment before you migrated. It does not walk through Contact-export orphans, duplicate External IDs, or picklist mismatches; those are separate file checks.

1. Count what the job accepted, then what the org contains

After an import, Data Loader generates two CSV output files. “One file name begins with success, and the other starts with error.” The success file “contains the successfully loaded records, including a column with the newly generated record IDs.” The error file “contains the rejected records, with a column that describes why the load failed.”

If you loaded through the Bulk API, the job’s JobInfo includes numberRecordsProcessed (“the number of records already processed”) and numberRecordsFailed (“the number of records that weren’t processed successfully in this job”). Read those two fields from the completed job; do not invent a third number the API did not return.

Then count the object in the target org with SOQL COUNT(). COUNT() “returns the number of rows that match the filtering conditions” and “must be the only element in the SELECT list”:

SELECT COUNT() FROM Contact

Run the query as a user who can see the same records you meant to load. COUNT() “includes null values that match the filtering conditions,” which matters in the next section when you filter on a blank parent.

For each object, put Source rows, Success rows, Error rows, Success+Error, Vs source, Target COUNT(), and Target vs success on a Counts sheet. In E2, F2 and H2, then fill down:

=IF(OR(B2="",C2="",D2=""),"",C2+D2)
=IF(E2="","",E2-B2)
=IF(OR(C2="",G2=""),"",G2-C2)

Example data (not a customer org, and not the result of a real load). Four Contact rows were sent. Three landed in the success file and one in the error file. The target COUNT() is 3.

In LibreOffice Calc 25.2.3.2 those formulas produced Success+Error 4, Vs source 0, and Target vs success 0. A blank object row left the calculated cells blank. Matching totals here only mean the rejected row is accounted for in the error file. They do not mean every parent received the children you expected.

2. Did the parents resolve?

Children point at parents through a lookup or a master-detail field. Considerations for Relationships (Salesforce Help) states that you can convert a lookup to a master-detail relationship “if the lookup field in all the records contains a value,” and that you cannot create a master-detail relationship if the custom object already contains data unless you first fill that parent field. After a load, treat a child that arrived with a blank parent as a lookup left empty (or as a row that never arrived and should be in the error file)—not as proof that a required parent was filled.

Count children whose parent lookup is blank:

SELECT COUNT()
FROM Contact
WHERE AccountId = null

COUNT() includes rows that match the filter, so this returns how many Contacts have no Account. To inspect the rows, use a relationship query that reads the parent with the dot operator:

SELECT Id, LastName, Account.Name
FROM Contact
WHERE AccountId = null

Account.Name will be empty on those rows. Replace Contact / AccountId with the child object and parent field you actually loaded.

This is only a blank-parent count. It does not tell you whether a filled parent Id points at the wrong company, and it is not a picklist or External ID check.

3. Compare child counts per parent

A parent can “resolve” on every successful child and still be short if some of its children were rejected. Compare how many children each parent has in the source file versus the target.

In the target org, GROUP BY with COUNT(fieldName) returns a count per parent. Salesforce documents COUNT(fieldName) with GROUP BY for summary reporting, and notes that GROUP BY without an aggregate returns distinct values “including null”:

SELECT AccountId, COUNT(Id)
FROM Contact
GROUP BY AccountId

Salesforce record Ids usually change between the source org and the target org, so do not compare those Id values to the source file. Export (or keep) a parent external key on both sides—this example uses Parent_Legacy_Id__c—and count children per that key in a sheet.

Example data (same made-up load as section 1). Source Contacts: two children of AC-001, one child of AC-002, and one child with a blank parent. The rejected row was the AC-002 child, so the target has two children of AC-001, none of AC-002, and one blank parent.

On a Parents sheet, list each parent key (use (blank) as the label for empty parents). Put the source parent keys in J2:J5 and the target parent keys in K2:K4. For a named parent in A2:

=COUNTIF($J$2:$J$5,A2)
=COUNTIF($K$2:$K$4,A2)
=IF(OR(B2="",C2=""),"",C2-B2)
=IF(A2="","",IF(C2-B2=0,"Match","Review"))

For the (blank) row, count empty parent keys explicitly:

=COUNTIF($J$2:$J$5,"")
=COUNTIF($K$2:$K$4,"")

In LibreOffice Calc 25.2.3.2 those formulas produced:

Parent              Source children  Target children  Difference  Status
AC-001              2                2                0           Match
AC-002              1                0               -1           Review
(blank)             1                1                0           Match

AC-002 is the point: the object-level counts in section 1 matched, and one child loaded with a blank parent lookup, but this parent has no children in the target because its child is in the error file.

Widen the COUNTIF ranges to your last data row.

4. What matching counts do not prove

A matching per-parent count does not prove each child points at the intended parent. Two parents that each had two children can swap those children and still show Match. A blank parent lookup can be intentional on an optional lookup; treat it as a review list. Permissions matter: SOQL you cannot see, you cannot count.

Before you share any export outside your team, for a review or anything else, send only the objects and columns the reviewer needs. Remove or blank personal fields you don't need, such as personal emails, phone numbers, home addresses and birthdates, and never include passwords or org logins.

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

If you want a second pair of eyes on Contact and Account exports (CSV or Excel, up to 25,000 rows per file), there's a $19 Salesforce Export Check; the product page explains what's included and how to send the files. It reviews relationship and quality checks on those two objects (blank Account, Account values that are not in the Account file, blank required columns you name, External ID counts and wrong-parent Contacts when you send the matching files). It does not log into your org, merge records, or load data, and it is not a Bulk API job review or a children-per-parent count for every object.