Kimball for SaaS: Subscriptions, MRR, and Churn, Modeled Right

SaaS data is weird.

A customer pays you before they use the thing. Then they use it some unpredictable amount. Then they maybe upgrade. Then they churn. Then — sometimes — they come back three months later and you have to decide whether that's "reactivation" or "new." Finance wants recognized revenue one way, customer success wants NRR another way, and your CEO wants a single number for "ARR" that nobody can quite agree on.

The dimensional modeling fundamentals from the first article in this series (the coffee shop one) still apply. But SaaS breaks them in interesting ways. A transaction fact alone can't answer "what was this account's MRR last March?" — because MRR isn't an event, it's a state that changes over time.

This article is about the patterns SaaS actually needs. We'll meet a fictional company, walk through each pattern, and by the end you'll have a dimensional model that can answer the hard SaaS questions without re-deriving them every time.

---

The 60-second recap (if you skipped the coffee shop)

• Fact tables hold measurable events (verbs). Four flavors: transaction (atomic events), periodic snapshot (regular photos), accumulating snapshot (multi-stage journeys, updated in place), factless (coverage/eligibility).
• Dimension tables hold descriptive context (nouns): who/what/where/when.
• Grain = the precise definition of what one fact row represents. State it out loud before building.
• Star schema (flat dims, one hop from fact) beats snowflake almost always.
• SCD Type 2 preserves history by inserting a new row with valid_from/valid_to/is_current when an attribute changes. Type 1 overwrites. Type 2 is the workhorse.

Got it? Good. Now let's apply it to SaaS.

---

Meet Tabby

Tabby sells IoT collars that track cats' location, activity, and naps. Yes, this is a real product category. The business model:

• Subscription plans: Free (1 collar, basic stats), Pro ($12/mo, 3 collars, full history), Enterprise (custom, many collars, API access).
• Usage-based add-on: beyond your plan's collar limit, each active collar is $4/mo.
• Trial: every account gets 14 days of Pro for free, then auto-downgrades to Free unless they add payment.
• Accounts can have workspaces (think: a multi-cat household, or a small vet clinic with several "rooms"), and workspaces have users.
• Hierarchies: some Enterprise customers are "organizations" with multiple sub-accounts (a vet chain with 12 locations).

The dimensional model needs to handle all of that, plus reconstruct MRR as-of any historical date. Let's build it.

---

The subscription grain problem

Before any SQL, the most important decision: what is one row in the subscription fact?

Three candidates:

1. One row per subscription. A subscription is a billing relationship between an account and a plan. Pros: tiny, matches the source system. Cons: can't represent plan changes over time without a second table — and where does MRR history live?

2. One row per subscription-month. Each month a subscription is active, it gets a row with that month's MRR. Pros: trivial MRR queries — SUM(mrr) per month. Cons: one row per sub per month means 12× the rows per year per customer, and you have to materialize new rows monthly.

3. One row per invoice line. Most granular billing event. Pros: ties directly to revenue recognition. Cons: a mid-month upgrade produces two invoice lines, and "what was the MRR?" becomes a rolling calculation.

There is no universally right answer. Tabby uses two of these:

• An accumulating snapshot at one row per subscription for the lifecycle (trial → paid → churn — see below).
• A periodic snapshot at one row per subscription-month for MRR history.

Why both? Because they answer different questions. The lifecycle fact answers "how long do trials take to convert?" The monthly snapshot answers "what was MRR last March?" Trying to make one table answer both leads to grain mixing — the most common SaaS modeling sin.

The rule: if two questions need different grains, build two fact tables. Don't be a hero.

---

Account dimension & hierarchy

SaaS entities usually nest. For Tabby: account → workspace → user. Plus an optional parent account for organizations that own multiple sub-accounts (the vet chain case).

Two modeling choices:

• Flatten the hierarchy onto dim_account (account_id, parent_account_id, workspace_count, user_count). One table, denormalized. Easier queries, slight staleness on counts.
• Snowflake into dim_account, dim_workspace, dim_user with FKs. More normalized, more joins, but the counts are always live.

For Tabby we keep all three as separate Type 1 dimensions — they're genuinely different entities with their own attributes — but we also flatten the parent relationship onto dim_account so org-rollup queries are one hop:

