Slowly Changing Dimensions: Type 1, Type 2 and Beyond

In modern data warehousing, tracking how entity attributes evolve over time is one of the most critical challenges data architects face. Customer addresses change, product categories get reorganized, and sales territories undergo annual restructuring. If your data warehouse simply overwrites old values with new ones, historical business metrics—such as historical sales by region—become distorted or irreproducible.

Managing these shifting attributes falls under the discipline of slowly changing dimensions (SCDs). Originally formalized by Ralph Kimball, SCD patterns dictate how a dimensional model captures, preserves, or ignores temporal changes. While the underlying concepts date back decades, executing changing dimensions at petabyte scale on platforms like Snowflake, Databricks, AWS, and Azure requires a modern architectural approach.

Below, we break down the foundational SCD types, explore advanced hybrid patterns, and evaluate modern implementation patterns using dbt, SQL MERGE, and Lakehouse storage layers.

---

The Foundation: SCD Type 0 and Type 1

Before examining historical tracking mechanisms, it is worth clarifying the baseline strategies that either reject or ignore historical state.

Type 0: Retain Original

Type 0 dimensions are entirely static. Once a row is written, its values are immutable.

• Use Case: Immutable reference data, such as an original date of birth, an initial credit check score, or static currency definitions.
• Trade-off: No ability to adapt if upstream operational systems correct typographical errors without a hard data wipe.

Type 1: Overwrite

SCD Type 1 updates the existing record directly in place. When a customer changes their billing address, the existing database row is updated with the new address.

• Use Case: Correcting misspelled names, updating non-analytical operational attributes, or scenarios where preserving historical context provides zero business value.
• Storage & Query Overhead: Minimal. Table row counts remain static (1:1 with natural business keys), and downstream analytical queries do not require complex filtering on dates or validity flags.
• The Drawback: Historical context is permanently lost. If an executive queries revenue for the "Northeast Region" across a three-year period, transactions completed before an account rep relocated will be attributed to the new territory, distorting historical reporting.

-- Conceptual Type 1 MERGE in Snowflake / Databricks
MERGE INTO dim_customer AS target
USING stg_customer AS source
ON target.customer_id = source.customer_id
WHEN MATCHED AND (target.email != source.email OR target.phone != source.phone) THEN
  UPDATE SET target.email = source.email, target.phone = source.phone, target.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, phone, updated_at)
  VALUES (source.customer_id, source.email, source.phone, CURRENT_TIMESTAMP());

---

SCD Type 2: The Enterprise Gold Standard

SCD Type 2 is the most widely adopted pattern in enterprise data warehousing. Rather than overwriting a row, Type 2 tracks historical changes by inserting a new record whenever a monitored attribute mutates.

Anatomical Requirements of Type 2 Tables

To support point-in-time joins, an SCD Type 2 dimension requires specific tracking metadata:

1. Surrogate Key: A generated unique identifier (often an auto-incrementing integer or an MD5/SHA-256 hash of the natural key plus the effective date) separate from the source system's natural key.
2. Effective Start Date (valid_from): The timestamp when this record version became active.
3. Effective End Date (valid_to): The timestamp when this record version was superseded. Best practice sets active records to an arbitrary future date (e.g., 9999-12-31) rather than NULL to avoid performance penalties in indexed range joins.
4. Current Record Flag (is_current): A boolean or integer flag (TRUE/FALSE or 1/0) indicating the latest operational version.

+---------------+-------------+--------------+------------+------------+------------+
| customer_skey | customer_id | tier         | valid_from | valid_to   | is_current |
+---------------+-------------+--------------+------------+------------+------------+
| 1001          | CUST-882    | Standard     | 2023-01-01 | 2023-09-15 | FALSE      |
| 1002          | CUST-882    | Premium      | 2023-09-15 | 9999-12-31 | TRUE       |
+---------------+-------------+--------------+------------+------------+------------+

Analytical Joins with Type 2 Dimensions

When querying fact tables against Type 2 dimensions, queries must join on both the natural key and the transaction timestamp to reconstruct point-in-time reality:

SELECT 
  f.order_id,
  f.order_amount,
  d.tier
FROM fact_orders f
JOIN dim_customer d
  ON f.customer_id = d.customer_id
 AND f.order_date >= d.valid_from
 AND f.order_date < d.valid_to;

