COF-C03 Question 289
Single answerQuery result cacheA BI team runs the same dashboard query every few minutes against a Snowflake table named SALES_SUMMARY. The SQL text is identical each time, and the underlying table data has not changed. However, the team notices that some executions still consume warehouse compute instead of returning instantly from cache. Which condition would prevent Snowflake from using the query result cache for these repeated executions?
- A
The query is executed by a different user who has the required privileges on the underlying objects.
- B
The virtual warehouse used for the query is larger than the warehouse used for the previous execution.
- C
The SQL statement includes a non-reusable function such as CURRENT_TIMESTAMP in the SELECT list.
- D
The query references a permanent table rather than a transient table.
Show answer and explanation
Correct answer: C
Explanation
Snowflake can reuse persisted query results when the same query text is submitted again and the referenced data has not changed, provided other reuse requirements are also satisfied. One important exception is the use of non-deterministic or non-reusable functions, such as CURRENT_TIMESTAMP, which can produce different values on each run. In that case, Snowflake cannot safely return the previous result. Candidates should distinguish query result cache behavior from warehouse data cache behavior: persisted query results are based on query/result reuse conditions, not warehouse size. Snowflake documentation on persisted query results notes requirements such as identical text, no changes to contributing data, and restrictions involving functions whose results are not reusable.
- A. Incorrect.
Incorrect. Snowflake's persisted query results can be reused by another user if the SQL text is identical and the user has the necessary privileges on all referenced objects. A different user does not by itself prevent reuse of the query result cache.
- B. Incorrect.
Incorrect. Persisted query results are not tied to a specific warehouse size. If all cache reuse conditions are met, Snowflake can return the cached result regardless of warehouse size, and in many cases without needing warehouse compute for the actual result generation.
- C. Correct.
Correct. Queries that include non-reusable or non-deterministic functions such as CURRENT_TIMESTAMP, RANDOM, or UUID_STRING are not good candidates for persisted query result reuse because the result can change from one execution to the next even if the base table data is unchanged. This prevents Snowflake from reusing the prior query result.
- D. Incorrect.
Incorrect. The table type is not the deciding factor for persisted query result reuse. Whether a table is permanent, transient, or temporary does not by itself disqualify the query from using the result cache. Cache eligibility depends on factors such as identical SQL text, unchanged underlying data, valid privileges, and absence of non-reusable functions.