ADA-C01 Question 345
Select 2Use data cachingA BI team runs the same dashboard query every 5 minutes against a large fact table in Snowflake. The query text is identical each time and references only base tables. The administrator notices that execution time is inconsistent: sometimes the query returns almost instantly, and other times it takes much longer. The team confirms that no data in the referenced tables changed between runs. Which TWO actions are most likely to improve the chances that Snowflake can reuse cached data and return results faster?
- A
Ensure the dashboard submits the exact same SQL text each time, including avoiding unnecessary formatting or alias changes
- B
Keep the same virtual warehouse running so local disk cache remains available between query executions
- C
Increase the warehouse size so result cache entries are retained longer
- D
Rewrite the query to use a materialized view, because persisted query results are only used for queries against views
- E
Suspend and resume the warehouse after each dashboard refresh to clear stale cache state
Show answer and explanation
Correct answers: A, B
Explanation
Snowflake uses multiple caching layers relevant to this scenario. First, persisted query results can allow a repeated query to return almost instantly when the SQL text is identical and the underlying data and other reuse conditions have not changed. Second, the virtual warehouse cache stores micro-partition data on local disk while the warehouse remains running, which can speed up repeated access to the same table data. In contrast, suspending a warehouse clears the local disk cache. Warehouse size affects compute capacity, but not how long persisted query results are retained. Best practice for recurring BI workloads is to keep SQL stable and avoid unnecessary warehouse suspension if consistent low-latency performance is important. See Snowflake documentation on optimizing query performance, persisted query results, and warehouse caching behavior.
- A. Correct.
Correct. Snowflake can reuse persisted query results only when the new query matches the previous query exactly and other reuse conditions are met. Even logically equivalent SQL that differs in text, aliases, capitalization, or formatting may prevent result reuse. For repeated dashboard workloads, keeping the submitted SQL text stable is a practical way to maximize cache hits.
- B. Correct.
Correct. Snowflake's warehouse cache stores table data on the local disk of the virtual warehouse nodes. If the same warehouse remains running, subsequent queries may benefit from this local data cache and avoid some remote reads, improving performance consistency. Suspending the warehouse clears this local cache, so keeping it warm can help repeated dashboard queries.
- C. Incorrect.
Incorrect. Increasing warehouse size may improve raw compute performance, but it does not extend the retention period of persisted query results. Result cache retention is managed by Snowflake and is not controlled by warehouse size. This option reflects a common misconception that larger warehouses provide longer-lived result caching.
- D. Incorrect.
Incorrect. Persisted query results are not limited to queries against views. Snowflake can reuse query results for eligible repeated queries against base tables as well, provided the required conditions are met. Materialized views can improve performance in some cases, but they are not required for result cache reuse.
- E. Incorrect.
Incorrect. Suspending and resuming the warehouse removes the local warehouse cache, which reduces the chance of benefiting from cached table data on subsequent executions. It does not improve result cache behavior and would usually make performance less consistent for this scenario.