What Is a Historical Snapshot (SCD Type 2)?
A historical snapshot is a way of storing data that preserves how it looked at each point in time, rather than only its current state. The most common technique for this in data warehousing is the slowly changing dimension Type 2, usually shortened to SCD Type 2. When an attribute changes, instead of overwriting the old value, SCD Type 2 keeps the old record and adds a new one, each marked with the dates it was valid. The result is a full history of how the data changed over time.
The problem this solves is that source systems usually hold only the current state. If a customer moves from one sales region to another, the ERP simply updates the region. Ask later which region that customer was in last year, and the source cannot tell you, because it overwrote the old value. A historical snapshot keeps both, so reporting can show the past accurately, as it was at the time, not as it looks now.
Why Historical Snapshots Matter
Accurate historical reporting depends on knowing how things were, not just how they are. If a salesperson’s territory changed, their past sales should still credit the territory they were in when the sale happened. If a product moved categories, last year’s results should reflect the category it was in then. Without a historical snapshot, a change today silently rewrites the past, and trend analysis becomes unreliable.
This matters most for analysis that compares periods or attributes performance to a structure that changes over time. Reorganizations, territory changes, product reclassifications, and customer moves all distort historical reporting if the past is not preserved. SCD Type 2 is the standard way to keep that history intact, so comparisons across time hold up.
How SCD Type 2 Works
New record on change. When a tracked attribute changes, a new row is added for that entity instead of updating the existing row. The old row is kept.
Validity dates. Each row carries dates marking when it was valid, an effective date and an end date. This is what lets a query find the version that was current at any point in time.
A current flag. A flag usually marks which row is the current version, so reporting can easily filter to the latest state when that is what is wanted.
This sits within the broader family of slowly changing dimension techniques. Type 1 overwrites the old value and keeps no history. Type 2 keeps full history with new rows. Other types exist for specific needs, but Type 2 is the most common when history matters.
Historical Snapshots in ERP Environments
ERP data changes constantly, and much of it is the kind of master data that benefits from historical tracking. Customers change attributes, employees change departments, items change classifications, and the ERP typically holds only the current value. Building analytics that reports history accurately means capturing these changes as they happen and preserving them.
This pairs naturally with change data capture, which detects the changes as they occur in the source. The captured changes feed the historical snapshot, building the SCD Type 2 history over time. Modeling which attributes need history and maintaining it correctly is detailed work, and it is part of what pre-built ERP models handle for the dimensions where history matters most.
Common Challenges and Best Practices
- Decide what needs history. Not every attribute needs to be tracked over time. Apply SCD Type 2 where historical accuracy matters and use simpler handling elsewhere.
- Pair with change data capture. Detecting changes at the source is what feeds the historical snapshot. The two techniques work together.
- Get the validity dates right. Effective and end dates are what make point-in-time queries work. Errors here produce wrong historical results.
- Keep a current flag. A clear marker for the latest version makes current-state reporting simple alongside the full history.
- Mind the volume. Tracking history adds rows. Model it efficiently so the history does not slow current reporting.
Frequently Asked Questions
What does SCD Type 2 stand for?
SCD stands for slowly changing dimension. Type 2 is the variant that preserves full history by adding a new row when an attribute changes, rather than overwriting the old value. It is the most common technique for keeping a historical snapshot of dimension data.
What is the difference between SCD Type 1 and Type 2?
Type 1 overwrites the old value when data changes, keeping no history. Type 2 keeps the old record and adds a new one with validity dates, preserving full history. Type 1 is simpler; Type 2 is used when reporting needs to show how data looked in the past.
Why is a historical snapshot important for reporting?
Source systems usually hold only the current state, so a change today can silently rewrite the past. A historical snapshot preserves how data looked at each point in time, which keeps trend analysis and period comparisons accurate when attributes like territory or category change.
Historical Snapshots and QuickLaunch’s Approach
QuickLaunch Analytics uses change data capture and historical snapshot techniques in its pre-built models to preserve history for the ERP dimensions where it matters, so reporting reflects how data looked at the time rather than only its current state. This keeps trend analysis and period comparisons accurate across changes in the source, on a foundation refined across 250+ enterprise implementations.
