Reconcile CRM Record Counts Without Mistaking Differences for Data Loss
Prepared with AI assistance by Alactic Editorial. Examples identified as hypothetical are illustrative, not customer results.
A source export contains more records than the destination CRM. Has data disappeared? Totals alone cannot answer. Some records may have been excluded deliberately, several source rows may now form one destination object, and generated relationships may increase the destination population. Rejected imports introduce another difference.
The reliable approach is to build a reconciliation bridge: define the source population, account for approved structural changes, separate rejected and pending outputs, and compare the remaining expectation with a destination observation using the same scope. Then check individual keys and their expected multiplicity. Balanced totals are useful evidence, but they do not establish population completeness.
The fictional worked example below focuses on population accounting across migration stages. It does not decide which identities to merge or validate every migrated field.
1. Define what each count measures
A source row, destination object, import attempt, and relationship are different counting units. Calling all of them records hides distinctions that matter during reconciliation.
Before comparing totals, write a counting contract covering:
- Scope: source and destination object types, workspaces, and migration batches.
- Time boundary: the source snapshot and destination observation point, including treatment of changes during migration.
- Counting unit: physical rows, distinct planned output keys, destination objects, or relationship edges.
- Status rules: treatment of archived, inactive, deleted, rejected, and pending items.
- Evidence: the saved query, export, manifest, or import result behind each count.
A destination screen showing active people is not comparable with a source export containing archived people and detail rows. Prefer a reproducible export or query with explicit filters over an unexplained dashboard total.
Also separate the destination's entire population from objects attributable to the migration. Existing objects updated by an import do not increase the database total. If updates are in scope, track matched-existing objects separately from newly created objects and define how multiple planned updates to the same object are counted.
2. Build the bridge before importing
Use a sequence that keeps each stage accountable:
Source snapshot - approved source exclusions = eligible source rows Eligible source rows +/- documented structural adjustments = planned destination outputs Planned destination outputs - rejected outputs - pending or unsubmitted outputs = expected successfully materialised outputs Observed destination outputs - expected successfully materialised outputs = net count variance
This bridge assumes the destination observation is attributable to the migration and uses the same counting basis as the plan. Migrations that update existing objects need an explicit matched-existing population rather than a simple new-object count.
Give every adjustment a reason, owner, and evidence reference. An approved exclusion is a scope decision. A rejection is a processing failure. Reclassifying a rejection as an exclusion just to balance the ledger conceals unfinished work.
Keep intermediate stages visible. A correct final total can hide an extraction shortfall offset by duplicate destination objects. The bridge explains numerical movement; key-level checks establish whether the expected population is actually present.
3. Use a filled reconciliation ledger
All identifiers, rules, and numbers in this example are hypothetical. Batch HYP-B17 migrates a frozen snapshot into an initially empty destination scope. There are no concurrent writes or existing-object updates, and no pending jobs at observation.
An approved mapping bundles legacy detail rows into structured detail objects without making identity-merge decisions. Another mapping generates relationships. In this example, the rejected people have no dependent detail or relationship outputs; otherwise those dependencies would also need an explicit processing state.
HYPOTHETICAL RECONCILIATION LEDGER
Batch: HYP-B17
Observation: all submitted jobs at terminal status
Unit: source rows before transformation;
destination objects after transformation
Variance: observed minus expected
SOURCE SNAPSHOT
People rows 10,500
Legacy detail rows 1,500
SOURCE TOTAL 12,000
APPROVED EXCLUSIONS
Out-of-scope archived people 500
Designated test people 300
TOTAL EXCLUSIONS 800
Evidence: approved exclusion manifest
Owner: migration scope owner
ELIGIBLE SOURCE
People 9,700
Legacy details 1,500
ELIGIBLE TOTAL 11,200
Evidence: eligible-row manifest
STRUCTURAL ADJUSTMENTS
1,500 legacy detail rows become 1,200
structured detail objects -300
Generated relationship objects +600
NET STRUCTURAL ADJUSTMENT +300
Evidence: transformation manifest, mapping version
Owner: mapping owner
PLANNED DESTINATION OUTPUTS
People 9,700
Structured details 1,200
Relationships 600
PLANNED TOTAL 11,500
IMPORT OUTCOME
Rejected people outputs 150
Rejected detail outputs 0
Rejected relationship outputs 0
Pending or unsubmitted outputs 0
EXPECTED SUCCESSFULLY MATERIALISED TOTAL 11,350
Evidence: terminal results by planned output key
Owner: import operator
DESTINATION OBSERVATION
People 9,520
Structured details 1,200
Relationships 600
OBSERVED TOTAL 11,320
Evidence: batch-scoped destination export
Owner: reconciliation reviewer
NET COUNT VARIANCE
People: 9,520 observed - 9,550 expected -30
Details: 1,200 observed - 1,200 expected 0
Relationships: 600 observed - 600 expected 0
TOTAL NET VARIANCE -30
REQUIRED FOLLOW-UP
Key-level differences: not yet evaluated
Output-to-object multiplicity: not yet evaluated
150 rejected people: correction decision required
Closure status: OPENThe raw difference is 680 fewer destination objects than source rows. Numerically, the bridge explains it through 800 exclusions, a net addition of 300 objects through restructuring, 150 rejections, and a remaining shortfall of 30.
That does not mean only 30 outputs need investigation. Missing and unexpected objects may offset one another. Nor does it erase the 150 known rejections: those still require correction or an approved scope disposition. The ledger remains open until its key-level and multiplicity checks are complete.
Reuse this artifact by replacing the scope, object types, evidence references, and owners—not merely the totals. Keep the follow-up fields so a balanced ledger cannot be mistaken for completed validation.
4. Keep evidence behind each line
The ledger summarises evidence; supporting manifests make it testable. Retain a source manifest, exclusion manifest, transformation manifest, import outcomes, and destination observation.
The source manifest should identify each row within its object type and snapshot. The exclusion manifest should connect excluded keys to specific reasons and approval. A reason such as outside the agreed archive scope is reproducible; not needed is not.
The transformation manifest connects eligible inputs to planned outputs. Useful fields include source type, source key, mapping version, destination type, planned output key, and transformation rule. For many-to-one transformations, retain each output group's input membership. For one-to-many transformations, enumerate the expected outputs separately.
Also record the expected number of destination objects per planned output. It may be one after expansion into individual output keys. If the design intentionally expects another number, document that rule rather than assuming uniqueness.
Use deterministic output keys where the migration design allows them. If destination identifiers are assigned during creation, retain them against the planned output keys. This supports both missing-object and duplicate-object checks. Restrict access to these artifacts appropriately; reconciliation usually needs identifiers and states, not copies of every sensitive field.
5. Separate object types, states, and attempts
A grand total can conceal missing people offset by extra relationships. Maintain a breakdown by object type, then partition by batch or another useful boundary when it helps locate failures.
Do not sum import attempts as though they were distinct outputs. A rejected output that succeeds on retry is one planned output with a changed final state. Preserve attempt history for diagnosis, but calculate population totals from planned output keys and their current states.
Distinguish these stages:
- Prepared: the output exists in the migration plan.
- Submitted: a request or file was handed to the import process.
- Accepted for processing: the submission was acknowledged, but completion remains unconfirmed.
- Reported successful: terminal results indicate successful processing.
- Observed: the expected destination object is found under the agreed query.
Use the actual status meanings available in the migration tools. An acknowledgement does not prove persistence. If an output is merely accepted for processing, keep it pending until stronger evidence is available.
Apply dependency rules consistently. A rejected parent may leave child outputs rejected, blocked, or unsubmitted. Do not count those children as successful simply because they were present in the original plan.
6. Compare keys in both directions, then check duplicates
For each object type, construct an expected successful key set and an observed key set using the same mapping and scope. Compute both differences:
Missing expected keys = expected set - observed set Unexpected observed keys = observed set - expected set
Hypothetical investigation result: the people shortfall of 30 in HYP-B17 could consist of 40 missing expected keys and 10 unexpected observed keys. Investigating only 30 missing items would understate the work.
Even empty set differences are not sufficient when duplicate objects are possible. Distinct-key comparisons discard multiplicity: two destination objects carrying the same expected migration key look like one key. Group destination objects by planned output key and compare the observed object count with the expected count. Retain destination identifiers so separate objects can be distinguished from duplicated export rows.
Investigate systematically:
- Verify the observation. Check workspace, object type, attribution, archive filters, pagination, and timing. Save the query version.
- Trace every difference. Follow each key through the transformation manifest, attempt history, terminal result, and destination identifiers.
- Check multiplicity. Flag every planned output with too few or too many destination objects, including keys present in both sets.
- Classify the cause. Possibilities include incomplete observation, incorrect mapping, unprocessed output, misclassified rejection, duplicate creation, or a write to the wrong scope.
- Correct and remeasure. Preserve the original evidence and publish a new ledger version.
If a trustworthy destination observation is unavailable, label validation incomplete. Missing evidence establishes neither data loss nor success.
7. Recover without creating another discrepancy
Before retrying an apparently missing or rejected output, check whether it already exists under its destination identifier or migration key. A whole-batch rerun may create duplicates or repeat updates, depending on import behaviour.
Use a bounded recovery manifest containing affected output keys, reasons, proposed actions, and supporting evidence. Obtain the required authorisation before making changes. When recovery behaviour is uncertain, validate it on a controlled subset before expanding the work.
Respect dependencies: fixing a person may unblock relationships, while retrying relationships first may produce another failure. After recovery, rerun count, key-set, and multiplicity checks for every affected object type.
A frozen snapshot simplifies accounting but may require later change capture. A live migration needs separate accounting for inserts, updates, and deletions after the baseline. Do not silently mix those changes into the original ledger; use a delta manifest and a new observation boundary.
8. Close against population evidence, not count balance
Agree acceptance criteria before reviewing results. For every in-scope object type, require:
- Both key-level set differences are empty under the final approved scope.
- Every planned output maps to the expected number of destination objects, with no unexplained duplicate or excess objects.
- Every rejected, blocked, pending, or unsubmitted output has a resolved disposition supported by evidence.
- Every exclusion or scope change has approval and remains traceable to the original plan.
- The final ledger and destination observation use the same scope and time boundary.
A resolved disposition means successful recovery or an explicitly approved change to scope—not an unresolved owner assignment. Preserve original failures and approvals when updating the final expectation, rather than deleting inconvenient keys to make the comparisons pass.
Zero net count variance establishes numerical agreement, not population completeness. Empty key differences plus correct multiplicity establish population accounting under the agreed rules; they still do not prove that field values or relationship endpoints are correct. Those need separate validation.
For one completed batch, report net variance, missing keys, unexpected keys, multiplicity exceptions, rejections, and pending outputs separately. That turns a reassuring total into an evidence-backed closure decision—or a bounded recovery list.
Put the next conversation to work.
Explore Alactic Communications for WhatsApp conversations, campaigns and AI-assisted workflows. Choose the product and plan that fit your team.
Explore Communications