Slowly changing dimensions (SCDs) might not be the flashiest part of data modeling, but if you’ve ever tried to prepare a report about historical trends and come up empty, you know how critical they are. They define what your business can and can’t track over time.
Picture this: your data team wants to look back at how a product changed over the past year. You’re trying to trace price changes, new features, maybe even a full-on rebrand. But when you run the query, all you see is the current version. No history. No context. Just a blank wall.
That’s what happens when slowly changing dimensions aren’t set up right.
In this guide, we’ll walk through the different types of SCDs, when to use each one, and how to avoid the common traps so your teams aren’t left in the dark.
Table of contents:
Slowly changing dimensions are all about how you manage changes to dimensional data over time in your data warehouse or analytics system.
In your data warehouse, you’ll often have records—like a product, customer, or location—that stay tied to the same natural key (like a product ID), but the details are constantly shifting: names change, prices adjust, features are added or removed.
The way you choose to model these changes determines what your business can actually measure. Get it right, and you discover powerful data insights. Get it wrong, and your metrics become unreliable.
Let’s say you’re a product leader rolling out feature updates every few months. If your database isn’t capturing each version of that bundle, you’ll have no way of knowing which SKU combo actually drove results or whether a mid-year tweak quietly killed your conversions.
That’s why SCDs matter. They provide the historical context you need to connect changes over time with performance, helping you see what’s working and what’s not.
Historical tracking: Handles attribute changes over time. Some SCD types preserve history (e.g., old values, change dates), while others overwrite data.
Natural keys: Each record is tied to a natural key. This is something that uniquely identifies a business entity and doesn’t change over time.
Versioning: To keep track of when changes occur, SCDs often include metadata to track start and end dates, version numbers, or active/inactive flags.
Trend analysis: By preserving historical changes, SCDs allow you to analyze trends over time.
When dimensional data changes over time, you have a few different ways to handle it—each with its own tradeoffs. The “type” you choose affects how much history you keep, what your queries can tell you, and how complex your data model becomes.
Let’s break down the most common types, what they’re good for, and when to use them (or not).
Type 0: Fixed attributes
These are values that never change. Think of them as the constants in your data—attributes that are historically accurate and shouldn’t be updated. Examples include a customer’s date of birth, a product’s launch date, or a country code.
A common use case is a Date_Dim table, which helps simplify time-based reporting. Since the dates themselves never change, this table acts as a reliable reference for joins and filters.
When to use it: For data that’s static by nature. You’re not expecting history because the data isn’t supposed to evolve.
Type 1: Overwrite old values
Type 1 is used when data can change, but you don’t need to keep the old values. Every time a change happens, the old value is overwritten with the new one. It’s simple, efficient, and good enough when you only care about the current state.
For instance, a customer’s email address or shipping info might fall into this category. If someone moves or updates their contact details, you just want the latest info so your emails and packages go to the right place.
When to use it: When current accuracy is all that matters. Use this for operational data like contact info or profile fields that change, but don’t need historical analysis.
Type 2: Track full history with new rows
Type 2 is your go-to tool when you want to track every change to an attribute over time. Instead of overwriting the old value, you insert a new row for each version, keeping a full history of changes. Each version has the same natural key (like product_id) but a different surrogate key (like product_version_id) and metadata like timestamps or flags to track which version is active.
A great example is a product bundle that gets updated throughout the year. Maybe a few SKUs are swapped out or prices change. With Type 2, each version gets its own row, so you can track exactly when those changes occur and analyze how they impacted performance.
Here's how the data might look in tabular format for the `DimProductType` table:
This way, the business can look at how a change in products in the pack have affected sales.
When to use it: Ideal for slowly evolving entities like products, employees, or customer segments where you want to measure how changes affect KPIs over time.
Type 3: Store limited history with extra columns
Type 3 dimensions track changes in a row by adding a new column. Instead of adding a new row with a new primary key like with type 2 dimensions, the primary key remains the same, and an additional column is appended.
Let’s say you’re tracking warehouse locations, and you recently moved one to a new city. You might not care about tracking multiple past locations, but you do want to store the last one. So you keep current_location and previous_location as separate columns.
When to use it: Use Type 3 when you only need to keep one prior version of a value, like for reference or comparison, not full historical tracking.
Type 4: Separate history table
Type 4 uses two tables: one for current records and one for historical records. Every time a change happens, the current table is updated, and the previous version is pushed into the history table. This keeps the current table clean and fast for queries, while still giving you a full audit trail when needed.
One example is order details. You might only need the latest cart state for processing, but you also want to track what customers added or removed over time. In this case, the current table powers the live experience, while the history table is used for behavioral analysis.
When to use it: Type 4 is great when you want to separate current vs. historical data for performance or clarity, especially in digital systems that frequently change but only analyze history occasionally.
If you've ever worked across a messy data warehouse, you know the pain: tables that weren’t designed for change, no historical tracking, and mystery fields no one remembers creating.
The good news? You can bring order to the chaos. With a few smart steps, you can start implementing SCDs in a way that brings clarity and actually makes an impact.
Step 1: Audit what you’ve already got
Before diving into implementation, you must assess the current state of your data warehouse.
What dimension tables already exist?
Are any of them changing over time?
Do any require historical tracking?
Start by documenting what each dimension tracks, what the natural keys are, and whether changes are being handled the right way. This gives you a clear map of the terrain before you start making improvements.
Step 2: Prioritize the problem areas
If you spot a dimension (like customer_segment or product_pricing) that is clearly changing, but the current process is simply to overwrite the data, that’s your starting point.
These are the biggest risks to your reporting and analysis. Once those values get overwritten, the historical context is gone unless you've got logs or backup snapshots. Better to catch and fix these now than to explain KPI shifts later.
Step 3: Choose the right SCD types
Choosing the appropriate SCD type isn’t just a technical decision; it’s a strategic one that directly impacts how your business can analyze data. You should consider:
Frequency of change: Does the data change often or rarely?
Need for history: Is there a business need to analyze historical values, or do you only care about the current state?
Analytical requirements: Do you need a complete history, or just a flag for "previous" vs. "current" versions?
This decision depends on the nature of the data and how it supports reporting, forecasting, or compliance. Collaborating with data analysts, engineers, and business stakeholders aligns your models with real-world needs.
And if you're using ThoughtSpot’s Analyst Studio, this step becomes even more powerful. It provides an “extract” solution called Datasets, which allows you to define controlled data refresh schedules or work with periodic snapshots instead of always relying on live connections.
This flexibility is especially helpful for managing slowly changing dimensions, giving you more control over how and when changes are captured, while also optimizing performance and reducing load on source systems.
Step 4: Decide how to handle legacy data
Once you've chosen your SCD types, you face a critical decision: should you attempt to backfill historical data or start fresh?
Option 1: Start fresh: This involves acknowledging that historical data prior to your implementation date will not have SCD tracking.
Option 2: Backfill: If business requirements absolutely demand a complete history, you can attempt to backfill. This often requires complex logic to rebuild timelines from available snapshots or audit logs.
Step 5: Implement logic in the data pipelines
With your strategy in place, it's time to operationalize the process in your ETL/ELT pipeline. This process is critical in making sure that dimensional data evolves in a practical, queryable way.
1. Detect changes
Rather than comparing each column individually (which can be error-prone and inefficient), most teams use a hashing strategy. By hashing all tracked columns into a single fingerprint, it becomes easy to detect when something has changed.
2. Include critical metadata
Every slowly changing dimension needs context for analysis. That means including a few key columns in every record:
A surrogate key (so each row is unique)
Validity ranges (valid_from, valid_to)
A flag for the current record (is_current)
1. ETL: The old-school batch approach
ETL (Extract, Transform, Load) is great for processing large batches of data. But when it comes to tracking change, it’s not always the fastest.
That’s because it usually involves pulling and transforming big chunks of data at once, so changes aren’t picked up in real time.
That said, ETL has its perks. You’ll end up with a richer audit trail thanks to all the versions being stored. Just be ready to deal with heavier data processing workloads.
2. Change data capture: Real-time processing
Need fresher data? Change Data Capture monitors your source systems and captures changes as they happen.
It’s especially useful for high-frequency updates and for managing Type 2 SCDs, where every change should be recorded without overwriting the past. With CDC, you get fast, accurate change tracking that keeps your data warehouse in sync with reality.
3. Tracking history with effective dates
Effective dating is a popular way to manage Type 2 slowly changing dimensions by using two timestamps to show when each version of a record was active. Instead of overwriting data, you add a new row for each change and update the timestamps accordingly.
This method only works well if your timestamps are consistent and trustworthy. Use system-generated fields from the source when possible—otherwise, you risk gaps, overlaps, and broken history.
Accelerate data to insights
As a data leader, you’ve probably seen what happens when historical context is missing: KPIs shift without explanation, trust in the data erodes, and your team spends more time investigating inconsistencies than delivering insights.
The good news? You don’t need to patch this together manually. With the right analytics platform, tracking changes, preserving history, and surfacing insights become part of the workflow—not a separate project.
That’s exactly what ThoughtSpot is built for.
With tools like Analyst Studio and a flexible semantic layer, your team can model, refine, and explore dimensional data, without needing a dozen layers of SQL or a massive data engineering lift. With this kind of scalability, your team can move faster, trust the numbers, and lead with clarity.
See what ThoughtSpot’s agentic analytics can do for your business. Schedule a demo today.
Frequently asked questions
Why should I care about SCDs?
If you want to analyze historical trends, such as how customer behavior shifted after a product rebrand, you need SCDs. Without them, your reports will only reflect the most current values, which makes it impossible to get a true picture of what was happening in the past.
How do I know which type to use?
It depends on your business goal. If historical data doesn’t matter (e.g., fixing a typo), Type 1 is fine. But if you need full visibility into how things changed over time, go with Type 2. And if you just need to track one or two changes (like before/after), Type 3 can work.
What happens if I don’t handle SCDs correctly?
You lose context. Your reports might show weird inconsistencies, your users may get confused, and your insights are incomplete. It’s like reading a story but only seeing the last page.
Can I mix and match different SCD types?
Yes, and many teams do. One dimension might be Type 2, another Type 1, and some may not need tracking at all. It’s all about what level of history you want to preserve.
