Rebuilding an ALM Risk Pipeline: what DuckDB, dbt, Dagster, and Parquet bought me

I rebuilt a project I had shipped a few months ago: an Asset-Liability Management risk pipeline that computes IRRBB under BCBS 368, the EBA liquidity ratios (LCR, NSFR), and basic ICAAP-flavoured capital with stress testing. The original sat on Postgres + SQLAlchemy + Streamlit. The new one sits on DuckDB + dbt + Dagster + Streamlit.

The infrastructure as well as the modeling is more complex, and that’s what this post is about. The headline claim is that swapping the data plumbing took the project from “hard to demo, hard to extend, hard to host” to “171 invariant checks per build, sub-second cold start, scalable to production and zero dollars a month to keep online” while even adding more models.

v1: where it hurt

The goal of the first version was to demonstrate basic a database to dashboard pipeline:

• PostgreSQL as the warehouse, accessed through SQLAlchemy declarative models in models_sqlalchemy.py.
• A hand-written schema_postgres.sql DDL file that had to be kept in sync with the ORM models.
• A generate_data_postgres.py script that opened a session and INSERT-ed rows.
• A compute.py that ran SQLAlchemy queries, dropped the results into pandas, and did the maths.
• A Streamlit app that opened a fresh database session per page.

The pain wasn’t that any one of those layers was wrong. It was that they were independently editable. Adding a single column to the cashflow table meant touching the DDL, the ORM models, the generator, the queries module, and probably at least one Streamlit page. Nothing verified the four were consistent. The schema lived in four places, so it lived nowhere.

Two follow-on costs:

• Numerical results were opaque. compute.py did the LCR ratio in Python, mid-function, after a couple of joins. If the LCR looked weird, there was nothing to inspect between “the data” and “the number”.
• It was difficult to host. Streamlit Cloud is free; running a Postgres instance to back it is not.

v2: the new stack

The full v2 architecture is one DAG with five layers:

Parquet feeds (data/raw/)
       │
       ▼
  DuckDB warehouse (single file, ~28 MB)
       │
       ├──▶ dbt staging ─▶ intermediate ─▶ marts ─┐
       │                                          │
       ▼                                          ▼
  Python risk engine                          Streamlit
  (HW1F calibration, MC, EVE/NII,             (reads marts only;
   CPR, Black-76, ALMM)                        no engine work)
       │
       ▼
  Parquet outputs (data/risk_outputs/)
       │
       ▼
  Reloaded into DuckDB ──▶ dbt staging ──▶ marts ──▶ Streamlit

Every node, dbt model, risk-engine output table and raw Parquet is a Dagster asset. The whole DAG (~46 assets) renders in the Dagster UI.

Parquet: the schema-on-read floor

The synthetic generator now writes Parquet to data/raw/. The risk engine reads Parquet, computes, and writes Parquet to data/risk_outputs/. Everything in between is dbt or Dagster moving those columns around.

Some advantages of Parquet:

• Schemas are derived from Pydantic. The basel_common.types module defines the row models in Pydantic v2. The generator writes columns in that order with those dtypes; dbt staging views read them back with the same names. There is no DDL file. The schema lives in one place.
• Columnar reads are free. The Liquidity page reads three columns out of a 13-column cashflows table. It does so without DuckDB loading the other ten. The previous Postgres version selected the three but transferred all thirteen over the wire.
• git diff works on data. The committed warehouse is binary, but the source-of-truth synthetic feeds are versioned. Anyone who clones the repo gets the same numbers I see.

Pydantic: schema as a single source of truth

# basel_common/types.py
_BASE = ConfigDict(frozen=True, extra="forbid")

class CashflowRow(BaseModel):
    model_config = _BASE
    id: int
    date: _date
    direction: Direction          # StrEnum: inflow | outflow
    hqlatype: HQLAType            # StrEnum: Level1 | Level2A | Level2B | None
    amount: Decimal
    asf_factor: Decimal = Field(default=Decimal("0"))
    rsf_factor: Decimal = Field(default=Decimal("0"))
    scenario_id: int | None = None

