Reconcile Two Systems That Disagree
The customer is certain the ERP and the CRM agree. They have never checked. Build the reconciliation that produces a report someone can act on: normalised matching, per-field discrepancies, missing on each side, and duplicate keys reported rather than silently dropped.
The problem
Implement reconcile(erp, crm, fields) taking two lists of records (each a dict/object with an "id") and a list of field names to compare. Return a report with: only_in_erp and only_in_crm (sorted lists of ids), matched (count of ids present in both whose compared fields all agree), differs (a sorted list of {"id", "field", "erp", "crm"} entries, one per differing field), and duplicate_ids (sorted ids appearing more than once on either side). Compare values normalised — strings trimmed and lower-cased, numbers compared numerically so 1200 equals "1200.00" — but report the original values.
erp has C-1, crm has C-1 with a different cityone differs entry naming "city""ACME-1" vs "acme-1 "matchedthe same id twice in the CRM exportduplicate_ids: ["C-1"]- Compare on normalised values; report the original values as they appeared.
- One
differsentry per differing field, not one per record. - All output lists must be sorted so two runs produce a diffable report.
- Duplicate ids are reported and the first occurrence is used for comparison.
Your turn — write it
Edit the stub, hit Run (or ⌘/Ctrl + Enter), and watch the hidden tests. Stuck? the hints are right above and Reveal solution is one click away.
Implement reconcile(erp, crm, fields) returning the five-part report described above.
- Index each side by id first, recording which ids you saw more than once as you go.
- Normalise for comparison only:
str(v).strip().lower(), with a numeric comparison attempted first. 1200and"1200.00"must compare equal — try float conversion before falling back to string comparison.- Sort every list you return. A report that reorders between runs is a report nobody diffs.
Approach, complexity & discussion — open after you solve
The approach
Reconciliation is a set problem wearing business clothes. Index both sides by the join key, then produce four buckets: only in A, only in B, present in both and matching, present in both and differing. The differing bucket is the one anyone actually reads, so it must name the fields that differ rather than just flagging the record.
Two details separate a real reconciliation from a naive one. Normalisation: the same customer is ACME-001 in one system and acme-001 in the other, and money is 1200 here and 1200.00 there, so you compare normalised values while reporting the originals. Determinism: the report is read by two teams who will diff it week over week, so ordering must be stable rather than dictionary order.
Complexity
O(n + m) with hashing; O(n + m) space for the two indexes.
Common mistakes
- Comparing raw strings, so whitespace and casing differences show up as thousands of false discrepancies that bury the twelve real ones.
- Reporting "record differs" without naming the fields — which makes the report unusable by the people who have to fix it.
- Sorting by dictionary iteration order, so a re-run produces a different-looking report and nobody trusts the diff.
- Silently dropping records with a missing or duplicate key instead of reporting them, which is how a reconciliation quietly reconciles less than it claims.
Where this shows up
This is week two at almost every deployment. The customer believes their ERP and CRM agree; they have never checked. Producing the first reconciliation report is frequently the moment you gain credibility, because it puts a number on something everyone suspected — and the same code becomes the migration verification, then the ongoing drift check.
Explore the topic
See this challenge alongside everything else on the same subject — handbooks, system designs, algorithms and tools, in one place.