COF-C03 Question 287
Single answer4.3 Use Snowflake cachingA BI team runs the same dashboard query every few minutes against a SALES table. The query text is unchanged, the underlying table data has not changed, and the team wants the fastest possible response with the lowest compute cost. However, they notice that users sometimes run the dashboard from different virtual warehouses. Which action would most directly maximize the chance that Snowflake returns the result without using warehouse compute?
- A
Run the query repeatedly on the same virtual warehouse so the local disk cache is reused
- B
Ensure the exact same query text is submitted again and the underlying data remains unchanged
- C
Increase the warehouse size so the query result is stored longer in cache
- D
Suspend and resume the warehouse between dashboard refreshes so cache metadata is refreshed
Show answer and explanation
Correct answer: B
Explanation
Snowflake uses multiple caching mechanisms, including persisted query results and the virtual warehouse local disk cache. For the fastest response with the lowest compute cost, persisted query results are most important because Snowflake can return the cached result set without re-executing the query when the same query text is submitted and the underlying data has not changed. This cache is not dependent on using the same virtual warehouse. In contrast, the warehouse cache stores table data on local disk and helps avoid re-reading remote storage, but it still requires the warehouse to be running and is specific to that warehouse; suspending the warehouse clears it. This aligns with Snowflake documentation and best practices on understanding result reuse versus warehouse caching behavior.
- A. Incorrect.
This would help only with the warehouse data cache (local disk cache), which is tied to a specific virtual warehouse and is cleared when the warehouse is suspended. It can improve performance for repeated scans on the same warehouse, but it does not most directly maximize the chance of a no-compute response across users who may use different warehouses.
- B. Correct.
Correct. Snowflake's persisted query results cache can return results without requiring warehouse compute when the same query text is rerun and the underlying data and other relevant conditions have not changed. Unlike the warehouse local cache, persisted query results are not tied to a specific virtual warehouse, so this is the best way to maximize fast, low-cost repeat responses in this scenario.
- C. Incorrect.
Warehouse size affects compute resources, not how long persisted query results are retained. Increasing warehouse size may improve execution speed for uncached queries, but it does not directly increase the likelihood of reusing persisted query results without compute.
- D. Incorrect.
Suspending a warehouse clears its local disk cache rather than improving cache reuse. This would reduce the benefit of the warehouse cache and does not help Snowflake return a persisted query result.