Handling Late Arriving Fact Records

In this lesson, you will learn how to accurately incorporate event data that reaches your warehouse long after the transaction actually occurred, ensuring your historical snapshots remain reliable.

The Challenge of Time-Travel

In an ideal world, every event is recorded at the exact moment it happens. In reality, systems go offline, mobile apps stay disconnected, or batch uploads fail. When a record arrives days later, its "event date" points to the past. If you simply insert it into your current processing batch, your reports for those past dates will be outdated, and your historical snapshots—which have already been "closed"—will be inconsistent.

The Pattern: The "Placeholder" Strategy

To maintain accuracy, we use a pattern called Late-Arriving Fact Handling. The goal is to allow the record to be added to the historical period where it belongs without breaking the integrity of your current reporting.

When you identify a late-arriving record, you follow these steps:

1. Identify the Grain: Determine the event’s original date.
2. Retrieve the Dimension Key: Use the event_date to look up the correct surrogate_key for that specific point in time (especially important if using Slowly Changing Dimensions).
3. Re-process or Append: Insert the record directly into the fact table using its original timestamp.

-- Example: Inserting a late order from last month
INSERT INTO fact_sales (
    date_key, product_key, customer_key, sale_amount
)
VALUES (
    (SELECT date_key FROM dim_date WHERE full_date = '2023-10-15'), 
    101, 5005, 99.99
);

Preserving Snapshot Integrity

If you use Periodic Snapshot Fact Tables (which aggregate data like "Monthly Sales"), you have a choice:

• The "Stale" Approach: You accept the data is late and leave past snapshots as they were, only updating the "Current Month" snapshot.
• The "Restatement" Approach: You re-run the aggregation logic for the affected historical period to update those snapshot records.

The key is consistency. By using the date_key of the actual event rather than the date_key of the arrival time, you ensure that any query grouping by time returns the correct business results, even if the data arrived "late."

Key Takeaway: Always process late-arriving facts using their original event date to ensure historical accuracy, even if it requires re-calculating affected periodic snapshots.