SCD Type 3
A slowly changing dimension technique that adds a column for the previous value of an attribute. Each entity keeps one row holding the current and the prior value, so reports can group by either one.
Also: slowly changing dimension type 3 · Type 3 SCD · previous value column
On this page · 7 sections
- Category
- Data modeling
- Also known as
- slowly changing dimension type 3, Type 3 SCD, previous value column
- Goes deeper in
- Data Modeling
SCD Type 3 tracks a change by widening the table instead of adding rows. The dimension gets a second column, such as previous_sales_region next to sales_region, and usually a date for when the change took effect. When the attribute changes, the current value moves into the previous column and the new value takes its place.
The result is one row per entity that can answer the same question two ways: sales by the region each account belongs to now, or by the region it belonged to before. That is the case Type 3 exists for, where the business wants to compare an old and a new assignment side by side for a while, as after a sales territory realignment.
The history is limited to the columns you add. With one previous column, a second change pushes out the first value for good. Type 3 also cannot say which value applied on a given date for a given fact, because facts join to the single row and not to a version of it.
merge into dim_accounts as d
using stg_accounts as s
on s.account_id = d.account_id
when matched and d.sales_region <> s.sales_region then
update set
previous_sales_region = d.sales_region,
sales_region = s.sales_region,
region_changed_on = current_date;Type 3 answers a narrow question cheaply. If the requirement is to report under both the old and the new structure for a transition period, a previous-value column is far simpler than versioned rows and as-of joins.
It is the wrong tool for general history. An attribute that changes repeatedly, or one where reports must reflect the value at the time of each event, needs Type 2.
- Using Type 3 for an attribute that changes more than once. In the example above, a move from East to Central and then to South leaves Central as the previous value, and East is gone.
- Reading the previous column as history for facts. It records the last value, not the value that applied when each sale happened.
- Adding a previous column for every attribute in case it is needed, which widens the table without a report that uses it.
- What is the difference between SCD Type 2 and Type 3?
- Type 2 adds a row for every change, so it keeps full history and can show the value that applied at any date. Type 3 adds a column, so it keeps only the current and the previous value on one row. Type 3 is simpler to query but cannot represent more changes than it has columns.
- When should I use SCD Type 3?
- Use it when the business needs to report by an old and a new value at the same time for a limited period, such as a sales territory or cost-center reorganization, and the attribute changes rarely.
- Can SCD Type 3 keep more than one previous value?
- Only by adding a column for each one, such as a region column per year. That works for a small, fixed number of versions. Beyond that, Type 2 is the better fit.
