Result Cache

A result cache stores the output of a query so an identical later query can be answered without recomputing it. It is distinct from a data cache, which holds table contents near the compute that reads them: one remembers the answer, the other remembers the ingredients.

The lifetimes differ, and that difference matters operationally. In Snowflake a warehouse’s local data cache “is dropped when the warehouse is suspended,” while a persisted result “expires after 24 hours” and survives suspension entirely. Reuse extends it: “each time the persisted result for a query is reused, Snowflake resets the 24-hour retention period for the result, up to a maximum of 31 days from the date and time that the query was first executed.”

Reuse is conditional, and the conditions are stricter than people expect. The query must match exactly — “including syntax and capitalization” — so reformatted SQL misses. It must avoid non-deterministic functions such as RANDOM or UUID_STRING, and external functions. The underlying table data must be unchanged, the role must hold the required privileges, and relevant configuration must not have changed.

The practical consequence is for measurement. Running a query twice and reporting the second time measures cache retrieval, not query execution, and the number produced is not reproducible by anyone whose cache state differs. Compare with a known cache state, and prefer a metric that does not depend on recent history — bytes scanned is one.

Caching is also not a substitute for a table that is laid out badly. It hides the cost of the first execution from whoever runs the second, which is a different thing from making the work cheap.

Further reading

• Snowflake, Using persisted query results
• Optimizing the warehouse cache

See the worked examples in Snowflake Architecture and Workload Design.