Module 5: Point In Time Joins And Deduplication

Module introduction: joining facts against a dimension that has history

Next →

Why this module exists

Module 4 closed with a question left pending on purpose, in the very last line of its own project: "if fact_orders had a P002 order dated after August 15, does the JOIN connect it to the correct version of the dimension?" dim_product_scd — five rows, P002 historized into two versions that don't overlap in time — already exists, verified, with an assert that confirmed history was preserved correctly. This module asks exactly the question module 4 left open, and answers it with executed evidence, not intuition.

Up to now, every time this guide joined fact_orders against a dimension, the dimension had exactly one version per row. Module 2's JOIN (fact_orders with dim_store, dim_product, dim_date) never had to ask "which version?", because there was only ever one. That comfort ended in module 4: dim_product_scd has two rows for P002, each valid over a different date range. A JOIN that ignores that range — that matches product_id to product_id and nothing else — no longer has a single correct answer: it has two candidate rows, and picking the wrong one doesn't produce a visible error. It produces a number that looks perfectly reasonable, and is wrong.

This module teaches the pattern that solves that problem — the point-in-time join, which compares each sale's date against each dimension version's validity range, BETWEEN valid_from AND valid_to — and two related problems that show up in any warehouse that joins facts against historized dimensions: what to do when a dimension row arrives late, after the facts it should describe are already loaded; and how to deduplicate repeated rows when a source resends data, with ROW_NUMBER() and QUALIFY, plus ANTI JOIN/SEMI JOIN to precisely detect what changed between two snapshots. By the end, you'll be able to join any fact against any historized dimension without inflating rows, without losing revenue, and without attributing a past sale to a present-day version.

Connection to the module. This module doesn't modify dim_product_scd or fact_orders — both tables stay exactly as modules 1 and 4 left them. What it builds is the correct query pattern for joining them, and it contrasts that pattern, with real Kiosko numbers, against two ways of joining them that look reasonable and aren't.

An analogy: asking the price on the day you bought it, not today's price

Imagine you kept the receipt from a convenience store three months ago, and you want to know whether you paid a fair price for an energy bar. Who do you ask? It makes no sense to ask today's cashier "how much does the energy bar cost?" — that question gives you today's price, which may differ from three months ago if the supplier raised the cost or the store reclassified the product. The right question is "how much did the energy bar cost the day I bought it, according to the receipt?" That question has exactly one correct answer, and it's a historical answer, not today's answer.

That's exactly what a point-in-time join does with fact_orders and dim_product_scd: for each sale, it doesn't ask "what's this product's version right now?" — that's the question is_current = true answers, and it's the wrong question for a historical report. It asks "what was the current version on the day of this specific sale?", comparing order_ts against each version's valid_from/valid_to range. The first question gives you today's price applied retroactively to past purchases. The second gives you the real price that applied the day the sale happened — exactly like the receipt you kept.

Worked example: this module's map, before building it

Before touching the real JOIN, it's worth seeing, at a glance, what each lesson builds and in what order — the same kind of map that opened modules 3 and 4 before comparing or historizing.

# join_module_map.py
CONCEPTS = [
    ("Join by natural key alone", "product_id = product_id, no version filter. Produces fan-out."),
    ("Join broken by is_current", "Avoids the fan-out, but attributes EVERYTHING to the present. Silent."),
    ("Point-in-time join", "order_ts BETWEEN valid_from AND valid_to. The correct version, always."),
    ("Late-arriving dimension", "The fact arrives before SCD-2 captures the real change."),
    ("Deduplication with QUALIFY", "ROW_NUMBER() PARTITION BY key ORDER BY criterion, QUALIFY = 1."),
    ("ANTI JOIN / SEMI JOIN", "Unmatched rows (orphans) or rows that changed, no subquery."),
]

