Design and Evolution of a Reconciliation Center: From Version 1.0 to 3.0

Reconciliation Center Overview

In many systems we encounter various "centers" such as user, product, or order centers. The reconciliation center, though less known, plays a crucial role, especially in the era of micro‑service architectures.

Basic Concept

Reconciliation can be viewed narrowly as financial data verification (date, amount, etc.) or broadly as checking the consistency of two homogeneous data sets. The article focuses on the broad definition: an independent system capable of verifying element consistency between two similar data collections.

The core ability is to compare two data sets for equality, but a practical reconciliation system also needs non‑core features such as detail viewing, balance change alerts, and more.

Key terminology includes participants A and B, and the result categories: A‑over‑account (more in A), A‑under‑account (less in A), balanced (both have), and error account (special mismatch).

Example: A contains orderId=10005, amount=100.00; B contains orderId=10005, amount=80.00. If only orderId is compared, the result is balanced; if amount is also compared, it is an error account.

Types of Reconciliation

Timing: real‑time (compare as soon as data changes) vs. periodic (daily, monthly, etc.).

Business Scenarios: amount reconciliation, flow reconciliation, status reconciliation.

Mode: transaction reconciliation (most common) vs. fund reconciliation (financial).

Required Capabilities

Management of reconciliation tasks (enable/disable, dynamic rule configuration).

Alerting and exporting of discrepancy results.

Persistence of source data and comparison results.

Support for multiple scenarios and comparison modes.

Reconciliation Center 1.0

Background

After a service is completed, the order payment is frozen until settlement. A job scans for funds to be unfrozen and notifies the account center via asynchronous MQ messages. Message loss or processing failure can cause undetected unfreeze failures.

Manual Excel comparison is not sustainable; an automated system is needed.

The workflow includes: extracting files via FTP, parsing them into Java objects, using set operations (intersection for balanced, difference for over/under) to obtain results, and storing them in a database for web‑based review.

Collections automatically deduplicate; special handling is required if duplicate records must be considered.

Initial implementation exposed several issues:

No alerting for discrepancies, requiring manual checks.

High integration cost for each new scenario.

Limited intrinsic capabilities of the center.

Reconciliation Center 2.0

To reduce integration effort, a generic data carrier was introduced, separating the unique key and comparable fields from business‑specific fields (stored as JSON). Two engines and two sub‑centers were added, and an alert center now supports DingTalk (summary) and email (detailed) notifications. Configuration of keys and fields became dynamic.

Historical discrepancies from the past seven days are re‑included in subsequent comparisons to handle cross‑day timing differences and holiday gaps.

Reconciliation Center 3.0

File‑based processing limitations led to the adoption of a database‑backed approach. Internal data is fetched directly from business databases via the data warehouse, while external data remains file‑based. The data warehouse provides task scheduling and dependency management, eliminating manual timing configurations.

Version 3.0 solves most of the previous version's problems, enabling zero‑Java code integration, flexible WHERE‑style configuration, and permission management through the company's URM system.

Remaining challenges include:

Single parsing engine supporting only .txt files.

Only periodic reconciliation; real‑time not supported.

Only transaction reconciliation mode.

Applicable Scenarios and Benefits

Scenarios

Periodic or long‑term data consistency checks.

Large‑scale data comparison beyond Excel capabilities.

Systems that rely on asynchronous processes for eventual consistency.

Financial and audit‑related data verification.

Benefits

Timely exposure of system issues.

Improved user experience by reducing errors.

Increased operational efficiency and reduced manual effort.

Acts as an insurance layer for business systems, similar to log monitoring.

Promotes better data storage standards across services.

Conclusion

System design must prioritize business needs over flashy features, and continuously identifying pain points is essential for ongoing improvement.