Most integrations log an error when a record fails. Some problems never reach that log. A rule can filter a record out. An order can be cancelled in one system only. An amount can change after the sync. Each of these leaves the systems different with no error.
Reconciliation is a regular check that the systems agree after the integration runs. It relies on the field owners and identifiers agreed in deciding which system owns each field. It complements the logs and alerts covered in choosing an integration approach.
Define the records you compare
Each system must count the same set of records. Agree these points for each record type before the first run.
- Entity. Which legal entities or business units are in scope.
- Currency. Compare in transaction currency, or convert every system at one agreed rate.
- Tax basis. Compare every system ex GST, or every system inc GST.
- Status. Which statuses count, such as won, invoiced, cancelled or void.
- Eligibility. Which records the integration is meant to send. A record excluded by a filter should not count as missing.
- Cut-off. One time zone, one date field and one cut-off time for every system.
Allow the agreed sync lag before you call a record missing. For example, if orders should arrive within 15 minutes, leave out orders created in the last 15 minutes before the cut-off.
Compare keys and fields as well as totals
Counts and totals flag some differences. Compare record keys and fields as well, because errors can cancel out. For example, one missing $2,000 order and another $2,000 order sent twice leave the counts and totals unchanged.
- Counts and totals. Compare them for the period. They give a quick signal.
- Keys. List the external IDs in each system. Find IDs that appear in one system only, or more than once.
- Fields. For records in both systems, compare the fields that matter, such as amount, status, currency and customer ID.
Records with no external ID cannot be paired. List them separately, and ask the owning team to fix their keys.
Include records that changed later
A check of new records misses changes to older ones. Include these in each run:
- Records edited after an earlier sync, such as a changed invoice amount.
- Credits and voids raised against earlier invoices.
- Cancelled orders.
- Records deleted in one system.
Select this part of the check by the last-modified date, not the created date. Some systems hide deleted records from normal reports. Check whether yours keeps a deletion log you can report on.
Set up the daily check
Here is an example of a daily check. The business sends orders from a CRM to an ERP. The ERP raises invoices and passes them to the finance system. All figures are illustrative.
| Check | CRM | ERP | Finance | Result |
|---|---|---|---|---|
| Won orders, yesterday (count) | 42 | 41 | n/a | 1 fewer in ERP |
| Won orders, yesterday (total, ex GST) | $186,400 | $181,150 | n/a | $5,250 lower in ERP |
| Invoices raised, yesterday (count) | 38 | 38 | 38 | Match |
| Invoices raised, yesterday (total, inc GST) | $171,600 | $172,700 | $172,700 | $1,100 lower in CRM |
| Payments received, yesterday (total) | n/a | $94,300 | $94,300 | Match |
| Customers with no external ID | 3 | n/a | n/a | 3 to fix |
The order rows suggest one order worth $5,250 did not reach the ERP. The invoice rows suggest an amount changed after the CRM received it. Totals alone do not prove either cause. Compare the order and invoice IDs and amounts to find the records behind each difference.
Run the check at the same time each day, after the overnight sync and the agreed lag. A scheduled report or a short script can produce the table. A spreadsheet can work while volumes are small.
Investigate each mismatch
When a key or field differs, find the cause before you change anything.
| What you find | Possible cause | What to do |
|---|---|---|
| Record in source, missing in target | Sync failed, a filter excluded it, or the lag has not passed | Check the integration log and search the target. Fix the cause, then resend if the record is still missing. |
| Record in target, missing in source | Deleted or cancelled in source, or created by hand in target | Confirm with the field owner. Cancel, void or link it through the system's normal process. |
| Same record, different value | Edited after sync, a credit or void, or a different tax or rounding rule | Correct it in the owning system and let the sync update the copy. |
| Two target records for one source record | A retry or a failed match created a duplicate | Confirm the authoritative record and follow the system's approved correction process. Then fix the matching and retry logic that created the duplicate. |
| Same gap every day | A mapping or rule is wrong | Raise it with whoever supports the integration. |
Before you resend a record, check whether the destination already holds it. The write may have succeeded while the acknowledgement failed. A resend then creates a duplicate.
Correct data in the owning system. An edit to the copy is overwritten at the next sync, or sent back as a conflict.
Log each mismatch with the record ID, the cause and the fix. After a month, the log shows which causes recur.
Check runs that are stuck or failed
Some processes run as an orchestration. One event starts a run that moves through steps in several systems. A run can stop partway and leave records in some systems but not others.
List these runs in the daily check:
- Runs waiting past their timeout, such as a credit approval that has not arrived.
- Runs that failed after all retries.
- Runs that were compensated, meaning the orchestration undid earlier steps after a later step failed.
For each compensated run, check that it left no half-finished records. For example, look for a sales order with no invoice, or an open invoice on a cancelled order. Compare the run's step history with the records in each system, using the same record keys.
The guide to choosing an integration approach covers retries, timeouts and undo steps.
Name the people who act on it
A reconciliation without an owner turns into a report nobody reads. Agree these roles before the first run.
| Responsibility | Owner (example) |
|---|---|
| Reviews the daily check by 10 am | Named finance systems officer, with a named backup |
| Fixes order and customer gaps | Sales operations |
| Fixes invoice and payment gaps | Accounts receivable |
| Fixes faults in the integration | Integration partner, vendor or developer |
| Signs off the month-end reconciliation | Financial controller |
Agree a threshold that raises a gap with the reviewer, for example one missing record or $100 in total. Agree how fast each type of gap must be fixed.
Protect the data the check uses
A reconciliation needs identifiers, dates, statuses and amounts. It rarely needs names, addresses or contact details.
- Build the check from identifiers and amounts only.
- Treat identifiers and amounts as confidential. Anyone with system access can use a record ID to look up the customer.
- Keep reports and exports inside the systems or a secure shared location.
- Give access only to the people who review and fix the check.
- Keep working evidence for the period your audit and retention requirements set, then delete it.
Test the check before you rely on it
Test it in a sandbox or test environment loaded with a full day of production-sized data. Then introduce errors on purpose.
- Stop one order from syncing and confirm the check reports it.
- Change an invoice amount after sync and confirm the field comparison finds it.
- Remove one order and send another twice, with the same amount. Confirm the key comparison finds both while the totals match.
- Cancel an older order and confirm the check picks up the change.
- Run the check at peak volume, such as end of month, and time it.