Slowly Changing Dimensions: Tracking History in Data Warehouses

Data changes. Customer addresses change. Products get rebranded. Employees transfer departments. A data warehouse that only tracks current state loses valuable historical context.

Slowly Changing Dimensions (SCD) is the technique for handling attribute changes in dimension tables while preserving historical accuracy. The term comes from early data warehousing, where dimensions were considered “slowly” changing compared to facts which update constantly.

Understanding SCD is essential for accurate historical analysis. Get it wrong and your reports tell lies.

The Core Problem

Consider a customer dimension with a region attribute. Customer ABC was in the Northeast region until March 2025, then moved to the Southwest.

If you simply overwrite the region, what happens when you run a report showing sales by region for 2024? The customer appears in the Southwest, even though they were actually in the Northeast at that time.

This is not a minor edge case. Historical accuracy by geography, territory assignments, product categories, and organizational hierarchies is critical for business decisions.

SCD Type 1: Overwrite

Type 1 is the simplest approach. When an attribute changes, overwrite the old value with the new one.

UPDATE dim_customer
SET region = 'Southwest',
    last_updated = CURRENT_TIMESTAMP
WHERE customer_id = 'ABC';

After this update, the customer appears in the Southwest for all time periods, including historical reports.

When Type 1 Makes Sense

Type 1 is appropriate when historical tracking genuinely does not matter for the attribute in question. The decision comes down to whether you will ever need to report on the attribute’s past state. If you will not, Type 1 avoids the storage overhead and query complexity of versioning.

A practical example: a customer’s email address. Email addresses change, sometimes frequently, but business reporting almost never needs to know what a customer’s email was six months ago. When the email updates, overwriting it is correct behavior. There is no analytical question that requires knowing the old address. The old value has no business value once the new one takes effect. Storing it would only add rows that nobody queries.

Purely current-state attributes are another valid case. These are attributes that represent the present condition of the entity and where historical state has no bearing on business decisions. A customer’s account status (active, suspended, closed) is one example. The status matters for what you can do with the account right now, not for analyzing past behavior. If you need to know when an account was suspended, that belongs in an audit log, not in the dimension table.

Regulatory requirements matter here too, but in the opposite direction from what teams sometimes assume. In some regulated industries, certain attributes must be preserved exactly as they were at the time of a transaction. PCI-DSS compliance, for instance, has strict rules about storing payment card data, and those rules often prohibit keeping old card numbers in readable form. If a regulation says you cannot retain the old value, Type 1 is not just acceptable, it is required. The distinction is important: if a compliance framework mandates historical tracking for an attribute, you need Type 2 or Type 3, not Type 1. If the framework says the old value must not be retained, Type 1 is the correct choice. Know which rule applies before you decide.

Here is a comparison of Type 1 and Type 2 treatment for a typical customer dimension:

Attribute	SCD Type	Rationale for choice
customer_email	Type 1	No analytical question ever requires the old email address
customer_name	Type 1	Legal name changes do not affect historical sales attribution
region	Type 2	Historical sales by region require accurate past assignments
customer_tier	Type 2	Revenue analysis by tier segment depends on tier at time of sale
credit_score	Type 4	Changes too frequently for Type 2; mini-dimension is appropriate
first_purchase_date	Type 1	The date is set once at account creation and never changes

The attributes that warrant Type 2 are the ones that drive analysis. Region, tier, segment, and similar attributes are what analysts slice and dice by. Getting the historical value wrong produces incorrect reports. Attributes that never drive analysis, or that represent ephemeral state like a session token, are better off as Type 1.

-- Type 1 dimension table
CREATE TABLE dim_customer_type1 (
    customer_id VARCHAR(50) PRIMARY KEY,
    customer_name VARCHAR(200),
    customer_email VARCHAR(200),
    region VARCHAR(100),
    last_updated TIMESTAMP
);

The simplicity of Type 1 is attractive. There is no additional storage cost and no complex querying. The trade-off is complete loss of historical context.

SCD Type 2: Add New Row

Type 2 preserves complete history by inserting a new row for each change. The old row remains with its historical effective dates.

CREATE TABLE dim_customer_type2 (
    customer_id VARCHAR(50) NOT NULL,
    customer_name VARCHAR(200),
    region VARCHAR(100),
    effective_date DATE NOT NULL,
    expiry_date DATE,
    is_current BOOLEAN,
    version_number INT,
    PRIMARY KEY (customer_id, effective_date)
);

