Reproduce a fan trap and a chasm trap

Break it on purposeNeeds Python

Problem

Every table has a grain: what exactly one row is. Joining a table at one grain to a table with several rows per row of the first repeats the first table's rows, so summing its measure afterwards counts it once per match: a fan trap. Joining two independent one-to-many branches from the same entity (a customer's orders and the same customer's tickets) pairs every row of one with every row of the other: a chasm trap, whose rows correspond to nothing anyone observed, and whose multiplier changes from customer to customer. Both queries run fast and pass every check; only the totals are wrong.

Create the three-order freight example, then the customer with orders and tickets. Measure how wrong the sum is and show that aggregating first fixes it. Now add a customer C3 with orders but no tickets and see it vanish from the inner join. Then rewrite the query with one aggregate per branch.

The question. How far off are the freight total and customer C1's order total when summed across the join, and what happens to a customer with no tickets?

Predict

In the orders × tickets inner join, what happens to customer C3, who has orders and no tickets?

C3 appears with its ordersC3 appears with zeroC3 disappears

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

reproduce-a-fan-trap-and-a.py49 linesDownload

View the code

reproduce-a-fan-trap-and-a.py

# /// script
# requires-python = ">=3.10"
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""Reproduce a fan trap and a chasm trap.

A join to a finer-grained table repeats each row of the coarser one, so a sum
over the join counts it once per match (fan trap). Two independent
one-to-many branches from the same entity multiply (chasm trap), and an inner
join drops entities that lack one branch. Aggregating each branch first fixes both.
"""
import argparse, json, sqlite3

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders(order_id TEXT, freight INT);
CREATE TABLE lines(order_id TEXT, line_no INT);
INSERT INTO orders VALUES('A',10),('B',10),('C',10);
INSERT INTO lines VALUES('A',1),('B',1),('B',2),('C',1),('C',2),('C',3),('C',4);
CREATE TABLE ord(customer TEXT, amount INT);
CREATE TABLE tix(customer TEXT, ticket INT);
INSERT INTO ord VALUES('C1',100),('C1',200),('C1',300),('C3',50);
INSERT INTO tix VALUES('C1',1),('C1',2);
""")
q = lambda sql: db.execute(sql).fetchall()
rows = [
    {"case": "freight, summed over the join to lines", "result": q("SELECT SUM(o.freight) FROM orders o JOIN lines l USING(order_id)")[0][0], "correct": 30},
    {"case": "freight, summed on orders alone", "result": q("SELECT SUM(freight) FROM orders")[0][0], "correct": 30},
    {"case": "C1 orders, summed over the join to tickets", "result": q("SELECT SUM(amount) FROM ord JOIN tix USING(customer) WHERE customer='C1'")[0][0], "correct": 600},
    {"case": "customers in the orders x tickets inner join", "result": len(q("SELECT DISTINCT customer FROM ord JOIN tix USING(customer)")), "correct": 2},
]
fixed = q("""
WITH o AS (SELECT customer, SUM(amount) AS amount FROM ord GROUP BY customer),
     t AS (SELECT customer, COUNT(*) AS tickets FROM tix GROUP BY customer)
SELECT o.customer, o.amount, COALESCE(t.tickets, 0) FROM o LEFT JOIN t USING(customer) ORDER BY 1""")
rows.append({"case": "C1 orders, one aggregate per branch", "result": fixed[0][1], "correct": 600})
rows.append({"case": "customers, one aggregate per branch", "result": len(fixed), "correct": 2})
for r in rows: print(f"{r['case']:<48} {r['result']:>6}   (correct: {r['correct']})")
print("per-branch result:", fixed)

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"cases": rows}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.
Terminal
```
uv run reproduce-a-fan-trap-and-a.py
uv run reproduce-a-fan-trap-and-a.py --help     # every knob
```

Our run

Sums across joins, and after aggregating each branch first

Sums across joins, and after aggregating each branch first
Query	Result	Correct
freight, summed over the join to lines	70	30
freight, summed on orders alone	30	30
C1 orders, summed over the join to tickets	1,200	600
customers in the orders x tickets inner join	1	2
C1 orders, one aggregate per branch	600	600
customers, one aggregate per branch	2	2

Reference runApple M2 · 8 cores · 8 GB

Output · uv run reproduce-a-fan-trap-and-a.py

freight, summed over the join to lines               70   (correct: 30)
freight, summed on orders alone                      30   (correct: 30)
C1 orders, summed over the join to tickets         1200   (correct: 600)
customers in the orders x tickets inner join          1   (correct: 2)
C1 orders, one aggregate per branch                 600   (correct: 600)
customers, one aggregate per branch                   2   (correct: 2)
per-branch result: [('C1', 600, 2), ('C3', 50, 0)]

macOS 26.6.2 · Python 3.13.3 · run 2026-10-20

What we found

Summed across the join to order lines, freight came to 70 instead of 30: each order's freight was counted once per line. Customer C1's orders, summed across the join to tickets, came to 1,200 instead of 600: three orders times two tickets. The inner join also kept only 1 of the 2 customers; C3, with orders and no tickets, disappeared. With one aggregate per branch, joined afterwards, both were right: 600 for C1 and 2 customers.

Read it

You cannot join across grains without either an allocation rule or double counting. The safe pattern for both traps is to aggregate each branch to the shared dimension's grain first, then join the aggregates, and to record next to each measure the grain it lives at (freight lives at order grain and must be aggregated before any join to lines).

The chasm trap is the worse of the two: its multiplier is the size of the other branch, so it differs per customer, and a spot check on one customer does not reveal the size of the error on another.