Every mid-market SaaS data team eventually gets the same Tuesday-morning Slack message: “Hey, the board deck says ARR is $22.4M but the sales dashboard says $22.7M, can you check that?”
Nine times out of ten the answer is the same: there are three different SQL queries defining ARR in the warehouse, each correct under different assumptions, and none of them is documented anywhere a human can find.
The contract normalization
Before any rollup, you have to agree on what a “contract” is. In our experience, mid-market SaaS billing systems produce four shapes and you have to handle all four explicitly:
- Annual prepaid. Cash collected up front, recognized monthly.
- Monthly subscription. Recurring charges, no end date until churn.
- Custom multi-year. Negotiated terms, often with a step-up clause in year 2.
- Usage-based add-on. Variable component on top of a base subscription.
The model below normalizes all four into a single shape with a recognized_per_month field. That’s the column the rollup operates on — not contract_value, which is the common “just sum it” trap.
The three tests that gate it
not_null(customer_id)— obvious, but a surprising number of legacy systems let an “internal” account through with NULL.positive(arr)— ARR is never negative; a negative value almost always means a refund landed in the active-contracts table without status flipping. Catch it before it poisons the rollup.relationship(customer_id, customers.id)— every ARR row must point at a known customer. Orphaned rows usually mean an upstream model dropped a join.
The lineage hook
The actual reason this 47-line model is worth $22.4M of trust is the lineage gate that sits between billing.contracts and finalized_arr. Any PR that touches an upstream model causes the CI to flag this rollup as “potentially affected,” which forces a human to acknowledge before merge. We’ll do a separate post on the gate itself.
What this gets you
One number. Defined once. Versioned in git. Tested every run. Approved by your CFO before the first board deck. When sales says ARR is $22.7M and the finance dashboard says $22.4M, you point at this model, and the conversation moves from “who’s wrong” to “why is the sales dashboard using a different model.”
Cheaper than another reconciliation meeting. Cleaner than another spreadsheet. Auditable forever.
––