ARA-C01 Question 365
Single answerPerformance impactsA retail company runs an hourly dashboard workload in Snowflake against a 12 TB SALES_FACT table joined to several small dimension tables. The dashboard filters on ORDER_DATE and REGION, and the SQL text is stable across executions. Users report that response times have become inconsistent: some runs complete in seconds, while others take several minutes even though data volume and the warehouse size have not changed. An architect reviews Query Profile and warehouse history and finds the following: many queries are scanning a large percentage of micro-partitions in SALES_FACT, automatic clustering is not enabled, and several dashboard sessions frequently trigger result cache misses because underlying tables are updated every few minutes. Which action would MOST directly improve query performance consistency for this workload?
- A
Define a clustering key on SALES_FACT using columns such as ORDER_DATE and REGION so pruning is more effective for the dashboard predicates
- B
Increase the Time Travel retention period on SALES_FACT so repeated dashboard queries can access older versions more efficiently
- C
Convert the small dimension tables to temporary tables before each dashboard run to reduce join overhead
- D
Rely on the result cache by forcing all users to run the exact same SQL text from the same role and warehouse
Show answer and explanation
Correct answer: A
Explanation
This scenario is centered on performance impacts from poor micro-partition pruning and overreliance on result caching. In Snowflake, large table scan efficiency is heavily influenced by how well micro-partition metadata can eliminate irrelevant partitions. When a table is frequently filtered by specific columns, and query profile shows a large percentage of micro-partitions being scanned, clustering can materially improve pruning and reduce elapsed time variability. This is particularly valuable when result cache is unreliable due to frequent DML on underlying objects. Snowflake best practices emphasize using clustering selectively for very large tables where query predicates align with candidate clustering columns and where pruning benefits justify maintenance cost. By contrast, Time Travel is for data protection and historical access, temporary tables do not solve fact-table scan inefficiency, and result cache cannot be treated as a guaranteed performance strategy when data is changing often. Relevant Snowflake documentation includes guidance on micro-partitions, clustering keys, query profile interpretation, and result cache behavior.
- A. Correct.
Correct. The primary issue described is poor micro-partition pruning on a large fact table filtered by ORDER_DATE and REGION. Defining an appropriate clustering key can improve co-location of rows with similar values across micro-partitions, reducing the number of partitions scanned and making performance more consistent. This is especially relevant when result cache cannot be relied on because frequent DML changes invalidate cached results. For large tables with selective filter predicates, clustering is a practical architectural optimization.
- B. Incorrect.
Incorrect. Time Travel retention affects how long historical table versions are available for recovery and historical queries; it does not improve pruning or runtime performance for current dashboard queries. In some cases, longer retention can increase storage costs, but it is not a direct performance tuning mechanism for scan efficiency.
- C. Incorrect.
Incorrect. The dimension tables are already small, so they are unlikely to be the main source of inconsistent performance. Recreating them as temporary tables before each run adds operational complexity and is not a recognized Snowflake optimization for this scenario. The Query Profile evidence points to excessive scanning of the large fact table, not join overhead from small dimensions.
- D. Incorrect.
Incorrect. Result cache can improve performance when queries are identical and underlying data has not changed, but the scenario explicitly states that the base tables are updated every few minutes, leading to frequent cache misses. Standardizing SQL text, role, and warehouse may increase cache reuse in some cases, but it will not address the root cause of inconsistent performance when data changes invalidate cached results.