Everything else I’ve built in this portfolio — the dashboard, the dbt warehouse, the simulator, the training program, the copilot — runs off one SQLite file. This post is how that file gets built, and the three places the public data bit me.
The pipeline
Three sources, all keyless, all public:
- USAspending API v2 (api.usaspending.gov, CC0 1.0) — top-tier agency reference, agency budget execution by fiscal year and period, government-wide budgetary resources, VA’s federal accounts (TAS), and File D account balances.
- Treasury FiscalData (api.fiscaldata.treasury.gov) — Debt to the Penny (daily, back to 2015) and the Monthly Treasury Statement’s receipts table.
- FRED’s keyless CSV endpoint (fred.stlouisfed.org) — seven macro series: outlays (FGEXPND), M2, 10-year yield, effective federal funds rate, 10Y-3M spread, unemployment, CPI-U.
The flow is deliberately boring:
etl/pull_*.py → data/raw/*.json|csv (every call logged)
etl/load_sqlite.py → data/budget.db (15 tables, idempotent)
queries/01…10_*.sql → reference analyses
The part I’d defend in any code review is the audit trail: every API call is
logged with its exact URL, parameters, and HTTP status in
data/pull_log.md. If you want any number in the database, the log tells you
the exact call that produced it. That’s what “public sources, every figure
cited” means mechanically — the citation isn’t a URL pasted next to a number,
it’s a reproducible request.
What’s in it
Fifteen tables, 46,042 rows at the freeze:
| Table | Rows | Grain |
|---|---|---|
toptier_agencies | 111 | every top-tier agency + latest snapshot amounts |
agency_budgetary_resources | 30 | tracked agency × fiscal year |
agency_obligation_by_period | 216 | agency × FY × fiscal period, cumulative |
gov_budgetary_resources | 33 | government-wide, by fiscal period |
va_federal_accounts | 44 | VA TAS accounts |
debt_to_penny | 2,935 | day |
mts_receipts | 1,221 | month × MTS line |
seven fred_* tables | 40,813 | series × date |
file_d | 0 | (see below) |
The tracked agencies in the per-period tables are 036 (VA), 020 (US Treasury), and 012 (USDA). The ten reference queries — FY-over-FY execution, debt trajectory, MTS receipts by source, outlays vs trailing average, and the rest — are in the repo, each with a one-line comment saying what question it answers.
The three things that got it wrong
1. Agency codes. An early draft of the puller had a comment labeling the tracked agencies HHS and DoD. It was wrong: the real HHS and DoD top-tier codes are 075 and 097, and neither was in the per-period tables. The codes actually tracked are 036/020/012 — VA, Treasury, USDA — and I verified them against the API’s own top-tier reference before naming them in anything. Lesson I’ve since applied everywhere: verify codes against the reference table before you write them into a headline.
2. The File D endpoint was broken. The USAspending POST /download/accounts/ endpoint returned HTTP 500 for several FY2024
fiscal periods at pull time (you can see the 500s in the pull log). So
file_d ships as a 0-row table — empty by design, schema complete —
rather than half-filled. The recovery path is etl/topup.py: it re-fetches
only empty or missing payloads, never overwrites good data, and is safe to run
any time. A gap in the data should be a documented gap, not a silent one.
3. FRED retires series without much fanfare. The classic federal funds
series — FEDFUNS, GFED01, GFED02 — are gone (404). The replacement is
DFF, the effective rate on a daily basis. The puller has a comment that
says exactly that, because someone (me, next year) will want to re-add them.
The freeze
The database is a frozen artifact: pulled once, loaded once, shipped as a file. Every consumer repo pins its SHA-256 — if a copy drifts or gets stale, their build fails rather than silently serving old numbers. The reproducibility story is the log plus the freeze: fresh pulls at any time, pinned values in between.
What runs on it
- The Power BI dashboard and the dbt + DuckDB warehouse read frozen CSV exports of this database.
- The flow simulator freezes its JSONs from it.
- The NL→SQL copilot ships a SHA-pinned copy and runs its 40 pre-approved queries against it.
- The training program teaches off the same tables.
One spine, one pull log, one hash. The next post is the Excel side of the house: six macros for the monthly close.
— Joanna