Designing a Star Schema for a SaaS Subscription Model

The Big Picture

Software-as-a-Service (SaaS) businesses live and die by metrics like Monthly Recurring Revenue (MRR), churn, expansion, cohort retention, and lifetime value. To analyze these KPIs at scale, you need a data model that is both business-friendly and performance-optimized. A properly designed star schema delivers exactly that by combining a central Fact table—where numeric measures live—with a set of conformed Dimension tables that provide rich context.

Core Components of the SaaS Star Schema

1. Fact Subscription

The fact_subscription table records every billable subscription state on a specific date (often daily snapshots) or every subscription lifecycle event (start, upgrade, cancel, renew). Common measures include:

• mrr – Monthly recurring revenue in the customer’s currency.
• arr – Annual recurring revenue (12 × MRR).
• seat_count – Number of licensed seats.
• is_active – Boolean flag for active subscriptions.
• delta_mrr – Change in MRR relative to previous snapshot (for churn/expansion analysis).

2. Dimension Customer

Holds slowly changing attributes about the client organization/person such as industry, segment, geography, acquisition channel, and account owner.

3. Dimension Plan

Describes pricing plans (e.g., Basic, Pro, Enterprise), billing frequency, included features, and list price. Useful for mix and upsell analyses.

4. Dimension Time

The most reused dimension, providing day/week/month/quarter hierarchies, fiscal calendars, and flags like is_month_end.

5. Optional Dimensions

• Dimension Product – If your SaaS offers multiple SKUs/modules.
• Dimension Sales Rep – For quota and pipeline alignment.
• Dimension Promotion – To track discounts, coupon codes, or campaigns.

Why a Star Schema Beats an OLTP Schema for Analytics

Operational subscription systems—think Stripe Billing, Chargebee, or your own microservice—are designed for transactional consistency, not analytics. They often have highly normalized relational schemas with dozens of join tables, making even a simple churn query slow and unreadable. A star schema solves this by:

1. Reducing Join Depth: All dimensions connect directly to the fact table via surrogate keys.
2. Enforcing Business Logic: Metrics (e.g., churned MRR) are pre-calculated or derivable with simple SUMs.
3. Enabling BI Tools: Columnar warehouses (BigQuery, Snowflake, Redshift) and BI layers (Looker, Metabase) optimize star joins automatically.

Modeling Granularity: Snapshot vs. Event

Snapshot Grain: One row per subscription per day (or month). Simplifies period-end reporting and retention curves but can bloat quickly.

Event Grain: One row per lifecycle event (create, upgrade, cancel). Storage-friendly and explicit about change reasons but requires window functions to derive period states. Many teams model both: an fact_subscription_event at event grain and a derived fact_subscription_daily snapshot table.

Practical Example: Calculating Net New MRR

Suppose you want to calculate Net New MRR for January 2024: expansion + new − churn − contraction. With the star schema, the SQL is straightforward:

SELECT
d_month.month_start_date AS month,
SUM(CASE WHEN f.delta_mrr > 0 AND f.is_new_customer THEN f.delta_mrr ELSE 0 END) AS new_mrr,
SUM(CASE WHEN f.delta_mrr > 0 AND NOT f.is_new_customer THEN f.delta_mrr ELSE 0 END) AS expansion_mrr,
SUM(CASE WHEN f.delta_mrr < 0 AND f.is_cancellation THEN -f.delta_mrr ELSE 0 END) AS churn_mrr,
SUM(CASE WHEN f.delta_mrr < 0 AND NOT f.is_cancellation THEN -f.delta_mrr ELSE 0 END) AS contraction_mrr,
SUM(f.delta_mrr) AS net_new_mrr
FROM fact_subscription_daily f
JOIN dim_time d_day ON d_day.date_key = f.date_key
JOIN dim_time d_month ON d_month.month_key = d_day.month_key
WHERE d_month.month_start_date = '2024-01-01'
GROUP BY d_month.month_start_date;


Best Practices

Conformed Dimensions

Share customer, time, and plan dimensions across other fact tables (e.g., usage, payments) for drill-across capabilities.

Surrogate Keys Over Natural Keys

Use integer surrogate keys generated in the warehouse to decouple analytics from source-system IDs, allowing for late arriving dimension rows.

SCD Type 2 for Slowly Changing Data

Capture changes in customer industry or plan attributes over time so historical reports remain accurate. Append new rows in the dimension with effective_from and effective_to dates.

Audit Columns

Add batch_id, load_timestamp, and source_system to support data lineage and recovery.

Common Pitfalls & How to Fix Them

Over-Normalizing Dimensions

Mistake: Splitting customer contact, address, and segment into separate tables.
Fix: Denormalize into one wide dim_customer. The star schema favors readability over disk savings.

Mixing Currencies in the Fact

Mistake: Storing MRR in different currencies without a conversion rate.
Fix: Add mrr_usd or maintain a currency dimension with daily FX rates.

Double-Counting Upgrades

Mistake: When you snapshot daily, an upgrade can appear as both contraction and expansion across different days.
Fix: Use delta_mrr sign to categorize events and aggregate at period level.

Galaxy & the Star Schema

While Galaxy is not a modeling tool, its AI-powered SQL editor accelerates query writing against your star schema. Autocomplete surfaces the right dimension columns, while the copilot can suggest optimized aggregation strategies—great for analysts exploring churn drivers without wading through dozens of tables.

Putting It All Together

A SaaS star schema turns subscription data into a single source of truth. With clearly defined facts and dimensions, finance and product teams can self-serve answers, dashboards run faster, and machine-learning features (e.g., churn prediction) get a clean training set. Invest the time to model correctly now, and you’ll pay down months of future “spreadsheet tax.”