When the customer moves regions:

-- Close out the current row
UPDATE dim_customer_type2
SET expiry_date = '2026-03-26',
    is_current = FALSE
WHERE customer_id = 'ABC' AND is_current = TRUE;

-- Insert the new row
INSERT INTO dim_customer_type2 (
    customer_id, customer_name, region,
    effective_date, expiry_date, is_current, version_number
) VALUES (
    'ABC', 'Customer ABC', 'Southwest',
    '2026-03-27', '9999-12-31', TRUE, 2
);

Now historical queries can find the correct region by looking for the row where the fact date falls between effective_date and expiry_date.

Querying Type 2 Dimensions

The date-range join is what makes Type 2 historical queries work. The join condition f.order_date >= d.effective_date AND f.order_date < d.expiry_date looks straightforward, but understanding why both bounds are necessary is important for writing correct queries and debugging when they go wrong.

The effective_date marks when a dimension row becomes valid. The expiry_date marks when it stops being valid. A fact row matches a dimension row when the fact’s transaction date falls somewhere inside that validity window. Both bounds are needed because without one or the other, the logic breaks down.

If you omit the effective_date condition, a March sale could match a dimension row that was inserted in April, meaning the sale gets attributed to a region that was not yet assigned. If you omit the expiry_date condition, a March sale could match a dimension row that was closed in February, attributing the sale to a region the customer had already left. Both scenarios produce wrong results silently, with no error, just incorrect aggregates.

The is_current flag is a shortcut that helps with certain queries. When is_current = TRUE, the row has expiry_date = '9999-12-31', meaning it is the currently valid version. For queries that only care about current state, WHERE is_current = TRUE is simpler than adding the date range conditions. For historical queries, you need the full date range logic because is_current does not tell you anything about what the attribute value was at a past date.

Here is a practical walkthrough. An analyst wants March sales broken down by customer region. The fact table has a sale on March 20 for customer ABC. The dimension has two rows for customer ABC: one with effective_date = 2025-01-01 and expiry_date = 2025-03-25 (region = Northeast), and one with effective_date = 2025-03-26 and expiry_date = '9999-12-31' (region = Southwest). The March 20 sale falls in the first window, so it joins to the Northeast row. The result correctly shows the sale attributed to the Northeast, which is what the data says happened at the time.

Surrogate keys make this work correctly at scale. Each dimension version has a unique surrogate key. The fact table stores the surrogate key that was active at transaction time. When you join on both customer_id and surrogate_key, each fact matches exactly one dimension row, not multiple. Without surrogate keys, two dimension rows for the same customer with the same natural key could both match a fact if the date logic is not perfectly precise.

SELECT
    f.order_date,
    f.total_amount,
    d.region AS customer_region_at_time
FROM fact_sales f
JOIN dim_customer_type2 d
    ON f.customer_id = d.customer_id
    AND f.order_date >= d.effective_date
    AND f.order_date < d.expiry_date
WHERE f.order_date BETWEEN '2025-01-01' AND '2025-12-31';

This is more complex than a simple join, but it returns accurate historical context.

Natural Keys and Surrogate Keys in Type 2

The customer_id in your dimension is the natural key. It identifies the business entity. The surrogate key (often a hash or sequence) uniquely identifies each version of that entity.

CREATE TABLE dim_customer_type2 (
    surrogate_key VARCHAR(50) PRIMARY KEY,  -- Hash or sequence
    customer_id VARCHAR(50) NOT NULL,        -- Natural key
    customer_name VARCHAR(200),
    region VARCHAR(100),
    effective_date DATE NOT NULL,
    expiry_date DATE,
    is_current BOOLEAN
);

The fact table should reference the surrogate key, not the natural key. This ensures each fact points to exactly one dimension row.

SCD Type 3: Add New Attribute

Type 3 keeps both old and new values in the same row, using separate columns.

ALTER TABLE dim_customer ADD COLUMN previous_region VARCHAR(100);
ALTER TABLE dim_customer ADD COLUMN region_change_date DATE;

When a customer moves:

UPDATE dim_customer
SET previous_region = region,
    region = 'Southwest',
    region_change_date = CURRENT_DATE
WHERE customer_id = 'ABC';

