The Challenge
In banking, data flows through multiple Systems of Record (SORs) — each maintained by different teams, on different platforms, with different update cycles. When a payment is initiated, it touches the core banking system, the payment gateway, the reconciliation engine, and the reporting layer. If any of these systems disagrees about the amount, status, or timing of a transaction, the result is a reconciliation break — and breaks mean manual investigation, delayed settlements, and audit findings.
The existing reconciliation process was largely manual: analysts would export data from each system, load it into spreadsheets, and eyeball the differences. This was slow, error-prone, and impossible to scale as transaction volumes grew. The team needed an automated framework that could reconcile data across all SORs on a daily basis, flag mismatches immediately, and produce audit-ready reports.
The Approach
The reconciliation framework centers on Apache Airflow, with each SOR pair getting its own DAG that runs on a configurable schedule. The DAGs were modular by design: a shared library of reconciliation operators handled the common patterns — extract, transform, compare, report — while each DAG only defined the SOR-specific logic. This meant adding a new SOR pair was a configuration exercise, not a development project.
The Airflow deployment was migrated from a self-managed EKS cluster to Amazon MWAA (Managed Workflows for Apache Airflow). This eliminated the operational burden of maintaining the Airflow infrastructure — upgrades, scaling, and monitoring were handled by AWS — while preserving full compatibility with the existing DAGs. The migration was done incrementally, with DAGs moved one at a time and validated in parallel before the old cluster was decommissioned.
Validation was layered: SQL-based checks ran at the ingestion layer (schema and completeness), the processing layer (business rule conformance), and the consumption layer (downstream report consistency). Any mismatch triggered an automated alert with full context — which SORs were involved, which records differed, and the exact nature of the discrepancy — so analysts could investigate immediately rather than discovering breaks days later.
The Outcome
The automated framework reduced reconciliation time from days to hours. What previously required a team of analysts working through spreadsheets now runs unattended every morning, with results delivered to stakeholders before the business day begins. Mismatch detection that once took days of manual effort now happens in near-real-time, with alerts routed directly to the teams that can fix them.
The YAML-driven configuration approach proved its value when the bank expanded into new regions. Onboarding a new regional SOR pair took less than a day — a process that previously required weeks of development and testing. The audit-ready reports generated by the system have been accepted by internal and external auditors without modification, significantly reducing compliance overhead.
Key Learnings
The most important design decision was making the reconciliation logic data-driven rather than code-driven. By externalizing SOR metadata, validation rules, and comparison logic into YAML configurations, the system became adaptable to new requirements without code changes. The second lesson was the value of layered validation: catching issues at the ingestion layer — before they propagate downstream — is always cheaper than discovering them in the final reports.