The same nine frozen CSVs from the data spine, three different answers to “make this queryable.” One project per stack — power-bi-dashboard and dbt-budget — same inputs, different contracts.

Power BI: the model as text

The deliverable is both diffable source (.pbixproj + TMDL — Textual Metadata Definition Language: every table, column, measure, and relationship is a line you can read and diff) and a compiled .pbit you open in Power BI Desktop. A .pbit is just a zip of plain text — that’s the modern way to keep a semantic model under version control, instead of binary .pbix blobs.

Two modeling decisions that actually matter here:

  • Star schema, not flat tables. Two dimensions (dim_agency, dim_period) drive nine fact tables; filters flow from dimensions to facts, which is what makes slicers and drillthroughs work at all.
  • MAX, not SUM, for cumulative values. The USAspending obligation series is already cumulative within each fiscal year — summing it double-counts. “Obligated YTD” is MAX(...) over the period range. Every one of the 19 DAX measures has a Python twin, and python3 measures/measures.py reproduces all 19/19 pinned values without Power BI installed — so the model’s numbers are verifiable by anyone who can run Python.

No account, no service, no cloud: open the .pbit, point the DataFolder parameter at the data folder, refresh (~10s for 46K rows).

dbt + DuckDB: $0, no warehouse, 82 tests

dbt-budget turns the nine CSVs into a tested warehouse in under ten seconds, entirely local:

data/*.csv → staging (9 views) → intermediate (3 views) → marts (6 tables)

Eighteen models plus 82 data tests, all passing: 43 at the source edge (not-null/shape on the raw CSVs), 32 model-attached (primary keys, foreign keys to dim_agency, accepted values, plus a custom obligation_monotonic generic), and 7 singular domain assertions. Two targets — dev (a file) and ci (in-memory, rebuilt from CSVs every run) — both produce the same PASS=100 / ERROR=0.

What the tests caught

The singular tests exist because real data disagrees with tidy expectations in specific, documented ways. Five:

  1. Obligations are not monotonic. Cumulative obligated for a fiscal year can decrease between periods when an agency restates. USDA (012) does this in the frozen window. So obligation_monotonic takes exclude_codes: ['012'] — a scoped, documented exception, not a disabled test. The parameter exists so the exception is visible in the repo, not buried.
  2. Treasury’s debt identity fails on exactly one day. total = held-by-public + intragov holds to the penny on 2,934 of 2,935 days; on 2025-08-04 the components are $10,000,000,000.00 short of the total. That single row is excluded by name, with a comment.
  3. MTS “receipts” is receipts and outlays. The table carries outlay lines (negative by design) plus parent rows with NULL amounts. The tests assert the structure that’s actually true — parents NULL, children reconcile to parents, totals non-negative — instead of pretending the file is what its name says.
  4. Agency codes keep their leading zero. 012, 020, 036 are staged as VARCHAR, never numeric — a numeric stage silently turns 012 into 12 and breaks every join against USAspending.
  5. gov_budgetary_resources is revised, not accumulated. Each period carries the current estimate for the full year, so values step rather than accumulate. The staging docs say so, because otherwise every reader will assume cumulative and be wrong.

That’s the whole argument for test layers: the warehouse isn’t correct because it’s small, it’s correct because every disagreement with the data has a named, visible home.

Next: the thing that ties all of this together — teaching the federal budget in 48 steps.

— Joanna