LESSONS = [
    ("Why joining on product_id alone breaks history", "The fan-out, EXECUTED: 40 rows -> 50"),
    ("The point-in-time join pattern", "Correct vs broken, EXECUTED: snacks vs health-snacks"),
    ("Late-arriving dimensions", "Kimball late arriving dimension, EXECUTED with off-week orders"),
    ("Where duplicate rows come from", "A resent batch, EXECUTED: 40 -> 43 rows"),
    ("Deduplicating with ROW_NUMBER and QUALIFY", "43 -> 40 rows, EXECUTED, grain restored"),
    ("Anti-joins to find what changed", "DuckDB's native ANTI JOIN, EXECUTED over 2 scenarios"),
    ("Project: Kiosko's correct historical revenue", "The 6 previous lessons integrated, EXECUTED"),
]

print("=== This module's six central concepts ===\n")
for name, description in CONCEPTS:
    print(f"- {name}")
    print(f"  {description}\n")

print("=== The seven lessons that build on them ===\n")
for i, (name, description) in enumerate(LESSONS, start=2):
    print(f"L{i}. {name}")
    print(f"    {description}\n")

What to expect. Running python3 join_module_map.py, the output is exactly this:

=== This module's six central concepts ===

- Join by natural key alone
  product_id = product_id, no version filter. Produces fan-out.

- Join broken by is_current
  Avoids the fan-out, but attributes EVERYTHING to the present. Silent.

- Point-in-time join
  order_ts BETWEEN valid_from AND valid_to. The correct version, always.

- Late-arriving dimension
  The fact arrives before SCD-2 captures the real change.

- Deduplication with QUALIFY
  ROW_NUMBER() PARTITION BY key ORDER BY criterion, QUALIFY = 1.

- ANTI JOIN / SEMI JOIN
  Unmatched rows (orphans) or rows that changed, no subquery.

=== The seven lessons that build on them ===

L2. Why joining on product_id alone breaks history
    The fan-out, EXECUTED: 40 rows -> 50

L3. The point-in-time join pattern
    Correct vs broken, EXECUTED: snacks vs health-snacks

L4. Late-arriving dimensions
    Kimball late arriving dimension, EXECUTED with off-week orders

L5. Where duplicate rows come from
    A resent batch, EXECUTED: 40 -> 43 rows

L6. Deduplicating with ROW_NUMBER and QUALIFY
    43 -> 40 rows, EXECUTED, grain restored

L7. Anti-joins to find what changed
    DuckDB's native ANTI JOIN, EXECUTED over 2 scenarios

L8. Project: Kiosko's correct historical revenue
    The 6 previous lessons integrated, EXECUTED

Notice the order: first it shows the way of joining that breaks the grain — fan-out, extra rows (lesson 2) — then the way that fixes the grain but breaks the attribution — is_current, the silent trap (lesson 3, alongside the correct pattern) — then two problems that show up even with the correct JOIN already written — the dimension that arrives late (lesson 4) and duplicates arriving from the source (lessons 5 and 6) — and finally a new tool — ANTI JOIN/SEMI JOIN — to detect, with a single query, what changed between two snapshots (lesson 7). The closing project (lesson 8) integrates all six pieces over the same fact_orders and dim_product_scd as always.

Diagram: where you were, where you're going to be

flowchart LR
    subgraph M4["Module 4 (already written)"]
        A["dim_product_scd\n5 rows, P002 with 2 versions\nvalid_from/valid_to/is_current"]
    end

    subgraph M5["This module (5 of 8)"]
        B["L2: JOIN by product_id alone\nfan-out, EXECUTED"]
        C["L3: Point-in-time JOIN\nvs is_current, EXECUTED"]
        D["L4: Late arrival\nof the dimension, EXECUTED"]
        E["L5: Where the\nduplicates come from, EXECUTED"]
        F["L6: QUALIFY + ROW_NUMBER\nEXECUTED"]
        G["L7: ANTI JOIN / SEMI JOIN\nEXECUTED"]
        H["L8: Correct historical\nrevenue, EXECUTED"]
    end

    subgraph M6["Module 6 (next)"]
        I["Accumulating snapshot\nover fact_sessions"]
    end

    A --> B --> C --> D --> E --> F --> G --> H --> I

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The map: the six concepts, before building them
L2        Why product_id alone breaks history, EXECUTED
L3        The point-in-time join pattern, EXECUTED
L4        Late-arriving dimensions, EXECUTED
L5        Where duplicate rows come from, EXECUTED
L6        Deduplicating with ROW_NUMBER and QUALIFY, EXECUTED
L7        Anti-joins to find what changed, EXECUTED
L8        Project: Kiosko's correct historical revenue, EXECUTED

