ARA-C01 Question 378
Single answerInterpret a Query Profile, identify bottlenecks, and outline recommendationsA data engineering team reports that a daily transformation query has become significantly slower over the last two weeks. The query joins a 12 TB SALES_FACT table to a 15 MB REGION_DIM table, filters on SALES_DATE for the last 7 days, and aggregates revenue by region. In the Snowflake Query Profile, the architect observes the following: (1) the TABLE SCAN on SALES_FACT reads almost all micro-partitions with very low pruning, (2) most elapsed time is spent in the scan step rather than the join or aggregate steps, and (3) the join to REGION_DIM is implemented efficiently as a broadcast join. Which recommendation would MOST directly address the primary bottleneck shown in the Query Profile?
- A
Define or refine clustering on SALES_FACT using SALES_DATE so micro-partition pruning improves for the 7-day filter
- B
Increase the warehouse size by two levels because broadcast joins require more memory than partitioned joins
- C
Convert REGION_DIM into a materialized view so the join no longer requires a broadcast operation
- D
Rewrite the query to use a CROSS JOIN followed by a WHERE clause so the optimizer can reorder joins more effectively
Show answer and explanation
Correct answer: A
Explanation
This scenario tests the ability to interpret a Query Profile and distinguish the true bottleneck from secondary plan details. The key signals are: very low micro-partition pruning on the large SALES_FACT table, most elapsed time concentrated in the scan step, and an already efficient broadcast join to a very small dimension table. In Snowflake, when a selective filter such as SALES_DATE for the last 7 days cannot effectively prune micro-partitions, query performance often degrades because far more data is scanned than necessary. A recommendation to improve clustering around the filter column is therefore the most targeted fix. This aligns with Snowflake best practices around using clustering strategically for very large tables where selective filters benefit from improved pruning. By contrast, changing the join method or focusing on the small dimension table would not address the root cause shown in the profile. Relevant Snowflake documentation and guidance include Query Profile interpretation, micro-partitions and data pruning, and clustering keys for large tables with selective query predicates.
- A. Correct.
Correct. The Query Profile shows the dominant cost is the TABLE SCAN on SALES_FACT, with almost all micro-partitions being read and very low pruning. Since the query filters on SALES_DATE for only the last 7 days, improving clustering on SALES_DATE can make micro-partition metadata more selective and reduce scan volume. This directly targets the observed bottleneck rather than changing parts of the plan that are already efficient.
- B. Incorrect.
Incorrect. Increasing warehouse size can sometimes reduce elapsed time by adding compute resources, but it does not directly fix poor micro-partition pruning. The profile indicates the main issue is that nearly all partitions are being scanned. Also, the broadcast join is already described as efficient and is not the dominant cost driver in this plan.
- C. Incorrect.
Incorrect. Materialized views can help some repeated query patterns, but this recommendation does not address the specific evidence in the Query Profile. The join to REGION_DIM is already efficient, and converting a small dimension table into a materialized view would not solve the fact that the large fact table scan reads almost all micro-partitions.
- D. Incorrect.
Incorrect. A CROSS JOIN would generally increase work and is not an optimization here. Snowflake's optimizer already handles join strategies, and the profile does not indicate a join-order problem. The real issue is the large scan on SALES_FACT with poor pruning, not the join implementation.