ADA-C01 Question 328
Single answerResult cacheA BI team reports that a dashboard query is taking 20-30 seconds every time it runs, even when analysts repeatedly execute the exact same SELECT statement within a few minutes. As the Snowflake administrator, you review the workload and confirm the SQL text is unchanged and the referenced tables have not been modified. You need to identify the MOST likely reason Snowflake is not reusing the result cache for these repeated executions. Which factor best explains this behavior?
- A
The warehouse is configured with auto-suspend, so result cache entries are cleared whenever the warehouse suspends.
- B
The query uses a function such as CURRENT_TIMESTAMP, which makes the statement non-reusable for result caching.
- C
The virtual warehouse size is too small to support result cache reuse for dashboard workloads.
- D
The result cache is only reused when the same user reruns the query; different users cannot benefit from cached results.
Show answer and explanation
Correct answer: B
Explanation
Snowflake can reuse persisted query results, often called the result cache, when a later query matches the original query text exactly enough and the referenced data has not changed, among other conditions. However, result reuse is not guaranteed if the query contains non-deterministic or context-sensitive functions such as CURRENT_TIMESTAMP, because the result can legitimately differ between runs. Administrators troubleshooting unexpected re-execution should verify more than just unchanged tables and matching SQL text; they should also inspect whether the query includes functions or constructs that make cached results ineligible for reuse. Snowflake documentation on persisted query results notes conditions for reuse, including unchanged underlying data, compatible privileges, and queries that do not rely on non-reusable functions. This is a practical troubleshooting pattern for BI workloads where teams expect repeated dashboard refreshes to benefit from result caching.
- A. Incorrect.
Incorrect. The persisted query result cache is not tied to whether a virtual warehouse is running or suspended. Auto-suspend affects compute usage, but persisted query results are stored separately by Snowflake for a limited period and can still be reused even after the warehouse suspends, provided other reuse conditions are met.
- B. Correct.
Correct. Queries that include non-reusable or non-deterministic elements such as CURRENT_TIMESTAMP, RANDOM, UUID-generating functions, or other session-dependent constructs typically prevent reuse of persisted query results. Even if the SQL text appears identical and the underlying tables are unchanged, Snowflake may not use the result cache when the statement's output can vary from execution to execution.
- C. Incorrect.
Incorrect. Warehouse size does not determine whether persisted query results can be reused. Result cache reuse is based on factors such as identical query text, unchanged underlying data and objects, privileges, and whether the query is deterministic enough for reuse. A small warehouse may affect performance when execution is required, but not eligibility for result cache reuse.
- D. Incorrect.
Incorrect. Persisted query results are not restricted to the same user only. Other users can benefit from cached results if they execute the exact same query and have the necessary privileges on the underlying objects. This option reflects a common misconception that result cache is user-scoped in all cases.