Late-Arriving Data: The Warehouse After Hours

How to handle facts that arrive before their dimensions, dimension changes that arrive after the events they describe, and the inferred-member and re-windowing patterns that keep history honest.

Warehouses like clean timelines. Source systems do not provide them.

An order can arrive before the customer record it references. A shipment correction can show up after the month has closed. A customer address change can be back-dated because it was effective last week but entered today. If the model assumes records arrive in the same order the business experienced them, the first bad day will teach you otherwise — usually in front of someone who has already sent the numbers to a board.

Late-arriving data is the family of patterns for those days. The last chapter ended on a promise: that a changelog-derived Type 2 dimension can be rebuilt to absorb a back-dated change, and that this property is the whole subject of the chapter you’re reading. It is. But before we can cash that promise we have to separate two problems that look alike and cure differently.

There are two main cases. Late-arriving facts, where the event shows up before its dimension row exists at all — you have a foreign key with nothing to point at. And late-arriving dimension changes, where the descriptive history itself changes after facts have already been loaded against it — the row exists, but the version the fact should have joined to didn’t, or existed with the wrong window.

The first is a missing member problem. The second is a misplaced boundary problem. They share a root cause — the business experienced things in an order the warehouse learned them in a different order — and almost nothing else. Solve them with the same tool and you’ll corrupt one while fixing the other.

Late facts need somewhere to point

Suppose an order line arrives with customer_id = 8812, but the customer snapshot does not contain customer 8812 yet. Maybe the order-capture system and the customer master replicate on different schedules; maybe a new customer’s first act was to place an order and the master hasn’t caught up. The fact has a real, meaningful natural key. The dimension row is missing.

The naive join drops the order:

select
    order_items.order_item_id,
    customers.customer_sk
from {{ ref('stg_order_items') }} as order_items
inner join {{ ref('dim_customer_history') }} as customers
    on  order_items.customer_id = customers.customer_id
    and order_items.order_date >= customers.valid_from
    and order_items.order_date <  customers.valid_to

That is reconciliation poison. The sale happened; revenue moved; a book left a warehouse. The star schema reports the total as if none of it occurred, because a descriptive table arrived late. And it fails silently — an inner join that drops a row leaves no error, no null, no log line. The number is just quietly wrong, and it stays wrong until someone reconciles against the source system and finds the gap. That reconciliation is expensive precisely because nothing pointed at the problem.

The first rule of late facts, then, is never let a missing dimension drop a fact. Switch the inner join for a left join and route the miss to a member that always exists. Back in the dimensions chapter every dim_ table got a reserved unknown member — the -1 row, its surrogate key a hash of a literal '-1' — for exactly this moment:

coalesce(
    customers.customer_sk,
    {{ dbt_utils.generate_surrogate_key(["'-1'"]) }}
) as customer_sk

The unknown member keeps the fact visible. Totals reconcile even when attribution is incomplete; the report shows “Unknown customer” as a line rather than shorting the grand total. That’s the floor of correctness: facts survive, numbers foot, and the incompleteness is legible instead of hidden.

But the -1 member throws away something you were holding. You know this order belongs to customer 8812. Collapsing it into a single anonymous bucket alongside every other unresolved order means that when 8812 finally lands, nothing reconnects. The order stays “Unknown” forever, or someone runs a manual repair. There is a better move when the natural key is present.

Inferred members preserve the identity

An inferred member is a placeholder dimension row minted from the fact itself. The moment a fact references a customer the dimension has never seen, you insert a row for that customer — natural key populated, descriptive attributes defaulted to “Unknown,” and a flag that says “this is a stand-in, not a real record yet”:

customer_sk       customer_id   full_name              city      is_inferred
hash(8812)        8812          Unknown (inferred)     Unknown   true

The fact points at this row. Its foreign key is never null, and — this is the whole trick — the customer_sk on that inferred row is the deterministic hash of the natural key, generate_surrogate_key(['customer_id']), exactly the key the fact computed for itself back in chapter 4. The fact and the placeholder agree on the surrogate key without ever having met, because they both derived it from 8812.

That agreement is what makes the back-fill free. When the real customer 8812 lands in the source — with a name, a city, a segment — the dimension rebuilds, produces a real row for 8812 whose customer_sk is hash(8812), and the inferred placeholder simply stops being generated. The fact’s foreign key already equals hash(8812); it now resolves to the real row instead of the placeholder, and every report that grouped the order under “Unknown (inferred)” silently reattributes to the real customer. Nothing about the fact changed. The FK was durable from the start; only the thing it pointed at improved.

