Slowly Changing Dimensions
Slowly changing dimensions (SCDs) are dimension attributes whose values change over time, together with the numbered set of techniques—originating with Ralph Kimball—for deciding whether that history is preserved, overwritten, or tracked.
A dimension describes the context of a fact: who, what, where. Most descriptive attributes are not static—a customer moves to a new city, a product is reclassified, a sales rep changes territory. The question an SCD strategy answers is: when an attribute changes, what should happen to the historical rows that referenced the old value? The common approaches are numbered.
Type 0 retains the original value and ignores updates (e.g., original credit score, date of birth). Type 1 overwrites the old value with the new one and keeps no history—simple, but past facts now appear to have always had the current attribute value. Type 2 adds a new dimension row with a new surrogate key and tracks validity with effective/expiration dates and often a "current" flag, preserving full history so facts join to the row that was current when the fact occurred. Type 3 adds a new column to keep a limited number of prior values side by side (e.g., "current region" and "previous region").
Hybrid approaches (sometimes called Type 4, 6, or 7) combine these—Type 4 splits rapidly changing attributes into a "mini-dimension," while Type 6 blends 1, 2, and 3 to support both current and historical views.
Key Characteristics
- ▶Describe how dimension attribute changes are handled over time
- ▶Type 0 retains the original; Type 1 overwrites with no history
- ▶Type 2 adds a new surrogate-keyed row with effective/expiration dates and a current flag
- ▶Type 3 adds a column to keep a small, fixed number of prior values
- ▶Hybrids (Type 4/6/7) combine techniques, including mini-dimensions for fast-changing attributes
- ▶Type 2 requires facts to join on the time-valid surrogate key, not the business key
Why It Matters
- ▶The SCD choice directly controls the truthfulness of historical analysis
- ▶Overwriting (Type 1) silently rewrites history—last year's sales get attributed to today's region
- ▶Type 2 preserves "as-was" reporting, essential for accurate trend analysis, attribution, and audit
- ▶Joining Type 2 facts on the natural/business key instead of the surrogate key picks up the wrong version or fans out
- ▶Applying expensive Type 2 tracking to fast-changing attributes is a mismatch—model them as facts or a mini-dimension instead
Example
A customer in the West region in 2024 generates $50,000 in sales, then moves to the East region in 2025. Under Type 1, a 2024 sales-by-region report now credits East with that $50,000—incorrect. Under Type 2, the dimension holds two rows for the customer (West, valid through the move date; East, valid after), each with its own surrogate key, and the 2024 facts point to the West row, so the report correctly credits West.
Coginiti Perspective
Coginiti's ELT orientation is well suited to SCD management: because data stays in place on the warehouse or lakehouse and is remodeled rather than moved, teams can re-derive or correct SCD logic against the retained source without re-ingesting. CoginitiScript publication supports incremental materialization with merge and merge_conditionally strategies keyed on a unique_key with update_on_changes_in columns—the building blocks for implementing Type 1 overwrites and Type 2 row-versioning in a governed, repeatable pipeline. #+test blocks let teams assert SCD invariants (one current row per business key, no overlapping effective-date ranges) on every run. In the semantic layer (SMDL), relationships are defined on the surrogate keys so that Semantic SQL joins facts to the time-correct dimension version automatically, keeping "as-was" reporting consistent across every consumer rather than depending on each analyst to remember the join rule. Materializing versioned dimensions as Iceberg tables on a supported platform also gives teams open-format history they can time-travel and audit.
More in OLTP, OLAP & Workload Types
Analytical Workload
An analytical workload is a class of database queries that examine, aggregate, and analyze large volumes of historical data to extract business insights and support decision-making.
Conformed Dimensions
Conformed dimensions are dimensions that mean the same thing—and carry the same keys, attributes, and values—across multiple fact tables or data marts, letting you combine and compare metrics from different business processes with confidence.
Dimension Table
A dimension table is a database table in a star or snowflake schema that stores descriptive attributes used to filter, group, and drill-down in analytical queries.
Fact Table
A fact table is a database table in a star or snowflake schema that stores measures (quantitative data) and foreign keys to dimensions, representing events or transactions in a business process.
Fan and Chasm Traps
Fan traps and chasm traps are two classic join hazards in dimensional and relational models where the structure of the joins causes measures to be over-counted or under-counted, producing numbers that look valid but are wrong.
Grain
Grain is the level of detail represented by a single row in a table—the precise answer to the question "what does one record mean?"—and it is the foundational decision in dimensional modeling because every dimension and measure in a table must be consistent with it.
See Semantic Intelligence in Action
Coginiti operationalizes business meaning across your entire data estate.