Handling the Row That Arrives Late

Handling late arriving data means processing facts that belong to an earlier business period but reach your system afterward. Preserve that distinction, then decide how the delay should affect reference lookups, stored facts, and published reports.

Keep Event Time Separate From Arrival Time

A sale can occur on one date and reach the warehouse on another. Those timestamps answer different questions. Replacing the event date with the load date makes processing easier by changing the business meaning.

CREATE TABLE #LateFacts
(
    EventId int PRIMARY KEY,
    CustomerCode nvarchar(20) NOT NULL,
    EventAt datetime2 NOT NULL,
    ArrivedAtUtc datetime2 NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #LateFacts VALUES
 (1, N'C100', '2026-01-10T12:00:00', '2026-02-02T08:00:00', 25.00);
SELECT EventId, EventAt, ArrivedAtUtc,
       DATEDIFF(day, EventAt, ArrivedAtUtc) AS calendar_boundaries_crossed
FROM #LateFacts;

The interval expression counts day boundaries, not exact elapsed 24-hour periods. Real comparisons also need a consistent time-zone interpretation. The example uses invented values to make the two clocks visible.

Recognize a Missing Dimension Member

A fact can arrive before its customer or product reference record. Rejecting it forever loses valid business activity, while inventing a full description creates false information. An inferred member can preserve the known business key until details arrive.

CREATE TABLE #CustomerDimension
(
    CustomerKey int IDENTITY PRIMARY KEY,
    CustomerCode nvarchar(20) NOT NULL UNIQUE,
    CustomerName nvarchar(100) NULL,
    IsInferred bit NOT NULL
);
INSERT #CustomerDimension(CustomerCode, CustomerName, IsInferred)
SELECT DISTINCT f.CustomerCode, NULL, 1
FROM #LateFacts AS f
WHERE NOT EXISTS
 (SELECT 1 FROM #CustomerDimension AS d
  WHERE d.CustomerCode = f.CustomerCode);

This single-session example creates a placeholder for the known code. Production code needs concurrency protection when several workers can discover the same missing member. Keep a unique constraint as a final boundary.

An inferred member is different from one generic unknown row. It preserves a specific business identity so later details can complete that member. Use the generic unknown only when that is the agreed meaning.

Complete the Placeholder Deliberately

When the reference details arrive, update the inferred member according to your dimension policy. Preserve the surrogate key if existing facts already reference it. That avoids rewriting facts merely to replace a placeholder description.

UPDATE #CustomerDimension
SET CustomerName = N'Example Customer', IsInferred = 0
WHERE CustomerCode = N'C100' AND IsInferred = 1;
SELECT CustomerKey, CustomerCode, CustomerName, IsInferred
FROM #CustomerDimension;

A type-two historical dimension needs additional care. The arriving details may describe a particular effective interval rather than the customer's entire history. Do not overwrite past meaning without understanding the dates.

Look Up the Historically Correct Version

For historical dimensions, the current customer row may be wrong for an old event. Match the business key and the event's effective interval. Use nonoverlapping intervals and an explicit boundary convention.

WITH History AS
(
    SELECT * FROM (VALUES
      (10, N'C100', CONVERT(date, '20250101', 112), CONVERT(date, '20260201', 112)),
      (11, N'C100', CONVERT(date, '20260201', 112), CONVERT(date, '99991231', 112))
    ) AS v(CustomerKey, CustomerCode, ValidFrom, ValidTo)
)
SELECT f.EventId, h.CustomerKey
FROM #LateFacts AS f
LEFT JOIN History AS h
  ON h.CustomerCode = f.CustomerCode
 AND f.EventAt >= h.ValidFrom
 AND f.EventAt < h.ValidTo;

The interval includes its start and excludes its end. Check for gaps and overlaps, since either can produce a miss or multiple matches. A lookup returning a key is not enough unless it is the correct historical key.

Use Replay Windows With Defined Limits

Reprocessing a recent window can capture delayed or corrected data when the target application is idempotent. Define the window from observed source behavior and business tolerance. Do not assume every correction arrives within it.

Keep a separate route for events older than the routine replay period. That may involve a targeted backfill or an approved historical correction. Record which published periods could change.

A repeated event needs a stable identifier and a clear replacement rule. Otherwise, replay adds the same amount twice. Reconcile the affected keys and aggregates after applying the correction.

Decide Whether Published History Can Change

Operational dashboards may accept corrected historical totals, while closed reporting periods may require adjustments rather than restatement. This is a business decision. The load should implement the agreed policy explicitly.

Retain the original event, arrival, and correction context where the design requires it. Tell downstream consumers when a historical refresh is needed. A late row is manageable when its timing and consequences remain visible.

Late data is not automatically bad data, it is data that needs a time-aware policy.

This post was rewritten from scratch in September 2026. The original, published on 2011-11-29, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

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