Data reconciliation checks whether an output agrees with its source under declared rules. Define the population, cutoff, identity keys, expected transformations, and acceptable differences, then compare counts, control totals, and record-level values. Retain record differences (including missing and duplicated keys) as exceptions so the owner can explain what passed and what still needs a decision.
Two reports can show the same revenue total while containing different orders. A missing $500 order and an extra $500 order cancel at the total level, but the customer and order evidence is still wrong. A useful reconciliation tests both aggregate agreement and the records that should make up that aggregate.
Define the population before comparing results
Specify the business entity, reporting period, inclusion statuses, currency, and extraction or snapshot time on both sides. An order-created report and an order-shipped report can differ without either being broken. Write down which event the output is supposed to measure before treating the difference as a defect.
Keep late arrivals and corrections visible. If a source order changes after the output snapshot, its current value is not the same comparison point. Use a shared snapshot or a documented lag allowance where the system supports it. Otherwise identify the timing exception rather than claim that a current-source comparison establishes historical parity.
AWS’s DMS validation documentation describes comparing each source row with its corresponding target row and reporting mismatches. It also explains that continuously changing rows can prevent validation. Those are useful boundaries for a reconciliation design: identify the row relationship and the state being compared before interpreting a mismatch.
Establish record identity and expected changes
Choose keys that identify the business record at the required grain. An order number alone may identify a header but not its lines; a customer name may identify several different accounts. Preserve source-system identifiers and legal entity context. Test whether the key is unique on each side before joining, because a many-to-many match can multiply values and create a misleading comparison.
Document expected transformations such as currency conversion, unit changes, status mapping, and aggregation. A transformed output does not need to be byte-for-byte identical to the source. It needs to agree with the approved transformation for the declared population. Retain the rate, mapping version, and rounding rule that explain the expected value.
The ERP migration article puts validation into a broader cutover decision. Reconciliation can also support recurring reports without a migration. In either case, distinguish missing source history from a failed calculation; an absent input cannot be proved correct by comparing the output with itself.
Use counts and totals without stopping there
Assume a hypothetical daily order extract with three source records: A for $500, B for $700, and C for $300. The expected output is one record per order with unchanged USD amounts. The output contains A for $500, B for $700, and D for $300. Both sides have three records and total $1,500, but they do not contain the same orders.
| Check | Observed result | Meaning |
|---|---|---|
| Record count | Three on each side | Counts agree |
| Amount total | $1,500 on each side | Amounts agree in aggregate |
| Missing source key | C, $300 | Source record absent from output |
| Unexpected output key | D, $300 | Output record lacks the expected source match |
The result should fail this example’s acceptance rule because it requires complete key coverage. Do not net the $300 exceptions and close them as zero. Assign the missing and extra records to the pipeline or business owner, preserve the compared snapshots, and investigate whether the cause is a filter, mapping, duplicate reference, or an input correction.
Add field checks after key coverage. A record can be present with the wrong customer, currency, or status while its amount is correct. Select fields whose errors could change the business decision, and preserve their compared values under appropriate access controls. Sensitive fields may need restricted review or bounded diagnostics rather than inclusion in a broadly shared exception report.
Check a report against its source sample
Start with permitted source and output extracts that cover the same period and business records.
Close exceptions with evidence and an owner
An exception register should distinguish timing differences, approved transformations, source defects, and output defects. Give each entry an owner, affected record or population, impact, disposition, and verification evidence. An approved difference needs its rule and scope; a comment saying expected does not establish that the rule was applied to the correct records.
AWS’s data parity walkthrough describes checks for modernization programs using data quality rules. Adapt the check coverage to your actual transformation. A passed record count cannot establish all field values, and a sample cannot establish that every untested record agrees.
Repeat the checks after a repair and retain the prior result. For recurring pipelines, preserve the source and output versions or the receipts needed to reconstruct them. Monitor coverage as well as failures: a job that skipped a table should not appear equivalent to a job that checked it and found no differences. The ERP integration strategy article addresses ownership and recovery around those continuing feeds.
Where Permute makes a reconciliation repeatable
Permute sells connected business data and custom applications. We can join permitted source records and supplied outputs under declared identity, transformation, and cutoff rules, retaining source evidence behind the resulting comparison. That supports a scoped reconciliation instead of rebuilding the same joins in each review.
We can reuse approved definitions across reports and applications while enforcing current permissions. A data owner could inspect which records explain an aggregate difference and review the mapping or input that produced it. What a context layer actually does describes the connection between declared business rules and evidence-backed outputs.
Separate agreement from business correctness
A reconciliation can establish agreement under its checks; it does not prove that the source itself represents the real world correctly. We do not choose accounting treatments or authorize source repairs through a report. Missing history, inaccessible fields, and unmatched keys remain limitations. Source changes or supported external actions need their own owners, approvals, and permissions.
Reconcile the same population at a stated cutoff using keys and transformations that the business owner accepts. Test aggregate totals alongside record coverage and material fields. Keep differences separate until evidence explains each disposition. Retain the compared versions and acceptance decision so the result can be repeated after the next correction.
Review a report that agrees only at the total
Bring its source extract and output to identify which record and field checks the acceptance decision needs.
Questions about source and output reconciliation
Is sampling enough to approve a migration?
A sample supports conclusions only about the selected coverage and method. Use the risk and acceptance criteria agreed for the migration, and state what was not tested. Full counts and totals can complement sampling but do not replace untested field comparisons.
Is reconciliation the same as entity matching?
No. Entity matching determines which records refer to the same thing, while reconciliation checks agreement under a declared relationship and transformation. Unresolved identity can block a reconciliation, so preserve it as an exception instead of forcing a match.
Should every difference fail the check?
Use an approved tolerance only where the business meaning supports one, such as a stated rounding rule. Keep the tolerance, unit, and affected fields attached to the check. Missing records and duplicate keys should not disappear inside an amount tolerance.