This allows queries to reference both current and previous values.

Type 3 Query Example

SELECT
    customer_id,
    region AS current_region,
    previous_region AS prior_region,
    region_change_date,
    CASE
        WHEN region_change_date <= '2025-12-31' THEN previous_region
        ELSE region
    END AS region_at_year_end_2025
FROM dim_customer;

Type 3 only tracks one level of change. If the customer moves again, you lose the first historical value. For tracking multiple changes, Type 2 is necessary.

SCD Type 4: Mini-Dimension

Type 4 separates frequently changing attributes into a separate table called a mini-dimension.

-- Main customer dimension with stable attributes
CREATE TABLE dim_customer (
    customer_key VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    customer_name VARCHAR(200),
    date_of_birth DATE,
    gender VARCHAR(10)
);

-- Mini-dimension for changing attributes
CREATE TABLE dim_customer_profile (
    profile_key VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    region VARCHAR(100),
    customer_tier VARCHAR(20),
    credit_score INT,
    effective_date DATE
);

Facts join to the appropriate mini-dimension row based on the transaction date. This avoids the performance problem of scanning large dimension tables with many version rows.

SCD Type 6: Hybrid Type 1 and Type 2

Type 6 combines Type 1 and Type 2 behaviors. A row has both current values (overwritten on change) and historical values (new rows inserted).

CREATE TABLE dim_customer_type6 (
    surrogate_key VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    customer_name VARCHAR(200),
    region VARCHAR(100),           -- Type 1: current value, overwritten
    region_history VARCHAR(200),   -- Type 2: historical value from when row was inserted
    effective_date DATE,
    is_current BOOLEAN
);

This hybrid approach is less common but useful when you need both current-state queries to be simple and historical queries to be accurate.

Implementing Type 2: A Practical Load Process

Loading Type 2 dimensions requires careful change detection.

def load_customer_dimension_type2(staging_customers, dim_customers):
    """
    Load customer dimension with Type 2 handling.
    staging_customers: new data from source system
    dim_customers: existing dimension data
    """

    for staging_row in staging_customers:
        # Find existing current row
        existing = find_current_customer(dim_customers, staging_row.customer_id)

        if existing is None:
            # New customer: insert with effective date
            insert_customer({
                'surrogate_key': hash(staging_row.customer_id),
                'customer_id': staging_row.customer_id,
                'customer_name': staging_row.customer_name,
                'region': staging_row.region,
                'effective_date': staging_row.extract_date,
                'expiry_date': '9999-12-31',
                'is_current': True
            })

        elif has_changed(existing, staging_row):
            # Attribute change: close out old, insert new
            close_out_customer(existing, staging_row.extract_date)

            insert_customer({
                'surrogate_key': hash(staging_row.customer_id + str(uuid)),
                'customer_id': staging_row.customer_id,
                'customer_name': staging_row.customer_name,
                'region': staging_row.region,
                'effective_date': staging_row.extract_date,
                'expiry_date': '9999-12-31',
                'is_current': True
            })

        else:
            # No change: update slowly changing attributes if needed
            update_customer_name(existing, staging_row.customer_name)


def has_changed(existing, new_row):
    """Check if any tracked attributes have changed."""
    return (
        existing.customer_name != new_row.customer_name or
        existing.region != new_row.region
    )

Handling Multiple SCD Types in One Dimension

In practice, a single dimension often uses multiple SCD types for different attributes.

Attribute	SCD Type	Rationale
Customer Name	Type 1	Historical accuracy not needed
Customer Region	Type 2	Critical for regional analysis
Customer Tier	Type 3	Need to compare current vs previous
Credit Score	Type 4	Mini-dimension for frequent changes

CREATE TABLE dim_customer (
    -- Primary key
    surrogate_key VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL,

    -- Type 1 attributes (overwritten)
    customer_name VARCHAR(200),

    -- Type 2 attributes (versioned)
    region VARCHAR(100),
    region_effective_date DATE,
    region_expiry_date DATE,

    -- Type 3 attributes (current + previous)
    previous_tier VARCHAR(20),
    current_tier VARCHAR(20),

    -- Metadata
    is_current BOOLEAN,
    load_timestamp TIMESTAMP
);

SCD Timeline Visualization

