Design a Star Schema for a Subscription Business

1. Star schema design

Grain: one row per subscription-month — i.e., one fact row per active subscription per calendar month it was active. This grain is chosen because "MRR by industry and plan tier" and "revenue per account by cohort" are both monthly, additive-over-accounts questions, and subscription-month is the finest grain that still lets every one of those questions be answered by a simple GROUP BY with no double counting (an invoice-grain fact, by contrast, would need extra logic to avoid summing multiple invoices per month per account if billing cadence ever changes).

CREATE TABLE dim_account (
    account_key    INT PRIMARY KEY,
    account_id     INT NOT NULL,
    name           TEXT,
    industry       TEXT,
    signup_date    DATE,
    signup_cohort  TEXT,        -- e.g. '2026-01' for cohort analysis
    effective_date DATE,
    end_date       DATE,
    is_current     BOOLEAN
);

CREATE TABLE dim_plan (
    plan_key    INT PRIMARY KEY,
    plan_id     INT NOT NULL,
    plan_name   TEXT,
    tier        TEXT,
    monthly_price NUMERIC
);

CREATE TABLE dim_month (
    month_key   INT PRIMARY KEY,   -- e.g. 202603
    month_start DATE,
    quarter     INT,
    fiscal_year INT
);

CREATE TABLE fact_subscription_month (
    subscription_month_key BIGINT PRIMARY KEY,
    subscription_id        INT NOT NULL,   -- degenerate dimension
    account_key             INT REFERENCES dim_account(account_key),
    plan_key                 INT REFERENCES dim_plan(plan_key),
    month_key                 INT REFERENCES dim_month(month_key),
    mrr_amount                 NUMERIC NOT NULL,
    is_churned_this_month        BOOLEAN NOT NULL DEFAULT false
);

is_churned_this_month is set true on the row for the last active month of a subscription whose end_date falls in that month — this makes "count churned accounts per month" a straightforward filter rather than a date-comparison query against subscriptions directly.

2. SCD type for plan

SCD Type 2 on dim_account (or, better, tracked directly via plan_key on the fact row — see note below), because Finance's "MRR by plan tier" needs to reflect the plan that was actually active in that month, not the account's current plan. If plan were Type 1 (overwritten), an account that upgraded from Starter to Enterprise in March would retroactively show as Enterprise for January and February's MRR too, misattributing historical revenue to the wrong tier — exactly the failure Type 2 exists to prevent.

Concretely, because grain is subscription-month, the cleanest implementation is for fact_subscription_month.plan_key to simply point at whichever dim_plan row was active that month (plan itself rarely changes attributes, so dim_plan doesn't need Type 2 — it's the account's association to a plan that changes, which the fact table's monthly grain already captures without needing SCD-2 machinery on the dimension). If industry on dim_account needs the same historical accuracy (e.g., an account is re-classified from "Retail" to "E-commerce" and Finance needs point-in-time-accurate industry attribution), that's where Type 2 on dim_account earns its keep:

Before:

account_key | account_id | industry | effective_date | end_date   | is_current
501         | 88         | Retail   | 2024-06-01      | NULL       | true

Re-classified 2026-02-15. After:

account_key | account_id | industry    | effective_date | end_date   | is_current
501         | 88         | Retail      | 2024-06-01      | 2026-02-14 | false
502         | 88         | E-commerce  | 2026-02-15      | NULL       | true

Facts for subscription-months before February point at account_key 501 and correctly attribute to Retail; facts from February onward point at 502 and correctly attribute to E-commerce.

3. A periodic snapshot fact table

Yes — build a monthly snapshot fact table at account grain (one row per account per month, not per subscription) with columns like ending_mrr, is_active, months_since_signup. This is actually very close to what fact_subscription_month already provides if an account has exactly one subscription, but the distinction matters once accounts can hold zero or multiple concurrent subscriptions: a periodic snapshot guarantees a row for every account every month — including months where an account had zero active subscriptions — which the subscription-grain transaction fact cannot represent (there's no subscription row to hang a "zero" on). This is exactly what "how many accounts churned each month" needs to be a clean denominator-inclusive calculation (churned accounts / total accounts that month, not just accounts with a subscription that happened to end) rather than an inference from absence of rows, which is fragile and easy to get wrong (a missing row can mean "churned" or "not yet a customer" or "data pipeline gap," and a transaction-grain fact table can't distinguish those cases on its own).