Late-Arriving Dimension Rows: Inferred Members in a Data Warehouse

The sale arrived overnight, but the customer details did not. A late-arriving dimension should not force you to lose the sale or attach it to the wrong customer. An inferred member preserves the business key and gives the fact a stable surrogate key while attributes wait.

Separate Unknown Identity From Missing Attributes

A fact can carry a valid customer business key before the customer feed supplies the name and other attributes. Create a placeholder for that specific business key. That differs from one generic unknown customer row used when identity itself is unavailable. A specific inferred member can be completed later without moving facts to another surrogate key.

I check which part is missing before selecting the pattern. Do you know the customer's durable business key, or only that someone made a purchase? Those are different situations. Keep an explicit unknown identity policy for the second case. Never invent a business key from a display name just to make the dimension lookup return one row.

Prepare the Dimension for Late-Arriving Rows

Run these permanent examples in a disposable database. The surrogate key is generated independently from the business key. A unique business key protects this simple current customer model. IsInferred states whether attributes still await the source feed. FirstSeenUtc and CompletedAtUtc make the waiting period visible without pretending the placeholder's generic name is a real customer attribute.

CREATE TABLE dbo.DimCustomer
(
    CustomerKey int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DimCustomer PRIMARY KEY,
    CustomerCode nvarchar(40) NOT NULL CONSTRAINT UQ_DimCustomer_Code UNIQUE,
    CustomerName nvarchar(100) NOT NULL,
    IsInferred bit NOT NULL,
    FirstSeenUtc datetime2(0) NOT NULL,
    CompletedAtUtc datetime2(0) NULL
);
CREATE TABLE dbo.FactSale
(
    SaleID bigint NOT NULL CONSTRAINT PK_FactSale PRIMARY KEY,
    CustomerKey int NOT NULL,
    SoldAtUtc datetime2(0) NOT NULL,
    Amount decimal(19,4) NOT NULL,
    CONSTRAINT FK_FactSale_Customer FOREIGN KEY(CustomerKey)
        REFERENCES dbo.DimCustomer(CustomerKey)
);
CREATE INDEX IX_FactSale_CustomerKey ON dbo.FactSale(CustomerKey);
CREATE TABLE #IncomingSale
    (SaleID bigint NOT NULL PRIMARY KEY,CustomerCode nvarchar(40) NOT NULL,
     SoldAtUtc datetime2(0) NOT NULL,Amount decimal(19,4) NOT NULL);
INSERT #IncomingSale VALUES
    (1,N'C100','2026-09-01T10:00:00',25.0000),
    (2,N'C100','2026-09-01T11:00:00',40.0000),
    (3,N'C200','2026-09-01T12:00:00',15.0000);

These values are fictional demonstration inputs. Run the temporary staging examples in the same connection. Validate source keys for blanks, length, and normalization before loading. The target collation determines whether two differently cased customer codes are equal. Make that rule agree with the source system instead of discovering the mismatch through an unexpected unique constraint error.

Load Late-Arriving Dimension Keys Before the Facts

Collect missing business keys from the incoming batch and insert one placeholder per key. The transaction keeps member allocation and fact loading together. UPDLOCK and HOLDLOCK protect the absence check under a locking protocol, while the unique constraint provides the final identity rule. Concurrent loaders still need deadlock handling and a bounded retry policy at the load boundary.

SET XACT_ABORT ON;
DECLARE @load_time datetime2(0)=SYSUTCDATETIME();
BEGIN TRY
    BEGIN TRAN;
    INSERT dbo.DimCustomer
        (CustomerCode,CustomerName,IsInferred,FirstSeenUtc,CompletedAtUtc)
    SELECT DISTINCT i.CustomerCode,N'(attributes pending)',1,@load_time,NULL
    FROM #IncomingSale AS i
    WHERE NOT EXISTS
    (
        SELECT 1 FROM dbo.DimCustomer AS d WITH(UPDLOCK,HOLDLOCK)
        WHERE d.CustomerCode=i.CustomerCode
    );
    INSERT dbo.FactSale(SaleID,CustomerKey,SoldAtUtc,Amount)
    SELECT i.SaleID,d.CustomerKey,i.SoldAtUtc,i.Amount
    FROM #IncomingSale AS i
    JOIN dbo.DimCustomer AS d ON d.CustomerCode=i.CustomerCode
    WHERE NOT EXISTS
    (
        SELECT 1 FROM dbo.FactSale AS f WITH(UPDLOCK,HOLDLOCK)
        WHERE f.SaleID=i.SaleID
    );
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT>0 ROLLBACK;
    THROW;
END CATCH;

The SaleID check prevents inserting the same source sale again during a simple retry. It does not define what to do when a replay changes an existing sale. Reject or explicitly reconcile those changes before loading. I keep the source sale identifier stable throughout the pipeline so retries do not create apparently new purchases that merely repeat old work.