gantt
    title Type 1 vs Type 2 vs Type 3 Behavior
    dateFormat YYYY-MM-DD
    section Type 1
    Overwrite: 2025-01-01, 2025-06-01

    section Type 2
    Row v1 (Northeast): 2025-01-01, 2025-06-01
    Row v2 (Southwest): 2025-06-02, 2026-03-28

    section Type 3
    Row (current=Southwest, prev=Northeast): 2025-06-02, 2026-03-28

Common Pitfalls

Forgetting to Close Expired Rows

The most common mistake is inserting a new row without setting the expiry_date on the old row.

-- WRONG: Old row has no expiry, creates ambiguous history
INSERT INTO dim_customer (...) VALUES (...);

-- RIGHT: Close out old row first
UPDATE dim_customer
SET expiry_date = '2026-03-26'
WHERE customer_id = 'ABC' AND is_current = TRUE;

INSERT INTO dim_customer (...) VALUES (...);

Using Natural Keys in Fact Tables

Natural and surrogate keys trip up a lot of teams. A natural key is the identifier your source systems already use — customer_id, product_sku, store_number. These carry business meaning, which means they can change. A store number gets reassigned. A customer ID gets reused after a soft delete. A surrogate key is a synthetic number, usually generated from a sequence or hash, that lives only in the warehouse. It has no business meaning and never changes.

The trouble with natural keys in fact tables becomes obvious the second you run a historical report. You want to sum sales by region. You join fact_sales to dim_customer on customer_id and — without a date filter — you get the currently active dimension row, not the one that existed when the sale happened. A customer who moved from Northeast to Southwest in April will have their March sale attributed to the Southwest, because that’s what the join returned.

Surrogate keys solve this cleanly. Every fact stores the surrogate_key that was active at transaction time, not whatever happens to be current now. When the customer moves, the dimension gets a new surrogate_key. Old facts still point to the old surrogate_key; new facts point to the new one. A date-range join on effective_date and expiry_date then does the right thing automatically.

The performance upside is real too. Integer surrogate keys join faster than VARCHAR natural keys and compress better in Snowflake or Redshift. You also avoid storing long string identifiers in every fact row.

Keeping the natural key in the fact table as an attribute is not wrong — it makes debugging easier since you can trace any fact back to its source system without an extra lookup. Just do not use it as the foreign key. The surrogate_key is your join path; the natural key is there for convenience.

-- Fact table: surrogate key is the join path
CREATE TABLE fact_sales (
    sale_id VARCHAR(50) PRIMARY KEY,
    surrogate_key VARCHAR(50) NOT NULL,  -- Join to dimension via this
    customer_id VARCHAR(50),              -- Business identifier, not for joins
    order_date DATE,
    total_amount DECIMAL(10,2)
);

-- Dimension: both keys present
CREATE TABLE dim_customer_type2 (
    surrogate_key VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL,     -- Natural key
    region VARCHAR(100),
    effective_date DATE NOT NULL,
    expiry_date DATE,
    is_current BOOLEAN
);

Missing Change Detection

Comparing all attribute values on every load is expensive but necessary. Hash comparisons help.

-- Compute hash of tracking attributes
SELECT
    customer_id,
    MD5(CONCAT_WS('|', customer_name, region)) AS hash_diff
FROM staging_customers;

Compare hash_diff to detect changes without comparing each attribute individually.

Query Performance with Type 2

Type 2 dimensions grow over time. A customer dimension might have hundreds of rows per customer after years of updates.

Query performance suffers if you scan all versions for every query.

Mitigation strategies:

Index strategically. Index on (customer_id, effective_date, expiry_date).

Use a view for current values. Separate the current-state query path from historical queries.

CREATE VIEW dim_customer_current AS
SELECT * FROM dim_customer WHERE is_current = TRUE;

Consider partitioning. Partition the dimension table by customer_id range for faster access.

Use PIT tables. A Point-In-Time table pre-computes the correct surrogate key for each date.

Slowly Changing Dimensions Production Failure Scenarios

Dimension explosion from Type 2 on high-cardinality change attributes

The failure mode here is row multiplication in the fact table join. Consider a dimension like dim_order where someone has applied Type 2 to order_status. An order in a typical fulfillment pipeline transitions through created, confirmed, picking, packed, shipped, in_transit, out_for_delivery, delivered, and potentially cancelled or returned. That is nine status values, but real-world order management systems often have more granular states, and retry scenarios can add additional transitions. In a worst case, a single order can accumulate 20 or more status changes over its lifecycle.

