When a CRM, accounting platform, project system, or custom application is connected to another tool, a successful sync message can feel like proof that everything is fine. It is not. A connector can run without an obvious error while a record is missing, duplicated, delayed, or mapped to the wrong value.
Data reconciliation is the deliberate comparison of two systems to find those differences. It gives a small business a way to answer a practical question: do the records that should agree actually agree, and what should happen when they do not?
Why a successful integration can still produce bad data
Most integrations move events or records from one system to another. They may retry a request, skip a malformed record, encounter a changed field, or process the same event twice. Some failures are visible in an error log. Others are accepted by both systems but create a different result.
For example, a new customer may exist in the CRM but never arrive in the billing system. A retry may create two project records because the receiving system did not recognize that the first request had already been processed. Or both systems may contain the customer, but one has an outdated status.
Reconciliation does not replace validation, idempotent processing, or monitoring. It is a separate check that looks at the outcome across system boundaries.
Start with a narrow comparison
A first reconciliation job should answer one business question, not compare every table and field. Choose a record type where disagreement has a clear operational cost, such as active customers, open projects, invoices, or scheduled appointments.
Define four things before writing code:
- Source and destination: Which system creates the record, and which system depends on it?
- Time window: Are you checking records changed today, the last seven days, or the full active set?
- Agreement rule: Which fields must match, and which fields are allowed to differ because the destination has its own workflow?
- Action: Should a mismatch create an alert, a repair task, a retry, or a human review item?
This scope prevents a comparison from producing a long list of differences that nobody can interpret.
Use a stable identifier to compare records
Names and email addresses are useful for display but are often weak reconciliation keys. They can change, be formatted differently, or belong to more than one record. Use a stable identifier that both systems can store, such as a source-system ID or an integration-specific external ID.
If a stable key does not exist, treat that as an integration design problem. A matching rule based on several fields may help find candidates, but it should not silently merge records. Ambiguous matches belong in an exception queue for review.
Keep a mapping table that records the relationship between the source ID, destination ID, last successful comparison, and current state. This makes it easier to distinguish a genuinely missing record from a record that was renamed or archived.
Compare more than record counts
Counts are a useful first signal. If the source has 1,240 active projects and the destination has 1,238, there is a reason to investigate. But equal counts do not prove that the same projects exist in both systems.
A practical comparison usually has several layers:
- Existence: Does every in-scope source record have a destination record?
- Uniqueness: Does each source ID map to one destination record rather than several?
- Freshness: Is the destination’s last-updated value within an acceptable delay?
- Critical fields: Do fields such as status, owner, amount, or due date match according to the agreed rule?
- Relationships: Do important links, such as a project-to-customer relationship, still point to the right record?
For large datasets, compare hashes or normalized summaries first, then retrieve full details only for records that differ. That reduces API traffic while preserving a useful check.
Classify discrepancies before fixing them
Not every difference deserves an automatic repair. Classify each result so the response matches the risk:
- Missing: The source record has no destination match.
- Duplicate: More than one destination record maps to the same source record.
- Stale: The destination exists but has not received a recent change.
- Field mismatch: A mapped value differs from the agreed business rule.
- Ambiguous: The systems cannot determine whether two records represent the same thing.
Missing and stale records may be safe candidates for a replay if the operation is designed to be safe to repeat. Duplicates and ambiguous matches usually need human review, because an automatic merge can discard information or break relationships.
Make the exception visible and actionable
A reconciliation report should not end with “12 mismatches.” Each item should include the record type, stable identifiers, detected difference, first-seen time, last attempt, and suggested next action. Link to the relevant records when access controls allow it, and avoid copying sensitive fields into broad notifications.
Route exceptions to an owned queue or workflow. Assign a person responsible for deciding what happens, and record the resolution. A small business may start with a daily report and a shared review list. As volume grows, the same model can become an automated ticket, dashboard, or repair workflow.
Measure the process, not just the connector
Useful measures include the number of records compared, missing records, duplicates, field mismatches, age of the oldest unresolved exception, and the percentage resolved by replay versus human decision. Watch for recurring discrepancy types. If the same field fails every week, improve the mapping or validation rule instead of repeatedly repairing the output.
Also test the reconciliation job itself. Use a small set of known cases: a matching record, a missing record, a duplicate mapping, a stale update, and an intentionally allowed difference. Confirm that the report identifies each case and that the response does not modify production data without an explicit control.
A practical first step
Choose one high-value record type and run a read-only comparison for a limited time window. Document the identifier, fields, tolerance for delay, discrepancy categories, and owner for review. Once the report is trusted, decide which exception types can be safely replayed and which must remain human decisions.
Reliable integrations are built from more than API calls. They need clear ownership, safe retries, useful monitoring, and a way to verify that connected systems still agree. Next step: Schedule a short consultation to identify the next useful improvement.