EP 5 | How to design a reconciliation system 💵 💶?

Hi, Aniket here 👋🏻 and welcome to my System Design Newsletter 😊!

Reconciliation in payment systems is the process of comparing internal systems' records with those in external systems at regular intervals to figure out and fix discrepancies. It is vital because:

1. It uncovers issues in accounting systems that often go unnoticed until this critical step, ensuring errors are caught and corrected.

2. Effective reconciliation identifies anomalies and potential fraud by comparing internal records with external data.

3. Successful reconciliations enable actual fund transfers between payment systems, banks, and third-party platforms.

Figure 1: Components of a recon system

The process broadly involves the following steps:

1. Data ingestion and transformation: Workflow orchestrators like Apache Airflow or big data processors like Apache Spark are generally used to fetch, transform, and normalize data from external sources through jobs running at regular intervals.

   1. Transaction statements from external services are received in CSV, excel or via APIs.

   2. All data is converted into a common format through a transformation job.

   3. Data is normalized by aligning timestamps, converting currencies, and eliminating duplicates for accurate comparison with internal records.

2. Reconciliation Service

   1. Data collection pipeline sends normalized data to reconciliation service either through streaming platforms like Kafka or API call.

   2. Transactions are fetched from internal accounting system (read Accounting in payment systems 👆🏻👆🏻) for comparison.

   3. The service matches external and internal transactions, identifying discrepancies like missing entries or amount mismatches (Figure 1).

3. Reporting: Results can be viewed either in recon dashboards built inhouse/using Retool or visualization platforms such as Metabase.