Late-arriving dimensions

Description

Lesson 3 assumed something worth making explicit: that dim_product_scd already had, at the moment of the JOIN, every version a historical report might need. That assumption is correct for Kiosko's forty orders for the week — all of them earlier than the August 15 change — but it stops being true in one concrete, real case: when a sale happens after a price or category change, but the process that historizes that change in dim_product_scd — module 4's MERGE INTO — hasn't run yet. This lesson builds that scenario with two fixed Kiosko orders, dated after August 15, and shows what happens to the point-in-time join when the dimension runs slower than the facts it describes.

Connection to the module. Kimball names this situation precisely — "late arriving dimension" — and describes two variants: the primary one (a fact arrives referencing an entity that doesn't yet exist in the dimension) and a secondary one, less cited but just as relevant here, that applies directly to an SCD-2 dimension: a retroactive change requires inserting a new row and reprocessing the facts that already joined against the wrong version while the change wasn't historized yet. This lesson builds that second variant with real Kiosko data.

An analogy: mail that arrives before the change-of-address notice

Imagine someone sends you a certified letter at your old address, the very day you moved — before the post office has processed your change-of-address notice. The letter exists, the event (the mailing) already happened, but the system that should route it correctly doesn't have the updated information yet. Two things can happen: the letter arrives at the old address (silently wrong, because the system "believes" that's still your current address), or the system waits until the change-of-address notice is processed before trying to deliver it (correct, but requires someone to hold it and retry).

That's exactly what happens to a P002 sale dated August 16 if the MERGE that historizes the August 15 price change hasn't run yet: the point-in-time join, executed at that moment, finds the old version of dim_product_scd — the only one that still exists — and uses it, with no error at all, because as far as the database engine is concerned, that's still, at that instant, the only "current, no closing date" version. The problem isn't the JOIN's logic — it's still the correct one — it's that one of its two inputs, the dimension, doesn't yet reflect reality.

Worked example: two orders dated after P002's change

These two orders are not part of Kiosko's canonical forty for the week (August 3-9) — they're a demonstration batch, declared explicitly for this lesson, dated after P002's real change (August 15, 2026):

# late_orders_demo.py
import duckdb

from dim_product_scd import DIM_PRODUCT_SCD_ROWS

con = duckdb.connect()
con.execute("""
    CREATE TABLE dim_product_scd (
        product_key  INTEGER PRIMARY KEY,
        product_id   VARCHAR NOT NULL,
        product_name VARCHAR,
        category     VARCHAR,
        unit_cost    DOUBLE,
        valid_from   DATE NOT NULL,
        valid_to     DATE,
        is_current   BOOLEAN NOT NULL DEFAULT true
    )
""")
con.executemany("INSERT INTO dim_product_scd VALUES (?, ?, ?, ?, ?, ?, ?, ?)", DIM_PRODUCT_SCD_ROWS)

