Fan traps and chasm traps are structural schema traps in dimensional modeling where one-to-many joins cause cartesian row multiplication, leading to inflated sums and distorted metrics in analytical reports. They are resolved by pre-aggregating fact measures to a common grain before joining, a technique known as drilling across.
Fan traps and duplicated metrics
A fan trap occurs when a query joins a master table to a child table across a one-to-many relationship and attempts to sum a measure from the master table:
Schema: Customer (1) ---> Orders (N) ---> Order_Items (M)
If an order has a header-level shipping fee of twenty dollars and contains four line items, joining orders to order_items duplicates the order row four times. Running SUM(shipping_fee) calculates eighty dollars instead of twenty dollars. The measure on the "one" side fans out across the "many" side, corrupting financial reports.
Chasm traps and cartesian blowups
A chasm trap occurs when two independent fact tables share a dimension table, and a query attempts to join both facts directly through that shared dimension:
Schema: Orders (M) <--- Customer (1) ---> Support_Tickets (N)
If a customer has five orders and three support tickets, joining orders and support_tickets through customer produces a cartesian product of fifteen rows. Any aggregate function like SUM(order_amount) or COUNT(ticket_id) calculates wild, mathematically invalid totals because each order multiplies against every support ticket.
The drill-across pattern in BI
To prevent these metric corruptions, never join two distinct fact tables directly across a one-to-many relationship. Instead, apply the Kimball drill-across pattern:
- Aggregate each fact table independently in separate subqueries or CTEs to the grain of the shared dimension.
- Join the pre-aggregated summary tables together on the conformed dimension key.
Modern BI tools and semantic layers prevent chasm traps automatically by issuing separate SQL queries for each fact and stitching the results together in memory.