SUBMISSION: Case 3 — SaaS Subscription Lifecycle & SCDs

🎯 Hands-on Portfolio Deliverable: In this project, you will architect the revenue analytics mart for a B2B SaaS company. You will model subscription states, track plan upgrades/downgrades with SCD Type 2, build a periodic monthly snapshot fact, and author the executive MRR waterfall and Net Revenue Retention (NRR) queries.

🏢 Business Scenario & Executive Prompt

You are the Lead Data Modeler at CloudSync, a B2B SaaS platform with $25M in Annual Recurring Revenue (ARR). The executive leadership team has identified severe reporting discrepancies: Finance's monthly MRR reports do not reconcile with Sales' CRM pipeline, and Product has no way to track whether customers who upgrade from the Professional tier to the Enterprise tier retain better than direct enterprise signups.

You must design a clean, resilient dimensional warehouse model that handles the entire subscription lifecycle:

1. State Transitions: Upgrades, downgrades, add-on seats, billing interval switches (monthly vs annual), and cancellations.
2. Periodic Snapshots: An unambiguous monthly snapshot fact table that provides instantaneous MRR reporting on the 1st of every month.
3. Executive Metrics: Support standard SaaS investor metrics: Monthly Recurring Revenue (MRR), Net Revenue Retention (NRR), and Logo Churn.

---

🎯 Required Deliverables

You must upload a single .sql or .zip file containing:

1. saas_subscription_scd.sql: Production DDL for fct_subscription_months, dim_subscription_state, and dim_plan.
2. Architecture Justification: Explain why you chose periodic snapshot grain over pure event grain for financial reporting.
3. saas_mrr_analytics.sql: Two analytical SQL queries computing (a) The 12-month MRR Waterfall, and (b) Net Revenue Retention (NRR) by customer signup cohort.

---

"You have a SaaS product where customers can sign up, change plans, upgrade, downgrade, pause, churn, and reactivate. Source data is an event log of every state change. Design the warehouse model so analysts can answer: MRR by plan over time, NRR by cohort, churn by segment, expansion revenue."

This is the SCD interview question in its hardest form. It tests whether you can pick the right grain and SCD strategy for a state-changing entity.

---

Step 1 — Understand the source

Source event log (one row per state change):

subscription_events:
    event_id  customer_id  event_type             plan       price   event_time
    100       cust_7       subscribed             basic      29       2025-01-15
    150       cust_7       upgraded               pro        99       2025-04-10
    200       cust_7       paused                 pro        99       2025-06-01
    250       cust_7       resumed                pro        99       2025-07-15
    300       cust_7       downgraded             basic      29       2025-10-01
    350       cust_7       churned                basic      0        2026-02-15

Six events for one customer, across 13 months.

Step 2 — Decide what to materialize

Three candidate models:

Option A: One row per state change (event grain)

fct_subscription_events(customer_id, event_type, plan, price, event_time)

Faithful to source but doesn't easily answer "MRR for March 2025". You'd have to compute state-at-month-end from the event stream every query.

Option B: One row per customer per month (snapshot grain)

fct_subscription_months(customer_id, subscription_month, plan, monthly_revenue, is_active)

For each customer, materialize one row per month they were subscribed (or every month for the analysis window). MRR for March 2025 = SUM(monthly_revenue) WHERE subscription_month = '2025-03-01'.

Option C: Type 2 dim_subscription_state

dim_subscription_state(state_key, customer_id, plan, price, effective_from, effective_to, is_current)

For each customer, a Type 2 dimension tracking their subscription state. Facts (fct_payments, fct_usage) join to the version active at the time of the fact.

Which to pick

Most warehouses use Option B + Option C together.

• dim_subscription_state (Type 2) — for joining to other facts and for history queries.
• fct_subscription_months — pre-materialized monthly snapshots for fast MRR / NRR / cohort retention queries.

The snapshot fact is derived from the Type 2 dimension. They stay consistent because they're built from the same event log.

Step 3 — Build dim_subscription_state from events

WITH events AS (
    SELECT *,
        LEAD(event_time) OVER (PARTITION BY customer_id ORDER BY event_time) AS next_event_time
    FROM subscription_events
)
SELECT
    {{ generate_surrogate_key(['customer_id', 'event_time']) }} AS state_key,
    customer_id,
    plan,
    price,
    event_type,
    event_time AS effective_from,
    COALESCE(next_event_time, '9999-12-31'::timestamptz) AS effective_to,
    next_event_time IS NULL AS is_current
FROM events;

Each event becomes a row in the dimension. effective_from is the event time; effective_to is the next event time (or far-future for the current state).

Step 4 — Build fct_subscription_months from the dimension

WITH months AS (
    SELECT generate_series(
        '2024-01-01'::date,
        DATE_TRUNC('month', CURRENT_DATE),
        '1 month'::interval
    )::date AS month_start
),
customer_state_at_month_end AS (
    SELECT
        m.month_start,
        d.customer_id,
        d.plan,
        d.price AS monthly_revenue,
        CASE
            WHEN d.event_type IN ('churned', 'paused') THEN FALSE
            ELSE TRUE
        END AS is_active
    FROM months m
    JOIN dim_subscription_state d
        ON m.month_start >= d.effective_from
       AND m.month_start <  d.effective_to
)
SELECT
    month_start AS subscription_month,
    customer_id,
    plan,
    monthly_revenue,
    is_active
