ARA-C01 Question 416
Select 24.2 Troubleshoot performance issues with existing architectures.A retail company has a star-schema data mart in Snowflake. Analysts report that a dashboard query that used to finish in under 10 seconds now regularly takes 2-3 minutes during business hours. The query joins a 6 TB SALES_FACT table to several small dimension tables and filters on SALES_DATE and REGION. The architect reviews Query Profile and observes that most elapsed time is spent scanning the SALES_FACT table, with little evidence of partition pruning. The SALES_FACT table is loaded continuously in timestamp order, and users frequently filter by SALES_DATE, REGION, and sometimes CHANNEL. Which actions should the architect take to improve performance while minimizing unnecessary cost? (Choose two.)
- A
Define a clustering key on commonly filtered columns such as SALES_DATE and REGION, then monitor clustering depth to verify improved micro-partition pruning
- B
Increase the virtual warehouse size for all dashboard workloads to 4XL so the query can brute-force scan the table faster
- C
Use SYSTEM$CLUSTERING_INFORMATION and Query Profile to confirm poor pruning and validate whether reclustering the large fact table will materially help
- D
Convert the small dimension tables to temporary tables so the optimizer can complete joins faster
- E
Create a materialized view that pre-joins the fact table with all dimension tables regardless of query patterns
Show answer and explanation
Correct answers: A, C
Explanation
This scenario is centered on troubleshooting performance in an existing Snowflake architecture by identifying the real bottleneck rather than simply adding compute. Query Profile shows the query spends most of its time scanning the 6 TB fact table, and there is little partition pruning. In Snowflake, effective micro-partition pruning is critical for large-table performance. When tables are loaded in one order but queried by different dimensions, clustering can degrade over time relative to access patterns. The best practice is to first validate the problem using Query Profile and clustering metadata, such as SYSTEM$CLUSTERING_INFORMATION, then apply a clustering key only when the filtering columns and workload justify the maintenance cost. Increasing warehouse size may mask the issue but often raises cost without fixing excessive data scanning. Likewise, changing small dimensions to temporary tables or creating a broad pre-joined materialized view does not directly address the primary bottleneck. Relevant Snowflake guidance includes using Query Profile for bottleneck identification, understanding micro-partition pruning behavior, and applying clustering strategically for very large tables with selective filter predicates.
- A. Correct.
Correct. The scenario indicates that the large fact table is being scanned heavily and that micro-partition pruning is weak. Because data is loaded continuously in timestamp order, the table may not be well organized for common filter predicates like SALES_DATE and REGION, especially if query access patterns differ from load order over time. Defining an appropriate clustering key on frequently filtered columns can improve pruning and reduce the amount of data scanned. Monitoring clustering depth and pruning effectiveness is important because clustering adds maintenance cost and should be justified by measurable benefit.
- B. Incorrect.
Incorrect. Upsizing the warehouse may reduce elapsed time for some scans by adding compute, but it does not address the root cause shown in Query Profile: poor pruning on a very large table. This approach can significantly increase cost while still scanning far more data than necessary. Warehouse scaling is appropriate in some situations, but here the more targeted optimization is improving data organization and pruning first.
- C. Correct.
Correct. Before committing to clustering, the architect should verify the extent of clustering issues using Query Profile and functions such as SYSTEM$CLUSTERING_INFORMATION. This aligns with Snowflake best practices: diagnose whether poor micro-partition pruning is the actual bottleneck and determine whether clustering on the candidate columns will likely improve selectivity. This option focuses on evidence-based tuning rather than applying a costly feature blindly.
- D. Incorrect.
Incorrect. Temporary tables are session-scoped objects and do not make joins inherently faster. The dimension tables are already small, and the profile shows the dominant issue is scanning the large fact table, not dimension join overhead. This option reflects a common misconception that table type alone improves optimizer behavior or join performance.
- E. Incorrect.
Incorrect. A materialized view can help some repetitive query patterns, but creating one that pre-joins the large fact table with all dimensions regardless of workload is usually expensive and not targeted. Materialized views in Snowflake are best when they match repeated query patterns and provide a clear maintenance-versus-performance tradeoff. In this case, the core issue is lack of pruning on the fact table, so clustering analysis is the more direct and cost-conscious response.