Reconcile Two Systems That Disagree

Start from brute force

The direct version loops: for each ERP record, scan the whole CRM list for the same id, compare the fields, and scan both lists again to find ids that appear twice or on one side only. It is correct and needs only O(1) working space beyond the report, but it costs O(n · m) id comparisons for n ERP and m CRM records, plus O(n² + m²) for the duplicate scans. Two exports of 100,000 rows each mean 10 billion comparisons for the join alone. The other naive move, comparing raw values, is fast and wrong: "ACME" against "acme " and 1200 against "1200.00" become thousands of false discrepancies.

The approach

Hash both sides by id, then reconcile with set algebra. The reference _index walks each list once, keeps the first record per id in by_id, and adds every repeat to dupes, so a duplicate is reported instead of silently overwriting the first copy. reconcile then takes set(e) & set(c) for the ids on both sides, and the two set differences for only_in_erp and only_in_crm.

For each shared id it compares every requested field through _norm, which tries a number first, so 1200 and "1200.00" both become "n:1200.0", and otherwise trims and lower-cases the text. The n: and s: prefixes keep a number and a string from colliding by accident. A field whose normalized values differ produces one differs entry carrying the original values; a shared id with no differing field adds 1 to matched. Every list is sorted at the end, differs by (id, field), so two runs on the same data produce the same report. Ids themselves are joined exactly as written; only field values are normalized.

Walk through an example

The demo input has one shared customer whose fields differ only in formatting, plus one id on each side alone.

Complexity

Time O((n + m) · F + k log k): each record is indexed once, each shared id runs _norm on F field pairs, and the k items in the output lists are sorted. Space O(n + m) for the two indexes, plus the report. The nested-loop join is O(n · m · F) time with O(1) working space, because it rescans the other list for every record.

Edge cases to test

• {"amount": 1200} against {"amount": "1200.00"} → matched. Both normalize to "n:1200.0"; a raw comparison reports a false difference.
• {"zip": "02134"} against {"zip": "2134"} → matched, because numeric-first normalization drops the leading zero. Compare codes and identifiers stored as text as strings.
• ERP id "ACME-1" and CRM id "acme-1 " → one id in each only-in list. The join key is compared exactly; only field values are normalized, so normalize keys before indexing if the systems format them differently.
• {"v": None} against {"v": ""} → a differs entry, because None normalizes to "" and the empty string to "s:". A field missing on one side and None on the other do match.
• {"v": 0.1 + 0.2} against {"v": "0.3"} → a differs entry, because 0.30000000000000004 is not 0.3. Compare money as Decimal or round to cents first.
• A record with no "id" is indexed under None; next to string ids, Python’s sorted then raises TypeError. Validate keys first and report keyless records as their own bucket.

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.

Follow-ups an interviewer asks next

The exports are too big for memory.

Sort both files by id with an external sort, or have each database return its rows in id order, then merge the two sorted streams the way merge sort does: advance whichever side has the smaller id, and compare when the ids are equal. Memory drops to about one record per side, time is O((n + m) log(n + m)) for the sort plus a linear merge, and duplicates show up as equal neighbors.

The two systems use different keys for the same customer.

Build a crosswalk first: a mapping table from ERP id to CRM id, seeded from a shared field such as a tax number or a normalized email, and reviewed by a person wherever the match is fuzzy. Reconcile through the crosswalk, and report unmapped ids as their own bucket, because a customer missing from the mapping is a different problem from a customer missing in a system.

Run it every night and report only what changed.

Store each run’s report keyed by (id, field) and diff it against the previous run: new discrepancies, resolved ones, and ones still open, with how long each has been open. The sorted, deterministic output is what makes that diff meaningful. Alert on growth in the open count rather than its size, since a stable backlog is a known quantity.

Some fields need tolerance, like prices or timestamps.

Make normalization per field instead of global: amounts compared as Decimal to the cent, timestamps converted to UTC and compared within a window such as one second, names case-folded, and codes compared as exact strings. A small table from field to comparator keeps the rules explicit and reviewable, and it also fixes the leading-zero problem with ZIP codes.

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.

