Batch Reconciliation System

Design a data reconciliation system for a fintech platform that processes 50 million payment transactions per day across three upstream sources (payment gateway, bank ledger, internal wallet service). Each batch job runs at midnight to reconcile the day's transactions. Some upstream jobs fail partially, data arrives late, or records go missing. The business requires: every transaction must be accounted for, discrepancies must be flagged and resolved within 4 hours, and the reconciliation must be re-runnable without creating duplicates. How would you design this end to end?

How to Approach This Problem

What Makes This Problem Unique Reconciliation is one of the most commonly asked data engineering system design questions in fintech, payments, and banking interviews — and the one most candidates answer incorrectly. The typical wrong answer is: "compare tables A and B, find mismatches, send alerts." That describes a one time script, not a production grade reconciliation system. The hard problems are not the comparison logic. They are re runnability, partial upstream failures, and late arriving data — and these interact with each other in ways that break naive designs. Hard problem 1: Idempotency under re runs Upstream jobs fail. Your reconciliation job will be re run — by an on call engineer

Clarifying Questions to Ask the Interviewer

Functional Requirements What constitutes a "match"? Exact transaction ID match? Amount + timestamp within tolerance? What is the matching key? What are the three upstream sources and their formats? REST API pull? SFTP batch file drop? Database replication? What does "reconcile" mean here — find missing transactions, find amount mismatches, or both? Should reconciliation run for a single calendar day (T+0), or should we support multi day windows for late arrivals? What is the resolution workflow for discrepancies? Automated rule based resolution, manual review queue, or both? Do we need to reconcile in both directions — upstream to internal and internal to upstream? Should the system support

Envelope Estimation

Scale Estimates Transaction volume: 50M transactions/day to reconcile per batch run Peak business days: up to 80M transactions (2× baseline) Data arrives from 3 sources — each exports a snapshot once per day after close of business Data size per day: Average transaction record: ~500 bytes (IDs, amounts, timestamps, metadata) 50M × 500B = 25GB raw per source × 3 sources = 75GB/day in staging After 30 day retention: ~2.25TB in staging (before compression) With Parquet + Snappy compression: ~400 500MB/source/day → ~1.5GB/day total Reconciliation processing: 3 way join on 50M records: Spark with 20–40 executors (4 cores, 16GB each) completes in ~15–25 minutes Idempotency store lookups: ~50M Redi

Idempotency: The Most Important Design Decision

Why Idempotency Is Non Negotiable In production, reconciliation jobs re run constantly: The bank ledger file arrived 2 hours late → re run A Spark executor OOM'd mid job → re run An ops engineer made a config fix → re run A backfill for a missed day → re run Without idempotency, every re run creates duplicate discrepancy tickets, double alerts to the finance team, and corrupted audit logs. The system becomes untrustworthy. The run id Pattern Every reconciliation job execution gets a unique run id : The run id is written to the idempotency store (Redis) before any processing begins: Upsert Pattern for Reconciliation Results All writes use ON CONFLICT DO UPDATE so re runs overwrite previous re

Handling Partial Upstream Failures

The Three Types of "Missing" Data When records don't match, there are three fundamentally different causes with different resolution paths: Type Description Resolution SLA Impact Late data Record exists upstream, hasn't arrived yet Wait and re run within window Defer alert until T+4h Missing data Record never existed in source (upstream bug/gap) Escalate to source team, manual fix Immediate alert Corrupt data Record arrived but amount/timestamp is wrong Reject, re request, auto resolve if within tolerance Depends on amount Detecting Late vs Missing Data You cannot distinguish late from missing at T+0. The solution is a staged check approach : Upstream Readiness Check Before running reconcili

Data Modeling

Core Tables reconciliation runs discrepancies audit log (append only) Staging Layer Schema (S3 Parquet) Key Design Decisions Why Postgres for discrepancies, not a data warehouse? Discrepancies are an OLTP workload — status updates, point lookups by transaction id, concurrent writes from resolver. Postgres handles this much better than BigQuery/Redshift. Move closed discrepancies to the warehouse after 30 days for historical analytics. Why the UNIQUE constraint on (business date, transaction id, discrepancy type)? This is the idempotency guarantee at the DB level. Even if the application has a bug and tries to insert the same discrepancy twice, the DB prevents it. Why a generated column for a

Loading system design guide...