SUBMISSION: Case 3 — SaaS Subscription Lifecycle & SCDs

# 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) ```sql 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) ```sql 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 ```sql 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 ```sql 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