SaaS Revenue Metrics Platform
One governed star schema, 163 tests, and the one-line test that saves the board pack.
The problem
A SaaS CFO gets three different ARR numbers from three teams. This is the single pipeline everyone trusts — 511,089 invoice lines, deliberately dirty, transformed into one governed star schema.
The build
Snowflake with role separation (LOADER / TRANSFORMER / REPORTER), dbt staging → intermediate → marts handling dedup, timezone repair, FX conversion and month allocation, Airflow orchestrating a 03:00 UTC ELT, Snowflake Cortex generating narrative insights, Power BI reading only the analytics layer.
Architecture
— one teal highlight: the marts layerWhat I found
A single duplicated row in a 123-row employee table inflates one account executive's revenue by exactly 2.0×, while moving the company total by only +0.58%. The dashboard doesn't look broken — it looks like FX. A 2× error gets fixed Monday because it's absurd on sight; this one gets found by the AE checking commission, weeks later, after the number is in a board pack. What stands between that bug and the board is a one-line uniqueness test on a dimension nobody thinks is interesting.
The reps
The fan-out is reproduced by a script in the repo, not just described — because the argument for boring tests is only convincing if you can show the damage.
Results
- 511,089invoice lines transformed
- 163dbt tests between data and dashboard
- +0.58%total distortion from a bug that doubles one AE
- 10Kcustomers, 24 months simulated
What's verified
Every case study carries this blockWhat was measured, and what was not. Nothing in the second column is estimated into the first.
Simulator and manifest, the fan-out reproduction, sqlfluff, pytest and Airflow DAG import — all in CI on every PR.
No Snowflake account attached: RBAC grants, COPY INTO, dbt build, the Cortex call and query profiling are unrun. The profiling case study has every runtime cell deliberately blank rather than filled with plausible numbers.