This is the central tradeoff between the two members:

  • The -1 unknown member is for when the business identity is genuinely absent — a null key, a corrupted key, a walk-in with no account. There’s nothing to reconnect to, so one shared bucket is honest.
  • The inferred member is for when the natural key is present and meaningful but the descriptive row is merely late. It preserves the identity so the eventual arrival heals the history on its own.

Use both. The left join coalesces a genuinely-null key to -1; a present-but-unknown key gets an inferred member. They are not competitors; they cover different failure modes on the same load.

Building inferred members in dbt

Here’s the pattern for the bookshop, built into the Type 2 customer dimension itself so that as-of range joins resolve, not just current-state ones. Recall from the last chapter that dim_customer_history is the versioned table (built on customers_snapshot, exposing valid_from / valid_to / is_current / the durable customer_sk), and dim_customer is the thin where is_current view over it. We extend the history model to union real versions with inferred placeholders:

-- models/marts/dim_customer_history.sql  (extended with inferred members)
{{ config(materialized='table') }}

with versions as (
    select
        {{ dbt_utils.generate_surrogate_key(['customer_id', 'dbt_valid_from']) }} as customer_version_sk,
        {{ dbt_utils.generate_surrogate_key(['customer_id']) }}                   as customer_sk,
        customer_id,
        full_name,
        email,
        city,
        country,
        segment,
        dbt_valid_from                       as valid_from,
        dbt_valid_to                         as valid_to,
        dbt_valid_to = to_date('9999-12-31') as is_current,
        false                                as is_inferred
    from {{ ref('customers_snapshot') }}
),

-- natural keys that facts reference but the snapshot has never seen
missing_keys as (
    select distinct order_items.customer_id
    from {{ ref('stg_order_items') }} as order_items
    left join versions
        on order_items.customer_id = versions.customer_id
    where versions.customer_id is null
      and order_items.customer_id is not null
),

inferred as (
    select
        {{ dbt_utils.generate_surrogate_key(['customer_id', "'inferred'"]) }} as customer_version_sk,
        {{ dbt_utils.generate_surrogate_key(['customer_id']) }}               as customer_sk,
        customer_id,
        'Unknown (inferred)'       as full_name,
        cast(null as varchar)      as email,
        'Unknown'                  as city,
        'Unknown'                  as country,
        'UNKNOWN'                  as segment,
        cast('1900-01-01' as date) as valid_from,
        to_date('9999-12-31')      as valid_to,
        true                       as is_current,
        true                       as is_inferred
    from missing_keys
)

select * from versions
union all
select * from inferred

Two details are load-bearing. First, the inferred row’s window runs from a floor date (1900-01-01) to the sentinel — it spans all time on purpose, so that a fact of any order_date falls inside it. An inferred member has no version history; it’s a single open row standing in for a customer whose real windows aren’t known yet, and it must catch every fact that references the key regardless of when the order happened. Second, the inferred customer_version_sk hashes in the literal 'inferred' so it can never collide with a real version’s key (which hashes the natural key plus dbt_valid_from). The durable customer_sk, though, is deliberately identical to the eventual real row’s — that’s the reconnection seam.

The missing_keys CTE is doing inferred-member detection: it’s the set of customer natural keys that appear in facts but nowhere in the snapshot. Anything in that set gets a placeholder. Anything that leaves the set — because the real customer finally arrived — stops getting one, automatically, on the next dbt build.

The back-fill happens for free

Watch a single customer move through the lifecycle. On Monday, order line references customer 8812; the snapshot has never heard of them. missing_keys contains 8812, inferred mints a placeholder, and the fact — which computed customer_sk = hash(8812) at load time — joins cleanly to it. A revenue report shows the order under “Unknown (inferred).”

On Thursday the customer master catches up and the snapshot captures 8812: name “Ada Okafor,” city “Lagos,” segment “TRADE.” Now versions contains a real row for 8812 with customer_sk = hash(8812). The left join in missing_keys finds a match, so 8812 drops out of the missing set; inferred no longer produces a placeholder. The same dbt build that ingested the real customer also retired the stand-in. Thursday’s revenue report shows the identical order under “Ada Okafor · Lagos,” with zero fact rows touched.

The as-of join that both reports run is the standard half-open range join against dim_customer_history:

