ADA-C01 Question 329
Select 3Result cacheA BI team runs the same dashboard query every few minutes against a large fact table in Snowflake. The SQL text is generated identically each time, and the team expects repeated executions to return almost instantly by using the result cache. However, they notice that the query frequently consumes warehouse credits instead of reusing a cached result. As the Snowflake administrator, which TWO conditions would prevent Snowflake from reusing the persisted query result for this query?
- A
The underlying table data changed after the original query result was generated.
- B
The query is executed by a different user who has the required privileges on the referenced objects.
- C
The SQL text includes a non-reusable function such as RANDOM() or CURRENT_TIMESTAMP.
- D
The same query is run again within 24 hours, but on a larger virtual warehouse.
- E
The query text is identical, but the session parameter TIMEZONE is different from the original execution.
Show answer and explanation
Correct answers: A, C, E
Explanation
Snowflake persisted query results, often called the result cache, can return prior query output without re-executing the query when strict reuse conditions are satisfied. Key requirements include identical query text, unchanged underlying data, and no use of non-deterministic functions. In addition, session or configuration settings that can affect the returned result must be compatible with the original execution. Result cache reuse is different from the warehouse data cache: warehouse cache depends on the virtual warehouse and local disk state, while persisted query results can be reused across warehouses and, when permissions allow, across users. Snowflake documentation on persisted query results and query result reuse highlights these constraints, especially around data changes, deterministic behavior, and result-affecting session settings.
- A. Correct.
Correct. Snowflake can reuse persisted query results only when the underlying data has not changed in a way that affects the result. If referenced table or micro-partition data changes after the cached result was created, Snowflake must execute the query again rather than return a stale result. This is a core validity requirement for result cache reuse.
- B. Incorrect.
Incorrect. Persisted query results are not limited strictly to the original user, provided the repeated query is identical and the user has the necessary privileges on the underlying objects. A common misconception is that the result cache is always user-specific; in practice, Snowflake can reuse persisted results across sessions and users when eligibility requirements are met.
- C. Correct.
Correct. Queries that contain non-reusable or non-deterministic functions, such as RANDOM(), UUID_STRING(), or time-dependent functions like CURRENT_TIMESTAMP, are not eligible for persisted query result reuse because the result can legitimately differ between executions even when the SQL text is unchanged.
- D. Incorrect.
Incorrect. The persisted query result cache is independent of warehouse size. If the query is otherwise eligible, Snowflake can return the cached result without recomputing it, regardless of whether the repeated query is submitted on a different virtual warehouse. Candidates often confuse result cache behavior with warehouse local disk cache, which is tied to the warehouse cluster.
- E. Correct.
Correct. Session settings that affect query results can invalidate reuse of persisted query results. TIMEZONE is an important example because it can change the output of time-related expressions and conversions. Even with identical SQL text, a changed session parameter that impacts semantics means Snowflake may not reuse the prior result.