Implementing Type 3 Current and Original Columns
In this lesson, you will learn how to track limited historical context in a dimension table by maintaining both the current and the previous state of an attribute side-by-side.
The Core Concept
When a business needs to track how a specific attribute has changed without creating multiple rows for a single entity (like you do in a Type 2 approach), the Type 3 strategy is your best tool. Instead of versioning the entire record, you add two specific columns to your dimension table: one for the current_value and one for the original_value (or sometimes the "previous" value).
This allows end-users to compare where a record stands today versus where it stood previously, without needing to perform complex joins across historical row versions.
A Concrete Example
Imagine you are tracking a sales_territory for a customer dimension. If a customer moves from the "North" region to the "South" region, a Type 3 implementation would look like this:
| customer_id | customer_name | territory_current | territory_original |
|---|---|---|---|
| 101 | Acme Corp | South | North |
| 102 | Beta Inc | East | East |
When an update occurs, the data engineering pipeline performs three steps:
- It moves the existing value from
territory_currenttoterritory_original. - It overwrites
territory_currentwith the new incoming value. - It keeps the same surrogate key for the record, ensuring that fact tables pointing to this customer remain connected without needing key updates.
When to Use It
Type 3 is highly efficient for simple "Before vs. After" comparisons. Because it keeps the row count stable, your dimension tables remain small and performance stays fast. However, remember that this approach is limited; it only tracks the most recent change. If the attribute changes a third time, the "original" value from the first change is overwritten and lost forever. If you need to keep a complete, infinite history of all changes, you would need to move back to a Type 2 row-versioning strategy.
Key Takeaway: Use Type 3 columns when you need to perform quick, one-time comparisons between current and previous attribute values while keeping your dimension table size constant.