Reference solution

def _norm(v):
    if v is None:
        return ""
    try:                                   # numeric first: 1200 == "1200.00"
        return "n:" + repr(float(v))
    except (TypeError, ValueError):
        return "s:" + str(v).strip().lower()


def _index(rows):
    by_id, dupes = {}, set()
    for r in rows:
        rid = r.get("id")
        if rid in by_id:
            dupes.add(rid)                 # keep the FIRST occurrence
        else:
            by_id[rid] = r
    return by_id, dupes


def reconcile(erp, crm, fields):
    e, e_dupes = _index(erp)
    c, c_dupes = _index(crm)

    both = set(e) & set(c)
    differs, matched = [], 0
    for rid in both:
        row_diffs = [
            {"id": rid, "field": f, "erp": e[rid].get(f), "crm": c[rid].get(f)}
            for f in fields
            if _norm(e[rid].get(f)) != _norm(c[rid].get(f))
        ]
        if row_diffs:
            differs.extend(row_diffs)
        else:
            matched += 1

    return {
        "only_in_erp": sorted(set(e) - set(c)),
        "only_in_crm": sorted(set(c) - set(e)),
        "matched": matched,
        "differs": sorted(differs, key=lambda d: (d["id"], d["field"])),
        "duplicate_ids": sorted(e_dupes | c_dupes),
    }


erp = [{"id": "C-1", "name": "ACME", "amount": 1200}, {"id": "C-2", "name": "Beta", "amount": 5}]
crm = [{"id": "C-1", "name": "acme ", "amount": "1200.00"}, {"id": "C-3", "name": "Gamma", "amount": 9}]
r = reconcile(erp, crm, ["name", "amount"])
print(r["matched"], r["only_in_erp"], r["only_in_crm"], r["differs"])
# -> 1 ['C-2'] ['C-3'] []

function norm(v: any): string {
  if (v === null || v === undefined) return "";
  const n = Number(v);
  if (v !== "" && !Number.isNaN(n)) return "n:" + n;      // 1200 === "1200.00"
  return "s:" + String(v).trim().toLowerCase();
}

function index(rows: any[]) {
  const byId = new Map<string, any>();
  const dupes = new Set<string>();
  for (const r of rows) {
    if (byId.has(r.id)) dupes.add(r.id);                  // keep the FIRST
    else byId.set(r.id, r);
  }
  return { byId: byId, dupes: dupes };
}

function reconcile(erp: any[], crm: any[], fields: string[]): any {
  const e = index(erp), c = index(crm);
  const differs: any[] = [];
  let matched = 0;

  for (const id of Array.from(e.byId.keys())) {
    if (!c.byId.has(id)) continue;
    const a = e.byId.get(id), b = c.byId.get(id);
    const rowDiffs = fields
      .filter(function (f) { return norm(a[f]) !== norm(b[f]); })
      .map(function (f) { return { id: id, field: f, erp: a[f], crm: b[f] }; });
    if (rowDiffs.length) differs.push.apply(differs, rowDiffs);
    else matched++;
  }

  const eIds = Array.from(e.byId.keys()), cIds = Array.from(c.byId.keys());
  return {
    only_in_erp: eIds.filter(function (i) { return !c.byId.has(i); }).sort(),
    only_in_crm: cIds.filter(function (i) { return !e.byId.has(i); }).sort(),
    matched: matched,
    differs: differs.sort(function (x, y) { return (x.id + "|" + x.field).localeCompare(y.id + "|" + y.field); }),
    duplicate_ids: Array.from(new Set(Array.from(e.dupes).concat(Array.from(c.dupes)))).sort(),
  };
}

const erp = [{ id: "C-1", name: "ACME", amount: 1200 }, { id: "C-2", name: "Beta", amount: 5 }];
const crm = [{ id: "C-1", name: "acme ", amount: "1200.00" }, { id: "C-3", name: "Gamma", amount: 9 }];
const r = reconcile(erp, crm, ["name", "amount"]);
console.log(r.matched, r.only_in_erp, r.only_in_crm, r.differs);
// -> 1 ["C-2"] ["C-3"] []