SfSkills Domains Docs GitHub

Home / data / data-reconciliation-patterns

data-reconciliation-patterns

Patterns for reconciling Salesforce data with external systems: count-level, field-level, and record-level reconciliation, external ID upsert patterns, Change Data Capture for delta detection, and soft-delete tombstone strategies.

Open the skill folder on GitHub

Data Reconciliation Patterns

This skill activates when a Salesforce integration has produced a data discrepancy — record counts differ, field values drift, or records exist in one system but not the other. It provides a layered reconciliation strategy (count, field, record) plus the upsert and delta-detection primitives needed to close those gaps reliably.

Before Starting

Gather this context before working on anything in this domain:

Core Concepts

The three levels of reconciliation form a diagnostic ladder. Start at count level, then drill down only when counts diverge.

Count-Level Reconciliation

Count-level reconciliation compares the number of records in Salesforce against the source system. In Salesforce, use SELECT COUNT() FROM Object__c WHERE <filter> — the aggregate returns a single integer with no row limit. Cross-reference this against the equivalent count query on the source side. A mismatch means records were not created, were deleted, or were filtered differently.

Count-level reconciliation is cheap and fast. Run it after every bulk load job before treating a sync as complete. Note that COUNT() counts only the rows the running user can see. Over the REST/SOAP API it always respects that user's sharing, so the count is trustworthy only when the integration user holds View All Data on the object (or the count comes from the Tooling API for metadata counts) — that is a permission, not an execution mode. Run the same query from Apex and the default mode is gated by the apiVersion in the class's .cls-meta.xml, not by the org's release: at 67.0+ (Summer '26) SOQL defaults to user mode and a class with no sharing keyword runs with sharing, so a batch reconciliation class that returned every row when pinned to 58.0 silently under-reports once it is saved at 67.0. Declaring that class without sharing does not restore the old count: the operation-level default is set by the apiVersion, not by the sharing keyword, so a count that must see every row opts back in on the statement with WITH SYSTEM_MODE plus a // reason: comment. Canonical table: 8 § Apex security idiom by API version.

Field-Level Reconciliation

Field-level reconciliation compares the values of specific fields across matched records. Because Salesforce has no native checksum API, all hashing is client-side. The typical pattern is: export a projection of key fields from both systems, compute an MD5 or SHA-256 hash per row, then compare hashes to identify diverged records.

In Apex, Crypto.generateDigest('SHA-256', Blob.valueOf(inputString)) produces a hash suitable for comparison. Feed it a concatenated string of the fields you care about, normalized to lowercase and trimmed. Field-level reconciliation is expensive at scale — scope it to the delta since the last clean reconciliation rather than running full-table scans.

Record-Level Reconciliation

Record-level reconciliation uses a shared key — typically a Salesforce External ID field joined to the source system's primary key — to produce a full outer join and surface records that exist in one system but not the other. In Salesforce, External ID fields are created as custom fields with the External ID checkbox checked. Up to 25 External ID fields are allowed per object. Text-type External ID fields are case-sensitive by default; if the source system sends mixed-case keys, either normalize to uppercase/lowercase before loading or use a case-insensitive lookup strategy.

Common Patterns

External ID Upsert via Bulk API 2.0

When to use: Creating or updating records in bulk where the source system has a stable primary key that maps to a Salesforce External ID field.

How it works: Bulk API 2.0 upsert jobs specify externalIdFieldName in the job creation payload. The platform performs a lookup against that field for each incoming row; if a match is found the record is updated, otherwise it is inserted. The job returns per-row success/failure results in successfulResults and failedResults endpoints.

Critical constraint: The External ID field must be unique (Unique checkbox on the field definition) to avoid DUPLICATE_EXTERNAL_ID errors. If a non-unique external ID field is used in an upsert and two Salesforce records match the incoming value, that row fails with StatusCode DUPLICATE_EXTERNAL_ID — documented as "A user-specified external ID matches more than one record during an upsert." The row appears in failedResults; the rest of the job continues.

Why not REST single-record upsert: For large datasets, REST PATCH by external ID (e.g., PATCH /services/data/vXX.0/sobjects/Account/External_Id__c/ABC123) works but is serial and subject to API call limits. Use Bulk API 2.0 for anything over a few hundred records.

The two surfaces report an ambiguous match differently. SOAP and Bulk return StatusCode DUPLICATE_EXTERNAL_ID. REST PATCH by external ID instead returns HTTP 300 (the status literally named Multiple Choices) — "a 300 error is reported, and the record isn't created or updated", plus a list of the matching records. MULTIPLE_CHOICES is not a Salesforce StatusCode: match DUPLICATE_EXTERNAL_ID in failedResults, and reserve HTTP-300 handling for the REST path.

CDC-Driven Delta Load

When to use: You need near-real-time detection of changes (inserts, updates, deletes, undeletes) on Salesforce records to sync to an external system.

How it works: Change Data Capture publishes platform events to the Pub/Sub API channel /data/<ObjectName>ChangeEvent whenever a CDC-enabled record changes. Each event carries a changeType header (CREATE, UPDATE, DELETE, UNDELETE) and a replayId. Subscribers store the last-processed replayId and use it on reconnect to replay missed events.

Gap recovery: If a subscriber goes offline, it can replay up to 72 hours (3 days) of events using the stored replayId. Beyond the retention window, a full reconciliation run (count + record-level) is required to identify the gap. Not all objects support CDC; consult the Salesforce CDC Developer Guide for the supported object list.