Lessons 2 and 3 are the backbone: the same question — how do you join fact_orders against a dimension with more than one version per product? — answered three times, with three different JOINs, so you see in numbers why two of the three are wrong even though neither one crashes with an error. Lesson 4 extends the correct pattern to a real-time problem: what happens when the dimension pipeline runs slower than the facts it describes. Lessons 5 and 6 change topic — from "which version" to "how many times" — and solve deduplication, with the same evidentiary rigor. Lesson 7 adds a tool — ANTI JOIN/SEMI JOIN — that serves both lesson 4's problem (orphan facts) and a new one (detecting what changed before applying a MERGE). Lesson 8 integrates everything.

Going deeper: why this module needs dim_product_scd already historized

It might seem that a point-in-time join is an independent topic, teachable with any example table with a couple of dates. This module resists that temptation for one concrete reason: there is no observable difference between a correct join and a broken one if the dimension never had more than one version. With dim_product — module 2's star version, no history, one row per product — any of the three ways of joining this module compares (product_id alone, is_current, point-in-time) produces exactly the same result, because there's only ever one candidate row per product. The difference only shows up, with evidence, when a dimension has more than one version per natural key — exactly what dim_product_scd has been, ever since module 4 historized P002.

This isn't a minor detail: it's the reason this guide dedicated an entire module (4) to building the historized dimension before teaching how to join it correctly. Without dim_product_scd already built and verified — five rows, P002 with two versions that don't overlap in time — this module wouldn't have any real scenario to demonstrate the problem on. fact_orders, for its part, supplies the other half of the scenario: the forty orders from the week of August 3-9, 2026, all of them earlier than P002's change (August 15). That date isn't arbitrary — it means that, if the JOIN is wrong, every single one of that week's ten P002 orders is going to land on the wrong version, not just some of them. The error is complete, not partial, and that's why it's easy to measure precisely in the lessons that follow.

Common mistakes

Thinking this module is going to modify dim_product_scd or fact_orders. What happens: someone, seeing "point-in-time join" in the module's title, expects new columns to get added to one of the two tables, or something module 4 left "incomplete" to get fixed. Why it happens: after an entire module spent building a table, it's natural to expect the next module keeps building on it in the same direction. How to spot it: if you expect dim_product_scd to end this module with a column different from the eight it already has (product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current), you have this confusion. How to fix it: dim_product_scd and fact_orders are, for this module, fixed input data — module 4 already left them complete and verified. What this module builds is exclusively the query that joins them correctly, without touching either one.

Assuming "the join is broken" means it's going to fail or throw an error. What happens: someone expects a badly written JOIN to produce a DuckDB error message, a Python exception, or at least a visible NULL value. Why it happens: in most programming languages, a logic error does eventually produce a visible error. How to spot it: if, in lesson 3, you expect the broken JOIN (is_current = true) to crash with some error, you're going to be surprised when it runs perfectly and returns forty rows, not one more or fewer. How to fix it: a mis-attributed JOIN in a dimensional model is, almost always, a silent error — it produces a result with the correct shape (same row count, same column types) but with the wrong content. It's exactly the kind of error a verification query — like the category count you're going to build in lesson 3 — exists to catch, because nothing else is going to catch it for you.

