When a fast-growing software startup rolled out its multi-tenant SaaS product, everything seemed perfect until the subscription plans started changing. New features were introduced mid-cycle, and old features were quietly phased out, yet the system needed to remember precisely which subscriber had access to which features at any given time. If this scenario sounds familiar, you are not alone.
In today’s subscription-driven world, businesses often juggle dozens of plan variations and feature sets across multiple customers. Traditional, rigid database models struggle to keep a complete historical record of these evolving details without becoming unwieldy. That’s where Kimball’s Type 2 Slowly Changing Dimension (SCD) approach steps in as a lifesaver. By systematically capturing each change in a dedicated ‘history’ table, you can maintain a clear snapshot of all active and past subscription data no matter how frequently your plans get updated.
This blog will explore using Kimball's Type 2 methodology to seamlessly track changing plan features for multi-tenant SaaS solutions. Ready to see how it works? Let’s dive in!
Understanding the SCD Problem
Maintaining accurate historical records can be a real challenge in any well-designed data system, especially when the data changes slowly over time. This is precisely what Slowly Changing Dimensions (SCD) aims to solve. A “dimension,” in data warehousing terms, is an attribute or characteristic that provides context to your business operations, such as customer details, subscription plans, or product information.
As these attributes evolve, say a subscription plan upgrades its features, and some models might overwrite the old information with the new. This approach loses historical context: you no longer know precisely when or how the plan changed. That’s where SCD comes in. By capturing changes in a structured way, you preserve every version of that dimension.
Ralph Kimball introduced multiple techniques (Type 1 through Type 6) for handling these shifts in dimensional data. Type 1 overwrites old data, while Type 3 partially tracks changes in additional columns. However, for companies needing a complete audit trail or wanting to analyse how things used to be, Type 2 is often the go-to choice. It stores each change as a new row, complete with timestamps (or versioning), ensuring nothing is lost. This becomes invaluable when making data-driven decisions, diagnosing anomalies, or simply answering, “Which features did a particular tenant have six months ago?”
Why This Matters in Multi-Tenant SaaS?
Multi-tenant SaaS platforms are inherently complex. Each tenant subscribes to a plan tailored to their needs, whether a small startup or a large enterprise. These subscriptions often have unique features, usage limits, and pricing tiers. Over time, plans evolve: new features roll out, older ones get deprecated, and pricing structures change to reflect market demands.
Your data can quickly become chaotic without a clear strategy to track these changes. Imagine explaining to a client why your billing report shows they had access to a feature six months ago when, in reality, that feature didn’t exist back then. Or consider the compliance implications if you have to prove a customer's exact features on a specific date.'
Adopting a Slowly Changing Dimension, the Type 2 design seamlessly captures every revision, preserving a complete history. This ensures:
Accurate Billing and Compliance:
You can demonstrate exactly what was available to whom at any point in time, simplifying audits.
Better Analytics:
With complete historical data, your business intelligence tools can identify trends in feature usage, leading to data-driven product decisions.
Customer Satisfaction:
A reliable record reduces confusion for your support team and the customer, building trust in your platform.
Implementing SCD Type 2 delivers traceability and structure for a fast-moving Saas, creating a single source of truth for how your plans and features evolve.
Kimball’s Type 2 Approach: A Detailed Overview
When you need to track changes over time without losing past versions of your data, Kimball’s Type 2 Slowly Changing Dimensions (SCD) approach shines. Here’s the basic idea:
Retain the Full History of Changes:
When a dimension attribute changes, like a subscription plan’s feature set, you insert a new row representing the latest state. Instead of overwriting the old record, the existing row is marked as “expired,” and the new record is marked “current.”
Use Effective Dates and Flags:
In a typical Type 2 design, you include columns such as:
Start_Date:
The date when this version of the record became valid.
End_Date:
The date when a new version superseded this version.
Is_Current:
A boolean flag (or a similar indicator) that tells you which row is active at any given time.
Assign Version Numbers:
Some teams prefer to use version numbers for clarity. Each time a dimension changes, a version counter increments. This can be especially helpful for quickly referencing which iteration of a subscription plan you are looking at.
Schema Example:
Consider a simple table called Subscription_Dim. Whenever a plan evolves (e.g., adding a new feature), you end the old row by setting ‘End_Date’ and flipping ‘Is_Current’ to false. Then, insert a fresh row with the updated plan details, marking its ‘Is_Current’ as accurate.
By following these steps, you can track precisely when each change happened and still know which subscription version is valid at a particular time. This becomes extremely useful for reporting, auditing, and data analysis, especially in a multi-tenant environment where changes can happen at different times for different subscribers.
Step-by-Step Implementation in a BCNF-Normalized Model (Narrative Format)
Implementing a Type 2 Slowly Changing Dimension (SCD) strategy in a fully BCNF-normalized schema might seem challenging, but it follows a straightforward narrative once you break it down. You aim to capture each state of your subscription plans or features as a distinct record, complete with start and end boundaries.
Step 1: Identifying the Dimension
Before you can manage changing data, you need to locate the core entity, often called a “dimension”, that will store historical versions. In a multi-tenant SaaS, this might be a ‘Plan_Feature or Subscription_Plan’ table, where each row defines a plan's features. Because we are preserving multiple records over time, it’s essential to design your primary key so each plan version can be stored separately.
Step 2: Integrating Tracking Fields
Add columns like ‘Start_Date (or Effective_From)’ and ‘End_Date (or Effective_To)’ to keep the timeline clear. These mark when a version of the plan became valid and when it stopped being the current record. A boolean or status indicator, such as Is_Current, helps differentiate active rows from expired ones at a glance. Together, these additions allow you to maintain complete snapshots of the plan at various stages in its lifecycle.
Step 3: Managing Version Updates
Any time a plan changes, be it an added feature, removal, or modification, you close out the old version and introduce a new one. Concretely, that means setting the previous record’s End_Date to the change date, switching ‘Is_Current’ to false, and creating a new record with the updated details. The new record’s ‘Start_Date’ becomes the moment the revised plan takes effect, and it’s marked as the current version.
Step 4: Preserving Referential Integrity
Referential integrity remains pivotal in BCNF models, so any foreign keys referencing the dimension table should point to the specific version ID (often called a surrogate key or dimension key). This ensures that historical fact tables or any related data always connect to the correct instance of the plan. If a tenant had a specific set of features on June 15th, your data model can confirm which row represented those features on that date.
Step 5: Reporting and Analysis
When you need to run queries, you can focus on current data or dive into historical states. Filtering for Is_current=true gives you an up-to-date view of what tenants are experiencing now. Meanwhile, historical reports can leverage both date fields to isolate the plan version applied at any time. This design makes compliance checks, trend analysis, and debugging significantly more reliable because every change is neatly captured, stored, and easily retrieved.
You effectively evolve your database to handle temporal data by weaving these elements into your BCNF schema. Each plan update or new feature gets its permanent record, so you never lose sight of how your subscriptions evolved and maintain strict normalisation rules.
Applying Type 2 to the Subscription Feature-Tracking Use Case
Let’s translate the Type 2 concept into the practical reality of tracking plan features. Suppose you have a subscription entity that stores the plan a tenant is on and the features to which they are entitled. At the moment a brand signs up, you create an entry in your dimension table (e.g., Subscription_Dim) with a ‘Start_Date, End_Date’ set to an extreme future date (like 9999-12-31, often used in data warehousing), and ‘Is_Current’ set to true.
Now, imagine partway through their subscription, your product team introduces a brand-new analytics feature or modifies an existing one. For that tenant, you’d:
Close Out the Old Record:
Update ‘End_Date’ to the change date, and set ‘Is_Current’ to false. This ensures you retain all historical details about when the previous set of features stopped being valid.
Insert a New Record:
Create a fresh row reflecting the updated features with a new ‘Start_Date’, the same overarching subscription ID, and ‘Is_Current’ set to true.
Benefits and Best Practices
By embracing Type 2 Slowly Changing Dimensions in a multi-tenant SaaS setting, you gain historical clarity and a wealth of analytical advantages. Here are some key benefits and recommended practices:
Conclusion
Plan features in a subscription-based SaaS environment often shift mid-cycle, making historical tracking vital for accurate billing and transparent audits. By leveraging Kimball’s Type 2 Slowly Changing Dimension approach, each plan update becomes a new record rather than overwriting the old one. This ensures a clear timeline of subscription states, which is particularly important in multi-tenant architectures, where changes may impact different tenants at different points.
Highly normalized schemas can accommodate these records by adding fields like Start_Date and End_Date, plus a version indicator. As a result, you maintain an authoritative record of exactly who had access to what and when, fostering better reporting, smoother compliance checks, and increased confidence across teams and customers alike. Ready to explore how Type 2 SCD can transform your multi-tenant SaaS data model? Reach out to our team at HummingWave.
