In this lesson, you will learn how to lock down specific dimension attributes so that they remain immutable, preserving their original values for the entire history of your data warehouse.
The Concept of Type 0 Attributes
In data warehousing, we often face the challenge of source systems updating information (like a customer’s original birth date or the date a store first opened). While other dimension types track these changes (like Type 2 SCDs), a Type 0 attribute is designed to be "fixed" or "never-changing." Once a record is loaded into the dimension table, the value for that attribute is locked. Even if the source system changes, your dimension table will ignore those updates to preserve the integrity of the initial data.
Why Use Type 0?
This strategy is essential for attributes that define the origin of an entity. If you are analyzing performance based on when a customer first joined or where they were originally recruited, you don't want those values to change over time. If a source system "updates" a birth date, that is likely an error or a correction of data that shouldn't invalidate the historical reporting based on the initial value.
Implementation Logic
When performing your ETL (Extract, Transform, Load) process, you implement Type 0 by creating a conditional logic check. Before updating an existing record in your dimension table, the pipeline checks if the attribute is already populated. If it is, the pipeline simply ignores the new incoming value.
Consider this pseudo-code logic for updating a Dim_Customer table:
-- During ETL process
UPDATE Dim_Customer
SET
-- These columns can change (Type 1 or 2)
Current_Email = Stage_Customer.Email,
-- This is a Type 0 column: We only set it if it's NULL
Original_Sign_Up_Date = COALESCE(Dim_Customer.Original_Sign_Up_Date, Stage_Customer.Sign_Up_Date)
WHERE Customer_SK = Stage_Customer.Customer_SK;
By using COALESCE (or a CASE statement), you ensure that if Original_Sign_Up_Date already exists, it is never overwritten by incoming data from the source, effectively freezing that attribute in time.
Key Takeaway: Type 0 attributes ensure historical consistency by refusing to update once a value is established, treating the initial entry as the permanent truth regardless of source system changes.