ARA-C01 Question 374
Select 24.1 Outline performance tools, best practices, and appropriate scenarios where they should be applied.A retail company runs hundreds of dashboard queries every morning against a 12 TB Snowflake fact table joined to several dimensions. Users report that the dashboards are intermittently slow even though the SQL text is largely unchanged. An architect reviews recent executions and notices that some runs complete in seconds while others take several minutes. The company wants to identify the root cause and apply the most appropriate Snowflake performance tools before simply increasing warehouse size. Which TWO actions should the architect take?
- A
Use Query Profile on representative slow queries to determine whether time is dominated by remote disk I/O, join processing, or spilling, and compare it with faster executions of the same query pattern.
- B
Review warehouse load and concurrency metrics in Snowsight/ACCOUNT_USAGE to determine whether queued overload or insufficient clusters are causing variable response times, and consider multi-cluster warehouses if concurrency is the issue.
- C
Enable Search Optimization Service on every table referenced by the dashboard because it is the primary tool for improving performance of large analytical joins and aggregations.
- D
Rely on automatic clustering of micro-partitions by increasing the virtual warehouse size, because larger warehouses reorganize table storage and eliminate pruning problems over time.
- E
Use EXPLAIN alone as the primary diagnostic tool for the slow executions, because it captures actual runtime bottlenecks such as spilled bytes and remote scan time after the query finishes.
Show answer and explanation
Correct answers: A, B
Explanation
The best answers are to use Query Profile and to review warehouse concurrency/load behavior. In Snowflake, performance troubleshooting should start with evidence from actual executions rather than immediately scaling compute. Query Profile is the primary tool for examining runtime bottlenecks, including scan behavior, join costs, and spilling. For variable dashboard performance, architects should also check whether warehouse contention, queueing, or concurrency spikes are contributing to latency; Snowflake exposes this through Snowsight monitoring and ACCOUNT_USAGE views such as QUERY_HISTORY and warehouse-related usage views. If concurrency is the main issue, a multi-cluster warehouse can be an appropriate solution. By contrast, Search Optimization Service should be used selectively for appropriate access patterns, not broadly across all analytical tables. Likewise, warehouse size does not recluster storage, and EXPLAIN does not replace runtime diagnostics. These approaches align with Snowflake best practices for using the right performance tool for the right scenario.
- A. Correct.
Correct. Query Profile is one of the most important runtime diagnostic tools in Snowflake for understanding where time is spent in an executed query. It helps identify whether performance issues are due to scan volume, poor pruning, expensive joins, data spilling, remote disk I/O, or other execution-stage bottlenecks. Comparing a slow run to a fast run of the same SQL pattern is a practical way to isolate changing conditions such as cache effects, warehouse contention, or differences in partition pruning.
- B. Correct.
Correct. Intermittent slowness with otherwise similar SQL often points to concurrency or warehouse pressure rather than purely query design. Reviewing warehouse load, queued queries, and concurrency metrics in Snowsight or ACCOUNT_USAGE views helps determine whether the warehouse is saturated. If many dashboard queries start at the same time, a multi-cluster warehouse can be an appropriate remedy for concurrency-driven latency, whereas resizing alone may not address queueing patterns as effectively.
- C. Incorrect.
Incorrect. Search Optimization Service is a specialized performance feature best suited for selective point-lookups and certain highly selective predicates, including some join cases with low-cardinality access patterns. It is not a blanket recommendation for every table in a dashboard workload, and it is not the primary tuning mechanism for broad analytical joins and aggregations over large fact tables. Applying it everywhere would be costly and often ineffective.
- D. Incorrect.
Incorrect. Increasing warehouse size provides more compute for query execution, but it does not reorganize table storage or automatically solve micro-partition pruning issues. Snowflake storage layout and clustering behavior are separate concerns from warehouse sizing. This option reflects a common misconception that compute scaling changes micro-partition organization.
- E. Incorrect.
Incorrect. EXPLAIN is useful for viewing the planned execution strategy before runtime, but it does not show actual runtime statistics such as bytes spilled, queueing, or remote scan behavior observed during execution. For intermittent slowdowns, actual execution diagnostics from Query Profile are more appropriate than relying on EXPLAIN alone.