ARA-C01 Question 375
Select 24.1 Outline performance tools, best practices, and appropriate scenarios where they should be applied.A retail company runs a nightly ELT pipeline in Snowflake followed by a dashboard workload used by analysts at 8:00 AM. Recently, the dashboard queries have become inconsistent: some complete in seconds while others take several minutes, even when users apply the same filters. The architect notices that the largest fact table is clustered by load date only, while analyst queries typically filter by customer_id, region, and recent transaction dates. The company wants to improve query performance without simply increasing warehouse size. Which TWO actions should the architect take to diagnose and address the issue most appropriately?
- A
Use the Query Profile on representative slow queries to identify whether time is being spent on table scans, joins, or spilling, and then evaluate clustering on columns commonly used in selective filters.
- B
Enable Search Optimization Service on the fact table for columns such as customer_id if the workload includes highly selective lookups, after confirming this access pattern is common enough to justify the cost.
- C
Increase the statement timeout so long-running dashboard queries are allowed to finish, which will reduce performance variability for end users.
- D
Suspend and resume the virtual warehouse before dashboard hours to clear cached data, ensuring all users see consistent runtimes.
- E
Recluster the table more frequently on load date only, because maintaining clustering on the ingestion column is the primary way to optimize all analytical queries.
Show answer and explanation
Correct answers: A, B
Explanation
The best answers are to first diagnose with Query Profile and then apply an optimization that matches the workload pattern. Snowflake performance tuning best practices emphasize understanding query behavior before changing compute size. Query Profile helps identify scan-heavy steps, join bottlenecks, and spilling, while table design techniques such as clustering and Search Optimization Service should be selected based on actual filter patterns. In this scenario, the inconsistency is likely driven by poor micro-partition pruning and/or access patterns not aligned with the current clustering key. Search Optimization Service is especially relevant if queries frequently use highly selective predicates like customer_id on a large table. By contrast, increasing timeouts or clearing cache does not improve underlying performance. Relevant Snowflake documentation areas include Query Profile and query history for troubleshooting, clustering keys and micro-partition pruning for storage optimization, and Search Optimization Service for selective lookup acceleration.
- A. Correct.
Correct. Query Profile is one of the primary Snowflake performance troubleshooting tools and should be used to determine where time is spent in a slow query, such as scanning large numbers of micro-partitions, expensive joins, or local/remote spilling. In this scenario, queries filter on customer_id, region, and recent dates, so reviewing scan behavior and pruning effectiveness is essential. If the current clustering by load date does not align with filter predicates, clustering strategy may need to be adjusted to improve micro-partition pruning.
- B. Correct.
Correct. Search Optimization Service is appropriate for specific access patterns, especially highly selective point-lookups or selective equality predicates on large tables. If analysts frequently query by customer_id and expect fast selective retrieval, this service can materially improve performance without simply scaling compute. However, it has storage and maintenance cost, so validating the workload pattern first is a best practice.
- C. Incorrect.
Incorrect. Increasing statement timeout does not improve query performance; it only allows queries to run longer before cancellation. This addresses a symptom, not the root cause of inconsistent runtimes. Candidates might choose this if they confuse operational tolerance settings with performance optimization techniques.
- D. Incorrect.
Incorrect. Suspending and resuming the warehouse clears the local disk cache, which typically makes repeated queries slower rather than more consistent. Cache behavior can affect runtime, but deliberately clearing cache is not a performance best practice for user-facing analytics. This option reflects a misunderstanding of Snowflake caching and warehouse behavior.
- E. Incorrect.
Incorrect. Re-clustering more frequently on load date only is unlikely to help when the dominant query predicates are customer_id, region, and recent transaction dates. Clustering strategy should align with common filtering and pruning patterns. Load date may be useful for data lifecycle or time-based filtering, but it is not automatically the best clustering choice for all analytical workloads.