Identifying Business Requirements for Historical Tracking

In this lesson, you will learn how to distinguish between dimension attributes that require a "memory" of past values and those that only need to reflect the most recent state.

When building a data warehouse, we often assume every attribute needs to be tracked historically. However, doing this for every column leads to massive, unmanageable dimension tables. The goal is to separate historical attributes (which drive trend analysis) from current-state attributes (which only support categorization).

Defining the Requirement

Before modeling, you must ask the business: "If a customer changes their home address, do we need to attribute their past sales to the old city, or should all their sales reflect their new city?"

1. Attributes Requiring History (Slowly Changing): These are dimensions where the business requires a "snapshot" of the value at the time an event occurred. Common examples include Job_Title, Region, or Customer_Segment. If these change, you must track them so that when you query a fact table, you can see how sales performed for a specific Job_Title at that specific time.
2. Attributes Requiring Only Current State: These are often descriptive fields where historical accuracy is irrelevant to performance reporting. Examples include Email_Address, Phone_Number, or Secondary_Contact_Name. Overwriting these (or keeping only the current value) saves storage and simplifies your query logic.

Example: Customer Dimension

Imagine a Customer dimension. If we track everything, the table grows every time someone changes their email:

-- Historical Requirement: Track 'Region' to see sales trends by location.
-- Current State Requirement: 'Email' is just for contact; history is unnecessary.

CREATE TABLE Dim_Customer (
    Customer_SK INT PRIMARY KEY,
    Customer_ID INT,
    Region VARCHAR(50),      -- Historical Tracking Needed
    Email VARCHAR(100),      -- Current State Only
    Effective_Date DATE,
    End_Date DATE
);

If the Email changes, we simply update the existing row for that Customer_ID. If the Region changes, we expire the old record and create a new one to preserve the historical association. Identifying this distinction early prevents "Dimension Bloat" and keeps your analytical queries running fast.

Key Takeaway: Only preserve history for attributes that provide analytical value in longitudinal reporting; use current-state overrides for purely descriptive contact or administrative information.