COF-C03 Question 288
Single answerQuery result cacheA BI team runs a dashboard query every 10 minutes to show the current day's total sales:
SELECT SUM(amount) FROM sales WHERE order_date = CURRENT_DATE();
The SQL text is submitted unchanged by the dashboard tool, and the underlying SALES table is continuously receiving new rows throughout the day. The team expects Snowflake to reuse the query result cache to avoid repeated compute charges, but they observe that the warehouse continues to execute the query each time.
Which factor best explains why the query result cache is not being reused in this scenario?
- A
The query uses CURRENT_DATE(), so the statement includes a non-reusable function and is not eligible to reuse the persisted query result.
- B
The query result cache can only be reused if the warehouse size is identical and the warehouse remains running between executions.
- C
Aggregate queries such as SUM() are not eligible for query result caching unless the source table is clustered.
- D
The query result cache is only available for queries against permanent tables, not tables that are actively receiving inserts.
Show answer and explanation
Correct answer: A
Explanation
Snowflake's persisted query results, commonly called the query result cache, can return results without re-executing the query when specific conditions are met. Key requirements include identical query text, no relevant underlying data changes, unchanged configuration that affects results, and absence of non-reusable functions. Functions such as CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), RANDOM(), and UUID_STRING() are considered non-reusable for this purpose.
In this scenario, even though the SQL text is unchanged, the use of CURRENT_DATE() prevents reuse of the persisted query result. Additionally, because the SALES table is continuously changing, that could also prevent reuse if new rows affect the result set. But the best answer is the non-reusable function, because it is a definitive blocker built into the query itself.
This aligns with Snowflake documentation on persisted query results: reuse requires identical text and stable inputs, and queries using non-reusable functions are not candidates for result reuse. A practical best practice is to avoid volatile functions in queries when expecting persisted query result reuse, or to pass fixed literal values from the application when appropriate.
- A. Correct.
Correct. Snowflake can reuse persisted query results only when the new query matches the previous query exactly and the contributing data has not changed, among other conditions. Queries that include non-reusable functions such as CURRENT_DATE(), CURRENT_TIMESTAMP(), RANDOM(), or UUID_STRING() are not eligible to reuse persisted query results because the result can vary over time even when the SQL text is identical. In this scenario, CURRENT_DATE() makes the query ineligible for query result reuse.
- B. Incorrect.
Incorrect. Persisted query results are not tied to a warehouse remaining running, and reuse does not require the same warehouse size. One benefit of the query result cache is that a matching eligible query can return without re-executing compute, regardless of warehouse state, provided all reuse conditions are satisfied.
- C. Incorrect.
Incorrect. Aggregate queries are eligible for persisted query results. Using SUM() does not prevent result cache reuse. Clustering can improve pruning and execution performance for some workloads, but it is unrelated to whether a query can reuse a persisted query result.
- D. Incorrect.
Incorrect. Snowflake can cache results for queries against permanent tables, and the fact that a table receives inserts does not by itself make result caching unavailable. However, if the underlying data contributing to the result has changed, Snowflake will not reuse the previous result. The stronger and more direct reason in this scenario is the use of CURRENT_DATE(), which itself prevents reuse.