FROM customer_state_at_month_end
WHERE is_active = TRUE;

For each (month, customer), look up the state active at month start; include only if active.

Step 5 — The big analytics queries

MRR by plan over time

SELECT subscription_month, plan, SUM(monthly_revenue) AS mrr
FROM fct_subscription_months
GROUP BY 1, 2
ORDER BY 1, 2;

MoM growth in MRR

WITH monthly AS (
    SELECT subscription_month, SUM(monthly_revenue) AS mrr FROM fct_subscription_months GROUP BY 1
)
SELECT
    subscription_month,
    mrr,
    LAG(mrr) OVER (ORDER BY subscription_month) AS prev_mrr,
    100.0 * (mrr - LAG(mrr) OVER (ORDER BY subscription_month))
         / NULLIF(LAG(mrr) OVER (ORDER BY subscription_month), 0) AS mom_pct
FROM monthly;

NRR by cohort

(Cohort = the month each customer first subscribed.) Standard cohort analysis pattern — covered in SQL Interview & Case Lab.

Churn by segment

Join fct_subscription_months to dim_customer (Type 2) by the version active that month. Compute: did the customer disappear from fct_subscription_months in the following month? = churn.

Step 6 — Trade-offs to mention

You: "I went with two layered models. The Type 2 dimension is the truth — every state change is captured as a versioned row. The monthly snapshot fact is a derived materialization for fast MRR / cohort queries. Both come from the same event log so they stay consistent.

The grain decision: monthly is the standard for SaaS, but if revenue recognition is daily (e.g., usage-based pricing), I'd switch to daily snapshots.

Pausing creates a subtlety — the customer is technically subscribed but not paying. I excluded paused customers from fct_subscription_months because MRR shouldn't count them. Different shops may treat this differently — confirm with the business.

For real-time MRR, the monthly snapshot is too coarse. I'd add a fct_subscription_daily materialization or compute current MRR directly from dim_subscription_state filtered to is_current = TRUE."

Common follow-ups

Follow-up 1: "What's expansion revenue vs new revenue vs churn revenue?"

Compute via month-over-month change per customer:

WITH monthly AS (
    SELECT customer_id, subscription_month, monthly_revenue
    FROM fct_subscription_months
),
changes AS (
    SELECT
        customer_id,
        subscription_month,
        monthly_revenue AS this_month,
        COALESCE(LAG(monthly_revenue) OVER (PARTITION BY customer_id ORDER BY subscription_month), 0) AS last_month
    FROM monthly
)
SELECT
    subscription_month,
    SUM(CASE WHEN last_month = 0 THEN this_month ELSE 0 END) AS new_revenue,
    SUM(CASE WHEN last_month > 0 AND this_month > last_month THEN this_month - last_month ELSE 0 END) AS expansion_revenue,
    SUM(CASE WHEN last_month > 0 AND this_month < last_month THEN last_month - this_month ELSE 0 END) AS contraction_revenue
FROM changes
GROUP BY 1;

Churn requires a separate query because churned customers aren't in fct_subscription_months after their churn month — you'd compare month N customers to month N-1 customers and SUM the missing customers' last revenue.

Follow-up 2: "How would you model annual subscriptions (not monthly)?"

monthly_revenue should still be MRR (annual_price / 12). For revenue recognition, you'd carry the full annual amount as a separate measure and amortize across 12 months.

Follow-up 3: "Customer can have multiple subscriptions (one for product A, one for product B). How?"

Add subscription_id (one per active subscription) and grain becomes "one row per subscription per month". Customer can have multiple rows per month, one per subscription.

---

📥 Download Starter Assets & Mock Database

Choose your preferred environment to get started immediately:

• 💾 Pre-Seeded SQLite Database (Recommended — Zero Setup):
  ⬇ Download case3_saas_subscription.sqlite
  Pre-loaded with raw source tables, mock transactional records, and schema skeletons. Double-click to open in DB Browser for SQLite, DBeaver, or VS Code.

• 📄 ANSI / PostgreSQL SQL Starter Script:
  ⬇ Download case3_saas_subscription_starter.sql
  Contains clean DDL statements, table definitions, and mock INSERT statements ready to paste into PostgreSQL, DuckDB, Snowflake, or BigQuery.

🛠️ How to Complete & Run This Case Study

You can complete this hands-on project using whichever tool matches your local setup:

• Option 1: SQLite / DB Browser / DBeaver (Easiest — Zero Installation)
  Download case3_saas_subscription.sqlite above. Open it in DB Browser for SQLite, DBeaver, or VS Code (with the free SQLite Viewer extension). All source ingestion tables and sample records are already populated. Write your target dimensional models and verification queries directly against the database with 0 server configuration.