select
    orders.order_id,
    customers.city,
    customers.is_inferred,
    sum(orders.extended_price) as revenue
from {{ ref('fct_order_items') }} as orders
join {{ ref('dim_customer_history') }} as customers
  on  orders.customer_sk = customers.customer_sk
  and orders.order_date >= customers.valid_from
  and orders.order_date <  customers.valid_to
group by 1, 2, 3

Selecting is_inferred in the report — or filtering where not is_inferred for a clean view, or where is_inferred for an operational worklist — is the flag paying off. It tells consumers exactly which rows are provisional, and it gives operations a query for “how many inferred members are still unresolved, and how old are they?” An inferred member that has sat unresolved for two weeks is not a modeling curiosity; it’s a broken replication feed with a dollar figure attached, and the flag is what surfaces it.

Note the join key is the durable customer_sk and the predicate is the half-open window (>= valid_from, < valid_to) against the sentinel — the same contract from the last chapter. This is the durable-key-on-the-fact design at work: the fact stores only the durable key and its own event date, and the query resolves the version. That choice is about to matter enormously for the second half of the problem.

Late dimension changes are the harder half

Now flip the problem. The dimension row exists. What arrives late is a change to it — and worse, a change that belongs in the past.

The order arrived on June 10. At the time, the customer lived in Boston, and the fact was correctly loaded. On June 20, the source sends a correction: the customer moved to Denver, effective June 1. The move is real, it’s dated, and it predates an order you’ve already attributed to Boston. The June 10 order should report as Denver, because on June 10 the customer already lived there — the business just didn’t tell you until the 20th.

This is categorically harder than a late fact, for two reasons. First, there’s no null to catch — the join succeeded, to a version that existed, producing a plausible-looking answer that happens to be wrong. Second, fixing it means editing history that other rows already depend on. If a later change also exists — say the customer moved again to Austin on July 1, and that version is already recorded — then absorbing the June 1 Denver change means splitting and re-windowing an established sequence:

Before the correction lands:
  Boston  valid_from 2026-01-05   valid_to 2026-07-01
  Austin  valid_from 2026-07-01   valid_to 9999-12-31

After Denver (effective 2026-06-01) is discovered:
  Boston  valid_from 2026-01-05   valid_to 2026-06-01
  Denver  valid_from 2026-06-01   valid_to 2026-07-01
  Austin  valid_from 2026-07-01   valid_to 9999-12-31

Boston’s window has to contract — its valid_to moves earlier — and a brand-new Denver window has to slot in between two versions that already exist, without leaving a gap or an overlap. This is surgery on a tiled timeline, and it’s exactly the operation dbt snapshots cannot perform.

Why snapshots can’t reconstruct a back-dated change

It’s worth being precise about why, because the failure is structural, not a configuration you forgot to set.

A snapshot answers one question per run: does the source’s current state differ from the latest version I recorded? If yes, it closes the open version at “now” (or at the source’s updated_at) and appends a new open version. Two properties of that mechanism doom the back-dated case:

It only ever appends at the end. A snapshot closes the current version and inserts after it. There is no code path that reopens a closed window, splits it, and inserts a version between two existing ones. When the Denver change surfaces on June 20, the snapshot — if it notices at all — would close Austin (the current version) and append Denver after July 1, which is nonsense. The mechanism has no notion of “this change was effective earlier than a version I’ve already sealed.”

It dates changes to when it looked, not when they were true. Even setting the ordering problem aside, the snapshot stamps the new version’s valid_from from the source’s updated_at (timestamp strategy) or the run clock (check strategy). The correction was entered June 20; whatever updated_at the source writes is a June 20-ish value, not June 1. The business-effective date lives in a column — an effective_date the source sends — and the snapshot mechanism has no slot to read it into. The effective date is data the snapshot structurally ignores.

And --full-refresh can’t rescue you, for the reason the last chapter hammered: rebuilding a snapshot from current source state yields exactly one version per row — today’s. The history the snapshot accumulated is the only record that a customer was ever in Boston; blow it away and you get a single Denver (or Austin) row with no past at all. The snapshot is stateful and unrebuildable by design, which means a back-dated change it can’t represent is a change it can never represent, no matter how many times you run it.

The mitigation is the one the last chapter set up: when the source can hand you the change as dated data, stop inferring history from state and derive it from the data. A back-dated correction only exists as a problem because the snapshot infers timing from observation. Give the timing to the model as a column and the whole class of problem dissolves.