Every status change inserts a new dimension row. After a year in production, a single order has 20+ rows in the dimension table. When you join the fact table to this dimension using the standard date-range join, each fact row matches every version of that order it falls within the date range for. A fact table with one row per order suddenly produces 20 rows per order in the result set because each fact matched multiple dimension versions.

The downstream impact is not subtle. Revenue reports show 20 times the actual values. Order counts are inflated by the same factor. Any metric that sums or counts facts joined to this dimension is wrong. The reports do not error, they just return numbers that are impossible. In a pipeline that feeds downstream systems, these inflated numbers propagate into dashboards, into data models, into business decisions.

The root cause is applying Type 2 to an attribute that changes too frequently. Type 2 is designed for attributes that change rarely relative to the frequency of fact loads. An attribute like customer region might change once a year. An order status changes multiple times per order. The storage and query cost of Type 2 only makes sense when the change frequency is low enough that dimension table growth is bounded.

Mitigation: Apply Type 2 only to attributes that genuinely need historical tracking for analytics. Status fields that change frequently belong in the fact table as degenerate dimensions or use Type 1. Set a maximum version count per natural key and alert when exceeded, so the explosion is visible before it contaminates aggregates.

Late-arriving facts referencing expired dimension rows

Late-arriving facts are a fact of life in any ETL pipeline that pulls from source systems with their own processing delays. A payment system might hold a transaction for fraud review before releasing it. A retail system might batch-upload end-of-day transactions hours after the sales occurred. A partner API might be unavailable and retry the next morning. In all these cases, the fact record arrives with a transaction date that is earlier than the load date.

The problem with Type 2 dimensions is that the dimension row active at the transaction date may have already expired by the time the late fact is loaded. Here is the specific mechanics: the fact has a transaction date of March 20. The dimension row for the customer on March 20 had effective_date = 2025-03-01 and expiry_date = 2025-03-22. On March 22, when the ETL job ran and detected the customer had not changed since March 1, it closed that row and opened a new one with effective_date = 2025-03-22. Now the late fact, arriving on March 25, joins to the dimension on fact_date BETWEEN effective_date AND expiry_date. The March 20 fact date falls between March 1 and March 22, which is the correct range, so it should match. The failure mode is actually slightly different: the dimension row that was correct at transaction time might have been expired by the time the late fact was loaded, and if the query only looks at is_current = TRUE rows, the late fact finds no match at all.

The silent nature of this failure is the worst part. The fact record is in the table. The query runs without error. But because the join misses, the fact contributes zero to any aggregation. Revenue is understated. Counts are low. The reports do not show an error, they show a gap that nobody can explain without digging into the join logic.

The second failure mode is more direct: the dimension row was correct on March 20, but the customer changed region on March 22. The ETL closed the March 1 row and opened a new one for the new region. The late fact on March 25 joins to the March 22 row, which has the wrong region for March 20. The fact contributes to the right aggregate total but the wrong dimension slice.

Mitigation: Build late-arrival handling into fact loads. For late facts, use the dimension row that was active on the fact’s transaction date, not the currently-active row. This requires storing the full historical dimension state at load time, not just the current snapshot. A common pattern is to maintain a separate historical dimension table or to look up the correct surrogate key by joining the staging fact to the dimension on transaction date before closing any rows.

Stale PIT table causing wrong surrogate key joins

A Point-In-Time table is supposed to map (customer_id, date) to the correct surrogate_key. It is a performance optimization: instead of doing a date-range join against the full dimension table at query time, you join to the PIT table which already has the right surrogate key for each date. The PIT table is built by a periodic refresh job that snapshots the dimension state.

The failure happens when the PIT refresh cadence does not match the rate of dimension changes. Suppose the PIT table refreshes daily at midnight. On March 27, the PIT reflects dimension state through March 27. On the morning of March 28, before the next refresh, customer ABC moves from the Northeast to the Southwest. The dimension gets a new row with a new surrogate key. The PIT table still maps March 28 to the old surrogate key because it has not refreshed yet.

Now consider a query that needs March 28 data. The query joins to the PIT table on date, gets back the old surrogate key, then joins to the dimension on that surrogate key. The dimension row it finds is the old Northeast row, not the new Southwest row. The March 28 sale gets attributed to the Northeast even though the customer moved that morning.

