DAA-C01 Question 181
Single answerLeverage result, metadata, and virtual warehouse cachingA BI team runs the same dashboard query every 5 minutes against a large SALES_FACT table. The SQL text is identical each time, but the query includes CURRENT_TIMESTAMP() in the SELECT list to display the refresh time. The underlying tables are not changing. After suspending and resuming the virtual warehouse, the team notices the query still scans data instead of returning instantly. They want to maximize repeated-query performance while keeping the dashboard accurate. Which change would best enable Snowflake to leverage caching for this workload?
- A
Remove CURRENT_TIMESTAMP() from the query and display the refresh time in the BI tool instead of in Snowflake SQL
- B
Increase the virtual warehouse size so the result cache can be reused after resume
- C
Disable auto-suspend so the warehouse local disk cache is preserved indefinitely and the query will use result cache
- D
Create a materialized view on SALES_FACT because result cache cannot be used for repeated dashboard queries
Show answer and explanation
Correct answer: A
Explanation
Snowflake provides several performance mechanisms that are often confused: persisted query results (result cache), metadata-based optimizations, and virtual warehouse local disk caching. Persisted query results can return a previous query's results almost instantly when the same query is rerun and the underlying data and relevant conditions have not changed. Queries that include non-deterministic functions such as CURRENT_TIMESTAMP() generally prevent effective reuse of persisted results because the expected output changes from run to run.
Virtual warehouse cache is different: it caches data files locally on the warehouse nodes and can improve repeated scans, but it depends on the same warehouse continuing to run; suspending the warehouse clears that local cache. Metadata caching/optimization helps Snowflake avoid unnecessary file scans based on micro-partition metadata, but it does not replace the benefits of result reuse for identical dashboard queries.
Best practice for BI workloads is to keep repeated SQL deterministic when possible and move display-only changing values, such as a dashboard refresh timestamp, to the presentation layer. This aligns with Snowflake documentation on persisted query results and warehouse caching behavior.
- A. Correct.
Correct. Snowflake can reuse persisted query results when the exact query text is repeated and the underlying data has not changed, but queries with non-reusable functions such as CURRENT_TIMESTAMP() are not good candidates for persisted result reuse. Moving the displayed refresh time to the BI layer allows the SQL to remain deterministic and greatly improves the likelihood that repeated executions can be served from the result cache. This is the best change because it directly addresses the reason the identical dashboard query is not returning instantly.
- B. Incorrect.
Incorrect. Warehouse size affects available compute resources and can improve execution speed, but persisted query result reuse is not enabled by making the warehouse larger. Result cache is a cloud services feature and is not tied to warehouse size or preserved by resume behavior. If the SQL includes CURRENT_TIMESTAMP(), changing warehouse size does not fix the cacheability problem.
- C. Incorrect.
Incorrect. Keeping a warehouse running can help preserve the virtual warehouse's local disk cache, which may reduce remote storage reads for repeated scans. However, that is different from persisted query results, and it does not make a non-deterministic query eligible for result-cache reuse. Also, local disk cache is tied to the warehouse cluster and is lost when the warehouse is suspended. The core issue here is the query text itself, not only warehouse suspension.
- D. Incorrect.
Incorrect. A materialized view may help some workloads, but it is not the best answer for a query that is already being repeated unchanged against stable data. Snowflake can serve repeated deterministic queries from persisted results without adding maintenance overhead. The statement that result cache cannot be used for repeated dashboard queries is false; in fact, repeated dashboard queries are a common use case for result caching when the query is deterministic and underlying data is unchanged.