Module 5: Point In Time Joins And Deduplication
Why joining on product_id alone breaks history
Description
This lesson does the first thing anyone would try when joining fact_orders against dim_product_scd: a JOIN on the key that connects them, product_id, with no additional filter. With dim_product — module 2's star version — this worked perfectly, hundreds of times, across the three previous modules. With dim_product_scd — module 4's historized version — it produces something different: more rows than went in. You're going to build that JOIN, count it, and confirm exactly where the extra rows come from.
Connection to the module. This is the lesson that makes visible, with a concrete number, the problem the module introduction only described in words. It's not the solution yet — that's lesson 3 — it's the evidence that the most obvious way of joining two tables related by a natural key stops being enough as soon as one of them has more than one row per key.
An analogy: asking by last name in a family with two people sharing one
Imagine you're looking for the "Garcia" file at a civil registry office, and the office, without you realizing, has two distinct records under the last name Garcia — a father and a son, both alive, both with active records. If your request is simply "give me the Garcia file," without specifying which one, the correct response isn't "here's one" — it's, quite rightly, "here are both, because your request didn't distinguish between them." That's not a system error: it's exactly what you asked for. The error is in the question, not the answer.
That's precisely what happens when joining fact_orders against dim_product_scd by product_id alone. dim_product_scd has two rows with product_id = 'P002' — the old version (snacks, closed) and the new one (health-snacks, current) — exactly like the two Garcias in the example. Asking "give me the P002 row" without specifying which version doesn't have a single correct answer: DuckDB, quite rightly, gives you both.
Worked example: the unfiltered JOIN, counted
First, this module's two input tables, both inherited unchanged: fact_orders (40 rows, from module 1) and dim_product_scd (5 rows, from module 4). If you already have kiosko.py, raw_orders.py in your working folder, reuse them as-is; here dim_product_scd.py, this module's new file, gets added, with the exact final state module 4's project left it in:
# dim_product_scd.py -- the final state module 4's project (lesson 8) left it in
# (product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current)
DIM_PRODUCT_SCD_ROWS = [
(1, "P001", "Bottled Water 600ml", "beverages", 0.40, "2026-08-01", None, True),
(2, "P002", "Energy Bar", "snacks", 0.60, "2026-08-01", "2026-08-14", False),
(5, "P002", "Energy Bar", "health-snacks", 0.68, "2026-08-15", None, True),
(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),
]Notice that this list doesn't rebuild the historization with MERGE INTO — module 4's project already did that, and verified it with an assert. Here it's loaded directly, as fixed input data, because this module's focus is the JOIN, not building the SCD. If you want to rebuild it from scratch with MERGE INTO, the way module 4 taught it, that lesson is still there, unchanged.
Now, this lesson's complete script: it rebuilds fact_orders, loads dim_product_scd, and runs the unfiltered JOIN.
# naive_join.py
from datetime import datetime
import duckdb
from kiosko import DIM_PRODUCT, DIM_STORE, Order, transform_fact_orders
from raw_orders import RAW_ORDERS
from dim_product_scd import DIM_PRODUCT_SCD_ROWS
orders = [
Order(order_id=r[0], store_id=r[1], product_id=r[2], quantity=r[3],
unit_price=r[4], order_ts=datetime.fromisoformat(r[5]))
for r in RAW_ORDERS
]
fact_orders = transform_fact_orders(orders, DIM_STORE, DIM_PRODUCT)
con = duckdb.connect()
con.execute("""
CREATE TABLE fact_orders (
order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
)
""")
con.executemany(
"INSERT INTO fact_orders VALUES (?, ?, ?, ?, ?, ?, ?)",
[(r["order_id"], r["store_id"], r["product_id"], r["quantity"],
r["unit_price"], r["revenue"], r["order_ts"]) for r in fact_orders],
)
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)
print("=== Reference grain: fact_orders, before any JOIN ===")
print(con.sql("SELECT COUNT(*) AS total_rows FROM fact_orders"))
print("\n=== How many versions each product has in dim_product_scd ===")
print(con.sql("""
SELECT product_id, COUNT(*) AS dim_versions
FROM dim_product_scd
GROUP BY product_id
ORDER BY product_id
"""))
print("\n=== The naive JOIN: only by product_id, no version filter ===")
print(con.sql("""
SELECT COUNT(*) AS joined_rows
FROM fact_orders f
JOIN dim_product_scd d ON f.product_id = d.product_id
"""))What to expect. Running python3 naive_join.py, the output is exactly this:
=== Reference grain: fact_orders, before any JOIN ===
┌────────────┐
│ total_rows │
│ int64 │
├────────────┤
│ 40 │
└────────────┘
=== How many versions each product has in dim_product_scd ===
┌────────────┬──────────────┐
│ product_id │ dim_versions │
│ varchar │ int64 │
├────────────┼──────────────┤
│ P001 │ 1 │
│ P002 │ 2 │
│ P003 │ 1 │
│ P004 │ 1 │
└────────────┴──────────────┘
=== The naive JOIN: only by product_id, no version filter ===
┌─────────────┐
│ joined_rows │
│ int64 │
├─────────────┤
│ 50 │
└─────────────┘
fact_orders goes in with 40 rows. The unfiltered JOIN comes out with 50. Ten extra rows — not random, but exactly the ten P002 orders that exist in Kiosko's week, each one duplicated because it found two candidate rows instead of one. No error, no warning: DuckDB did exactly what the query asked, find every row in dim_product_scd whose product_id matches each order's — and for P002, that's two.
Diagram: where the ten extra rows come from
flowchart TD
A["fact_orders: 40 rows\n10 of them with product_id = P002"] --> C{"JOIN ON f.product_id = d.product_id\nno other filter"}
B["dim_product_scd: 5 rows\nP001, P003, P004 with 1 version\nP002 with 2 versions"] --> C
C -->|"P001, P003, P004\n1 match each"| D["30 result rows\n(unchanged)"]
C -->|"P002\n2 matches each"| E["20 result rows\n(10 orders x 2 versions)"]
D --> F["Total: 50 rows\n(40 expected + 10 extra)"]
E --> F
Verify the fan-out row by row, for the exact ten P002 orders:
print("=== P002 duplicates: 10 orders x 2 versions = 20 rows ===")
print(con.sql("""
SELECT f.order_id, d.product_key, d.category, d.unit_cost
FROM fact_orders f
JOIN dim_product_scd d ON f.product_id = d.product_id
WHERE f.product_id = 'P002'
ORDER BY f.order_id, d.product_key
"""))=== P002 duplicates: 10 orders x 2 versions = 20 rows ===
┌──────────┬─────────────┬───────────────┬───────────┐
│ order_id │ product_key │ category │ unit_cost │
│ varchar │ int32 │ varchar │ double │
├──────────┼─────────────┼───────────────┼───────────┤
│ ORD-1002 │ 2 │ snacks │ 0.6 │
│ ORD-1002 │ 5 │ health-snacks │ 0.68 │
│ ORD-1006 │ 2 │ snacks │ 0.6 │
│ ORD-1006 │ 5 │ health-snacks │ 0.68 │
│ ORD-2001 │ 2 │ snacks │ 0.6 │
│ ORD-2001 │ 5 │ health-snacks │ 0.68 │
│ ORD-2005 │ 2 │ snacks │ 0.6 │
│ ORD-2005 │ 5 │ health-snacks │ 0.68 │
│ ORD-4003 │ 2 │ snacks │ 0.6 │
│ ORD-4003 │ 5 │ health-snacks │ 0.68 │
│ ORD-5001 │ 2 │ snacks │ 0.6 │
│ ORD-5001 │ 5 │ health-snacks │ 0.68 │
│ ORD-5005 │ 2 │ snacks │ 0.6 │
│ ORD-5005 │ 5 │ health-snacks │ 0.68 │
│ ORD-6002 │ 2 │ snacks │ 0.6 │
│ ORD-6002 │ 5 │ health-snacks │ 0.68 │
│ ORD-6006 │ 2 │ snacks │ 0.6 │
│ ORD-6006 │ 5 │ health-snacks │ 0.68 │
│ ORD-7002 │ 2 │ snacks │ 0.6 │
│ ORD-7002 │ 5 │ health-snacks │ 0.68 │
└──────────┴─────────────┴───────────────┴───────────┘
20 rows 4 columns
Every P002 order_id appears exactly twice: once matched with product_key = 2 (the closed version, snacks), once with product_key = 5 (the current version, health-snacks). Neither row is "the wrong one" from DuckDB's point of view — both satisfy the condition f.product_id = d.product_id with total legitimacy. The error isn't in the engine: it's in a JOIN condition that can't distinguish between the two versions that exist.
Going deeper: why this is dangerous even when no one notices
This JOIN's real danger isn't that it produces 50 rows instead of 40 — that number is easy to catch with a grain check, exactly the kind module 1 taught. The real danger shows up when someone aggregates over the result without checking the row count first. If you run SUM(revenue) directly over this JOIN, without having counted the rows beforehand, each P002 order's revenue gets summed twice — once for each duplicated row — and the total stops being 106.15 and becomes a higher number that still looks perfectly reasonable if you don't already know it should be 106.15.
print("=== Inflated revenue if you aggregate over the naive JOIN, without checking the count first ===")
print(con.sql("""
SELECT ROUND(SUM(f.revenue), 2) AS inflated_total_revenue, COUNT(*) AS rows
FROM fact_orders f
JOIN dim_product_scd d ON f.product_id = d.product_id
"""))=== Inflated revenue if you aggregate over the naive JOIN, without checking the count first ===
┌────────────────────────┬───────┐
│ inflated_total_revenue │ rows │
│ double │ int64 │
├────────────────────────┼───────┤
│ 127.75 │ 50 │
└────────────────────────┴───────┘
127.75 instead of 106.15 — a difference of 21.60, which isn't coincidence: it's exactly the revenue of the ten P002 orders (21.60, the same number you're going to see again in lesson 3), counted once too many because each of those ten orders got duplicated. A revenue report built on this JOIN, without the grain check in between, would report a business 21.60 bigger than it actually is — an error no technical alarm catches, because 127.75 is a perfectly believable number for anyone who doesn't know the reference 106.15.
This is, in essence, the same kind of fan-out a badly designed JOIN produces in any relational model — a row on the "one" side that actually has more than one counterpart on the "many" side — except here the specific cause is a historized dimension: each extra version of a product is, as far as an unfiltered JOIN is concerned, one more row to match against. The more columns dim_product_scd historizes over time — if Kiosko ever had ten products changing frequently — the more severe the fan-out gets if no one fixes the JOIN condition.
Common mistakes
Trusting that "the JOIN ran without errors" is enough evidence it's correct. What happens: someone writes JOIN dim_product_scd d ON f.product_id = d.product_id, runs it, sees a result with reasonable columns and no error message, and declares the JOIN good. Why it happens: in many programming contexts, "ran without errors" really is a strong signal of correctness. In SQL, a JOIN almost never fails on an incomplete condition — it simply returns more or fewer rows than someone expected, with no warning at all. How to spot it: always compare the JOIN result's COUNT(*) against the original fact table's COUNT(*) — if they don't match, you have fan-out (more rows) or lost rows (fewer rows), exactly the same grain-check principle module 1 taught for fact_orders on its own. How to fix it: never declare a JOIN correct without that comparison — it's the first line of defense against fan-out, even before thinking about the correct filter logic (lesson 3).
Thinking the fan-out is a DuckDB bug or a rare edge case. What happens: someone, surprised by the 50 rows, suspects an error in the engine or the input data, instead of recognizing that the JOIN, as written, precisely describes what was asked. Why it happens: 40 rows going in and 50 coming out feels counterintuitive if you don't explicitly think about how many candidate rows each product_id has on the dimension side. How to spot it: before suspecting the engine, count the versions per product_id in the dimension table — exactly this lesson's GROUP BY product_id query — if any product_id has more than one row, the fan-out isn't a bug, it's expected arithmetic. How to fix it: any time you join against a table that could have more than one row per natural key — a historized dimension, a table with retries, a catalog with versions — count the versions per key before writing the JOIN, not after being surprised by the result.
Aggregating (SUM, AVG, COUNT) over a JOIN result without checking the row count first. What happens: someone jumps straight to SELECT SUM(revenue) FROM fact_orders f JOIN dim_product_scd d ON ..., without running a control COUNT(*) first. Why it happens: the final goal — total revenue, or revenue by category — feels more important than the intermediate step of counting rows, and that step feels skippable to get to the answer faster. How to spot it: if your final query includes an aggregate function over a JOIN, and you never ran, at any point in the process, a COUNT(*) over that same JOIN to compare against the original fact table's count, your aggregated result has no guarantee of being correct. How to fix it: this lesson's discipline — count before aggregating — isn't an optional extra step: it's the only way to catch a fan-out before it contaminates a number someone is going to use to make a business decision.
Exercises
Exercise 1 — Confirm that P001, P003, and P004 don't suffer fan-out. Using the already-built fact_orders and dim_product_scd, write a query that compares, for each product_id that isn't P002, the number of orders in fact_orders against the number of rows the unfiltered JOIN produces for that same product.
See solution
print(con.sql("""
SELECT
f.product_id,
COUNT(DISTINCT f.order_id) AS orders_in_fact,
COUNT(*) AS rows_after_join
FROM fact_orders f
JOIN dim_product_scd d ON f.product_id = d.product_id
WHERE f.product_id != 'P002'
GROUP BY f.product_id
ORDER BY f.product_id
"""))Expected output:
┌────────────┬─────────────────┬──────────────────┐
│ product_id │ orders_in_fact │ rows_after_join │
│ varchar │ int64 │ int64 │
├────────────┼─────────────────┼───────────────────┤
│ P001 │ 16 │ 16 │
│ P003 │ 7 │ 7 │
│ P004 │ 7 │ 7 │
└────────────┴─────────────────┴───────────────────┘
For the three products with no history, orders_in_fact and rows_after_join match exactly — none of them suffers fan-out, because each has a single candidate row in dim_product_scd. This confirms, with additional evidence, that this lesson's problem is specific to P002 — the only product with more than one version — and not a general defect of the JOIN.
Exercise 2 — Calculate how many rows the fan-out would produce if P002 had a third version. Using the result from module 4, lesson 5, exercise 2 (P002 goes up to 0.72 on 2026-08-25, a third version), predict how many rows this lesson's unfiltered JOIN would produce if that third version were already in dim_product_scd. Don't run it — reason it out with the numbers you already have.
See solution
With three versions of P002 instead of two, each of the ten P002 orders would find three candidate rows instead of two, producing 10 x 3 = 30 rows for P002, instead of the current 20. Adding the 30 unchanged rows from P001, P003, and P004, the total would rise to 30 + 30 = 60 rows — twenty more than the expected forty, instead of ten. This confirms a general pattern: an unfiltered JOIN's fan-out grows linearly with the number of historical versions of the dimension — every additional version of any product adds as many extra rows as that product has orders.
Exercise 3 — Explain why COUNT(DISTINCT f.order_id) after the unfiltered JOIN still gives 40, even though COUNT(*) gives 50. In 2-3 sentences, explain this apparent contradiction using what you learned about exactly what the fan-out produces.
See solution
COUNT(*) counts result rows, and each row of the JOIN is a combination of an order with a dimension version — with fan-out, some orders generate more than one row, so the total rises to 50. COUNT(DISTINCT f.order_id), on the other hand, counts unique orders that appear in the result, regardless of how many times each one appears — since the original forty orders are still the same forty orders (none was lost, no new order appeared), that count still gives 40. The difference between 50 and 40 is exactly the measure of the fan-out: ten result rows that correspond to orders already counted, each repeated once too many times.
Summary and next step
This lesson built the most obvious JOIN between fact_orders and dim_product_scd — joining only by product_id, with no additional filter — and confirmed, with a query run against real data, that it produces fan-out: 50 rows instead of 40, the ten extra ones corresponding exactly to the ten P002 orders that exist in Kiosko's week, each matched to the product's two historical versions. You also confirmed that aggregating without checking the count first inflates revenue from 106.15 to 127.75 — a silent error, with no warning message at all.
Before moving on you should be able to: explain from memory why an unfiltered JOIN against a historized dimension produces fan-out; recite the exact number of extra rows it produces in dim_product_scd (ten, one per P002 order); and apply the "count before aggregating" discipline to any new JOIN you write from here on.
Lesson 3 fixes the fan-out — but shows, with the same evidentiary discipline, that the most common way of fixing it (is_current = true) isn't the correct one: it removes the extra rows, but attributes every P002 sale to the present's category and cost, regardless of when each sale happened. The pattern that actually solves the whole problem — the point-in-time join — is that lesson's central topic.
Resources
- Kimball Group — "Slowly Changing Dimension Type 2" — the formal definition of why
dim_product_scdhas more than one row perproduct_id, this lesson's fan-out root cause. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. In English. - DuckDB —
FROMandJOINclause documentation, the syntax reference for theJOINthis lesson builds and measures. duckdb.org/docs/current/sql/query_syntax/from. In English. - DuckDB — official Python client documentation, used to build and query
fact_ordersanddim_product_scdin this lesson. duckdb.org/docs/current/clients/python/overview. In English.