Skip to content

About

Keep Amazon estimates, posted charges, and settlement cash apart, then find the variance. Runnable on DuckDB with synthetic data.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

5 Commits

Folders and files

Repository files navigation

Amazon fee ledger demo

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.

Quickstart

python3 -m venv .venv
.venv/bin/pip install -r requirements.txt
.venv/bin/python run.py

The 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 test

What the models do

The 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:

  • estimate for planning values
  • charge for posted financial activity
  • cash for 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.

Use it in a warehouse

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.

Limits

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.

The production models

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.

License

MIT

About

Keep Amazon estimates, posted charges, and settlement cash apart, then find the variance. Runnable on DuckDB with synthetic data.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages