The point-in-time join pattern

Description

Lesson 2 showed a broken JOIN in an easy-to-catch way: it produces more rows than it should, and a simple row-count comparison gives it away. This lesson shows something harder: a JOIN that fixes the extra-rows problem — filtering by is_current = true — but is still wrong, in a way no row count can detect. And, alongside it, it builds the JOIN that is actually correct: the one that compares each sale's date against each dimension version's validity range. You're going to run both, side by side, over the same forty Kiosko orders, and see the exact difference each one produces.

Connection to the module. This is the module's central lesson. Lesson 2 laid the groundwork by showing why "joining with no filter" isn't enough; this lesson solves the complete problem, with the pattern this guide's design names explicitly: f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, '9999-12-31'). Everything that follows in this module — late arrival (lesson 4), deduplication (lessons 5 and 6), anti-joins (lesson 7) — assumes you already understand, with evidence, why this pattern is the correct one.

An analogy: the receipt, not today's counter

Pick back up the module introduction's analogy: you want to know how much you paid for an energy bar three weeks ago. is_current = true is like walking up to the store counter today and asking the current price — a quick answer, always available, and systematically wrong for any purchase that isn't from today. The point-in-time join is like pulling out that purchase's receipt, reading the printed date, and comparing that date against the store's price history to find out which price applied exactly that day. The question "what's the version in effect right now?" and the question "what was the version in effect on the day of this sale?" have, almost always, the same answer — when the sale is recent — and exactly the opposite answer when the sale predates a real change, as happens with Kiosko's forty orders against P002's August 15 change.

Worked example: the broken JOIN and the correct JOIN, side by side

Rebuild fact_orders and dim_product_scd exactly as in lesson 2 — same kiosko.py, raw_orders.py, dim_product_scd.py. With both tables loaded, first run the JOIN that "looks like" it fixes the previous lesson's fan-out:

# broken_vs_correct_join.py
# (fact_orders and dim_product_scd already loaded as in lesson 2)

print("=== Broken JOIN: is_current = true (avoids the fan-out, but pins the version to 'today') ===")
print(con.sql("""
    SELECT COUNT(*) AS joined_rows
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND d.is_current = true
"""))

=== Broken JOIN: is_current = true (avoids the fan-out, but pins the version to 'today') ===
┌─────────────┐
│ joined_rows │
│    int64    │
├─────────────┤
│          40 │
└─────────────┘

Forty rows — exactly the right number, no fan-out. The is_current = true filter does its job: for each product_id, it picks a single row from dim_product_scd, the current one. Looking only at this number, it's easy to call the JOIN good. It isn't. Notice which version it picked for P002:

print("=== Which version EVERY P002 order falls into, with the is_current filter ===")
print(con.sql("""
    SELECT DISTINCT d.product_key, d.category, d.unit_cost
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND d.is_current = true
    WHERE f.product_id = 'P002'
"""))

=== Which version EVERY P002 order falls into, with the is_current filter ===
┌─────────────┬───────────────┬───────────┐
│ product_key │   category    │ unit_cost │
│    int32    │    varchar    │  double   │
├─────────────┼───────────────┼───────────┤
│           5 │ health-snacks │      0.68 │
└─────────────┴───────────────┴───────────┘

Every single one of the ten P002 orders — Kiosko's forty orders are from the week of August 3-9, 2026, and P002's change happened on August 15 — falls into product_key = 5, the health-snacks/0.68 version. That change hadn't happened yet when those sales occurred. The JOIN with is_current = true doesn't know that, and can't: is_current describes the dimension's state now, at the moment you run the query — it has no way to look back and ask what the state was on the day of each sale.

Now, the correct JOIN: instead of is_current = true, it compares order_ts against each version's validity range.

print("\n=== Correct JOIN: point-in-time, BETWEEN valid_from and valid_to ===")
print(con.sql("""
    SELECT COUNT(*) AS joined_rows
    FROM fact_orders f
    JOIN dim_product_scd 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')
"""))

print("\n=== Which version EVERY P002 order falls into, with the point-in-time JOIN ===")
print(con.sql("""
    SELECT DISTINCT d.product_key, d.category, d.unit_cost
    FROM fact_orders f
    JOIN dim_product_scd 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')
    WHERE f.product_id = 'P002'
"""))

=== Correct JOIN: point-in-time, BETWEEN valid_from and valid_to ===
┌─────────────┐
│ joined_rows │
│    int64    │
├─────────────┤
│          40 │
└─────────────┘

=== Which version EVERY P002 order falls into, with the point-in-time JOIN ===
┌─────────────┬──────────┬───────────┐
│ product_key │ category │ unit_cost │
│    int32    │ varchar  │  double   │
├─────────────┼──────────┼───────────┤
│           2 │ snacks   │      0.6  │
└─────────────┴──────────┴───────────┘

Also forty rows — the point-in-time join produces no fan-out, exactly like is_current — but this time every single one of the P002 orders falls into product_key = 2, the snacks/0.60 version — the version that was in effect when those sales actually happened. Both JOINs produce the same number of rows. Only one of the two produces the correct category and cost.

Diagram: why BETWEEN finds the correct version

flowchart TD
    A["order_ts of a P002 sale\n(August 3-9, 2026)"] --> C{"BETWEEN valid_from AND\nCOALESCE(valid_to, 9999-12-31)"}
    B1["Version 1: snacks / 0.60\nvalid_from=2026-08-01\nvalid_to=2026-08-14"] --> C
    B2["Version 2: health-snacks / 0.68\nvalid_from=2026-08-15\nvalid_to=NULL"] --> C
    C -->|"order_ts falls WITHIN\nthis range"| D["Version 1 (snacks) -- correct MATCH"]
    C -->|"order_ts does NOT fall\nin this range"| E["Version 2 -- discarded for this order"]

P002 timeline -- why the 40 orders ALWAYS fall into version 1
──────────────────────────────────────────────────────────────────────────────
2026-08-01 ─────────────────────── 2026-08-14 │ 2026-08-15 ─────────────────>
│◄── Version 1: snacks / 0.60 (valid_to) ──►│  │◄── Version 2: health-snacks / 0.68 ──►
│                                            │  │           (is_current = true)
│    ▲    ▲   ▲  ▲  ▲   ▲  ▲   ▲   ▲   ▲     │  │
│  the 10 P002 orders of the week            │  │
│  (August 3-9) ALL fall here                │  │

Going deeper: the same money, different category — and different margin

Both JOINs in this lesson return forty rows, so a grain check alone — the kind lesson 2 taught — does not tell them apart. You need a different verification: aggregate by a dimension column, and compare. Group by category — it comes from dim_product_scd, not fact_orders — and compute, alongside revenue, the margin (revenue - quantity * unit_cost, using unit_cost, which also comes from the dimension):

print("\n=== Revenue and margin by category -- broken JOIN (is_current) ===")
print(con.sql("""
    SELECT
        d.category,
        ROUND(SUM(f.revenue), 2) AS revenue,
        ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
    GROUP BY d.category
    ORDER BY d.category
"""))

print("\n=== Revenue and margin by category -- correct JOIN (point-in-time) ===")
print(con.sql("""
    SELECT
        d.category,
        ROUND(SUM(f.revenue), 2) AS revenue,
        ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f
    JOIN dim_product_scd 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
"""))

=== Revenue and margin by category -- broken JOIN (is_current) ===
┌───────────────┬─────────┬────────┐
│   category    │ revenue │ margin │
│    varchar    │ double  │ double │
├───────────────┼─────────┼────────┤
│ beverages     │   44.05 │  14.75 │
│ electronics   │    40.5 │   21.6 │
│ health-snacks │    21.6 │   9.36 │
└───────────────┴─────────┴────────┘

=== Revenue and margin by category -- correct JOIN (point-in-time) ===
┌─────────────┬─────────┬────────┐
│  category   │ revenue │ margin │
│   varchar   │ double  │ double │
├─────────────┼─────────┼────────┤
│ beverages   │   44.05 │  14.75 │
│ electronics │    40.5 │   21.6 │
│ snacks      │    21.6 │   10.8 │
└─────────────┴─────────┴────────┘

beverages and electronics are identical in both tables — neither of those products ever changed, so both JOINs treat them the same. All the difference is in the last row: the broken JOIN reports 21.6 in revenue under the health-snacks category, with a margin of 9.36; the correct JOIN reports the same 21.6 in revenue, but under the snacks category, with a margin of 10.8. Notice what does not change and what does:

• The 21.6 revenue is identical in both cases — that's not coincidence. revenue lives in fact_orders, already computed as quantity * unit_price at the moment of sale; no JOIN against dim_product_scd can change it, broken or correct, because it doesn't participate in its calculation.
• The category changes from health-snacks (broken) to snacks (correct) — a "revenue by category" report built on the broken JOIN would attribute to health-snacks money that, in the week of the change, that category didn't even exist yet in Kiosko's real sales.
• The margin changes from 9.36 (broken) to 10.8 (correct) — a difference of 1.44, exactly what using unit_cost = 0.68 (the new cost) instead of unit_cost = 0.60 (the real cost for those sales) produces. A profitability report built on the broken JOIN would understate the real margin of those sales by 1.44, because it retroactively applies a cost that wasn't yet in effect.

Confirm the total revenue, to close the loop with the familiar number:

print("\n=== Total revenue: identical in both cases ===")
print(con.sql("""
    SELECT
        (SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
         JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true) AS broken_revenue,
        (SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
         JOIN dim_product_scd 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')) AS correct_revenue
"""))

=== Total revenue: identical in both cases ===
┌────────────────┬─────────────────┐
│ broken_revenue │ correct_revenue │
│     double     │     double      │
├────────────────┼─────────────────┤
│         106.15 │          106.15 │
└────────────────┴─────────────────┘

106.15 in both — the same number you already know from foundations. This is, precisely, why this error is so dangerous in practice: the number most people look at first — total revenue — gives nothing away. The error only shows up when you break it down by a dimension column (category) or compute a metric that depends on another dimension column (margin, which depends on unit_cost). A report that only shows the total is never going to find it.

Why COALESCE(d.valid_to, DATE '9999-12-31'), and not just valid_to

Notice a deliberate detail in the BETWEEN condition: order_ts wasn't compared directly against valid_to — it was wrapped in COALESCE(d.valid_to, DATE '9999-12-31'). The reason is a SQL rule about NULL you already saw, in another context, in module 4: valid_to is NULL for any current version (each product's most recent row), because it doesn't have a closing date yet. And in SQL, any comparison against NULL returns NULL, not true — not even BETWEEN. If the condition were simply f.order_ts BETWEEN d.valid_from AND d.valid_to, any order that should join against a product's current version (for instance, the orders you're going to add in lesson 4, dated after August 15) would fail to find that version, because something BETWEEN date AND NULL is never true.

COALESCE(d.valid_to, DATE '9999-12-31') solves this by replacing the NULL with a far-future date — any date reasonably later than any sale Kiosko is ever going to record — so the current version stays, in practice, "open until infinity" for the purposes of the comparison. 9999-12-31 isn't a magic value or part of Kiosko's business: it's simply "a date no real sale is going to exceed," the same trick any dimensional modeler uses to represent "no closing date yet" in a comparison that doesn't tolerate NULL.

Common mistakes

Using is_current = true because "it's the version that matters today." What happens: someone, building a historical report, filters by is_current = true reasoning that it's "the correct version of the product" — without distinguishing between a report describing the catalog's current state (where is_current = true really is correct) and a report describing past sales (where it isn't). Why it happens: is_current sounds, by its name, like the correct "default" answer, and in most simple reports — ones this module doesn't cover, like "show me today's catalog" — it really is. How to spot it: ask yourself what each row of the report you're building describes — if it describes a past event (a sale, with its own order_ts), is_current is almost never correct; if it describes the present state (the catalog as it stands today, with no relation to historical sales), it is. How to fix it: for any report that joins a fact — with its own date — against a historized dimension, use this lesson's point-in-time join. Reserve is_current = true for queries describing the present with no relation to a dated event (for example, "list Kiosko's current catalog").

Checking only the row count, and calling the JOIN good on that basis. What happens: someone applies lesson 2's discipline — comparing COUNT(*) before and after the JOIN — sees it comes out to 40 in both cases (broken and correct), and concludes the JOIN is fine, without aggregating by any dimension column. Why it happens: lesson 2 quite rightly taught that the row count is the first check — but it's a necessary check, not a sufficient one, when the problem isn't fan-out but incorrect attribution. How to spot it: if your only check on a JOIN against a historized dimension is COUNT(*), you can't tell apart this lesson's broken and correct JOINs — both pass that test. How to fix it: for any JOIN against a dimension with more than one version per key, always add a second check that depends on a dimension column that actually changed (category, unit_cost, in this case) — the row count proves there's no fan-out; the aggregation by dimension column proves the attribution is correct.

Forgetting the COALESCE and being surprised when rows go missing. What happens: someone writes f.order_ts BETWEEN d.valid_from AND d.valid_to, without COALESCE, and their JOIN silently loses any order that should join against a current version (valid_to IS NULL). With this lesson's forty orders — all earlier than P002's change, all falling into the closed version — this specific error doesn't show up, because no order needs to join against a valid_to = NULL version. Why it happens: NULL's behavior in comparisons isn't intuitive the first time you run into it, and the error only shows up with data that actually needs the current version. How to spot it: if your point-in-time JOIN loses rows specifically for recent products or dates — any sale that should fall into a historized product's newest version — suspect a valid_to without COALESCE first. How to fix it: COALESCE(d.valid_to, DATE '9999-12-31') — or an equivalent far-future date — is an integral part of the pattern, not an optional detail; this module's lesson 4 depends directly on it being present.

Exercises

Exercise 1 — Confirm that beverages and electronics are identical across both JOINs, row by row. Using this lesson's two category queries, write a third query that joins both JOINs' results by category and confirms, with a boolean column, that beverages and electronics didn't change.

print(con.sql("""
    WITH broken AS (
        SELECT d.category, ROUND(SUM(f.revenue), 2) AS revenue
        FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
        GROUP BY d.category
    ),
    correct AS (
        SELECT d.category, ROUND(SUM(f.revenue), 2) AS revenue
        FROM fact_orders f JOIN dim_product_scd 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
    )
    SELECT
        COALESCE(b.category, c.category) AS category,
        b.revenue AS broken_revenue,
        c.revenue AS correct_revenue,
        b.revenue IS NOT DISTINCT FROM c.revenue AS same_category_and_revenue
    FROM broken b
    FULL OUTER JOIN correct c ON b.category = c.category
    ORDER BY category
"""))

┌───────────────┬────────────────┬─────────────────┬───────────────────────────┐
│   category    │ broken_revenue │ correct_revenue │ same_category_and_revenue │
│    varchar    │     double     │      double      │          boolean          │
├───────────────┼────────────────┼─────────────────┼───────────────────────────┤
│ beverages     │          44.05 │            44.05 │ true                      │
│ electronics   │           40.5 │             40.5 │ true                      │
│ health-snacks │           21.6 │             NULL │ false                     │
│ snacks        │           NULL │             21.6 │ false                     │
└───────────────┴────────────────┴─────────────────┴───────────────────────────┘

Exercise 2 — Compute Kiosko's total margin (all three categories summed) in both scenarios, and compare. Using this lesson's category queries, sum the margin across the three categories in each case (broken and correct) and explain whether Kiosko's total margin changes between them.

print(con.sql("""
    SELECT
        (SELECT ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2)
         FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true) AS broken_total_margin,
        (SELECT ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2)
         FROM fact_orders f JOIN dim_product_scd 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')) AS correct_total_margin
"""))

┌──────────────────────┬───────────────────────┐
│ broken_total_margin  │ correct_total_margin  │
│        double        │         double        │
├──────────────────────┼────────────────────────┤
│                 45.71 │                 47.15 │
└──────────────────────┴────────────────────────┘

Exercise 3 — Explain why this lesson's correct JOIN would still give the same result if dim_product_scd had ten versions of P002 instead of two. In 2-3 sentences, explain why the pattern BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31') doesn't depend on how many versions the dimension has.

Summary and next step

This lesson built and compared two ways of joining fact_orders against a historized dimension, both without fan-out (forty rows in both cases): is_current = true — which attributes every sale to the version current today, regardless of when it happened — and the point-in-time join — f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, '9999-12-31') — which attributes each sale to the version that actually was in effect on the day of that sale. With Kiosko's ten P002 orders for the week, the difference is complete: health-snacks/margin 9.36 (broken) versus snacks/margin 10.8 (correct), with the same 21.6 revenue in both — the exact evidence for why "the total checks out" isn't enough verification.

Before moving on you should be able to: write from memory the complete point-in-time JOIN condition, including the COALESCE; explain why is_current = true is correct for "today's catalog" but incorrect for "historical sales"; and recite, without looking, the exact category and margin each of the two JOINs assigns to P002.

Lesson 4 takes this same pattern into a real-time problem: what happens when a P002 order, dated after August 15, arrives before the MERGE that historizes the real change has run — the late-arriving dimension.

Resources

• Kimball Group — "Slowly Changing Dimension Type 2" — the formal definition of valid_from/valid_to/is_current, the three columns behind this lesson's JOIN. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. In English.
• DuckDB — comparison operators documentation, including BETWEEN — the exact reference for how this lesson's date ranges are evaluated. duckdb.org/docs/current/sql/expressions/comparison_operators. In English.
• DuckDB — NULL values documentation, including the COALESCE function — the reference for why valid_to IS NULL needs COALESCE inside a BETWEEN. duckdb.org/docs/current/sql/data_types/nulls. In English.
• DuckDB — official Python client documentation, used to build and compare this lesson's two JOINs. duckdb.org/docs/current/clients/python/overview. In English.