Amazon fee totals do not match when estimates, posted charges, and cash get added together.
They answer different questions.
This repo keeps them apart, then shows the variance.
python3 -m venv .venv
.venv/bin/pip install -r requirements.txt
.venv/bin/python run.pyThe last command builds a local DuckDB database from synthetic CSVs and prints three tables:
- settlement reconciliation
- charge only fees by SKU, with estimates beside them
- guard results
A clean run exits with status 0. A guard failure exits with status 2.
Run the tests with:
make testThe numbered files in sql/ run in order.
The four staging models normalize financial events, settlement fee lines, storage estimates, and reimbursements.
fct_fee_ledger gives every amount a record_grade:
estimatefor planning valueschargefor posted financial activitycashfor settlement and reimbursement records
That field is the boundary. An estimate must never be summed into a charge total.
rpt_settlement_reconciliation compares posted charges in each settlement window with that settlement's fee lines. The sample includes a deferred transaction. It creates a variance, then appears in the explanation instead of becoming an unexplained guard failure.
rpt_fee_by_sku sums charge grade rows only. Storage estimates and reimbursements sit in separate columns and are not added to that fee total.
guard_fee_model returns rows only when the model finds a missing required value, a duplicate source ID, invalid settlement boundaries, an invalid grade, a charge total that no longer ties to the ledger, overlapping settlement windows, or an unexplained settlement variance above the tolerance.
The sample also includes a reimbursement that arrives later than the order it references. It stays visible as a separate cash grade adjustment in the later month.
Replace the raw CSV tables in run.py with warehouse source tables.
Keep the normalized staging outputs stable. The downstream SQL is ANSI leaning and stays close to functions available in DuckDB, Snowflake, and BigQuery. You may need to adjust DATE_TRUNC, decimal casts, and STRING_AGG syntax for your target.
For real SP-API pulls, land the raw responses or report files first. Normalize one fee component per row. Bring deferred status from Finances API v2024-06-19 transaction data. Do not assume a v0 financial event exists before a deferred transaction is released. Keep source identifiers so reruns can be deduplicated. Pull generated settlement reports on a schedule. Revisit recent financial activity because late events can arrive.
See docs/fee-sources.md for the verified SP-API reference.
See docs/data-contracts.md before mapping a real report. Amazon flat file columns vary by marketplace and change over time, so check your own output and adjust the named staging model.
This ships with synthetic data.
It has not been run against a live Amazon account.
Validate every mapping and total against your own settlement report before trusting a number.
dbt/ holds the dbt models behind this, taken from a real Amazon seller warehouse and
genericized. Staging adapters for every SP-API financial event list, the intermediate
layer, the fee marts, and the connector migration pattern. Start there if you are
building the real thing rather than reading the idea.
MIT