Applying Late Arriving Dimension Patterns

In this lesson, you will learn how to architect your ETL pipelines to prevent data loss when a fact record arrives in your warehouse before the corresponding dimension record exists.

The Problem: The "Orphaned" Fact

In a perfect world, a customer creates an account, and that record is loaded into the Dim_Customer table before they make their first purchase. However, in reality, distributed systems often deliver transaction data (the Fact) faster than metadata (the Dimension). If your ETL blindly enforces referential integrity, it will reject the transaction because the Customer_SK does not exist yet.

The Strategy: Inferring Members

Instead of rejecting the data, we use a technique called Inferring Members. When an incoming fact references a Natural_Key (e.g., Customer_ID) that is not yet in Dim_Customer, the ETL process performs three steps:

1. Placeholder Creation: The ETL creates a "dummy" or "inferred" row in Dim_Customer using the Natural_Key. We set the other attributes to "Unknown" or "Pending."
2. Surrogate Key Assignment: The ETL assigns a new Surrogate_Key to this inferred record and uses it to load the Fact record successfully.
3. Late Update: When the actual dimension data eventually arrives later, the ETL performs an UPDATE rather than an INSERT, filling in the real attributes while keeping the Surrogate_Key intact.

Implementation Example

Here is the conceptual flow for an incoming transaction:

-- 1. Check if the Customer exists
IF NOT EXISTS (SELECT 1 FROM Dim_Customer WHERE Natural_Key = 'CUST-123')
BEGIN
    -- 2. Insert inferred placeholder
    INSERT INTO Dim_Customer (Natural_Key, Name, Status, Effective_Date)
    VALUES ('CUST-123', 'Pending/Unknown', 'Inferred', GETDATE());
END

-- 3. Retrieve the SK (either original or the new placeholder)
SET @Customer_SK = (SELECT Customer_SK FROM Dim_Customer WHERE Natural_Key = 'CUST-123');

-- 4. Load the fact record
INSERT INTO Fact_Sales (Customer_SK, Amount, Sales_Date)
VALUES (@Customer_SK, 150.00, GETDATE());

By using this approach, your fact tables remain complete, and your reports don’t suffer from missing revenue, even when upstream systems are out of sync.

Key Takeaway: Never reject fact data due to missing dimensions; instead, create inferred placeholder records to preserve transaction integrity, then update them when the definitive metadata arrives.