While powerful, Type 2 introduces table growth. In high-churn domains—such as IoT device status tracking or real-time risk scoring—Type 2 tables can expand by millions of rows daily, leading to query performance degradation without proper clustering or partitioning strategies.

---

Intermediate Patterns: SCD Type 3 and Type 4

When complete historical auditing is unnecessary or Type 2 introduces too much operational overhead, architects deploy Type 3 or Type 4 patterns.

SCD Type 3: Previous and Current Columns

Type 3 tracks changes by adding a new column to the dimension table, such as current_tier and previous_tier.

• Mechanism: When a status changes, the value in current_tier is shifted to previous_tier, and the new value occupies current_tier.
• Advantage: Preserves a fixed record count while allowing comparisons between the current state and the immediate prior state.
• Limitation: Inflexible. If an entity changes states three times, the earliest historical state is discarded.

SCD Type 4: Rapidly Changing Dimensions (Mini-Dimensions)

When certain attributes change at high frequencies (e.g., customer credit score brackets, account balance bands, or age brackets), tracking them via Type 2 causes massive row explosion in the primary dimension table.

SCD Type 4 resolves this by bifurcating the model:

1. Base Dimension: Retains static or slowly changing attributes (name, national ID, birth date).
2. Mini-Dimension: Holds distinct combinations of fast-moving attributes grouped into bands.
3. Fact Table Linking: Fact records store surrogate keys for both the base dimension and the mini-dimension corresponding to the event timestamp.

---

Beyond the Basics: SCD Type 6 (Hybrid Patterns)

SCD Type 6 combines the mechanisms of Type 1, Type 2, and Type 3 (1 + 2 + 3 = 6).

In an SCD 6 table, new rows are inserted to track history (Type 2), an explicit column stores historical values (Type 3), and the current attribute value is backfilled into all historical rows for that entity (Type 1).

This allows analysts to query both:

• Point-in-time state: By filtering against valid_from and valid_to.
• Current-state segmentation across historical activity: By grouping historical facts by the current_attribute column without needing expensive window functions.

---

Comparing Slowly Changing Dimension Approaches

---

Modern Implementation: dbt, Delta Lake, and Snowflake

Manually orchestrating raw SQL MERGE statements for Type 2 changing dimensions is error-prone. Edge cases—such as late-arriving records, duplicate updates within the same micro-batch, and millisecond out-of-order writes—can corrupt valid_from and valid_to timelines.

Declarative Snapshots in dbt

Modern data engineering teams often abstract this logic using dbt snapshots. dbt automates Type 2 tracking by capturing state changes based on a timestamp column or a hash of monitored attributes:

{% snapshot customer_snapshot %}

{{
    config(
      target_schema='analytics_snapshots',
      unique_key='customer_id',
      strategy='timestamp',
      updated_at='updated_at',
      invalidate_hard_deletes=True
    )
}}

SELECT customer_id, email, address, tier, updated_at
FROM {{ source('raw_crm', 'customers') }}

{% endsnapshot %}

dbt handles surrogate hashing, updates to dbt_valid_to, and sets the active record metadata automatically.

Handling Late-Arriving Dimensions

A persistent issue in data warehousing occurs when a fact record arrives before the dimension version that matches it. For example, a customer places an order moments after changing their address, but the upstream CRM pipeline runs on a two-hour delay while transactional events stream in real-time.

To maintain integrity:

1. Infer Dummy Members: Write the fact record using a placeholder surrogate key linked to an inferred dimension record.
2. Backfill Pipeline: When the dimension event finally arrives, update the inferred record and retroactively re-point the fact foreign key using a targeted post-load procedure.

At Izan Technologies, we design robust dimensional frameworks that natively manage these operational realities, avoiding the cascading pipeline failures typical of naive ETL designs.

---

How Izan Technologies Helps

Whether you are consolidating legacy data warehouses into Snowflake or Databricks, or building cloud-native Lakehouse pipelines across AWS and Azure, modeling slowly changing dimensions correctly is the dividing line between trustworthy data and continuous discrepancies.

Izan Technologies designs and builds high-performance data warehousing, modern ELT, and custom machine learning solutions. Unlike traditional consultancies, our clients retain 100% ownership of all IP, code, and architecture we deliver—ensuring you maintain complete autonomy over your data infrastructure.

Ready to optimize your analytical architecture? Try our Smart AI DataStudio to accelerate your data transformation initiatives, or talk to an expert on our engineering team today.