Handling Late Arriving Dimension Data

In this lesson, you will learn how to maintain referential integrity when your fact records arrive before the corresponding dimension records in your data warehouse.

The Challenge

In a perfect world, a dimension member (like a new Product or Customer) is created before any sales are recorded against it. However, in real-world systems, a FactSales record might arrive at midnight containing a product_id that doesn't exist in your DimProduct table yet. If you have strict foreign key constraints or just want to avoid "orphaned" facts, you cannot simply drop this record.

The Strategy: "Inferred Members"

To handle this, we use a strategy called Inferred Members. When the ETL process detects a business_key in a fact table that does not exist in the dimension, instead of failing, the system creates a placeholder record in the dimension table.

1. Create the Placeholder: Insert a new row into DimProduct using the unknown business_key. Set the attributes to "Unknown" or "TBD".
2. Assign the Surrogate Key: Generate a new surrogate_key for this placeholder row and use it to load the fact record.
3. Backfill Later: When the actual dimension data eventually arrives, you perform an update (Type 1 overwrite) on that specific placeholder row to fill in the correct product details, keeping the surrogate_key the same.

Example Logic

-- Checking for missing products
INSERT INTO DimProduct (product_key, product_id, product_name, status)
SELECT 
    next_val(dim_seq), 
    f.product_id, 
    'Unknown Product', 
    'Inferred'
FROM FactSales f
LEFT JOIN DimProduct d ON f.product_id = d.product_id
WHERE d.product_id IS NULL;

By using this approach, your FactSales table remains linked to a valid surrogate_key at all times. Users can still sum up total sales for "Unknown Products," and once the data arrives, those sales will automatically be attributed to the correct product without needing to re-process the historical fact table. This ensures your Star Schema remains structurally sound and queryable even when source systems are out of sync.

Key Takeaway: Use inferred members to create placeholder records in your dimension tables, allowing fact records to be processed immediately while keeping your data integrity intact for future updates.