In this lesson, you will learn how to ensure that your fact tables correctly reference the appropriate surrogate key of a dimension record based on the exact transaction time.
The Challenge of Time-Variant Keys
In a star schema, fact tables store foreign keys that point to the primary surrogate keys of your dimension tables. When you implement Slowly Changing Dimensions (like Type 2 row versioning), a single business entity (e.g., a customer) might have multiple records in the dimension table over time, each with a different surrogate key.
If you have a transaction that occurred on October 1st, but your ETL process updates the customer's record on October 5th, your fact table must link to the surrogate key that was active on October 1st. If you accidentally point to the "current" record (the one created on October 5th), you break the historical accuracy of your data.
Ensuring Integrity During ETL
To maintain referential integrity, your ETL pipeline must perform a "lookup" that is constrained by time. You aren't just looking for the customer; you are looking for the customer's version that was valid during the event window.
Consider this logic for a Sales Fact load:
INSERT INTO Fact_Sales (Date_SK, Customer_SK, Product_SK, Sales_Amount)
SELECT
d.Date_SK,
c.Customer_SK,
p.Product_SK,
stg.Amount
FROM Staging_Sales stg
JOIN Dim_Date d ON stg.Transaction_Date = d.Date
JOIN Dim_Customer c ON stg.Customer_ID = c.Customer_ID
AND stg.Transaction_Date BETWEEN c.Row_Start_Date AND c.Row_End_Date
JOIN Dim_Product p ON stg.Product_ID = p.Product_ID
AND stg.Transaction_Date BETWEEN p.Row_Start_Date AND p.Row_End_Date;
By adding the BETWEEN clause in your join logic, you force the fact table to select only the surrogate key that existed during the transaction. If your lookup returns no record (or multiple records), you have an integrity issue that needs to be handled before the data hits your production tables. Failing to enforce this specific alignment leads to "lost history," where sales are incorrectly attributed to a customer's current status rather than their status at the time of purchase.
Key Takeaway: Always join your fact records to dimension surrogate keys using a time-range filter to ensure the fact accurately reflects the state of the business at the moment of the transaction.