The impact on historical analysis is a mixing of old and new customer attributes across dates that should be cleanly separated. Reports by customer tier, customer region, or any Type 2 attribute will have inconsistencies that are hard to trace because the error is in the PIT table mapping, not in the dimension or fact data itself.

The problem is more pronounced for volatile dimensions where changes happen multiple times per day. A customer dimension where high-value customers can change tier at any point during business hours is particularly problematic for a once-daily PIT refresh.

Mitigation: Refresh PIT tables on a schedule that matches the change frequency of the underlying dimension. For volatile dimensions, refresh hourly or after every dimension load cycle. Alternatively, use a “latest only” PIT strategy that returns the current surrogate key for queries without a date filter, and only use the date-specific PIT mapping for historical queries where you need point-in-time accuracy. Document PIT staleness assumptions clearly so analysts know what the refresh lag means for their results.

Concurrent load corrupting Type 2 expiry dates

The race condition in concurrent Type 2 updates is the core problem. Two ETL jobs run simultaneously, both processing updates to the customer dimension. Both jobs read the same current row for customer ABC, with is_current = TRUE and expiry_date = '9999-12-31'. Both jobs independently decide to close that row and insert a new one with the updated attribute values.

Job 1 runs first, setting expiry_date = '2026-03-27 02:00:00' and is_current = FALSE, then inserts a new row with the new region value. Job 2 runs at almost the same time, sees the same original current row (because Job 1 has not yet committed), sets expiry_date = '2026-03-27 02:00:01' and is_current = FALSE, and inserts its own new row with the same region value. After both jobs commit, customer ABC has two current rows: both have is_current = TRUE and both claim to be the valid version.

The downstream impact is a Cartesian product on joins. A query that joins fact_sales to dim_customer_type2 on customer_id with WHERE is_current = TRUE returns two rows for customer ABC. If the fact table has one row for that customer on a given date, the query produces two result rows, doubling the revenue. Any aggregation, any distinct count, any grouping by customer attributes is wrong.

The is_current flag check is not atomic at the read level. Both jobs read the current row simultaneously before either has written the update that would make the other aware of the conflict. The database’s transaction isolation level does not prevent this unless you explicitly lock the row at read time.

Mitigation: Serialize Type 2 updates for the same natural key using a queue or row-level lock. In Snowflake or Redshift, use MERGE with a condition on is_current = TRUE and expiry_date = '9999-12-31' as part of the same transaction. The merge will fail for the second job because the row no longer matches the condition after the first job commits. You can also add a unique index on (customer_id, is_current) where is_current = TRUE to prevent duplicate current rows at the database level. The constraint will reject the second insert outright rather than allowing the corruption to propagate.

Slowly Changing Dimensions Quick Recap

• Type 1 overwrites: current value only, no history. Use for attributes where history does not matter.
• Type 2 adds rows: complete history preserved. Use for geography, tier, segment — anything that affects historical analysis.
• Type 3 stores current and previous as columns: good for comparing before/after on the most recent change only.
• Type 4 mini-dimension: extract frequently-changing attributes into a separate dimension to avoid scanning dozens of version rows.
• Type 6 hybrid: Type 1 + Type 2 in the same row for both current simplicity and historical accuracy.
• Change detection via hash_diff is faster than comparing every attribute individually.
• Dimension version explosion is a real problem: index strategically, use PIT tables, consider partitioning by customer_id range.

For more on dimensional modeling, see Kimball Dimensional Modeling for the foundational star schema approach, or Data Vault for enterprise-scale change tracking built into the architecture.

Slowly Changing Dimensions are a critical tool for accurate historical analysis. Choosing the right SCD type depends on your analytical requirements:

• Type 1 for attributes where current value is all that matters
• Type 2 for complete historical audit trails
• Type 3 when you need to compare before and after states
• Type 4 mini-dimensions for frequently changing attributes

Most enterprise dimensions use a combination of types. A customer dimension might use Type 2 for geography and customer tier, Type 1 for contact information, and Type 3 for comparison purposes.

The implementation complexity is real. Change detection, version management, and historical querying all require careful handling. But the alternative is reports that tell lies about your business.

For more on dimensional modeling, see Kimball Dimensional Modeling for the foundational star schema approach, or Data Vault for enterprise-scale change tracking built into the architecture.