Designing safe cross-system data reconciliation

When two enterprise systems disagree, the first instinct is often to write a query, inspect a few rows, and patch the data manually. That can solve today’s incident while creating tomorrow’s audit problem.

A team of developers, analysts, QA engineers, operators, and domain experts built tooling for cross-system inconsistencies and duplicates. My role was to shape the architecture and help the group turn expert investigation steps into explicit, testable decisions. We replaced repeated ad hoc SQL and multi-team handoffs with a reviewable workflow.

Based on case volume and the manual handling it replaced, the team’s working estimate reached hundreds of avoided hours in a typical week. That was a planning estimate, not a controlled time study, so I would not use it as a benchmark for another system.

A data correction is a business operation, not a clever database script.

This case study shows how to separate detection from correction, explain why records matched, require evidence before a write, and make every correction safe to retry, audit, and reverse.

Safe cross-system reconciliation

Open in Studio

Separate detection from correction

A detector should be safe to run repeatedly. It reads stable snapshots or bounded query windows, normalizes each system’s representation, correlates records, and emits differences with reasons.

A corrector changes business state, so a false match can overwrite valid customer data rather than merely produce a noisy report. It therefore needs stronger controls: authorization determines who may approve the action, idempotency makes a retry safe, concurrency checks stop an old review from overwriting newer activity, validation proves the proposed state is allowed, and recovery defines how the team will reverse or compensate for a bad decision.

Keeping those responsibilities separate let the team improve matching logic without increasing write risk. It also allowed analysts to review evidence before any change occurred and gave QA clear boundaries for testing.

Identity resolution was the real domain problem

Cross-system records rarely share one perfect key. One system may use a domain identifier, another a record identifier, and a third an integration-specific reference. Attributes change over time, and duplicate records may each contain valid history.

We modeled correlation as explicit, explainable evidence:

• exact authoritative identifiers
• known crosswalk relationships
• scoped composite keys
• time-bounded lifecycle relationships
• confidence and ambiguity reasons.

If the evidence cannot select one record safely, surface an exception instead of manufacturing certainty.

Every difference needs a classification

“Rows differ” is not actionable. The tool should explain whether the difference is missing data, conflicting authority, delayed propagation, duplicate identity, invalid state, or an expected exception.

Classification connects technical comparison to business policy. It determines who may approve a correction, which system is authoritative, whether the change can be automated, and what validation must follow.

Model corrections as commands

Each correction carried a stable identifier, the observed before-state, an intended after-state, the policy that justified it, and the user or process that approved it. The handler verified that the source records had not changed since review before applying anything.

A sanitized command looked like this:

type ReconcileCustomerCommand = {
  commandId: string;
  caseId: string;
  sourceRecordId: string;
  expectedSourceVersion: number;
  targetRecordId: string;
  expectedTargetVersion: number;
  field: "status" | "externalReference";
  before: string | null;
  after: string;
  policy: "SOURCE_SYSTEM_AUTHORITATIVE" | "APPROVED_CROSSWALK";
  approvedBy: string;
};

The handler first reloads both records and compares their versions and the expected before value. If any of them changed after review, it rejects the command and sends the case back for evaluation. commandId is the idempotency key, so a retry can return the earlier result without applying the change again.

Together, those controls cover different failure windows. The optimistic version check prevents a stale analyst decision from overwriting newer business activity. The stable command id makes a retry return the earlier result instead of applying the correction again, while per-record serialization prevents two different remediation commands from changing the same record at the same time.

Where a true rollback was possible, we stored enough information to restore the prior state. Where an external side effect could not be reversed, we modeled a compensating action and made that limitation visible before approval.

Auditability was a feature

The audit trail recorded inputs, correlation evidence, classification, decision, commands, results, errors, and before/after values with sensitive fields appropriately protected. It answered who changed what, why, under which rule, and with what outcome.

The team also made the workflow observable at aggregate and individual levels: mismatch counts by category, age of unresolved cases, correction success and failure rates, repeated offenders, and correlation identifiers that connected application logs to a case.

Start with decision support and learn from experts

We began by comparing what the tool detected with expert manual investigations. Analysts explained not only what decision they made, but which evidence changed their confidence. Developers translated those explanations into explicit rules, and QA turned false positives and unexplained misses into durable test cases. Only after the evidence was trustworthy did we enable controlled corrections, first for low-risk categories and later for more complex cases.

This staged approach built confidence with analysts and operations because it respected their domain knowledge. The teaching moved in both directions: engineers taught the system’s safety model, while domain experts taught the team where apparently simple matches could be dangerously wrong. The tool did not replace expert judgment. It removed repetitive discovery and made judgment safer to apply.

Treat correction as a business operation

Data reconciliation is a distributed-systems problem with a human decision boundary. Clocks differ, delivery is delayed, identifiers drift, ownership is divided, and some effects cannot be reversed.

Design for that reality: make differences explainable, automate only the corrections you can prove safe, and preserve enough evidence to recover when the model turns out to be wrong. Keep the people who understand the data, the policy, and the operational consequences inside the design loop rather than consulting them at review time. Architecture draws the boundaries. Their discovery supplies the truth those boundaries have to protect.