A fact is late-arriving when it lands in the warehouse after the period it describes has already been reported on. A refund processed in April for a February order. A subscription cancellation backdated to last quarter for billing reasons. A correction from a finance close that adjusts last month’s revenue. These are not rare events. In most operational systems they are the steady-state behavior of the data, and pretending otherwise is the source of more silently-wrong dashboards than any other modeling mistake.
The wrong response is to treat late-arriving facts as exceptions to be handled with one-off scripts when finance complains. The right response is to assume every fact is potentially late and design the warehouse so that re-statement is a normal operation rather than a panic.
Why this is the default, not the exception
Operational systems do not promise immediate consistency with the warehouse. They promise eventual consistency with themselves. A payment gateway reconciles batches overnight. A CRM lets users edit historical fields. A subscription billing engine retroactively prorates when a plan changes mid-cycle. Every one of these creates facts that, from the warehouse’s perspective, “happened” at one moment but “arrived” at another.
If your warehouse models facts purely on the moment they happened — the business date — then a refund processed in April for a February order silently rewrites February’s revenue when the next pipeline run picks it up. The dashboard the CFO showed the board last week is now wrong, and nobody knows.
If your warehouse models facts purely on the moment they arrived — the load date — then April’s revenue is inflated by February events that happen to have landed in April, and the month-over-month comparison is meaningless.
Both are wrong because they are trying to compress two distinct timestamps into one. A late-arriving fact has, at minimum, three: the business date (when did this thing happen in the world), the system date (when did the source system record it), and the load date (when did the warehouse see it). Any honest model preserves all three.
The two-axis fact table
The pattern that survives this is what dimensional practitioners call a bi-temporal fact: every fact carries both an effective date (the business event time) and a recorded date (the time the warehouse first saw the fact in this version). Updates do not overwrite; they insert a new row with the same effective date and a later recorded date.
This sounds expensive and it is mildly expensive. It also makes every reporting question well-posed. “What was February revenue as we knew it on March 5th?” is a query you can answer. “What is February revenue as we know it today?” is a different query you can also answer. “What changed in our view of February between March 5th and today?” is a third query, and it is the query that surfaces every silent restatement that would otherwise go unnoticed.
The schema is unromantic: the fact table has the natural key of the source event, the effective date, the recorded date, the measures, and a dimension key set. A typical convention is valid_from and valid_to columns on each row, with the latest version having valid_to = '9999-12-31'. Each new arrival of the same source event closes out the previous row and inserts a new one.
What this changes about reporting
The disciplined version of this model forces every report to declare which version of the truth it is using. Most reports default to “current truth” — the latest known recorded date for each effective date. That is the right default for an executive dashboard that should reflect what we know today.
But the close report, the regulatory filing, and the board deck need a different default: “as-of truth” — the version of facts as we knew them on the date the report was originally produced. A board deck shown on March 5th should be reproducible on April 30th, even if April brought a wave of late-arriving February facts. The bi-temporal model lets you write that query in one line. A model that overwrites does not let you write that query at all.
The contract this enforces is: you cannot accidentally rewrite history. Every restatement is visible, queryable, and dated. Finance gets a clean audit trail of when each correction landed. Analysts get a way to reconcile two reports that disagree about the same period.
What it costs
The honest accounting: bi-temporal fact tables are roughly 20 to 40 percent larger than overwrite tables in steady state, depending on how often facts are revised. The query layer needs a discipline — every report selects a temporal filter, either current or as-of. Most teams handle this with a thin macro in dbt that wraps the temporal logic; the analyst writing a model never thinks about it directly.
The cost is real but small relative to the failure mode it prevents. The failure mode is silent data drift: a quarterly metric that “moves” between board meetings without anyone being able to explain why. That kind of drift erodes trust in the data function faster than any other failure, because it is invisible until someone notices, and once someone notices, every prior number is suspect.
Where to draw the line
Not every fact needs bi-temporal modeling. The threshold is whether the fact is reported on. Operational events that exist only to drive an ML feature, or to populate an internal dashboard nobody references for decisions, can be modeled simply. The minute a fact lands in a board deck, a regulatory filing, an investor update, or a metric the company makes commitments against, it needs the discipline.
The practical rule: any fact that touches revenue, customer count, retention, or any externally-reported metric is bi-temporal by default. Everything else can be evaluated case by case.
The structural point
Late-arriving facts are not a data quality problem. They are how the world works. Every operational system you integrate with will produce them, and the rate at which they arrive will not decrease. The warehouse’s job is to model the world honestly, which means modeling the fact that “what we knew” and “what was true” are two different timelines that converge slowly.
A warehouse that respects this distinction earns trust. A warehouse that pretends every fact is fully known at the moment it first lands forfeits trust the first time finance produces a corrected number. The first design is harder up front. The second design is harder forever.