Design and Implementation of a Channel Reconciliation System for ZuanZuan Payments

1 Background Introduction

With the rapid growth of ZuanZuan's business, transaction volume and payment amounts with external channels have surged, increasing the risk of financial errors; therefore, systematic reconciliation becomes essential to guarantee that no money is lost or over‑paid.

1.1 Reconciliation Definition

Reconciliation verifies the consistency among accounts, vouchers, and actual funds, typically using three models: account‑voucher, account‑account, and account‑actual checks, ensuring that every cent is correctly accounted for.

2 Overview

2.1 Business Status

To ensure data consistency, ZuanZuan compares its own transaction data with the data provided by third‑party payment channels.

2.2 Overall Architecture

The system distinguishes between online and offline reconciliation; online can be real‑time or near‑real‑time, while channel reconciliation is an offline process consisting of an offline reconciliation platform and a settlement center, with the platform further divided into a data ingestion layer and a data verification layer.

3 Design Idea

The reconciliation workflow is divided into three stages: data preparation, data verification, and error handling, which together form a complete reconciliation pipeline.

4 Data Preparation

Data preparation involves ingesting all necessary data from two sources: internal ZuanZuan data and external channel bills.

4.1 Internal Data

Four ingestion methods are supported:

Data Pull : the reconciliation system actively pulls data, requiring careful handling of pull frequency and performance.

File Push : upstream systems generate files (e.g., nightly at 02:00) and upload them via FTP; this approach has higher maintenance cost.

MQ Push : business systems send formatted MQ messages at designated times, which the reconciliation system subscribes to.

Binlog : the system listens to database binlog events, achieving zero‑intrusion for upstream systems while requiring robust filtering and idempotency handling.

Binlog ingestion is the preferred method, with file push used as a fallback for historical data.

4.2 External Data

External data refers to channel‑provided billing files, which face challenges such as varying readiness times, different file formats, and the need for repeated downloads. The solution employs task‑driven downloads that schedule staggered fetches, convert files to a unified CSV format, and store them locally to avoid redundant downloads.

4.3 Massive Data Solutions

Given daily volumes reaching 1 million records, the system adopts a hybrid storage strategy: recent data (within one year) is kept in MySQL with monthly sharding, while older data is archived to Hive. This combines the performance of sharding with the scalability of Hive.

5 Data Verification

5.1 Reconciliation Timing

Offline reconciliation is performed on a daily T+1 schedule.

5.2 Reconciliation Granularity

Both detail‑level and summary‑level reconciliations are executed in parallel to quickly detect discrepancies and pinpoint exact problematic records.

5.3 Reconciliation Method

Two approaches were evaluated: Hive SQL comparison and MQ‑based distribution. Hive SQL offers fast SQL‑driven checks without consuming online resources but is complex to maintain, while MQ distribution reduces single‑node pressure and simplifies result maintenance but is slower. The MQ‑based method was ultimately chosen for operational convenience.

6 Error Handling

Errors are captured with details such as scenario, error type, and processing node status. A unified error‑handling model stores these records and triggers remediation either via scheduled polling or automatic compensation during the reconciliation process.

7 Summary and Outlook

The integrated three‑module reconciliation system efficiently handles channel reconciliation, promptly identifies and resolves data inconsistencies, and enhances financial transparency, reducing complaints and supporting healthy business growth. Future work includes expanding reconciliation coverage to settlement, margin, and other business modules, aiming for end‑to‑end, zero‑gap verification while managing integration costs.

About the Author

Liu Kang, Backend Engineer, Payment Settlement Technology Department