Detect Changed Replays Instead of Hiding Them

Compare staged rows with already stored facts. A different customer code, timestamp, or amount requires an explicit correction policy. Skipping the row because SaleID exists would hide that change. Capture the discrepancy with its source key and let the approved load process decide whether to correct, reject, or retain the original record.

SELECT i.SaleID,i.CustomerCode AS IncomingCustomer,d.CustomerCode AS StoredCustomer,
       i.Amount AS IncomingAmount,f.Amount AS StoredAmount
FROM #IncomingSale AS i
JOIN dbo.FactSale AS f ON f.SaleID=i.SaleID
JOIN dbo.DimCustomer AS d ON d.CustomerKey=f.CustomerKey
WHERE i.CustomerCode<>d.CustomerCode OR i.SoldAtUtc<>f.SoldAtUtc OR i.Amount<>f.Amount;

Compare every field that defines the fact's business meaning in the real pipeline. A sample query should not become an accidental complete reconciliation rule after more columns are added. Keep the rejection and accepted populations visible. A load that hides corrections can produce a perfectly balanced row count with the wrong amounts attached to the wrong customer.

Fill the Placeholder in Place

When the customer feed arrives, update the existing inferred row by business key. Keep CustomerKey unchanged. Mark completion explicitly and retain the original first seen time. This example completes one fictional customer, leaving another awaiting attributes so the following monitoring query has a meaningful condition to inspect.

CREATE TABLE #IncomingCustomer
    (CustomerCode nvarchar(40) NOT NULL PRIMARY KEY,CustomerName nvarchar(100) NOT NULL);
INSERT #IncomingCustomer VALUES(N'C100',N'Sample Customer');
UPDATE d
SET CustomerName=i.CustomerName,IsInferred=0,CompletedAtUtc=SYSUTCDATETIME()
FROM dbo.DimCustomer AS d
JOIN #IncomingCustomer AS i ON i.CustomerCode=d.CustomerCode
WHERE d.IsInferred=1;

I verify that completion changes attributes without replacing identity. Deleting the stub and creating another row would require moving fact references and introduces unnecessary risk. The normal dimension pipeline must also handle customers that arrive before any fact. Keep both paths coordinated around the same unique business key and the same reviewed attribute rules.

Keep History Rules Deliberate

This example models one current dimension row per business key. A slowly changing type two dimension needs effective dates and a rule for assigning a fact to the correct historical member. Completing a stub is different from recording a later business change. Do not generate an extra historical version merely because the placeholder's attributes were initially unknown.

Determine whether the late feed describes attributes at sale time or current attributes. If the source cannot establish historical truth, label the limitation rather than manufacture it. A late-arriving dimension can preserve referential integrity while some descriptive history remains uncertain. The flag exposes that uncertainty instead of allowing a generic placeholder to look like a completed business record.

Report Late-Arriving Dimension Members Still Waiting

Count unresolved inferred members and list their age and attached facts. COUNT on the fact identifier avoids counting the empty side of a left join as a sale. Establish an expected source delivery window with the feed owner, then investigate members outside it. Do not choose a universal age threshold without understanding the upstream process.

SELECT COUNT_BIG(*) AS PendingMembers
FROM dbo.DimCustomer WHERE IsInferred=1;
SELECT d.CustomerKey,d.CustomerCode,d.FirstSeenUtc,
       DATEDIFF_BIG(hour,d.FirstSeenUtc,SYSUTCDATETIME()) AS WaitingHours,
       COUNT_BIG(f.SaleID) AS AttachedFacts
FROM dbo.DimCustomer AS d
LEFT JOIN dbo.FactSale AS f ON f.CustomerKey=d.CustomerKey
WHERE d.IsInferred=1
GROUP BY d.CustomerKey,d.CustomerCode,d.FirstSeenUtc
ORDER BY d.FirstSeenUtc;

An old late-arriving dimension placeholder can indicate a missing extract, changed key, or source record that will never arrive. Assign each exception to an owner and preserve the decision. A warehouse can carry a placeholder indefinitely, but the report should not pretend patience is a data quality strategy. Track resolved and unresolved members separately from ordinary load success.

Test Arrival Order and Repeated Loads

Rehearse facts first, dimensions first, repeated batches, several facts for one missing customer, and two concurrent loaders. Test changed replays and invalid business keys too. Reconcile fact populations and amounts before and after member completion. The surrogate key should remain stable when the stub is filled, and foreign keys should stay valid throughout the process.

I use inferred members to keep a valid fact load moving without hiding missing attributes. The durable business key gives later data somewhere precise to land. Controlled loading and an aging report keep the temporary state accountable. That combination preserves the sale today and makes tomorrow's customer detail an update rather than another identity puzzle.

Related reading on this blog: Slowly Changing Dimensions in Plain T-SQL and Surrogate Keys and Natural Keys.

An inferred member is not a fabricated customer, it is a stable place for attributes still on the way.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.