What you will be able to do
- Explain how long Snowflake keeps a persisted query result and how reuse extends that period
- Decide whether a repeated query can be served from the result cache, using the documented reuse conditions
- Identify what invalidates a cached result, and turn reuse off with USE_CACHED_RESULT
- Post-process a previous query's output with RESULT_SCAN
Key concept
Persisted query result (result cache) — When a query runs, Snowflake stores its result. If an identical query arrives later and nothing that produced the result has changed, Snowflake returns the stored result and does not execute the query again.
1.What the result cache stores, and for how long
The mechanism that can skip the most work is the query result cache. Snowflake's documentation calls these *persisted query results*. Every executed query has its result persisted for a period of time. If a user repeats a query that has already run, and the data in the tables behind it hasn't changed, the answer must be the same. Snowflake therefore skips execution entirely and hands back the stored result. This is why a reused result can come back much faster than the original run: no scanning, joining or aggregating takes place.
The lifetime is simple and doesn't depend on size. For results of every size, the cache expires after 24 hours, and the result is then purged. Persisted results have a second use besides retrieval optimization: you can query them again for post-processing, which the last section of this page covers.
Keep this separate from the warehouse cache. The documentation sends readers to a separate topic on how an *active warehouse* caches and reuses table data. That is a different mechanism with different rules. The result cache stores finished answers. The warehouse cache stores raw table data that a query still has to process.
Checkpoint 1 of 8· Check yourself
A query produces a 5 MB result that nobody reruns. When does that persisted result expire from the cache?
Cache expiry is 24 hours regardless of size. The 6-hour figure applies to the security token for large results, not to the cache. The 31-day figure is a ceiling that applies only when a result keeps being reused.
“For persisted query results of all sizes, the cache expires after 24 hours.”Source: docs.snowflake.com
2.The conditions a query must meet to reuse a result
Reuse is conditional, and the first condition is stricter than most people expect: the new query must match the earlier one exactly. Any difference in syntax inhibits reuse. Changing the case of keywords or adding a table alias is enough to prevent it, even though the result would be identical. Here are the documentation's four queries, run one after another:
SELECT DISTINCT(severity) FROM weather_events; SELECT DISTINCT(severity) FROM weather_events; SELECT DISTINCT(severity) FROM weather_events we; select distinct(severity) from weather_events;The other conditions are about what the query touches and who is asking. Functions that return a different value on every run can't be served from a stored answer. The documentation names UUID_STRING, RANDOM and RANDSTR as examples of these non-reusable functions. External functions also rule out reuse, and so does selecting from hybrid tables. The table data contributing to the result must also be unchanged: if it has changed, the stored result no longer applies, however recently it was produced. Access control is checked again on every reuse. The rule differs by statement type. For a cached SELECT, the role running the new query needs the access privileges on every table the cached query used. For a cached SHOW, the role must be the *same role* that generated the cached result.
| Condition for reuse | What prevents reuse |
|---|---|
| New query matches the previous query exactly | Any syntax difference, including uppercase vs lowercase or adding a table alias |
| No non-reusable functions | Calling UUID_STRING, RANDOM or RANDSTR |
| No external functions | Any external function in the query |
| No hybrid tables | Selecting from a hybrid table |
| Table data unchanged | A change to the table data contributing to the query result |
| Role has the required privileges (SELECT) | Role lacks access privileges on any table used in the cached query |
| Role matches (SHOW) | A different role from the one that generated the cached result |
Checkpoint 2 of 8· Check yourself
A query's result was persisted two hours ago. Since then, rows were inserted into one of the tables it reads. The same query is submitted again with identical text. What happens?
Reuse requires that the table data contributing to the result hasn't changed. Once it has, the old result no longer applies, even though the 24-hour lifetime hasn't expired.
“The table data contributing to the query result has not changed.”Source: docs.snowflake.com
Checkpoint 3 of 8· Check yourself
ANALYST_ROLE ran a SELECT joining two tables, and the result was persisted. A few minutes later, REPORTING_ROLE runs exactly the same SELECT. REPORTING_ROLE has the access privileges on both tables, and the data hasn't changed. With respect to the role condition, what's true?
The same-role rule applies to SHOW queries. For a SELECT, the requirement is that the executing role has the necessary privileges on every table in the cached query.
“the role executing the query must have the necessary access privileges for all the tables used in the cached query.”Source: docs.snowflake.com
Checkpoint 4 of 8· Exam question
A developer rewrites a report query, changing only a table alias from uppercase `O` to lowercase `o`; the join logic and filters are otherwise identical to a query that ran minutes earlier against unchanged data. Will the rewritten query reuse the earlier persisted result?
Correct answer: C — No, differing syntax such as alias casing changes the query's text fingerprint and defeats exact-match reuse of the persisted result.
- A. Result cache matching relies on the submitted SQL text rather than a normalized logical plan, so alias stripping does not describe how Snowflake decides reuse.
- B. Warehouse allocation is unrelated to alias text changes; a warehouse is only needed when the result cache cannot be reused, not because an alias changed.
- C. Even a cosmetic change like alias casing alters the exact SQL text Snowflake compares, so the rewritten query is treated as a different statement and cannot reuse the earlier persisted result.
- D. Snowflake does not normalize casing or aliasing before comparing query text, so this claim about automatic normalization is incorrect.
Sources1
3.What invalidates a cached result, and how to turn reuse off
A stored result stays correct only while its inputs are unchanged, so the remaining conditions are about change. The table data behind the result must not have changed. The persisted result must still be available. Any configuration options that affected how the result was produced must not have changed. The subtlest condition is about physical change: the table's micro-partitions must not have changed. That includes being reclustered or consolidated because of changes to *other* data in the table. A cached result can therefore be invalidated even when the rows your query reads are logically the same.
The function rule follows the same logic. The documentation defines non-reusable functions by their behavior: they return different results for successive runs of the same query. UUID_STRING, RANDOM and RANDSTR are only examples. A stored answer can't be right for a query whose output is meant to differ from run to run.
Two more points matter for the exam. First, meeting every condition makes a query *eligible*. It doesn't make reuse certain, because the documentation states that reuse isn't guaranteed. Second, reuse extends the cache lifetime. Each time a result is reused, Snowflake resets its 24-hour retention period, up to 31 days from when the query first ran. After that, the result is purged, and the next submission produces and persists a new one.
Reuse isn't tied to one role: for a SELECT, a different role can be served the stored result as long as it has the necessary privileges on every table used. When a result is reused, Snowflake bypasses query execution and retrieves the result directly from the cache.
Result reuse is on by default. It's controlled by the USE_CACHED_RESULT session parameter, which you can override at the account, user or session level. This is the setting to change when every run has to execute for real, for example in a benchmark. Snowflake's TPC-H sample-data guide turns it off for exactly that reason.
Checkpoint 5 of 8· Fill the gap
Before running a benchmark, you want every query to execute instead of returning a cached result. Which parameter completes this statement?
alter session set ? = false;USE_CACHED_RESULT is the session parameter that controls result reuse. Setting it to false makes each query run instead of reading a persisted result.
Source: docs.snowflake.comCheckpoint 6 of 8· Check yourself
A query was first run on 1 March, and an identical query has reused its result every day since. No data has changed. What happens to the cached result?
Reuse keeps restarting the 24-hour clock, but there's a hard ceiling of 31 days from the first execution.
“resets the 24-hour retention period for the result, up to a maximum of 31 days”Source: docs.snowflake.com
Checkpoint 7 of 8· Exam question
A dashboard query embeds `CURRENT_TIMESTAMP()` to stamp each report and is executed twice, a few minutes apart, against unchanged source data. How does the query result cache behave across these two executions?
Correct answer: B — The query never qualifies for result cache reuse because a non-deterministic function like `CURRENT_TIMESTAMP()` returns a different value on every execution.
- A. The local disk cache is a warehouse-level data cache, not a substitute check performed before the result cache, so this describes the wrong order of operations.
- B. Functions like `CURRENT_TIMESTAMP()` are non-deterministic, so Snowflake cannot guarantee identical output on repeat calls and excludes such queries from persisted result reuse.
- C. The function is re-evaluated on every execution rather than cached alongside the result, so this description of one-time evaluation is incorrect.
- D. Warehouse state does not control whether a non-deterministic function's output is cached; the function itself is what disqualifies the query from reuse, regardless of suspension.
4.Post-processing persisted results with RESULT_SCAN
The result cache isn't only used for automatic reuse. You can also query a persisted result directly. The RESULT_SCAN table function returns a previous query's result as a table, and you can filter, project or join it like any other table. This helps in two situations the documentation describes. One is building a complex query step by step, where you add a layer on top of an earlier result without recomputing it. The other is processing output that is otherwise awkward to reuse: SHOW, DESCRIBE and CALL statements. A stored procedure can't be called inside a larger SQL statement the way a function can, so post-processing its stored result is the only way to work with its output. The pipe operator (->>) is an alternative to RESULT_SCAN, and with it you don't have to display the first command's results.
The documentation's example takes the output of SHOW TABLES and keeps only the empty tables:
SELECT "schema_name", "name" as "table_name", "rows" FROM table(RESULT_SCAN(LAST_QUERY_ID())) WHERE "rows" = 0;Access to large results has one extra detail. A result larger than 100 KB is accessed with a security token that expires after 6 hours, while the result itself stays cached for 24 hours. Results smaller than that don't use a token. If the token has expired, you can retrieve a new one as long as the result is still in the cache. The Spark connector is an exception: its token lasts 24 hours whatever the result size.
Checkpoint 8 of 8· Check yourself
A client fetches a 2 MB persisted result 7 hours after the query ran. The token it received originally has expired. What's the situation?
The 6-hour limit applies to the token, not to the cache. The result stays cached for 24 hours, and a new token can be issued during that time.
“A new token can be retrieved to access results while they are still in cache.”Source: docs.snowflake.com
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Two queries that return the same rows will share a cached result, even if one uses lowercase keywords or a table alias.Why is that wrong?
Reuse requires an exact textual match. A change in case or an added alias prevents full cache reuse.
Covered in The conditions a query must meet to reuse a result
2.If the rows my query reads haven't changed, reclustering or changes elsewhere in the table can't affect result reuse.Why is that wrong?
The table's micro-partitions must also be unchanged. Reclustering or consolidation caused by changes to other data invalidates reuse.
Covered in What invalidates a cached result, and how to turn reuse off
3.If a query meets every listed condition, Snowflake will definitely return the cached result.Why is that wrong?
The conditions make a query eligible for reuse. The documentation states explicitly that meeting them doesn't guarantee reuse.
Covered in What invalidates a cached result, and how to turn reuse off
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“This can substantially reduce query time because Snowflake bypasses query execution and, instead, retrieves the result directly from the cache.”
↩︎ What the result cache stores, and for how long“For persisted query results of all sizes, the cache expires after 24 hours.”
↩︎ What the result cache stores, and for how long“UUID_STRING, RANDOM, and RANDSTR are good examples of non-reusable functions.”
↩︎ The conditions a query must meet to reuse a result“The query does not select from hybrid tables.”
↩︎ The conditions a query must meet to reuse a result“If the query was a SHOW query, the role executing the query must match the role that generated the cached results.”
↩︎ The conditions a query must meet to reuse a result“The table data contributing to the query result has not changed.”
↩︎ The conditions a query must meet to reuse a result“By default, result reuse is enabled, but can be overridden at the account, user, and session level using the USE_CACHED_RESULT session parameter.”
↩︎ What invalidates a cached result, and how to turn reuse off“Any configuration options that affect how the result was produced have not changed.”
↩︎ What invalidates a cached result, and how to turn reuse off“which return different results for successive runs of the same query”
↩︎ What invalidates a cached result, and how to turn reuse off“You can perform post-processing by using the RESULT_SCAN table function.”
↩︎ Post-processing persisted results with RESULT_SCAN“the security token used to access large persisted query results (i.e. greater than 100 KB in size) expires after 6 hours.”
↩︎ Post-processing persisted results with RESULT_SCAN“Snowflake uses persisted query results to avoid re-generating results when nothing has changed”
↩︎ Key concept“Any difference in syntax, including lowercase versus uppercase, or the use of table aliases, will inhibit 100% cache reuse.”
↩︎ Exam trap 1“The table’s micro-partitions have not changed (e.g. been reclustered or consolidated) due to changes to other data in the table.”
↩︎ Exam trap 2“Meeting all these conditions does not guarantee that Snowflake reuses the query results.”
↩︎ Exam trap 3“Instead of running the query again, Snowflake simply returns the same result that it returned previously.”
↩︎ Prediction“the role executing the query must have the necessary access privileges for all the tables used in the cached query.”
↩︎ Checkpoint“resets the 24-hour retention period for the result, up to a maximum of 31 days”
↩︎ Checkpoint“A new token can be retrieved to access results while they are still in cache.”
↩︎ Checkpoint - 2.
“A running warehouse maintains a cache of table data that can be accessed by queries running on the same warehouse.”
↩︎ What the result cache stores, and for how long - 3.
“To make sure that each pass runs the query instead of returning a cached result, turn off the query result cache for your session:”
↩︎ What invalidates a cached result, and how to turn reuse off