Part 4 of 5 · 2 min read
Slowly changing dimensions
A data-warehousing term for exactly this problem, applied to dimension tables feeding reports.
Slowly changing dimensions
Think of a company's org chart, redrawn every few months as people change roles, where old reports still need to show who managed whom at the time those reports were originally generated.
In plain words: a slowly changing dimension is a dimension table, in a data warehouse, whose values change occasionally rather than constantly, needing a deliberate strategy for how to handle that change. The most common strategy, called type 2, adds a new row for every change, exactly like the history table pattern from part 2, with its own valid-time columns.
A worked example. A customer_dimension table used for sales reports might need to show that a specific sale was attributed to a customer's old city, even after that customer later moved, if the report is reconstructing history as it looked at the time.
The key lines. No new SQL; the pattern is identical to the history table already shown, applied specifically inside a data warehouse's reporting schema.
Slowly changing dimension
A slowly changing dimension is a data-warehouse dimension table whose values change occasionally, requiring a deliberate strategy, most commonly adding a new row per change, for handling that change correctly in historical reports.
Example: a type 2 slowly changing customer_dimension table adds a new row when a customer's city changes, exactly like book_price_history adds a new row per price change.
For the curious Why Level 12 returns to this term
Level 12's modules on big data and analytics use "slowly changing dimension" as a standard, named term in data warehousing specifically. This module introduces the underlying idea early, in the general schema-design context it actually belongs to, so it is already familiar by the time Level 12 uses the term directly.
Quick notes
- Valid time is when a fact was true in the real world; transaction time is when it was recorded.
- A plain table overwrites old values; a history table preserves them, each tagged with a validity period.
- A slowly changing dimension applies this same history-keeping idea to data warehouse dimension tables.
- Type 2 slowly changing dimensions add a new row per change, matching the general history-table pattern.
- Not every table needs this; reach for it only when a real requirement needs the past preserved, not just the present.