Reverse Snowflake Joins

Fan traps and chasm traps are a consequence of using in same query combinations of tables with mismatching cardinalities. Since Reverse Snowflake Joins is not aware of foreign keys, it can only warn that something is wrong when aggregations are not from the same table.

Chasm traps occur when one table is connected to two or more tables by 1-to-N relationships. This causes multiplication of rows. For example one bad way is to directly try to compare actual sales against sales targets by products.


SELECT 

 p.product, SUM(o.sales), SUM(sp.sales) 

FROM 

 products p INNER JOIN orders o ON p.pid = o.pid 

 INER JOIN salesplans sp on p.pid = sp.pid group by p.pid

Fan traps are caused when joining one table by 1-to-N relationship to a second table that is joined to a third table by another 1-to-N relationship. This again is causing multiplication of rows. For example taking the sum of amounts from the orders table and the count of shipped items from the lineitem table.


SELECT 

 c.customer, SUM(o.ordertotal), SUM(l.quantity) 

  FROM customer c INNER JOIN orders o ON c.cid = o.cid 

  INNER JOIN orderlines l ON o.oid = l.oid 

  GROUP BY c.customer;