Michelle Lee
In progress

Budget Pipeline

A personal data pipeline that turns bank, card and Amazon order data into itemized spending and net worth reports. It’s a private tool for my own accounts, built the way I’d build a production pipeline: layered, tested and safe to rerun.

The problem

Shopping is my most expensive hobby, so I want to know exactly where the money goes. My bank shows each Amazon charge as one total and a merchant name. What I actually bought, and what I returned, only lives in Amazon’s order emails. Joining the two answers questions a budgeting app can’t, like how much went to clothing versus household things last month, so I can set limits and see whether I’m keeping them.

How it works

Budget Pipeline data flowBank and card data from SimpleFIN and Amazon emails from Gmail are extracted by Python into raw tables, cleaned into staging tables by dbt, and joined into marts. Prefect runs every step.SimpleFINbanks, cardsGmail (read-only)Amazon order emailsrawas extractedstagingtyped, dedupedmartsitemized spendingPython extractdbt models + tests in DuckDBPrefect runs each step in order, with retries
Extract and load first, then transform inside the warehouse (ELT).
  • Extract: Python pulls accounts and transactions from SimpleFIN, and order, shipping and refund emails from six Amazon sender addresses through the Gmail API with read-only access.
  • Load: records land unchanged in raw tables in DuckDB, keyed so a rerun can’t duplicate them.
  • Transform: dbt casts and dedupes into staging tables, then builds marts that match Amazon order items to card charges.
  • Orchestrate: a Prefect flow runs extract, load and transform in order, with retries on the network steps.

Where it stands

PieceStatus
Raw, staging and mart layersBuilt and tested on synthetic data
dbt project18 tests passing
Prefect flowRuns end to end; a second run changes nothing
SimpleFIN and Gmail extractorsWritten; connecting real accounts next
Scheduling and hosted warehouse (MotherDuck)Planned

Decisions worth explaining

  • DuckDB over Snowflake or Postgres. A personal dataset is megabytes. DuckDB runs locally at no cost, and MotherDuck can host the same database later without rewriting the dbt models.
  • ELT instead of ETL. Keeping raw data unchanged means a parsing fix can be replayed over history without calling the APIs again.
  • Synthetic data first. A seeded generator produces the same records every run, so tests and rerun checks are repeatable before any real account is connected.

How this app uses Google data

Budget Pipeline requests read-only Gmail access (gmail.readonly) to read order, shipping and refund emails from Amazon in the author’s own mailbox. It never sends, changes or deletes email, stores data only on the author’s computer or the author’s own database, and shares nothing with anyone. It is a personal tool and isn’t offered to other users. Details are in the privacy policy.

Contact

Michelle Lee · hello@michulee.com