Confusing "correct total revenue" with "correct attribution." What happens: someone runs lesson 3's broken JOIN, sees SUM(revenue) come out to 106.15 — the same number as always — and concludes the JOIN is fine because "the total checks out." Why it happens: Kiosko's total revenue (106.15) is such a familiar number, repeated in every module of this guide, that seeing it again feels like complete confirmation. How to spot it: if your verification of a join against a historized dimension stops at SUM(revenue) without breaking it down by any dimension column (category, in this case), you didn't test what a point-in-time join exists to test. How to fix it: revenue lives in fact_orders, already computed (quantity * unit_price) — no JOIN against dim_product_scd, correct or broken, can change that number, because it doesn't participate in its calculation. What does change with a mis-attributed JOIN is everything that depends on a dimension column: category (for grouping), unit_cost (for computing margin). Lesson 3 measures exactly that.

Exercises

Exercise 1 — Recall the two exact checklist rows this module resolves. Without rereading module 4's project, write from memory the exact wording of the two checklist rows (introduced in module 1, lesson 2) that correspond to this module.

See solution

The two rows read, literally: "Point-in-time join against a historized dimension" and "Explicit deduplication of repeated rows." Unlike module 4 — which resolved a single checklist row (historization) — this module resolves two, because both share the same kind of evidence: a query run against real Kiosko data that demonstrates, with numbers, the difference between doing it right and doing it wrong.

Exercise 2 — Explain, in your own words, why dim_product (the star version, with no history) can't demonstrate this module's problem. Without looking at this lesson's "Going deeper" section, write 2-3 sentences explaining what dim_product is missing for a point-in-time join and a join by product_id alone to produce different results.

See solution

dim_product has exactly one row per product_id — it never had a real change to historize, so any way of joining it (product_id alone, is_current, or a date range) always finds the same single candidate row. Without a second version of some product, there's no decision a JOIN can get wrong: there's no "wrong" row to pick. This module's problem only exists when the dimension has more than one candidate row per natural key — exactly what dim_product_scd has, ever since module 4 historized P002.

Exercise 3 — Predict, before lesson 2, how many rows a JOIN between fact_orders (40 rows) and dim_product_scd (5 rows) will produce using only f.product_id = d.product_id, with no other filter. Use what you already know: dim_product_scd has 5 rows for 4 distinct products (one of them, P002, with 2 versions). Explain your reasoning in 2-3 sentences.

See solution

More than 40. Every order of P001, P003, or P004 finds exactly one candidate row in dim_product_scd (those three products have a single version), so it joins once, unchanged. But every order of P002 finds two candidate rows — the two historical versions — and a JOIN with no additional filter matches it against both, producing two result rows for every P002 order instead of one. If fact_orders has ten P002 orders (a figure you're going to confirm with a query in lesson 2), the expected total is 40 + 10 = 50 rows, not 40. Lesson 2 confirms this exact number, executed.

Summary and next step

This module takes fact_orders (40 rows, unchanged since module 1) and dim_product_scd (5 rows, historized since module 4) and answers the question module 4 left open on purpose: how do you join a fact against a dimension with more than one version per product, without inflating rows or misattributing history? You're going to see, with executed evidence, three ways of attempting it — two broken, one correct — two additional problems that show up even with the correct form already written (late-arriving dimension, duplicate rows from the source), and a new tool (ANTI JOIN/SEMI JOIN) for detecting changes without subqueries.

Before moving on you should be able to: name this module's six central concepts and what problem each one solves; explain why dim_product (with no history) can't demonstrate any of these problems; and say from memory the two exact rows of module 1's checklist this module resolves.

Lesson 2 starts with the most visible of the three errors: joining fact_orders against dim_product_scd using only product_id, with no version filter, and measuring exactly how many extra rows it produces.

Resources

← PreviousMini-project: Kiosko's historized dim_productNext →Why joining on product_id alone breaks history