Why reconciliation breaks
Every day, records from systems that were never designed to agree must be matched, and every record that does not match must become a tracked exception with an owner and a reason. That is the whole job in one sentence. It breaks because the three sources disagree in format, timing and granularity, even when no money is missing.
| Source | What it says | When it arrives | Why it disagrees |
|---|---|---|---|
| Your ledger | What your platform believes happened: each authorization, capture, refund and fee, per customer | In real time | Records intent and internal state; may include transactions the processor has not settled yet |
| Processor settlement files | What the processor settled, usually net of fees, by batch or by day | Daily or several times a day, often by SFTP | Different identifiers, net amounts, fees and FX applied, batches that span your cut-off |
| Bank statement | What cash actually moved in and out of your accounts | Daily, sometimes intraday | One deposit may cover thousands of transactions; descriptions are short and inconsistent |
When the pipeline behind reconciliation is fragile, the symptoms are familiar. Finance waits until midday for yesterday's numbers. Analysts spend the morning on breaks that turn out to be timing differences. Fixes happen in spreadsheets that nobody can audit. And when a sponsor bank or an auditor asks how a figure was produced, the answer takes a week.
The architecture at a glance
The pattern below works for most payments companies on AWS, from a single processor to several. Each layer has one job and one control that matters most.
| Layer | Typical AWS services | Its job | The control that matters |
|---|---|---|---|
| Ingest | AWS Transfer Family, Amazon S3, Amazon Kinesis Data Streams or Amazon MSK, AWS DMS | Land every file and event exactly as received | Immutable raw storage and completeness checks |
| Standardize | AWS Glue, Apache Iceberg tables, AWS Glue Data Catalog | Turn every source into one transaction model | Data contracts that stop bad loads early |
| Match | AWS Glue or Amazon EMR with Spark, Amazon Athena | Pair records across sources in tiers | Versioned, tested matching rules |
| Exceptions | Iceberg exceptions table, AWS Step Functions, Amazon SNS | Give every break an owner, a reason and an age | Service-level timers and escalation |
| Report | Amazon Athena, Amazon QuickSight | One set of numbers for finance, operations and risk | Reports read the same tables the matching wrote |
| Control | AWS Lake Formation, AWS IAM, AWS CloudTrail, AWS KMS | Limit who sees what and record who changed what | Least privilege and a full audit trail |
Step 1: ingest without changing anything
Settlement and bank files land in Amazon S3, often delivered over SFTP through AWS Transfer Family. Payment events arrive through Amazon Kinesis Data Streams or Amazon MSK. Ledger changes can be captured with AWS DMS change data capture, or published by the ledger itself as events.
The raw zone is kept exactly as received. Turn on S3 versioning, and consider S3 Object Lock for the files you may need to produce as evidence. Any day can then be replayed from the original inputs, which is the single most useful property a reconciliation platform can have.
Before a file is processed, check that it is complete. Most settlement formats carry a header and trailer with a record count and control totals. Compare them with what was actually received, and check that the file covers the expected date and batch. A truncated file processed as if it were complete creates hundreds of false breaks and, worse, hides real ones.
- Register every file in a small manifest table: source, file name, checksum, business date, record count, control total, status.
- Reject duplicates by checksum before they reach any downstream table.
- Alert on missing files by a deadline, not only on failed ones. A file that never arrives fails silently.
Step 2: standardize into one model
AWS Glue jobs convert each source into a common schema in Apache Iceberg tables. Iceberg gives you ACID updates, time travel to any previous state and efficient upserts with MERGE INTO, which Athena and Spark both support. The model does not need to be large, but every field must mean the same thing for every source.
| Field | Rule |
|---|---|
| source, source_record_id | Where the record came from and its identifier there. Together they are unique. |
| business_date | The settlement or posting date under an agreed cut-off, not the file date |
| event_time_utc | Every timestamp converted to UTC at load |
| amount_minor, currency | Integer amounts in minor units (cents) with an ISO 4217 currency code; never floating point |
| gross, fee, net | All three kept where the source provides them, so fees can be matched as known variances |
| match_keys | Every identifier that could link records: your reference, processor reference, batch, authorization code, token |
| record_type | A small controlled list: capture, refund, chargeback, fee, adjustment, payout |
Data contracts describe what each source must contain: fields, types, allowed values and volumes within a range. Loads that break the contract stop early with a clear error. AWS Glue Data Quality can run these checks as rules inside the pipeline.
Step 3: match in layers
Matching works best as a sequence of tiers, from strict to loose. Each tier only sees what the previous ones left unmatched, and every match records which rule produced it.
| Tier | Rule | What it catches |
|---|---|---|
| 1. Exact | Same reference, same amount, same currency, business date within tolerance | The bulk of clean, one-to-one transactions |
| 2. Known variance | Same reference, amount differs by an expected fee, interchange or FX difference | Net settlement and fee deductions that are not real breaks |
| 3. Aggregate | Many ledger records sum to one settlement or bank line, by batch or payout | Batched settlements and bank deposits |
| 4. Timing | Record exists in one source and appears in the other within the next one or two business days | Cut-off and weekend differences |
| 5. Assisted | Fuzzy candidates scored by amount, date and description, confirmed by a person | The small remainder that rules cannot settle |
Each rule is versioned and tested against a fixed set of historical days with known outcomes. When someone changes a tolerance or adds a rule, the test set shows exactly which matches would change before the rule reaches production.
Step 4: turn breaks into an exception workflow
Unmatched records go to an exceptions table with a reason code, an age and an owner. A short, clear taxonomy matters more than a complete one: ten codes that everyone applies the same way beat a hundred that nobody agrees on.
| Reason code | Meaning | Usual owner |
|---|---|---|
| MISSING_SETTLEMENT | In the ledger, not settled after the timing window | Payments operations |
| MISSING_LEDGER | Settled or paid, but no ledger record | Engineering and finance |
| AMOUNT_MISMATCH | Matched by reference, amount differs beyond known variances | Payments operations |
| DUPLICATE | The same transaction appears twice in one source | Engineering |
| UNEXPECTED_FEE | Fee not in the agreed fee schedule | Finance |
| CHARGEBACK_OPEN | Dispute debited, case not yet resolved | Disputes team |
| BANK_UNMATCHED | Cash movement with no corresponding settlement or payout | Treasury |
AWS Step Functions can drive the workflow: assign by reason code, start a service-level timer, notify the owner through Amazon SNS or your ticketing tool, and escalate when the timer expires. Resolutions write back to the exceptions table with who resolved it and why, so the next person facing the same pattern can see what was done. Use our reconciliation workload calculator to estimate how many exceptions your team handles today and what a better match rate would free up.
Step 5: report from the same tables
Amazon Athena and Amazon QuickSight read the matched and exception tables directly, so finance, operations and risk see the same numbers. A separate reporting copy is how two versions of the truth appear. Four measures cover most needs:
- Match rate by tier and source, daily, to see whether rules are degrading.
- Open exceptions by reason code and age, to see where work is piling up.
- Unreconciled value in each currency, which is the number treasury and the board care about.
- Time to reconcile: when each business day was fully reconciled, against the target.
Step 6: control access and keep an audit trail
AWS Lake Formation permissions keep merchant and customer data visible only to the roles that need it, down to column and row level. Keep primary account numbers out of the reconciliation platform altogether: match on processor tokens and references instead. That keeps the platform smaller in PCI DSS scope, and it is rarely a limitation for matching.
Every rule change, manual match and exception resolution should record who did it, when, and which records it affected. AWS CloudTrail covers changes to the infrastructure; the application must log changes to rules and data. Encrypt every table and bucket with AWS KMS keys that are managed by the platform team, not by individual users.
Common failure modes
| Failure | What you see | The fix |
|---|---|---|
| Keys that are not unique | Wrong matches across processors or days | Composite keys: source plus reference plus business date |
| Fees and FX treated as breaks | Thousands of small amount mismatches every day | A known-variance tier driven by the fee schedule |
| Late or truncated files | Sudden spikes of missing settlements | Completeness checks against header and trailer totals |
| Loads that are not idempotent | Duplicates after a retry or a re-run | Checksums in the manifest and MERGE on natural keys |
| Ambiguous cut-offs | The same breaks every Monday and month-end | One written cut-off and timezone per source |
| Fixes in spreadsheets | Numbers that cannot be reproduced | Manual matches recorded in the platform, with a reason |
Where AI fits, and where it does not
A well-governed assistant can read an exception, the related records and the approved procedure, then explain the likely cause and propose a next step. That can remove much of the time an analyst spends gathering context before making a decision. It should not post entries, release funds or close exceptions on its own.
That boundary is a design decision, made before the first prompt is written: the assistant gets read-only tools, and anything high-risk goes through an approval step with a named person. Our guide on human approval for AI in payments operations covers the pattern in detail.
A realistic rollout
You do not need to rebuild everything at once. A first processor and bank account, end to end, proves the pattern and produces numbers people can check.
| Phase | Scope | Done when |
|---|---|---|
| 1. Foundation | Raw ingestion, manifest, canonical model for one processor, one bank account and the ledger | Any business day can be replayed and gives the same result |
| 2. Matching | Tiers 1 to 4, exception table, reason codes, daily report | The new process matches the current one on a month of history, and every difference is explained |
| 3. Workflow | Owners, timers and escalation; manual matches inside the platform | No reconciliation work happens in spreadsheets |
| 4. Scale | Further processors, currencies and bank accounts; assisted matching | Each new source is a configuration, not a project |
Running the old and new processes side by side for a few weeks is worth the effort. It is the evidence finance needs to trust the new numbers, and it usually reveals a few rules that existed only in someone's head.
Questions we hear
Should we buy a reconciliation product or build on AWS?
Products work well when your sources and processes are standard. Building makes sense when you have several processors, custom ledger logic or volumes where per-transaction licensing becomes expensive. Many companies do both: a product for bank reconciliation and a platform on AWS for transaction-level matching.
How fast can reconciliation run?
With incremental processing, most daily reconciliations can finish shortly after the last file arrives. The limit is usually when processors and banks deliver their files, not the pipeline.
Do we need streaming?
Usually not for reconciliation itself, since settlement and bank data arrive in files. Streaming helps when you want intraday visibility of the ledger side or early warning on missing transactions.
Where should matching rules live?
In version control, as configuration or code, with tests. Not in a database table that people edit directly, and never in a spreadsheet.
How does this help with a sponsor bank review?
Banks ask how you reconcile customer funds and how quickly you would know about a shortfall. Replayable inputs, a documented process and reports on unreconciled value answer those questions with evidence. See what your bank partner will ask.