Soft-Delete Tombstone for Hard Delete Detection

When to use: Your integration uses LastModifiedDate or SystemModstamp delta loads and needs to detect records deleted in Salesforce.

How it works: Standard SOQL WHERE LastModifiedDate > :lastRunDate does not return hard-deleted records. Salesforce exposes deleted records through the isDeleted = true filter in queries against the recycle bin (queryAll() REST endpoint or ALL ROWS SOQL in certain contexts). A tombstone pattern maintains a separate deletion log table in the external system, updated by either a CDC DELETE event or a periodic queryAll sweep.

Why this matters: Without tombstone tracking, records deleted in Salesforce silently diverge from the external system. Count-level checks will catch the discrepancy, but only a tombstone or CDC delete event can tell you which records were removed.

Delta Load via SystemModstamp

When to use: CDC is not available for the object, and you need periodic delta detection without full-table comparison.

How it works: Filter SOQL with WHERE SystemModstamp > :lastRunTimestamp. SystemModstamp is updated on every DML write plus system events like formula recalculations. This can produce false positives — records appear "changed" even when no user-visible fields were modified. Filter on LastModifiedDate if you only care about explicit DML changes, but be aware LastModifiedDate does not update on system-driven recalculations.

Decision Guidance

SituationRecommended ApproachReason
Need to verify bulk load job completenessCount-level SOQL reconciliation post-jobFast, cheap, catches missing rows immediately
Records exist in source but appear as duplicates in SalesforceVerify External ID field has Unique constraint; check for case sensitivity mismatchNon-unique external IDs cause DUPLICATE_EXTERNAL_ID on upsert
Need real-time detection of Salesforce changesCDC via Pub/Sub API with replayId gap recoveryLowest latency; handles insert/update/delete/undelete
Periodic batch sync; CDC not available for objectSystemModstamp or LastModifiedDate delta + periodic full reconciliationCovers most changes; supplement with tombstone for deletes
Field values drifted between systemsField-level hash comparison on key field projectionPinpoints diverged records without full data export
Hard deletes not surfaced by delta loadTombstone pattern via CDC DELETE events or queryAll sweepStandard SOQL misses hard deletes entirely

Step-by-step instructions for an AI agent or practitioner working on this task:

  1. Confirm the integration's upsert key: identify the External ID field, verify it has the Unique and External ID checkboxes set on the field definition, and validate that the source system sends a consistent key format (case, length, special characters).
  2. Run a count-level reconciliation after each bulk load: execute SELECT COUNT() FROM Object__c WHERE <scope filter> and compare to the source row count. A count mismatch is the entry point for deeper investigation.
  3. If counts match but functional issues remain, run a field-level hash reconciliation on the fields most likely to drift (status fields, date fields, currency fields) using Apex Crypto.generateDigest or a client-side hash on a field projection export.
  4. For record-level gap detection, use a full outer join on the External ID field — identify records in source not in Salesforce (missing inserts) and records in Salesforce not in source (orphans or unexpected inserts).
  5. Set up CDC event subscription with replayId persistence for objects that support it. Test gap recovery by simulating a subscriber outage and confirming replay from stored replayId.
  6. Implement a tombstone strategy for hard deletes: either consume CDC DELETE events or run a periodic queryAll sweep and mark removed records in the external system.
  7. Before closing a reconciliation run, verify that the Bulk API 2.0 job's failedResults endpoint returns zero rows — partial failures are silent unless explicitly checked.

Review Checklist

Run through these before marking work in this area complete:

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

  1. SystemModstamp fires on formula recalculation — When a formula field recalculates (e.g., due to a referenced lookup changing), SystemModstamp updates on the parent record even though no user-visible DML occurred. This causes delta loads to pull records that appear unchanged in the downstream system, wasting processing cycles and potentially triggering false update notifications. Use LastModifiedDate if you only want explicit DML changes.
  2. External ID Text fields are case-sensitive — A Text-type External ID field treats ABC123 and abc123 as different values. If the source system sends mixed-case keys, upserts will create duplicate records instead of updating. Either enforce normalization at the source (always uppercase/lowercase) or use a case-insensitive custom field approach with a formula-derived normalized field.
  3. DUPLICATE_EXTERNAL_ID on non-unique external IDs — If an External ID field is not marked Unique and two records share the same value, a Bulk API 2.0 upsert fails those rows with StatusCode DUPLICATE_EXTERNAL_ID ("A user-specified external ID matches more than one record during an upsert"). Other rows still process, so affected rows are silently skipped unless failedResults is checked. REST PATCH-by-external-ID signals the same ambiguity as HTTP 300 instead.

Output Artifacts

ArtifactDescription
Count reconciliation SOQL queryA parameterized SELECT COUNT() query scoped to the relevant object and date filter, ready to run post-bulk-load
Field-level hash comparison scriptApex or Python pattern that normalizes and hashes key field projections from both systems for comparison
Bulk API 2.0 upsert job specJSON job creation payload with externalIdFieldName set and failedResults check logic
CDC replay configurationPub/Sub API subscriber config with replayId persistence and 3-day window gap recovery procedure
Tombstone queryqueryAll SOQL for deleted record detection, parameterized by object and last-run timestamp

Package files