---
title: Automate Ecommerce Payment and Payout Reconciliation on AWS
description: One Athena query at the 10 MB minimum is about $0.00005 at $5 per TB scanned. Thirty quiet days are about $0.0015 before you count storage. The query classifies exceptions. It does not refund anyone.
url: https://www.factualminds.com/blog/automate-ecommerce-payment-payout-reconciliation-aws/
datePublished: 2026-10-19T00:00:00.000Z
dateModified: 2026-10-19T00:00:00.000Z
author: palaniappan-p
category: Data & Analytics
tags: ecommerce, amazon-athena, amazon-s3, amazon-eventbridge, payments
---

# Automate Ecommerce Payment and Payout Reconciliation on AWS

> One Athena query at the 10 MB minimum is about $0.00005 at $5 per TB scanned. Thirty quiet days are about $0.0015 before you count storage. The query classifies exceptions. It does not refund anyone.

On **19 October 2026**, payout day is still a join between two files that disagree. The [fintech architecture post](https://www.factualminds.com/blog/building-fintech-applications-on-aws-architecture-patterns/) 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](https://www.factualminds.com/examples/architecture-blog-2026/payout-reconciliation/exception-classes.md). 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](https://docs.aws.amazon.com/whitepapers/latest/big-data-analytics-options/amazon-athena.html) 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](https://docs.aws.amazon.com/scheduler/latest/UserGuide/getting-started.html) 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](https://www.factualminds.com/blog/automate-ecommerce-product-feed-validation-aws/) is that article's cutover, not a law of reconciliation.

```mermaid
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.

```sql
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](https://www.factualminds.com/blog/detect-stale-ecommerce-data-exports-aws/), 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](https://aws.amazon.com/eventbridge/pricing/). 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](https://www.factualminds.com/services/aws-data-analytics/) helps when the files disagree about identifiers, not when you need another dashboard.

## FAQ

### When should we not automate payout reconciliation this way?
Skip it when the commerce platform and the processor already share one report your finance team trusts, and when you have no second file to join. Also skip it if you need a subledger with balanced debits and credits. This workflow emits exception rows. It is not a general ledger.

### What could go wrong if a Lambda refunds from the exception report?
A duplicate_capture row becomes a second refund, or a missing_refund row refunds a charge that settled late. The report is input for a person. Any correction is a separate approved action. The idempotency article is what you read before that action exists.

### Does a successful Athena query mean the books balance?
No. It means the SQL you wrote returned rows under the rules you wrote. Fees, currencies, and timing windows you forgot stay invisible. AWS does not certify the result.

### Where should settlement files that contain card data live?
In a dedicated bucket with the narrowest read list you can stand, encryption, and no public access. If the file contains PAN or SAD, this design is the wrong place to start. Strip or tokenise before the object lands, and involve whoever owns your PCI scope.

---

*Source: https://www.factualminds.com/blog/automate-ecommerce-payment-payout-reconciliation-aws/*