COF-C03 Question 286
Single answer4.3 Use Snowflake cachingA BI team runs the same dashboard query every 5 minutes against a Snowflake table that is refreshed once each night. They want the dashboard to return as quickly as possible during the day while also minimizing compute usage. Which action will best allow Snowflake to serve the repeated query without re-executing it?
- A
Ensure the SQL text remains identical and the underlying data does not change, so Snowflake can use persisted query results.
- B
Keep the virtual warehouse running continuously so the local disk cache can answer the query even if the SQL text changes each time.
- C
Resize the virtual warehouse to a larger size so Snowflake can store more results in the metadata cache.
- D
Enable auto-clustering on the table so repeated SELECT statements are returned from the result cache.
Show answer and explanation
Correct answer: A
Explanation
Snowflake uses multiple caching-related mechanisms, and this scenario is specifically about persisted query results. For a repeated dashboard query, the best way to get the fastest response with minimal compute is to let Snowflake reuse the prior result set instead of running the query again. This generally requires the same query text and no relevant changes to the underlying data or query context. By contrast, the warehouse data cache improves performance by reducing data reads for a running warehouse, but the query still executes. Warehouse resizing affects compute capacity, not result reuse. Auto-clustering improves storage organization, not result caching. Snowflake documentation distinguishes persisted query results from the virtual warehouse cache and highlights that result reuse depends on conditions such as unchanged underlying data and matching query text.
- A. Correct.
Correct. Snowflake can reuse persisted query results (often called the result cache) when a new query matches a previous query closely enough, including identical SQL text, and when the underlying data and other relevant conditions have not changed. In this scenario, the table is refreshed only nightly, so repeated dashboard queries during the day are good candidates for result reuse. When the persisted result is reused, Snowflake can return the results without re-executing the query, reducing both latency and compute consumption.
- B. Incorrect.
Incorrect. The local disk cache is tied to the virtual warehouse and helps avoid re-reading data from remote storage, but it does not replace query execution and does not allow arbitrary SQL text changes to still qualify for result reuse. If the SQL text changes, Snowflake typically cannot reuse persisted query results. Also, keeping a warehouse running continuously may increase cost rather than minimize it.
- C. Incorrect.
Incorrect. Resizing a warehouse can improve query performance by adding compute resources, but it does not create a special 'metadata cache' that returns prior query results. Persisted query results are a separate caching mechanism from warehouse size. A larger warehouse may process the query faster, but it does not by itself allow Snowflake to skip execution.
- D. Incorrect.
Incorrect. Auto-clustering helps maintain clustering depth for large tables and can improve pruning efficiency for some queries, but it is unrelated to whether Snowflake can return a repeated query directly from persisted query results. Repeated SELECT statements are not returned from cache simply because auto-clustering is enabled.