• Option 2: Local PostgreSQL or DuckDB
  Download case3_saas_subscription_starter.sql. Execute the script in your local PostgreSQL database (via psql, DBeaver, or pgAdmin) or in DuckDB.
• Option 3: Cloud Data Warehouse (Snowflake / BigQuery)
  Copy and paste the DDL and mock seed data into a Snowflake worksheet or Google BigQuery dataset.

---

💻 Starter Scaffold

Use this template to begin your implementation:

-- ====================================================================
-- DELIVERABLE 1: saas_subscription_scd.sql
-- ====================================================================

-- 1. MONTHLY FACT TABLE: fct_subscription_months (Periodic Snapshot Grain)
CREATE TABLE IF NOT EXISTS fct_subscription_months (
    subscription_month_key  BIGINT PRIMARY KEY,    -- Surrogate key: hash(account_id, month_date)
    account_id              BIGINT NOT NULL,       -- Natural account ID
    subscription_id         BIGINT NOT NULL,       -- Natural subscription ID
    month_date              DATE NOT NULL,         -- First day of snapshot month (e.g. 2025-01-01)
    
    -- Dimension FKs
    subscription_state_key  INTEGER NOT NULL,      -- FK -> dim_subscription_state (Type 2)
    plan_key                INTEGER NOT NULL,      -- FK -> dim_plan
    account_key             INTEGER NOT NULL,      -- FK -> dim_account
    
    -- Financial Measures (MRR Breakdown)
    mrr_amount              NUMERIC(12, 2) NOT NULL, -- Active MRR at close of month
    new_mrr                 NUMERIC(12, 2) DEFAULT 0.00,
    expansion_mrr           NUMERIC(12, 2) DEFAULT 0.00,
    contraction_mrr         NUMERIC(12, 2) DEFAULT 0.00,
    churn_mrr               NUMERIC(12, 2) DEFAULT 0.00,
    net_mrr_change          NUMERIC(12, 2) NOT NULL,
    
    is_active               BOOLEAN NOT NULL,
    inserted_at             TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 2. DIMENSION: dim_subscription_state (Type 2 SCD for Plan/Status Changes)
CREATE TABLE IF NOT EXISTS dim_subscription_state (
    subscription_state_key  INTEGER PRIMARY KEY,
    subscription_id         BIGINT NOT NULL,
    
    plan_name               VARCHAR(100) NOT NULL, -- Starter, Professional, Enterprise
    billing_frequency       VARCHAR(50) NOT NULL,  -- Monthly, Annual
    seat_count              INTEGER NOT NULL,
    subscription_status     VARCHAR(50) NOT NULL,  -- Active, Paused, Canceled, Delinquent
    
    valid_from              TIMESTAMPTZ NOT NULL,
    valid_to                TIMESTAMPTZ,
    is_current              BOOLEAN NOT NULL DEFAULT TRUE
);

-- ====================================================================
-- DELIVERABLE 2: saas_mrr_analytics.sql
-- ====================================================================
-- Query 1: Monthly MRR Waterfall (Beginning MRR + New + Expansion - Contraction - Churn = Ending MRR)
-- TODO: Write query aggregating MRR components by month_date across the last 12 months.

-- Query 2: Net Revenue Retention (NRR) by Annual Cohort
-- TODO: Write cohort retention query tracking Year 1 vs Year 2 MRR expansion.

---

📊 Evaluation Rubric (100 Points Total)

Evaluation Criterion	Weight	Target Standard (Pass ≥ 80%)
1. Snapshot vs Event Grain Decision	25 pts	Correctly identifies that MRR/ARR analytics require a periodic monthly snapshot fact (account_id, month_date), while state transitions are tracked via event logs or Type 2 dimensions.
2. MRR Waterfall Integrity	25 pts	Explicitly distinguishes between New, Expansion, Contraction, and Churn MRR without double counting; formulas balance: Ending MRR = Beginning MRR + Net Change.
3. Type 2 State Dimension Modeling	25 pts	dim_subscription_state properly tracks plan upgrades/downgrades with non-overlapping valid_from and valid_to timestamps.
4. Analytical Verification & Cohorts	25 pts	Accompanying queries calculate Net Revenue Retention (NRR) and logo churn rates accurately across quarterly cohorts.

---

📤 Submission Instructions & Guidelines

1. Deliverable Formats: Upload your completed .sql file or a .zip archive containing your SQL scripts and documentation (.md, .pdf, or ERD diagrams).
2. Dialect Compatibility: Ensure all DDL and queries execute cleanly on standard ANSI SQL, PostgreSQL, Snowflake, BigQuery, or Databricks.
3. Self-Review: Cross-check your solution against the 100-Point Evaluation Rubric above before submitting (passing threshold is 80/100 points).
4. Upload: Use the drag-and-drop file upload zone below. You can optionally detail architectural trade-offs in the submission notes box.
5. Confirmation: Click Submit Assignment. Your submission will be recorded, your lesson and module progress will be marked complete, and you can download your submitted work at any time.