Automate Ecommerce Payment and Payout Reconciliation on AWS

On 19 October 2026, payout day is still a join between two files that disagree. The fintech architecture post names reconciliation as a control. It does not match a settlement file. This page matches the file and stops.

Reproduce this — Copy the exception classes. Write the match window, the currency, and the fee tolerance before the first query. An empty tolerance is not zero.

Who this is for

Finance operations and the person who lands the processor file. You already have an order export and a settlement export. You do not yet have a shared report both sides trust.

What you get, and what you do not

You get a daily exception report in S3: missing captures, missing refunds, fee mismatches, duplicate captures, unmatched settlement lines. You do not get a posted journal, a refund, or a claim that the books balance. Athena’s cost model charges bytes scanned. It does not charge you for being correct.

Opinionated take: classify and stop. The trade-off is a report a person must open. An automatic refund from a SQL row is how a late settlement becomes a double refund.

Architecture

Orders land in s3://recon-prod/orders/. Settlements land in s3://recon-prod/settlements/. A daily EventBridge Scheduler schedule starts a Lambda whose only job is StartQueryExecution. Athena writes CSV to reports/. Nobody in this design calls the processor.

If a single day’s files are small enough that you would rather not run a query engine, a Lambda join is enough. Move to Athena when you need the same SQL tomorrow and the files no longer fit a comfortable memory budget. The 100 MB figure in the feed-validation article is that article’s cutover, not a law of reconciliation.

flowchart LR
  orders[OrdersPrefix] --> query[AthenaQuery]
  settlements[SettlementsPrefix] --> query
  clock[DailySchedule] --> query
  query --> report[ExceptionReport]
  report --> reviewer[HumanReview]

Walkthrough

1. Separate prefixes and separate tables. External tables over CSV or, better, Parquet you converted on ingest. Partition by dt. A query without the partition predicate scans more than the day you care about.

2. One class, one query shape. Context: Athena engine version 3, partitioned external tables orders and settlements. This statement finds paid orders with no settlement line. The other classes on the sheet are the same shape, not a second framework.

SELECT
  o.order_id,
  o.processor_ref,
  'missing_capture' AS exception_class
FROM orders o
LEFT JOIN settlements s
  ON o.processor_ref = s.processor_ref
 AND s.dt = o.dt
WHERE o.dt = DATE '2026-10-18'
  AND o.status = 'paid'
  AND s.processor_ref IS NULL

Duplicate captures are a GROUP BY processor_ref with HAVING COUNT(*) > 1 on positive settlement amounts. Fee mismatch compares s.fee with the contracted rate you stored in a one-row table. The tolerance is yours.

3. Land the report, do not act. The Lambda records the query id. A second schedule invocation for the same dt reads that id and does not start another query if the first succeeded. The report key includes the date, so a rerun overwrites that day’s report only.

4. A person classifies. Corrections, if you make them, are out of band. Read tool-call idempotency before any writer uses a processor reference as a side effect. This article has no writer.

IAM

The scheduler role can lambda:InvokeFunction on the starter function. That function can athena:StartQueryExecution and glue:GetTable on these two tables, and s3:PutObject on reports/*. It cannot s3:DeleteObject on orders/ or settlements/. The workgroup should be one you can cap. Athena workgroup query limits are how you stop a SELECT * without a date.

Failure and retries

A failed query is not billed for bytes scanned, per the Athena cost model above. Retry a failed start. Do not retry a succeeded query that returned zero exceptions: zero can be true. Alert when the query fails or when the settlement prefix is empty. An empty prefix is the stale-export problem, not a balanced day.

Cost, as an assumption

Athena is $5 per TB scanned, with a 10 MB minimum per query. An AWS machine-learning blog walks that minimum to about $0.00005 per query. Assume one query a day at the minimum: about $0.0015 in 30 days. A settlement file that actually scans 1 GB is 1/1024 TB * $5, about $0.0049 per query, about $0.15 in 30 days. Scheduler invocations at one per day sit inside the 14 million free Scheduler invocations. S3 storage is extra. Confirm the Athena price for your Region on the Athena pricing page before you treat these figures as a budget. Not a measured saving.

Testing

Fixture day: one paid order with no settlement line, one settlement line with no order, two positive lines sharing a processor reference. Expect three exception classes and no call to a payment API. Run the starter twice and expect one query id.

What broke — Lab, 19 October 2026. Not a customer account. The join used order id, and the processor file uses its own reference. Every row became missing_capture. Detection: the exception count equalled the order count. Recovery: join on the processor reference both files actually share, and keep order id as a column, not as the key. Lesson: a complete report can still be the wrong key.

Production checklist

• Match window, currency, and fee tolerance are on the sheet.
• Queries require a date predicate.
• The starter cannot call a refund or capture API.
• Card data does not land in the raw prefix.
• A second run for the same day does not start a second successful query.
• Empty settlement prefix alerts. It does not report a clean day.

What This Post Doesn’t Cover

PCI scope, chargeback representment, marketplace attribution, and agent refunds. How to build a fintech platform. Promotion of a curated finance table. That gate, if you build a lakehouse later, is the Glue Data Quality article.

What to Do This Week

1. Put one historical day of orders and one settlement file in a non-production bucket.
2. Run the missing-capture query by hand. Read ten rows with finance.
3. Only then schedule it.

The join is the work. The data analytics practice helps when the files disagree about identifiers, not when you need another dashboard.

Frequently asked questions