COF-C03 Question 262
Single answerExploding joinsA data engineering team notices that a daily reporting query in Snowflake has become much slower after a new table was added. The query joins a 50 million-row SALES table to a 10 million-row PROMOTION_RULES table using a condition like SALES.SALE_DATE BETWEEN PROMOTION_RULES.START_DATE AND PROMOTION_RULES.END_DATE and SALES.REGION = PROMOTION_RULES.REGION. Query Profile shows a very large increase in rows produced during the join step compared with the input rows from both tables. Which action is the BEST way to address this exploding join problem while preserving correct results?
- A
Rewrite the logic so the join is as selective as possible before the range match, such as pre-filtering PROMOTION_RULES and ensuring the query returns at most the intended matches per sale
- B
Create a materialized view on SALES so Snowflake can automatically eliminate the extra rows created by the join
- C
Replace the join with a CROSS JOIN and then filter after the join so Snowflake can optimize the predicates more effectively
- D
Increase the warehouse size because exploding joins are caused primarily by insufficient compute rather than join logic
Show answer and explanation
Correct answer: A
Explanation
This scenario describes a classic exploding join: the join step produces far more rows than are fed into it, which is visible in Query Profile. In Snowflake, this commonly happens with many-to-many joins, missing or weak join predicates, and range/band joins such as BETWEEN conditions. The practical best practice is to reduce cardinality before the join, tighten predicates, and ensure the business logic limits each fact row to the intended dimension or rule rows. Query Profile is the key diagnostic tool because it shows row counts and where intermediate results expand unexpectedly. Increasing warehouse size may mask the symptom but does not correct the inefficient join pattern. This aligns with Snowflake performance tuning guidance: inspect Query Profile, identify operators with unexpected row expansion, and improve join selectivity or query design to reduce intermediate result size.
- A. Correct.
Correct. An exploding join occurs when the join condition causes many more output rows than expected, often due to non-selective predicates, range joins, or many-to-many relationships. In this scenario, a date-range join can match multiple promotion rules for a single sale, especially if rules overlap within a region. The best fix is to reduce the number of candidate rows before the join and/or redesign the logic so each sale matches only the intended rule rows. This addresses the root cause rather than just consuming more compute.
- B. Incorrect.
Incorrect. A materialized view can improve performance for some repeated query patterns, but it does not inherently fix an exploding join caused by join cardinality. If the underlying logic still produces too many matches, the row explosion remains. Materialized views are not a mechanism for automatically removing duplicate or unintended join results.
- C. Incorrect.
Incorrect. A CROSS JOIN explicitly creates a Cartesian product before filtering, which generally makes row explosion worse, not better. Snowflake's optimizer can handle many join patterns, but intentionally broadening the join to all combinations is not a best practice for a scenario already showing excessive row growth.
- D. Incorrect.
Incorrect. A larger warehouse may reduce runtime temporarily by providing more compute resources, but it does not solve the underlying cardinality problem. Exploding joins are fundamentally a query design and data relationship issue. Scaling compute can increase cost while still returning an unnecessarily large intermediate result set.