• One schema definition feeds ingestion, the risk engine, queries, and dbt source contracts. In the previous version (SQLAlchemy + Postgres), the same columns were declared in ~4 places that drifted apart.
• extra="forbid" turns a typo in a generated column into an immediate validation error, not a silent NULL three layers downstream.
• StrEnum makes “is this HQLA Level 2A?” a closed set the type checker enforces. This prevents stray "level2a" / "Level_2A" variants.
• Decimal (not float) for regulatory amounts: LCR/NSFR ratios don’t inherit binary-float rounding noise.
• frozen=True makes rows hashable and immutable, making them safe to pass through the MC engine without defensive copies.

DuckDB: the warehouse that fits in a Git commit

DuckDB is a single-file analytical database. The whole warehouse.duckdb is ~28 MB and lives at data/warehouse.duckdb, committed to the repo.

# basel_ingestion/load.py — Parquet -> warehouse, idempotent
con.execute(
    f"CREATE OR REPLACE TABLE {table} AS "
    f"SELECT * FROM read_parquet(?)", [str(pq)],
)

# basel_common/connection.py
return duckdb.connect(str(path), read_only=False)  # writers
# ...the Streamlit app opens the same file read_only=True

Three concrete things this changed:

• No service to host. Streamlit Community Cloud clones the repo and opens the DuckDB file read-only. That’s the entire backend. The Postgres from before meant either a paid managed instance or a fragile self-hosted one.
• read_parquet() is native and zero-copy — loading the feed is one CREATE OR REPLACE TABLE, fully idempotent.
• The “API” went away. In the previous version we might have considered putting a FastAPI layer between Streamlit and the database. With DuckDB embedded in the Streamlit process, the warehouse is the API. The dashboard reads marts directly, in SQL.
• read_only=True from the dashboard means concurrent viewers can’t corrupt or lock the file; the writers hold the only RW handle.
• Same SQL dialect as the marts, i.e., no impedance mismatch between the engine’s ad-hoc queries and the dbt layer.
• Read-heavy analytical queries are an order of magnitude faster. Even the dashboard’s heaviest possible queries, such as a 365-day rolling LCR over four scenarios, joined to capital ratios, runs in single-digit milliseconds. The same query against Postgres needed a careful index on (scenario_id, as_of_date) to come within 10× of that.

The “but what about concurrent writes” objection isn’t an objection here. The pipeline is exactly one writer (Dagster, materialising assets) and many readers (Streamlit sessions). DuckDB handles that with a write-ahead log; the WAL is the only file I gitignore.

dbt: SQL with tests, lineage, and a contract

This might be the greatest improvement from before. dbt is “just” a templated-SQL transformation tool, but the things that follow from “transformations are now declared as code with declared expectations” are what make it worth introducing.

-- dbt_project/tests/assert_lcr_inflow_cap_respected.sql
-- EBA Delegated Act 2015/61 Art. 33: capped inflows <= 75% of outflows.
-- This test FAILS (returns rows) if any mart ever violates the cap.
SELECT m.scenario_id, m.capped_inflows, m.outflows, p.lcr_inflow_cap
FROM  m
CROSS JOIN  p
WHERE m.capped_inflows > m.outflows * p.lcr_inflow_cap + 1e-6

• ref() builds the dependency graph automatically: staging -> intermediate -> marts rebuilds in the right order, and dbt knows what’s downstream of a changed source.
• Staging models (stg_cashflows) do the renaming/typing once (hqlatype AS hqla_type), so every mart speaks one vocabulary.
• Singular tests are executable regulation: the LCR inflow cap and the capital-stack ordering (CET1 <= Tier1 <= Total) are SQL assertions that fail the build if the numbers stop being lawful. 157 tests gate every dbt build.
• Tests live next to the transformations, version-controlled, runnable in CI. Invariants can’t sit still in a forgotten notebook.

The pipeline has 6 staging views (1:1 typed views over the raw Parquet sources), 3 intermediate views (enrichment that’s used by more than one mart), and ~23 mart tables (the things Streamlit actually queries).

Here are a few great things about upgrading to dbt:

