ARA-C01 Question 412
Single answerCache expirationA Snowflake architect is troubleshooting inconsistent query performance for a BI dashboard that runs the same SELECT statement every 10 minutes. The SQL text is unchanged, the underlying tables are not modified during business hours, and the virtual warehouse remains running. Users report that some executions return almost instantly while others take much longer, even though the dashboard query and data are the same. Which explanation best accounts for this behavior?
- A
The persisted query result cache can expire after 24 hours, so identical queries may stop using cached results even when the data and SQL text have not changed.
- B
The virtual warehouse local disk cache is retained indefinitely as long as the warehouse is not resized, so performance variation must be caused by network latency outside Snowflake.
- C
Snowflake automatically clears all query caches every 10 minutes to ensure transactional consistency, which explains the periodic slowdown.
- D
The metadata cache for cloud services expires whenever a query is submitted by a different user, so the same SQL will alternate predictably between fast and slow runtimes.
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because it aligns with Snowflake's persisted query result behavior: identical queries can return almost instantly when the result is reused, but that reuse window is limited, typically up to 24 hours, assuming the underlying data has not changed and other reuse conditions are met. In contrast, the warehouse cache accelerates table data access at the compute layer but is not an indefinite cache and is affected by warehouse lifecycle events. Snowflake does not use a universal 10-minute cache expiration policy for query results, and metadata caching is not the main factor in this scenario. For exam purposes, distinguish among result cache expiration, which explains sudden changes after an otherwise stable period, and warehouse cache retention, which is tied to the compute environment. See Snowflake documentation on persisted query results and warehouse caching behavior for the official rules and limitations.
- A. Correct.
Correct. Snowflake's persisted query results are typically reusable for up to 24 hours when the exact query text is rerun and the underlying data and other reuse conditions have not changed. If a dashboard runs frequently, many executions may return almost instantly from the result cache. After the 24-hour reuse window expires, the query must be executed again, which can cause a noticeable increase in runtime even though the SQL and data are unchanged. This is a common real-world explanation for seemingly inconsistent performance.
- B. Incorrect.
Incorrect. The warehouse data cache is not retained indefinitely. It is tied to the warehouse's compute resources and can be lost when the warehouse suspends, restarts, or the underlying compute resources change. Even if the warehouse remains running, saying it is retained indefinitely is inaccurate. Also, local disk cache behavior does not by itself prove the slowdown is due to external network latency.
- C. Incorrect.
Incorrect. Snowflake does not clear all query caches every 10 minutes. This distractor reflects a misunderstanding of how Snowflake maintains consistency. Query result reuse depends on whether the underlying data and query conditions still qualify, not on a fixed 10-minute cache invalidation schedule.
- D. Incorrect.
Incorrect. A different user does not inherently force expiration of all metadata caching in a way that would create this pattern. Persisted query result reuse has specific eligibility rules, including query text and whether the result is still valid, but metadata cache expiration is not the primary explanation described here, nor does it alternate predictably simply because of user changes.