con.execute("""
    CREATE TABLE late_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.executemany(
    "INSERT INTO late_orders VALUES (?, ?, ?, ?, ?, ?, ?)",
    [
        ("ORD-8001", "S01", "P002", 2, 1.20, 2.40, "2026-08-16T09:00:00"),
        ("ORD-8002", "S03", "P002", 1, 1.20, 1.20, "2026-08-20T10:30:00"),
    ],
)
print(con.sql("SELECT * FROM late_orders ORDER BY order_ts"))

┌──────────┬──────────┬────────────┬──────────┬────────────┬─────────┬─────────────────────┐
│ order_id │ store_id │ product_id │ quantity │ unit_price │ revenue │      order_ts       │
│ varchar  │ varchar  │  varchar   │  int32   │   double   │ double  │      timestamp      │
├──────────┼──────────┼────────────┼──────────┼────────────┼─────────┼─────────────────────┤
│ ORD-8001 │ S01      │ P002       │        2 │        1.2 │     2.4 │ 2026-08-16 09:00:00 │
│ ORD-8002 │ S03      │ P002       │        1 │        1.2 │     1.2 │ 2026-08-20 10:30:00 │
└──────────┴──────────┴────────────┴──────────┴────────────┴─────────┴─────────────────────┘

First, the happy path: join them against dim_product_scd exactly as module 4 left it — complete, with the August 15 MERGE already applied.

print("\n=== Late orders against COMPLETE dim_product_scd (the MERGE already ran) ===")
print(con.sql("""
    SELECT o.order_id, o.order_ts, d.product_key, d.category, d.unit_cost
    FROM late_orders o
    JOIN dim_product_scd d
        ON o.product_id = d.product_id
       AND o.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    ORDER BY o.order_ts
"""))

=== Late orders against COMPLETE dim_product_scd (the MERGE already ran) ===
┌──────────┬─────────────────────┬─────────────┬───────────────┬───────────┐
│ order_id │      order_ts       │ product_key │   category    │ unit_cost │
│ varchar  │      timestamp      │    int32    │    varchar    │  double   │
├──────────┼─────────────────────┼─────────────┼───────────────┼───────────┤
│ ORD-8001 │ 2026-08-16 09:00:00 │           5 │ health-snacks │      0.68 │
│ ORD-8002 │ 2026-08-20 10:30:00 │           5 │ health-snacks │      0.68 │
└──────────┴─────────────────────┴─────────────┴───────────────┴───────────┘

Correct: both orders, dated after August 15, fall into product_key = 5 — the health-snacks/0.68 version, the one that really was in effect on those dates. The point-in-time join works exactly as expected when the dimension already has the version the fact needs.

Now, the delay scenario: dim_product_scd_delayed, a snapshot of the dimension as if the August 15 MERGE hadn't run yet — P002 still has a single version, snacks/0.60, valid_to = NULL (not closed yet, because no one closed it):

print("\n=== The delayed scenario: the Aug-15 MERGE has NOT run yet ===")
con.execute("""
    CREATE TABLE dim_product_scd_delayed (
        product_key  INTEGER PRIMARY KEY,
        product_id   VARCHAR NOT NULL,
        product_name VARCHAR,
        category     VARCHAR,
        unit_cost    DOUBLE,
        valid_from   DATE NOT NULL,
        valid_to     DATE,
        is_current   BOOLEAN NOT NULL DEFAULT true
    )
""")
con.executemany(
    "INSERT INTO dim_product_scd_delayed VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
    [
        (1, "P001", "Bottled Water 600ml", "beverages", 0.40, "2026-08-01", None, True),
        (2, "P002", "Energy Bar", "snacks", 0.60, "2026-08-01", None, True),  # still not closed
        (3, "P003", "Instant Coffee Sachet", "beverages", 0.35, "2026-08-01", None, True),
        (4, "P004", "Phone Charger Cable", "electronics", 2.10, "2026-08-01", None, True),
    ],
)

print("\n=== Same point-in-time JOIN, against the DELAYED dimension ===")
print(con.sql("""
    SELECT o.order_id, o.order_ts, d.product_key, d.category, d.unit_cost
    FROM late_orders o
    JOIN dim_product_scd_delayed d
        ON o.product_id = d.product_id
       AND o.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    ORDER BY o.order_ts
"""))

=== Same point-in-time JOIN, against the DELAYED dimension ===
┌──────────┬─────────────────────┬─────────────┬──────────┬───────────┐
│ order_id │      order_ts       │ product_key │ category │ unit_cost │
│ varchar  │      timestamp      │    int32    │ varchar  │  double   │
├──────────┼─────────────────────┼─────────────┼──────────┼───────────┤
│ ORD-8001 │ 2026-08-16 09:00:00 │           2 │ snacks   │       0.6 │
│ ORD-8002 │ 2026-08-20 10:30:00 │           2 │ snacks   │       0.6 │
└──────────┴─────────────────────┴─────────────┴──────────┴───────────┘

The SQL query is identical, word for word, to the one that gave the correct result one paragraph earlier. The only difference is which snapshot of the dimension was available at the moment it ran. Against the delayed dimension, both orders fall into product_key = 2 — snacks/0.60, the old version — because for that version of dim_product_scd_delayed, valid_to is still NULL: since no one has closed that row yet, COALESCE(d.valid_to, '9999-12-31') treats it as "current until infinity," and any date — including August 16 and August 20 — falls within that range with no ambiguity.

Diagram: the same JOIN, two results, depending on how current the dimension is

flowchart TD
    A["ORD-8001, ORD-8002\norder_ts: August 16 and 20"] --> B{"Same point-in-time JOIN\nBETWEEN valid_from AND COALESCE(valid_to, 9999-12-31)"}
    B --> C["COMPLETE dim_product_scd\n(the Aug-15 MERGE already ran)\nP002 has 2 versions"]
    B --> D["dim_product_scd_delayed\n(the Aug-15 MERGE did NOT run)\nP002 still has 1 version, valid_to=NULL"]
    C --> E["Correct MATCH:\nhealth-snacks / 0.68"]
    D --> F["Silently incorrect MATCH:\nsnacks / 0.60\n(the only version that still exists)"]

Notice something important in this diagram: neither branch produces an error or a NULL row. Both find a valid MATCH, with the same data type and the same result shape. Verify this directly:

print("\n=== No row is lost -- the bug doesn't crash, it just lies ===")
print(con.sql("""
    SELECT COUNT(*) AS matched_rows
    FROM late_orders o
    LEFT JOIN dim_product_scd_delayed d
        ON o.product_id = d.product_id
       AND o.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    WHERE d.product_key IS NOT NULL
"""))

=== No row is lost -- the bug doesn't crash, it just lies ===
┌──────────────┐
│ matched_rows │
│    int64     │
├──────────────┤
│            2 │
└──────────────┘

Both rows find a MATCH. There's no LEFT JOIN with a NULL result to give the problem away — that's exactly what this module's lesson 7 is going to use to find rows that are genuinely orphaned, a different problem from this lesson's. Here the problem is subtler: the query finds an answer, with total confidence, and that answer is the wrong one.

Going deeper: Kimball's vocabulary, and the "restatement" solution

Kimball Group documents this situation under the name "late arriving dimension" — literally, when "the facts of an operational business process arrive minutes, hours, days, or weeks before the associated dimension context." The example they give is different from Kiosko's on the surface — an inventory row that arrives referencing a customer or product whose natural key doesn't exist anywhere in the dimension yet — but the mechanism is the same: a fact that needs dimensional context that isn't available yet.

For that primary case — a natural key that doesn't exist at all yet — Kimball's technique is to create a dimension row with "generic unknown values" for most descriptive columns, using the natural key that is known; when the real context arrives later, that row gets updated with a type-1 overwrite (the same concepts from module 4, lesson 3). Kiosko doesn't need to build this in this lesson — the four-product catalog has existed, complete, since this guide's very first day; no product_id ever arrives with zero dimension at all — so the pattern gets named without implementing it, with the same discipline module 4 used to name WHEN NOT MATCHED BY SOURCE/WHEN NOT MATCHED BY TARGET without implementing them.

What Kiosko does need, and what this lesson demonstrates with real data, is the second variant Kimball documents for this same technique: a retroactive change in a type-2 dimension — exactly P002's case — requires that "a new row be inserted into the dimension table, and then the associated fact rows must be restated." Translated to this lesson's scenario: as long as dim_product_scd_delayed doesn't have P002's new version, any sale dated after the real change that already joined against that outdated dimension was left with the wrong attribution — and it doesn't fix itself. The fix takes two steps: first, module 4's MERGE has to run and add the new row (dim_product_scd, complete, already has it); second, re-run the JOIN — restate the affected facts — against the now-updated dimension. That's exactly what this lesson's second code block did: the same query, run twice, against two different snapshots of the same dimension.

Common mistakes

Thinking a MATCH found always means the JOIN is correct. What happens: someone runs the point-in-time join, sees each order find exactly one dimension row, no NULL, no fan-out, and calls the result correct without asking whether the dimension was complete at the moment of the query. Why it happens: this module's lessons 2 and 3 taught you to check "did it find a match?" and "did it find exactly one?" — but neither of those questions can detect that the dimension itself is out of date. How to spot it: if your pipeline runs the JOIN at some point close to when the sales happened, explicitly ask yourself whether the process that updates the dimension (the MERGE) has already run for the period you're reporting on — a MATCH found isn't evidence the dimension is current. How to fix it: in a real production pipeline, execution order matters: the MERGE that historizes dimension changes must run, and be confirmed complete, before any historical report depends on that dimension for the affected period.

Confusing "late-arriving dimension" with "orphan order" (no match at all). What happens: someone reads "late arriving dimension" and expects the symptom to be a row with no MATCH, similar to this module's lesson 7 case (an order dated before any version of the product exists). Why it happens: both problems are related — an incomplete dimension — and it's easy to assume they show up the same way. How to spot it: compare this lesson's two scenarios — when the problem is that the MERGE hasn't captured a recent change yet, there is a MATCH (against the old version, which is still "open"); when the problem is that a date falls before any known version of a product, there's no MATCH at all. How to fix it: tell the two symptoms apart — no MATCH is a structural coverage problem (lesson 7 detects it with ANTI JOIN); a MATCH against the wrong version is a synchronization problem between the fact pipeline and the dimension pipeline, the one this lesson demonstrates.

Reprocessing all of historical facts every time the MERGE runs, "just in case." What happens: someone, after understanding this lesson's risk, decides the safest solution is to re-run the JOIN over all of fact_orders's history every time dim_product_scd changes, without limiting the reprocessing to the rows actually affected. Why it happens: it looks like the safest option — "if I don't know exactly what was affected, I reprocess everything" — and with a toy dataset like Kiosko's, the cost of doing so is invisible. How to spot it: in a production warehouse, with millions of fact rows, reprocessing everything every time any dimension changes is a strategy that doesn't scale — the cost grows with every MERGE, regardless of how many rows actually needed fixing. How to fix it: limit the "restatement" to facts whose order_ts falls after the new version's valid_from and before the moment the MERGE that created it ran — exactly the range this example demonstrates with ORD-8001 and ORD-8002. Identifying that range precisely, instead of reprocessing everything, is a pipeline design decision outside this guide's scope (that's airflow-and-declarative-orchestration-guide territory), but the logic of the affected range is part of what this lesson teaches.

Exercises

Exercise 1 — Add a third late order, dated 2026-08-14 at 23:00 (the day before the change), and predict which version it falls into. Before running anything, reason it out: 2026-08-14 is the exact valid_to date of P002's first version. Does it fall into the old version or the new one, against the complete dim_product_scd?

con.execute("INSERT INTO late_orders VALUES ('ORD-8003', 'S02', 'P002', 1, 1.20, 1.20, '2026-08-14T23:00:00')")
print(con.sql("""
    SELECT o.order_id, o.order_ts, d.product_key, d.category
    FROM late_orders o
    JOIN dim_product_scd d
        ON o.product_id = d.product_id
       AND o.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    WHERE o.order_id = 'ORD-8003'
"""))

┌──────────┬───────────┬─────────────┬──────────┐
│ order_id │ order_ts  │ product_key │ category │
│ varchar  │ timestamp │    int32    │ varchar  │
└──────────┴───────────┴─────────────┴──────────┘
                     0 rows

Exercise 2 — Confirm that Kiosko's original forty orders don't suffer from this problem. Using fact_orders (the forty real orders, not this lesson's demo orders) and dim_product_scd_delayed, run the same point-in-time join and confirm the result is identical to what you already saw in lesson 3.

print(con.sql("""
    SELECT d.category, ROUND(SUM(f.revenue), 2) AS revenue
    FROM fact_orders f
    JOIN dim_product_scd_delayed d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    GROUP BY d.category
    ORDER BY d.category
"""))

┌─────────────┬─────────┐
│  category   │ revenue │
│   varchar   │ double  │
├─────────────┼─────────┤
│ beverages   │   44.05 │
│ electronics │    40.5 │
│ snacks      │    21.6 │
└─────────────┴─────────┘

Exercise 3 — Explain, in your own words, the difference between "restatement" (Kimball) and simply "running the JOIN again." In 2-3 sentences, explain why this lesson's solution — running the same query twice, against two different snapshots of dim_product_scd — is exactly what Kimball calls "restatement," and not a coincidence of how the example is built.

Summary and next step

This lesson demonstrated, with two real Kiosko orders dated after P002's change, that lesson 3's point-in-time join is only as correct as the dimension it runs against: against the complete dim_product_scd, both orders correctly fall into health-snacks/0.68; against a delayed snapshot — as if the August 15 MERGE hadn't run yet — the same query, word for word, silently falls into snacks/0.60. You named Kimball's formal vocabulary for this problem — "late arriving dimension" — and its solution for the SCD-2 case: insert the new version and restate the affected facts by re-running the JOIN once the dimension has caught up.

Before moving on you should be able to: explain the difference between a JOIN with no MATCH (orphan) and a JOIN with a MATCH against the wrong version (late arrival); name the two "late arriving dimension" variants Kimball documents, and which one applies to dim_product_scd; and explain what "restatement" means in the context of an SCD-2 dimension.

Lessons 5 and 6 change topic: from "which version of the dimension" to "how many times does each row appear" — the duplicate-rows problem, which can coexist with a perfectly correct point-in-time join.

Resources

• Kimball Group — "Late Arriving Dimension" — the exact source for the technique this lesson implements: the primary case (natural key with no dimension) and the secondary case (retroactive change in SCD-2 with fact restatement). kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/late-arriving-dimension. In English.
• Kimball Group — "Slowly Changing Dimension Type 2" — the valid_from/valid_to/is_current mechanism the delay demonstrated in this lesson operates on. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. In English.
• DuckDB — official MERGE INTO statement documentation — the process whose exact execution moment determines whether a dimension is "current" or "delayed" in this lesson. duckdb.org/docs/lts/sql/statements/merge_into. In English.