Tests on business invariants, not just data. The LCR mart enforces that the EBA inflow cap is applied (capped_inflows ≤ 0.75 × outflows). The capital-ratio mart enforces CET1 ≤ Tier1 ≤ Total Capital. These are singular tests in dbt_project/tests/ which are SQL queries that must return zero rows. If they don’t, the build fails. There are 157 tests in total: uniqueness, not-null, accepted-values, relationships, and the singular invariants above.

One place per concept. The 30-day rolling LCR is in mart_lcr_daily.sql. The warm-up gate (NULL out the ratio until the window is full, to avoid spikes from incomplete windows) is in that file, in a WINDOW clause. The dashboard does not know any of this. In v1 the equivalent rolling logic lived in pandas inside the page code, and the warm-up artefact had to be solved separately on every page that used the series.

Lineage I didn’t have to draw. dbt docs generate produces a static site with the column-level lineage graph. The dashboard’s “Model lineage” expander now embeds the model parameters (Hull-White (a, σ), half-life, curve-fit residual, NMD overlay parameters) straight from a mart_model_metadata table, so the rendered LaTeX-equation panel and the numbers it cites can never disagree.

Refactors got cheap. Splitting mart_lcr into a static per-scenario point ratio and a daily rolling variant was a dbt run away. No application code changed.

The dbt docs site is auto-published to GitHub Pages by a workflow at .github/workflows/dbt-docs.yml. Every push to main that touches the dbt project rebuilds it. That’s the project’s reviewer surface: anyone can see the model graph without cloning the repo.

Dagster: the DAG you can point at

Dagster is the orchestration layer. The reason it’s interesting here, versus a cron-driven make-style runner, is that it’s asset-centric: the unit of work is not “run this script” but “materialise this asset”. You declare the dependencies between assets, and Dagster decides when each needs to run.

# basel_dagster/assets/ingestion.py
@multi_asset(
    outs={t: AssetOut(key=AssetKey(["raw", t]), group_name="ingestion")
          for t in RAW_TABLES},
    deps=[parquet_files],
)
def raw_tables(context):
    counts = load_parquet_dir(DATA_RAW, DATA_WAREHOUSE)
    for table in RAW_TABLES:
        yield MaterializeResult(
            asset_key=AssetKey(["raw", table]),
            metadata={"row_count": MetadataValue.int(counts.get(table, 0))},
        )

• The raw/<table> asset keys match what dagster-dbt resolves source('raw', ...) to, so the Python ingestion assets and the dbt models stitch into one continuous graph with no glue code. The DAG spans generation -> load -> dbt marts -> risk engine.
• Asset-centric (not task-centric): each node is a table/parquet, so the graph reads as data lineage, i.e., the story a reviewer wants.
• MaterializeResult metadata (row counts, file paths) is captured per run: built-in observability instead of print archaeology.
• A single daily_full_refresh schedule re-materializes everything in dependency order; partial reruns are free.

In this project, every dbt model is a Dagster asset (via the @dbt_assets decorator), and every risk-engine output Parquet is a Dagster asset. So is each raw Parquet load. The full DAG:

