
A plausible match is not the same thing as a reconciled record. Two rows can share an amount, a date and a familiar description while referring to different events. If an assistant can turn that resemblance into a change inside the order system, the workflow has erased the most important question: which evidence made the match safe to accept? The useful role for AI is narrower. It can propose a match inside a separate sheet while deterministic checks preserve the source rows, totals and exceptions.
Consider two invented tables. The order table contains O-101 for 42.00, O-102 for 42.00, O-103 for 18.50 and O-104 for 9.75. The receipt table contains R-201, which names O-101 for 42.00; R-202, which has no order identifier and an amount of 42.00; R-203, which names O-103 for 18.50; and two different rows that both carry receipt identifier R-204. One of those duplicate rows names O-104 for 9.75 and the other names O-102 for 42.00. These are fixture values, not business records or accounting advice.
The design starts with three layers. Keep the source snapshots unchanged. Build normalized working copies with a recorded transformation revision. Write every proposed or accepted relationship into a reconciliation sheet that points back to the source row identities. The sheet may classify a row, record a reason and collect an owner decision. It does not edit an order, remove a receipt, authorize a payment or make an accounting adjustment.
Normalization should make comparison explicit without sanding away evidence. Trim permitted whitespace from copied identifiers, standardize the documented case rule and parse amounts into a decimal representation under a named rounding policy. Python's decimal documentation explains that decimal values can represent numbers such as 1.1 exactly and preserve significant trailing zeros. That supports a checkable 42.00 fixture. It does not decide whether two 42.00 rows describe the same event.
Record the source count and source total before matching, then recompute both from the normalized copies. The invented order snapshot contains four rows totaling 112.25. The receipt snapshot contains five rows totaling 154.25. Those totals are not expected to equal one another because unresolved and duplicate records remain present. The invariant is that normalization does not silently add, drop or change a source amount.
Exact identifiers get the first pass because their rule can be inspected. In pandas 3.1.1, DataFrame.merge performs a database-style join. Its merge API can add an indicator showing whether a key appeared on the left, the right or both, and its validate argument can require one-to-one key uniqueness. The pandas merge guide shows that a one-to-one validation raises an error when either side contains duplicate join keys. That failure is useful evidence, not an inconvenience to suppress.
Run source-key checks before the join. A duplicate receipt identifier means the two R-204 rows cannot enter the eligible set until an owner determines what they represent. A blank order reference also leaves the exact-match set. Pandas documents another reason for that guard: null keys on both sides match each other, unlike the usual SQL behavior. Missing identifiers should become explicit exceptions before a table tool can turn absence into a relationship.
Which case would stop your reconciliation first: a blank identifier, a duplicate key, or a conflicting amount?
Now walk the clean eligible rows through the sheet. R-201 names O-101 and both copied amounts are 42.00, so the deterministic rule records an exact proposed match with the reason “identifier and amount agree.” R-203 names O-103 and both amounts are 18.50, producing the same disposition. The accepted portion of this synthetic sheet is 60.50. Both source rows remain untouched, and the sheet keeps their identities rather than replacing them with a summary.
R-202 is the tempting failure. Its amount is 42.00, and O-102 is an unmatched order for 42.00. An assistant could call that a high-confidence match after noticing that O-101 already has an exact receipt. The argument sounds coherent and still lacks an identifier. The amount may be common, the missing reference may belong to another period, or one of the source tables may be incomplete. The sheet can propose O-102 as a review candidate, but its decision stays unresolved and its amount stays outside the accepted total.
The two R-204 rows stop even earlier. A duplicated source identifier prevents the workflow from knowing whether it has two events with a bad key, one event copied twice or a correction that arrived without a new identity. Keeping both rows preserves the evidence needed for that decision. Deleting one because the lines look alike would make the totals cleaner by hiding the very exception the reconciliation is supposed to surface.
AI earns a place after these deterministic boundaries are visible. It can normalize a vendor description under a reviewed rule, propose candidates from dates and reference fragments, and explain which fields influenced a suggestion. Each proposal should carry the source row identities, candidate relationship, exact evidence used, conflicting evidence and required owner. A confidence score cannot replace those fields. The owner needs to see why the row was proposed and what the workflow refused to assume.
The reconciliation sheet also needs coverage checks. Every source row receives one disposition: exact proposed match, unmatched, duplicate, conflicting or held for review. Every accepted match uses each source row at most once. The accepted total is derived from the accepted rows, while the original source totals remain separately visible. A row with no disposition is a processing failure. A row with two accepted dispositions is a double-counting failure.
There is a reasonable objection that many business systems already support automatic reconciliation. That can be appropriate when the identifiers, policies, controls and exception handling have been established for that system. The boundary here is not “never automate.” It is “do not let a probabilistic proposal masquerade as a trusted record.” A deterministic rule can still be wrong, so its revision, inputs and owner remain reviewable. AI can still be useful, so its suggestions remain attached to evidence rather than discarded.
This workflow is deliberately upstream of payment, tax treatment and ledger correction. It does not determine what a receipt means under an accounting policy. It does not make a duplicate disappear. The record owner decides whether an exception is a correction, a separate event or unresolved, then uses the authorized business process outside this sheet. Keeping that decision outside the model protects both the source history and the person responsible for it.
A complete reconciliation packet contains the two source snapshot identities, transformation revision, source counts and totals, duplicate report, exact-join result, candidate proposals, one disposition per row and the owner decisions. Another reviewer should be able to recreate the accepted relationships from the same inputs and rules. If the table changes, the packet becomes stale rather than silently updating itself.
The assistant should make exceptions easier to inspect, not make them disappear. Let exact identifiers earn deterministic matches. Let AI make bounded proposals where the evidence is incomplete. Keep every original row, every total and every unresolved decision visible until the record owner decides what the business record should say.
If this was useful, forward it to one engineer who needs less noise in their feed.

