COF-C03 Question 263
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 ORDERS to CLICK_EVENTS on CUSTOMER_ID to enrich each order with customer activity. However, CLICK_EVENTS contains many rows per customer per day, and the report only needs to know whether the customer had at least one click event in the 7 days before each order. Query Profile shows a very large increase in intermediate rows during the join step. Which approach is the BEST way to reduce the exploding join while still meeting the reporting requirement?
- A
Pre-aggregate CLICK_EVENTS to one row per relevant customer and order window, then join that reduced result set to ORDERS
- B
Replace the join with a CROSS JOIN and apply the date filter in the WHERE clause after the join
- C
Create a larger virtual warehouse so Snowflake can process the exploding join faster without changing the SQL logic
- D
Cluster both tables on CUSTOMER_ID so the many-to-many join no longer produces excessive intermediate rows
Show answer and explanation
Correct answer: A
Explanation
Exploding joins occur when a join produces far more rows than expected due to join cardinality, often when detail-level records are joined without first reducing them to the required grain. In this scenario, the report needs a boolean-style existence check, not every matching click event row. The best solution is therefore to rewrite the query so the event table is reduced before the join, such as by aggregating, deduplicating, or using EXISTS/SEMijoin-style logic. In Snowflake, Query Profile can help identify this issue by showing a join step with a sharp increase in output rows relative to input rows. While scaling up a warehouse can sometimes improve runtime, Snowflake best practices emphasize fixing inefficient SQL patterns first when possible. Clustering may help data pruning, but it does not prevent row multiplication caused by join logic.
- A. Correct.
Correct. The problem is caused by joining ORDERS to a detail-level event table where multiple CLICK_EVENTS rows match each order, creating an exploding join with many intermediate rows. Since the business requirement is only to determine whether at least one qualifying event exists, the best practice is to reduce cardinality before the join. Examples include using a derived table, EXISTS, or an aggregated subquery that returns one row per required grain instead of all matching event rows. This minimizes unnecessary row multiplication and is aligned with query tuning practices in Snowflake.
- B. Incorrect.
Incorrect. A CROSS JOIN would make the explosion worse because it creates combinations of rows before filtering. Although Snowflake's optimizer can transform some joins, explicitly using a CROSS JOIN here is not an appropriate strategy for controlling row growth. This option reflects a misunderstanding of how join cardinality affects intermediate result sizes.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time in some cases by providing more compute resources, but it does not address the root cause: unnecessary row multiplication from the join logic. The query would still process an overly large intermediate result set, which can continue to waste compute and increase cost. SQL redesign is the better solution when the issue is an exploding join.
- D. Incorrect.
Incorrect. Clustering can improve pruning and scan efficiency for some workloads, but it does not change the logical cardinality of the join. If multiple CLICK_EVENTS rows still match each order, the join can still explode. This option is plausible because clustering is a performance feature, but it is not the primary fix for a many-to-many or one-to-many join explosion problem.