data/raw/*.parquet                ─┐
                                   ├──►  raw_tables (multi-asset)
                                   │           │
                                   │           ▼
                                   │     dbt staging / intermediate / marts
                                   │           │
                                   │           ▼
                                   │     risk_engine_run (Python)
                                   │           │
                                   │           ▼
                                   │     risk_outputs/*.parquet
                                   │           │
                                   │           ▼
                                   └──► risk_outputs_tables (DuckDB reload)
                                                │
                                                ▼
                                        dbt staging / marts
                                                │
                                                ▼
                                          Streamlit

renders in the Dagster UI as a graph you can click through.

What this bought me:

• The “rerun what’s stale” problem solved. I no longer need to remember that the risk engine has to run before the dbt marts that consume its outputs. Dagster runs risk_engine_run, then the multi-asset that reloads the Parquets into DuckDB, then the dbt models that source from them. One job, one materialise click.
• Risk-engine outputs are tested. The risk engine writes 13 Parquet files. Each gets a dbt staging view with column types, accepted values (e.g. model_family ∈ {hull_white, vasicek}), and range checks (avg_cpr ∈ [0, 1]). That’s the same treatment raw data gets. The engine doesn’t get a free pass because it’s Python.
• A typed boundary between Python and SQL. The risk engine doesn’t insert into DuckDB. It writes Parquet, Dagster reloads, dbt picks up as sources. The Parquet boundary is a serialisation checkpoint that forces the data shapes to be explicit.

Polars: fast, typed synthetic-data generation

This is an “extra step” that makes the project potentially more scalable for future improvements.

# basel_ingestion/generate.py
import polars as pl

def generate_callable_bonds(...) -> pl.DataFrame:
    return pl.DataFrame(rows, schema_overrides={"call_date": pl.Date})

# basel_risk_engine/run.py — interop on the way out
pl.from_pandas(df).write_parquet(path)

• Explicit, strict dtypes at construction (schema_overrides={"call_date": pl.Date}): the synthetic feed lands with the exact types the Pydantic models and DuckDB expect, no object-column surprises.
• write_parquet() is fast and columnar by default: the feed DuckDB reads is produced without an unnecessary pandas round-trip.
• pl.from_pandas(...) gives zero-friction interop: the numeric engine stays in pandas/NumPy where natural and hands off to Polars at the parquet boundary.
• Predictable memory and speed on the larger MC-path frames vs. pandas.

Concrete payoffs, with numbers

The point of changing infrastructure is to enable things that were hard before. Some specific things v2 made easy:

Thing	Before	After
Schema source of truth	4 files (DDL, ORM, generator, queries)	1 file (basel_common.types Pydantic)
Tests on the data layer	0	157 (dbt)
Tests on the risk engine	0	74 (Hypothesis property tests)
Lineage graph	mental model	dbt docs (auto-published)
Hosting cost	~$7/month (Postgres)	$0 (Streamlit Cloud + GitHub Pages)
Cold-start time	Postgres provisioning + data load	sub-second (read-only DuckDB file)
“Rerun what’s stale” workflow	manual ordering	dagster job execute full_refresh

The two numbers I’m proudest of are:

• 74 + 157 = 231 invariant checks per build. Hypothesis property tests on the risk engine (MC convergence to forward + convexity adjustment, CPR-adjusted schedule conserves notional exactly, Black-76 monotone in σ, supervisory outlier ratio = |ΔEVE_worst| / Tier1) plus dbt tests on the data layer (LCR inflow cap, capital stack ordering, RWA range, uniqueness on (scenario_id, as_of_date)). Catches regressions I’d otherwise eyeball.
• Curve-fit residual ~1e-16. Not a stack story, but enabled by it: switching from Vasicek to Hull-White 1F made the model arbitrage-free against today’s curve to machine precision. That fact is now surfaced on the dashboard’s lineage panel, sourced from a dbt mart, with the SDE rendered in LaTeX.

A list of possible future improvements

A few things are deliberately not in this version, despite being straightforward to add:

• No FastAPI. The warehouse is the API. Streamlit reads marts directly.
• No Postgres. DuckDB at this scale (~28 MB warehouse, ~50k rows in the biggest table) is faster for the read-heavy analytical workload and removes the hosting cost.
• No Airflow. Dagster’s asset-centric model fits the “one DAG, rebuild on demand, dev locally” workflow. Airflow’s task-centric model would have required materialisation state to be re-invented on top of it.
• No live Dagster instance. The orchestration story is the asset graph, not a hosted scheduler. A screenshot of the graph and a Loom of dagster job execute is in the README; the live UI is a local dagster dev.

These could all be added later if a real production deployment ever called for them.

Closing

The rewrite reduced the surface area, not increased it. Streamlit stayed. The risk-model maths got more substantive (Hull-White 1F, CPR, Black-76, ALMM survival horizon) because the surrounding plumbing stopped being where the time went.

The shorter version of the post is: modern analytical stacks are better than the obvious “Postgres + ORM + script” default, even for small projects, because the things they let you stop doing (writing DDL, drawing lineage graphs by hand, paying for a database, juggling which script to run first) compound faster than you’d think.

The repo is at thomasmartins/basel-risk-pipeline. The live Streamlit dashboard is at basel-risk-pipeline.streamlit.app. The auto-published dbt docs are at thomasmartins.github.io/basel-risk-pipeline.