Fan and Chasm Traps
Fan traps and chasm traps are two classic join hazards in dimensional and relational models where the structure of the joins causes measures to be over-counted or under-counted, producing numbers that look valid but are wrong.
A fan trap occurs when a table is joined to two other tables that are each related to it on the "one" side of a one-to-many relationship, and a measure is aggregated across that structure. The classic case is joining a fact (or parent) table to two child tables at different grains: aggregating a measure from one table "fans out" because the join multiplies its rows by the number of matching rows in the other. A common shape is a single order joined to both its multiple line items and its multiple shipments—summing order-level amounts across that join inflates them by the number of lines or shipments.
A chasm trap occurs when two "many" tables are joined through a common "one" table (a many-to-one-to-many path), with no direct relationship between the two many-side tables. Querying measures from both many-side tables at once produces a Cartesian-style explosion: every row on one side is paired with every row on the other that shares the central key, multiplying both sets of measures. For example, a Product joined to both Sales and Returns will pair each sale with each return for that product, double-counting both.
The distinction in short: a fan trap fans one measure out across a single chain of one-to-many joins; a chasm trap multiplies two independent one-to-many branches against each other through a shared parent. Both run without error, which is what makes them dangerous.
Key Characteristics
- ▶Both arise from how one-to-many relationships interact when more than two tables are joined
- ▶A fan trap fans one measure out across a single chain of one-to-many joins
- ▶A chasm trap multiplies two independent one-to-many branches through a shared parent
- ▶The underlying mistake is summing a measure across a join that changed the table's grain
- ▶Inflation is uneven—it depends on how many child rows each parent happens to have
- ▶Avoided by aggregating each fact to a common grain before joining, or by drill-across
Why It Matters
- ▶Both yield inflated aggregates while the query runs without error, so they are easy to ship and hard to detect
- ▶Totals that should reconcile to a known figure come out too high, and the inflation factor is uneven and hard to back out
- ▶One wide query that joins many tables and aggregates naively is the warning sign
- ▶The remedy is to pre-aggregate each fact to a common grain in separate subqueries before joining
- ▶Drill-across—querying each fact independently by conformed dimensions, then merging—sidesteps both traps
Example
Chasm trap: Product P has 3 sales (totaling $300) and 2 returns (totaling $40). Joining Sales and Returns directly through Product produces 3 x 2 = 6 rows. Summing sales over those 6 rows yields $300 x 2 = $600, and returns yield $40 x 3 = $120—both doubled or tripled. The fix is to aggregate each branch separately first (sales to $300, returns to $40) and then combine the pre-aggregated results, rather than joining the two detail tables in one query.
Coginiti Perspective
Coginiti's semantic model (SMDL) is the primary defense against fan and chasm traps, because relationships are declared once—one_to_one, one_to_many, many_to_one—with explicit join expressions, and Semantic SQL uses those declarations to construct queries that respect grain. When measures come from different fact entities, the semantic layer can aggregate each to its own grain before combining (the drill-across pattern), rather than producing the naive multi-table join that triggers the trap. Centralizing relationship cardinality in the governed catalog means analysts inherit safe join behavior instead of hand-writing joins that risk fan-out. #+test blocks reinforce this by asserting that aggregates reconcile to known control totals, catching any unexpected multiplication on every pipeline run. And because Coginiti follows ELT patterns with data staying in place across its supported platforms, the atomic facts that the semantic layer aggregates separately remain available at full grain, so each branch can be summed correctly on demand rather than pre-joined into a shape that bakes in the error.
More in OLTP, OLAP & Workload Types
Analytical Workload
An analytical workload is a class of database queries that examine, aggregate, and analyze large volumes of historical data to extract business insights and support decision-making.
Conformed Dimensions
Conformed dimensions are dimensions that mean the same thing—and carry the same keys, attributes, and values—across multiple fact tables or data marts, letting you combine and compare metrics from different business processes with confidence.
Dimension Table
A dimension table is a database table in a star or snowflake schema that stores descriptive attributes used to filter, group, and drill-down in analytical queries.
Fact Table
A fact table is a database table in a star or snowflake schema that stores measures (quantitative data) and foreign keys to dimensions, representing events or transactions in a business process.
Grain
Grain is the level of detail represented by a single row in a table—the precise answer to the question "what does one record mean?"—and it is the foundational decision in dimensional modeling because every dimension and measure in a table must be consistent with it.
HTAP (Hybrid Transactional/Analytical Processing)
HTAP is a database architecture that supports both transactional workloads and analytical workloads on the same data system, enabling real-time analytics without separate data warehouses.
See Semantic Intelligence in Action
Coginiti operationalizes business meaning across your entire data estate.