Re-windowing from effective dates

If the source provides an effective date per change — an effective_from in an audit table, a CDC feed, a changelog like stg_customer_changes (one row per customer per change) — build the Type 2 windows from those dates instead of from observation time. The window function does the tiling:

-- models/marts/dim_customer_history.sql  (changelog-derived variant)
{{ config(materialized='table') }}

with changes as (
    select
        customer_id,
        city,
        country,
        segment,
        effective_from
    from {{ ref('stg_customer_changes') }}
),

windowed as (
    select
        {{ dbt_utils.generate_surrogate_key(['customer_id', 'effective_from']) }} as customer_version_sk,
        {{ dbt_utils.generate_surrogate_key(['customer_id']) }}                   as customer_sk,
        customer_id,
        city,
        country,
        segment,
        effective_from as valid_from,
        coalesce(
            lead(effective_from) over (
                partition by customer_id
                order by effective_from
            ),
            to_date('9999-12-31')
        ) as valid_to,
        lead(effective_from) over (
            partition by customer_id
            order by effective_from
        ) is null as is_current
    from changes
)

select * from windowed

Each version’s valid_to is the next version’s valid_from — lead() over the change sequence, sentinel-closed. Now walk the June 20 correction through it. The correction arrives as a new changelog row: (customer_id, city='Denver', effective_from='2026-06-01'). On the next dbt build, that row joins the changes sequence, order by effective_from sorts it between Boston (Jan 5) and Austin (Jul 1), and lead() recomputes every boundary around it in one pass. Boston’s valid_to becomes June 1; Denver spans June 1 to July 1; Austin is untouched. The back-dated change re-windowed the timeline correctly because the model recomputes the whole tiling from data every run — no reopening, no splitting, no in-place surgery. A rebuild is the repair.

That is the property promised at the end of the last chapter, delivered. It’s also the reason a changelog-derived dimension is materialized='table' and safe to --full-refresh: the source of truth is the changelog, which retains every effective-dated row, so the model is pure derivation. Contrast that with the snapshot, where the model is the source of truth and rebuilding destroys it.

The as-of join is unchanged — it’s still the half-open range join against dim_customer_history:

select
    orders.order_id,
    customers.city,
    sum(orders.extended_price) as revenue
from {{ ref('fct_order_items') }} as orders
join {{ ref('dim_customer_history') }} as customers
  on  orders.customer_sk = customers.customer_sk
  and orders.order_date >= customers.valid_from
  and orders.order_date <  customers.valid_to
group by 1, 2

After the rebuild, the June 10 order — order_date = 2026-06-10 — now falls inside Denver’s [2026-06-01, 2026-07-01) window and reports as Denver, automatically. Nobody re-pointed the fact. The fact stored only its durable customer_sk and its order_date; the version was always resolved at query time, so the moment the windows moved, the answer moved with them. That’s the durable-key design paying its dividend exactly when it matters.

Re-pointing the facts

The previous paragraph hides a fork. It’s clean because the bookshop stores the durable key on the fact and resolves the version in the query. Under the alternative design — version key resolved at load time, where the fact stores customer_version_sk, the exact version believed valid when the fact loaded — a re-window leaves facts pointing at the wrong version, and someone has to re-point them.

Concretely: the June 10 order was loaded when Boston’s window still ran to July 1, so its stored customer_version_sk is Boston’s. After the correction splits that window, the order’s order_date now belongs to Denver — but the fact still carries Boston’s version key. The equi-join that made this design fast will happily join to Boston and report the old, wrong answer. The stored key is a bet that the window was right at load time, and the bet just lost.

Re-pointing is a targeted re-resolution. For a full-refresh fact build it happens for free (the range lookup re-runs against the corrected dimension), but production facts are incremental, so you repair only the affected customers:

-- macros/repair_customer_versions.sql  →  run via `dbt run-operation`
-- Re-resolve version keys for facts whose customer had a window change.
update {{ ref('fct_order_items') }} as f
set customer_version_sk = c.customer_version_sk
from {{ ref('dim_customer_history') }} as c
where f.customer_sk = c.customer_sk
  and f.order_date >= c.valid_from
  and f.order_date <  c.valid_to
  and f.customer_sk in (
      select customer_sk
      from {{ ref('stg_customer_changes') }}
      where loaded_at >= {{ var('repair_since', "current_date - 7") }}
  )
  and f.customer_version_sk <> c.customer_version_sk

