TopolearnCaches explain why the same query is sometimes instant and free, and sometimes not.
Snowflake has three layers that can make a query fast or free.
Result cache (cloud services layer): when a query is repeated, its result is returned instantly without using a warehouse, if
- the query text is identical (same case and whitespace),
- the underlying data has not changed,
- it contains no non-deterministic functions such as
CURRENT_TIMESTAMP()orRANDOM(), - and the role has the necessary privileges.
Results are kept for 24 hours, and each reuse extends that, up to 31 days. Set USE_CACHED_RESULT = FALSE in a session when benchmarking.
Warehouse cache (local SSD): a running warehouse caches the micro-partitions it has read. Later queries reading the same data on the same warehouse skip remote storage reads. It is lost when the warehouse suspends or resizes, which is one reason to group similar workloads on one warehouse and avoid ultra-short auto-suspend for dashboards.
Metadata: Snowflake keeps row counts and min/max values per micro-partition, so SELECT COUNT(*) FROM t or MAX(id) on a whole table can be answered from metadata alone, with no warehouse running.
The query profile shows which applied: "QUERY RESULT REUSE" for the result cache, and a "percentage scanned from cache" figure for the warehouse cache.
Example
ALTER SESSION SET USE_CACHED_RESULT = TRUE; -- the default
SELECT region, SUM(amount) FROM orders GROUP BY region; -- runs on the warehouse
SELECT region, SUM(amount) FROM orders GROUP BY region; -- identical text: result cache, ~ms
-- Not eligible: non-deterministic function
SELECT region, SUM(amount), CURRENT_TIMESTAMP() FROM orders GROUP BY region;
-- Answered from metadata, no warehouse needed
SELECT COUNT(*), MIN(id), MAX(id) FROM orders;
-- Benchmark honestly
ALTER SESSION SET USE_CACHED_RESULT = FALSE;Remember
- Result cache: identical query + unchanged data = instant, no warehouse
- Warehouse cache lives on local SSD and is lost on suspend
- COUNT/MIN/MAX on whole tables come from metadata
Watch out for
- Benchmarking a warehouse size with the result cache on and measuring cache hits.