CREATE TABLE dim_account (
    account_sk       BIGSERIAL PRIMARY KEY,
    account_id       TEXT NOT NULL,                  -- natural key
    account_name     TEXT NOT NULL,
    parent_account_id TEXT,                          -- for org rollups (NULL = top-level)
    plan_id          TEXT NOT NULL,                  -- SCD2-tracked (see below)
    plan_name        TEXT NOT NULL,
    signup_date      DATE NOT NULL,
    -- SCD Type 2 columns:
    valid_from       DATE NOT NULL,
    valid_to         DATE,
    is_current       BOOLEAN NOT NULL,
    UNIQUE (account_id, valid_from)
);

Note plan_id and plan_name are on the account dimension with SCD2 columns. This is the canonical SCD2 case — when an account upgrades Pro → Enterprise, we close out the Pro row and open an Enterprise row. More on that next.

dim_workspace and dim_user stay Type 1 (they reference the account but don't carry plan info themselves):

CREATE TABLE dim_workspace (
    workspace_sk BIGSERIAL PRIMARY KEY,
    workspace_id TEXT NOT NULL UNIQUE,
    account_id   TEXT NOT NULL,         -- denormalized for one-hop joins
    workspace_name TEXT NOT NULL,
    created_date DATE NOT NULL
);

CREATE TABLE dim_user (
    user_sk      BIGSERIAL PRIMARY KEY,
    user_id      TEXT NOT NULL UNIQUE,
    workspace_id TEXT NOT NULL,
    email        TEXT NOT NULL,
    role         TEXT NOT NULL,          -- OWNER / ADMIN / MEMBER
    created_date DATE NOT NULL
);

When to conflate, when to separate. If workspaces were purely a UI grouping with no attributes of their own, you'd flatten them onto dim_account. They have their own created-date, name, and (later) usage, so they earn their own dimension. The same logic applies to "do I need a dim_plan?" — Tabby has only three plans and they rarely change, so plan attributes live denormalized on dim_account. If plans had rich attributes (feature flags, rate limits, regional availability), they'd get their own SCD2 dimension.

---

SCD Type 2 in depth: plan changes are THE case

Here's the central SaaS data problem. An account named "Whisker Labs" was on Pro from January to June, then upgraded to Enterprise in July. You need to answer:

• "What was Whisker Labs' MRR in March?" → Pro price.
• "What's their MRR now?" → Enterprise price.
• "What plan were they on when they hit support ticket #4471?" → Depends on the date.

If dim_account overwrote plan_id in place (SCD1), every historical query would silently use today's plan. That's the bug. SCD2 fixes it.

The data looks like this:

account_sk	account_id	account_name	plan_id	plan_name	valid_from	valid_to	is_current
101	ACC_WL	Whisker Labs	PRO	Pro	2025-01-10	2025-07-14	false
102	ACC_WL	Whisker Labs	ENT	Enterprise	2025-07-15	NULL	true

Two rows, same account_id, different account_sk. The fact tables carry the account_sk that was valid at the time of the event. So a March invoice references account_sk = 101 (Pro), and an August invoice references account_sk = 102 (Enterprise).

The "as-of" query

The pattern you'll use constantly: what plan was this account on as of date X?

SELECT account_id, plan_name, base_mrr
FROM dim_account
WHERE account_id = 'ACC_WL'
  AND DATE '2025-03-15' BETWEEN valid_from
                            AND COALESCE(valid_to, DATE '9999-12-31');
-- Returns the Pro row (sk=101).

The COALESCE(valid_to, '9999-12-31') handles the currently-valid row (where valid_to IS NULL).

The "current only" query

When you only want the live state:

SELECT account_id, account_name, plan_name
FROM dim_account
WHERE is_current = true;

Keep both patterns in muscle memory. You'll write them weekly.

Doing the SCD2 update

When Whisker Labs upgrades, two statements:

-- 1. Close out the old Pro row
UPDATE dim_account
SET valid_to = DATE '2025-07-14', is_current = false
WHERE account_id = 'ACC_WL' AND is_current = true;

-- 2. Insert the new Enterprise row
INSERT INTO dim_account
    (account_id, account_name, parent_account_id, plan_id, plan_name,
     signup_date, valid_from, valid_to, is_current)
VALUES
    ('ACC_WL', 'Whisker Labs', NULL, 'ENT', 'Enterprise',
     '2025-01-10', '2025-07-15', NULL, true);

In production you'd wrap this in a transaction and drive it from a staging table of "accounts whose plan changed today." With dbt, you'd rebuild dim_account incrementally each day from the source, generating new SCD2 rows automatically. The shape is the same.

---

Accumulating snapshot: the trial → paid → churn lifecycle

Remember from the coffee shop article: an accumulating snapshot is one row per entity, with milestone date columns that get updated in place as the entity progresses through a defined pipeline.

For Tabby, the pipeline is:

trial_started → first_paid → expanded → churned (→ maybe reactivated)

One row per subscription. Updated as milestones happen.

CREATE TABLE fact_subscription_lifecycle (
    lifecycle_sk          BIGSERIAL PRIMARY KEY,
    account_sk            BIGINT NOT NULL REFERENCES dim_account(account_sk),
    subscription_id       TEXT NOT NULL,
    trial_start_date_sk   INT NOT NULL REFERENCES dim_date(date_sk),
    trial_end_date_sk     INT REFERENCES dim_date(date_sk),
    first_paid_date_sk    INT REFERENCES dim_date(date_sk),
    expanded_date_sk      INT REFERENCES dim_date(date_sk),    -- plan upgrade
    churned_date_sk       INT REFERENCES dim_date(date_sk),
    reactivated_date_sk   INT REFERENCES dim_date(date_sk),
    current_status        TEXT NOT NULL,     -- TRIAL / ACTIVE / CHURNED / REACTIVATED
    trial_to_paid_days    INT,
    paid_to_churn_days    INT,
    lifetime_mrr          NUMERIC(12,2)
);

Why this works for SaaS: the funnel questions — "how long do trials take to convert?", "what % of Pro accounts churn within 90 days?", "what's the median trial-to-paid interval by cohort?" — are all SELECT ... FROM fact_subscription_lifecycle WHERE first_paid_date_sk IS NOT NULL. No joins to transaction facts, no re-deriving from invoices. The lifecycle is materialized once, queried forever.

Contrast with a transaction fact (which never updates — you only append) and a periodic snapshot (which inserts a new row every period). The accumulating snapshot is the only one you update. That's the tell: if you're UPDATE-ing a fact row, it's almost certainly an accumulating snapshot.

The update pattern

When Whisker Labs converts from trial to paid:

UPDATE fact_subscription_lifecycle
SET first_paid_date_sk = 20250124,           -- they paid on Jan 24
    current_status     = 'ACTIVE',
    trial_to_paid_days = 14,
    lifetime_mrr       = 12.00               -- Pro monthly
WHERE subscription_id = 'SUB_WL_001';

When they later expand to Enterprise:

UPDATE fact_subscription_lifecycle
SET expanded_date_sk = 20250715,
    lifetime_mrr     = 499.00                -- Enterprise monthly
WHERE subscription_id = 'SUB_WL_001';

Same row, updated twice. That's the accumulating snapshot.

---

Periodic snapshot: monthly MRR

Now the second grain. We also need "what was every account's MRR at the end of each month?" — for MRR movement (the famous MRR waterfall: starting + new + expansion − contraction − churn = ending), for cohort retention curves, for the finance team's month-end close.

We could recompute this from invoices every time. We don't, for three reasons:

1. Performance — a 5-year MRR trend across 10,000 accounts shouldn't scan every invoice.
2. Snapshot truth — if a plan change gets backdated or an invoice is edited tomorrow, last month's reported MRR shouldn't silently change. The snapshot freezes "what we knew then."
3. Simplicity — the MRR waterfall query becomes a self-join on two adjacent months, not a temporal reconstruction.

CREATE TABLE fact_subscription_month (
    subscription_month_sk BIGSERIAL PRIMARY KEY,
    account_sk            BIGINT NOT NULL REFERENCES dim_account(account_sk),
    subscription_id       TEXT NOT NULL,
    month_sk              INT NOT NULL REFERENCES dim_date(date_sk),  -- first of month
    plan_id               TEXT NOT NULL,
    mrr                   NUMERIC(10,2) NOT NULL,
    quantity              INT NOT NULL,                 -- active collars
    is_active             BOOLEAN NOT NULL,
    UNIQUE (subscription_id, month_sk)
);

One row per subscription per month. The month_sk points to the first day of the month in dim_date (a common convention).

The MRR waterfall

The naive way to find "last month" is month_sk - 100, since month_sk is YYYYMMDD. Don't do this — it's not just imprecise, it's flat-out broken every January. 20260101 - 100 = 20260001, which isn't December 2025 (20251201), it isn't a valid date at all, and it matches nothing in dim_date. Every account's prev_mo join would silently come back NULL for January specifically, once a year, right at the month finance cares about most for year-end close.

The fix is to go through dim_date itself rather than doing arithmetic on the encoded key:

SELECT
    this_mo.month_sk,
    SUM(this_mo.mrr) AS ending_mrr,
    SUM(CASE WHEN prev_mo.account_sk IS NULL THEN this_mo.mrr END) AS new_mrr,
    SUM(CASE WHEN this_mo.mrr > prev_mo.mrr
             THEN this_mo.mrr - prev_mo.mrr END) AS expansion_mrr,
    SUM(CASE WHEN this_mo.mrr < prev_mo.mrr
             THEN prev_mo.mrr - this_mo.mrr END) AS contraction_mrr,
    SUM(CASE WHEN this_mo.is_active = false THEN prev_mo.mrr END) AS churned_mrr
FROM fact_subscription_month this_mo
JOIN dim_date d_this        ON d_this.date_sk = this_mo.month_sk
JOIN dim_date d_prev        ON d_prev.full_date = (d_this.full_date - INTERVAL '1 month')::date
LEFT JOIN fact_subscription_month prev_mo
       ON prev_mo.subscription_id = this_mo.subscription_id
      AND prev_mo.month_sk = d_prev.date_sk
GROUP BY this_mo.month_sk
ORDER BY this_mo.month_sk;

This joins through dim_date twice — once to get the current month's real calendar date, once to look up whatever date_sk actually represents "one calendar month earlier" — so the previous-month lookup is correct at every year boundary, not just the eleven months where subtracting 100 happens to work.

Try writing that against invoices. You can — but it's 5x the SQL and 50x the compute.

---

Usage / event fact: high-volume, separate from billing

Tabby's collars emit a "ping" every 15 minutes: location, activity score, nap flag. That's millions of events per day. Don't put this in the billing fact — it'll drown your invoice queries.

Separate transaction fact for usage:

CREATE TABLE fact_usage_event (
    usage_event_sk  BIGSERIAL PRIMARY KEY,
    event_ts        TIMESTAMP NOT NULL,
    event_date_sk   INT NOT NULL REFERENCES dim_date(date_sk),
    account_sk      BIGINT NOT NULL REFERENCES dim_account(account_sk),
    workspace_sk    BIGINT NOT NULL REFERENCES dim_workspace(workspace_sk),
    collar_id       TEXT NOT NULL,                -- the device
    event_type      TEXT NOT NULL,                -- LOCATION / ACTIVITY / NAP
    activity_score  INT,
    nap_minutes     INT
);

This is your classic high-volume event fact. Partition it by date in production. Aggregate it nightly into a fact_daily_usage (periodic snapshot) for dashboards. Keep the raw event table for deep-dives.

Why separate from billing. Mixing event-grain with invoice-grain in one fact is a Category 5 anti-pattern. Either you store one row per ping and bloat the billing columns with NULLs on 99.9% of rows, or you aggregate pings and lose the raw event. Two tables, no compromise.

---

Factless fact table: entitlements

Final pattern. SaaS products gate features by plan: Pro gets "nap history," Enterprise gets "API access." The question "which accounts had API access on June 1st?" is an entitlement lookup.

Model it as a factless fact table — row presence means "this account had this feature as of this date":

CREATE TABLE fact_entitlement (
    entitlement_sk BIGSERIAL PRIMARY KEY,
    account_sk     BIGINT NOT NULL REFERENCES dim_account(account_sk),
    feature_id     TEXT NOT NULL,                  -- 'API_ACCESS', 'NAP_HISTORY', etc.
    effective_date_sk INT NOT NULL REFERENCES dim_date(date_sk),
    UNIQUE (account_sk, feature_id, effective_date_sk)
    -- no measures: the row's existence IS the fact
);

Now "how many accounts had API access in Q2?" is a COUNT(DISTINCT account_sk) with a date filter. No need to reverse-engineer from the plan table — the entitlements are materialized, auditable, and snapshot-stable.

(You could also model entitlements as attributes on dim_plan and infer them from dim_account.plan_id. That works for current entitlements. It fails for historical entitlements when plans or features change. SCD2 on entitlements directly is the robust answer.)

---

A real query: net revenue retention

Let's put the pieces together. Net Revenue Retention (NRR) — the SaaS north-star metric — compares a cohort's MRR now vs 12 months ago, including expansion and net of churn.

WITH cohort AS (
    -- Accounts that were active and paying 12 months ago
    SELECT DISTINCT account_sk
    FROM fact_subscription_month
    WHERE month_sk = 20250101 AND mrr > 0
),
mrr_then AS (
    SELECT account_sk, SUM(mrr) AS mrr_12mo_ago
    FROM fact_subscription_month
    WHERE month_sk = 20250101
    GROUP BY account_sk
),
mrr_now AS (
    SELECT account_sk, SUM(mrr) AS mrr_current
    FROM fact_subscription_month
    WHERE month_sk = 20260101 AND is_active = true
    GROUP BY account_sk
)
SELECT
    SUM(t.mrr_12mo_ago) AS starting_mrr,
    SUM(COALESCE(n.mrr_current, 0)) AS ending_mrr,
    SUM(COALESCE(n.mrr_current, 0)) / SUM(t.mrr_12mo_ago) AS nrr
FROM cohort c
JOIN mrr_then t ON t.account_sk = c.account_sk
LEFT JOIN mrr_now n ON n.account_sk = c.account_sk;

NRR > 1.0 means your existing customers are growing faster than they're churning. Anything above 1.1 (110%) is healthy; above 1.3 is elite.

That query would be a horror show against invoices. Against the periodic snapshot, it's a two-CTE join. That's the model paying for itself.

---

Common SaaS-specific mistakes

1. Double-counting MRR after a mid-month upgrade. If Pro is $12 and Enterprise is $499, and an account upgrades on the 15th, do you count $12 + $499 = $511 for that month? No. You prorate or snapshot once at month-end. Pick a convention (Tabby: month-end snapshot) and document it.

2. Forgetting SCD2 on accounts. If dim_account.plan_id is Type 1, every historical MRR query is wrong. The plan-changing case is the reason SCD2 exists.

3. Mixing event-grain with invoice-grain in one fact. Don't put collar pings and invoice lines in the same table. Two facts, two grains.

4. Storing "current MRR" on dim_account. It'll be stale by the end of today. MRR lives in fact_subscription_month, not on the dimension.

5. Treating reactivation as a new account. A churned customer who returns is the same account. Track it via reactivated_date_sk on the lifecycle fact, not by creating a new account_id. Otherwise your "new business" metric is inflated.

6. Not handling org hierarchies. If Whisker Labs is a subsidiary of "PetCo Org," and you query accounts individually, you'll double-count the org's MRR. Either roll up via parent_account_id explicitly or build a dedicated org-rollup snapshot.

---

Exercises

A few to test yourself. Hints are hidden; full solutions in solutions.sql in the companion repo.

1. Tabby wants to add a "discount percentage" attribute that some accounts negotiate. Should this be SCD Type 1, 2, or 3 on dim_account? Why?

Hint
Does anyone need to know the historical discounts, or just the current one? If finance needs to reconstruct past invoices at the negotiated rate, you need history → Type 2. If it's "what discount do they get today," Type 1 is fine.

2. Write the "as-of" query: what plan was account ACC_WL on as of 2025-05-01?

Hint
SELECT ... FROM dim_account WHERE account_id='ACC_WL' AND DATE '2025-05-01' BETWEEN valid_from AND COALESCE(valid_to, DATE '9999-12-31').

3. An account upgrades Pro → Enterprise mid-month. Explain why the MRR snapshot and the invoice fact might disagree on that month's MRR, and which one finance usually prefers.

Hint
The snapshot shows month-end state ($499). The invoice shows what was actually billed (often a proration: a credit for unused Pro days + a charge for partial Enterprise). Finance usually prefers invoice truth for revenue recognition; the snapshot is for operations/CRM.

4. Write a query using fact_subscription_lifecycle to compute the median number of days from trial start to first paid, for accounts that converted in 2025.

Hint
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY trial_to_paid_days) with a filter on first_paid_date_sk being non-null and in 2025.

5. Why should fact_usage_event (collar pings) be a separate fact table from fact_invoice_line? Name two reasons.

Hint
Different grain (one row per ping vs one row per invoice line) and different volume (millions/day vs dozens/day). Mixing them bloats the smaller table and pollutes the larger with NULL billing columns.

---

What's next

Everything in this article has been well-behaved: a subscription's lifecycle moves through its milestones in a predictable order, and MRR snapshots update on a fixed monthly cadence. Part 3 is where that good behavior stops — orders that fork into multiple shipments, carrier webhooks that arrive out of order and can make a status silently regress if the update logic isn't written for it, and a measure (orders currently in transit) that genuinely cannot be summed across days the way MRR can be summed across accounts.

Same fundamentals, messier processes. Onward to Part 3.

---

Resources

• Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — chapter 14 (financial services) covers subscription/recurring-revenue patterns well.
• dbt Labs — Modeling subscription revenue — MRR, churn, upgrades/downgrades, directly on-topic.
• Subscriptions vs. usage modeling on the dbt blog — recurring-revenue patterns.

---

This is Part 2 of a five-part series — Part 1 has the fundamentals this article builds on. The companion repo has the full schema, seed data, and exercises for this part. Onward to Part 3.