The in (…) clause scopes the update to customers who actually had a recent change — you do not want to re-scan every fact row on every load — and the final <> predicate touches only rows whose stored key is genuinely stale. This is the operational tax of the version-key design: correct, fast to query, but every late dimension change becomes an update you have to remember to run against the fact table.

So the two designs pay for late changes in different currencies. Durable key on the fact: the re-window is the entire fix; queries re-resolve automatically; the cost is a range join in every historical read. Version key on the fact: queries are cheap equi-joins; the cost is a fact-repair job on every window change, and a silent wrong answer in the window between the change landing and the repair running. Neither is universally right — but if your source can send back-dated changes and you can’t guarantee a repair runs promptly, the durable key’s “wrong answers are structurally impossible” is worth a lot more than the version key’s query speed. For the bookshop we keep the durable key and let the range join earn its keep.

Re-windowing needs tests

Deriving windows from data is powerful and sharp. The lead() machinery is only as good as the changelog it reads, and a changelog is a source you didn’t fully control. One duplicate change row, one pair of rows with identical effective_from, one clock-skewed timestamp, and the tiling breaks: overlapping windows that double-count, gaps that drop facts, or two rows both flagged is_current. None of these throw an error. They produce a table that looks fine and joins wrong.

So the re-windowing model carries the same guard the last chapter built, non-negotiably:

# models/marts/_marts.yml
models:
  - name: dim_customer_history
    data_tests:
      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: valid_from
          upper_bound_column: valid_to
          partition_by: customer_id
          gaps: not_allowed
    columns:
      - name: customer_version_sk
        data_tests: [unique, not_null]
      - name: customer_id
        data_tests:
          - unique:
              config:
                where: "is_current"

Read those as the four invariants a re-window must never violate:

  • exactly one current row per natural key (unique where is_current);
  • no overlapping windows and no gaps (mutually_exclusive_ranges with gaps: not_allowed, so the timeline tiles completely);
  • no zero-length windows (two changes with the same effective_from collapse a version to nothing — the range test catches the resulting overlap);
  • and, implicitly, that every fact date lands in exactly one version when range-joined by durable key and event date, which is what the half-open windows plus complete tiling guarantee.

These are not decorative. On a healthy snapshot they rarely fire because dbt’s merge maintains the invariant for you. On a derived dimension — where you compute the windows yourself from a source of varying discipline — they fire the moment the changelog contradicts itself, and a red build is precisely what you want in that moment instead of a boardroom discovering it two quarters later. Re-windowing is history surgery. Check the stitches every run.

Backfills are part of the design

Every technique in this chapter has an operational twin, and skipping the twin leaves the model half-built. The modeling patterns — inferred members, effective-dated windows, the durable key — decide what’s possible. Operations decides whether it actually happens.

Answer these before declaring the model done:

  • How far back can corrections arrive? This sizes your reprocessing lookback. If dimension changes can be effective 90 days back, a fact build that only reprocesses yesterday will miss the re-point.
  • Do we reprocess a lookback window every day, or react to change events? A nightly rebuild of the changelog-derived dimension plus a bounded fact-repair window is the common answer; the version-key design forces you to have one.
  • Which facts must be restated when a window moves, and who is told? Under the durable-key design, “restatement” is invisible — yesterday’s report and today’s differ and no one flagged it. Sometimes that’s correct; sometimes a closed month silently changing is a compliance problem. Decide, and communicate the policy.
  • Who gets alerted when an inferred member stays unresolved? An inferred member is a dated IOU. where is_inferred and valid_from < current_date - 14 is a monitoring query, and someone should own its output.
  • Can month-end numbers change after close? If back-dated corrections can land in a closed period, either you freeze reported periods (and carry the correction as a visible adjustment) or you accept that history is mutable and say so out loud.

If the answer to any of these is “we hope not,” the warehouse isn’t designed yet — it’s just built.

Final thoughts

Late data is where dimensional modeling proves whether it is honest or merely tidy. Unknown members keep facts visible when keys are missing. Inferred members preserve identity when dimensions lag. Effective-dated Type 2 models re-window history when the business sends back-dated truth. Durable and version keys choose where that correction is paid: at query time or during fact restatement. The important move is not pretending the timeline is clean. It is deciding, explicitly, how the warehouse behaves when it is not.

Next: Star, Snowflake, and Galaxy

Comments