Explain Result Caching: Query, Metadata & Warehouse Caches in Databases

60 / 66

Snowflake

Result Caching: Query, Metadata & Warehouse Caches

Leverage Snowflake's multi-layer caching to reduce cost and latency on repeated queries.

Snowflake employs several distinct caching layers, and understanding the difference is a common interview distinction point. The result cache stores the complete result of a query for 24 hours (extendable up to 31 days with reuse); if an identical query (same SQL text, same session context, and unchanged underlying data) is submitted again, Snowflake returns the cached result instantly with zero compute cost — no warehouse even needs to be running.

The result cache is like getting the exact same photocopy handed back instantly because someone already made that exact copy today; the metadata cache is like being told a book's page count from its catalog entry without opening it; the warehouse cache is like a librarian who remembers where the books were on the cart from your last visit, but forgets once they go home for the day.

Key Concepts

The metadata cache (part of the cloud services layer) stores table statistics, min/max values, and row counts, enabling certain queries — like SELECT COUNT(*) FROM table or SELECT MAX(col) FROM table — to be answered directly from metadata without scanning any actual data or even requiring a running warehouse, since these values are already known from micro-partition metadata.

metadata cacheSELECT COUNT(*) FROM tableSELECT MAX(col) FROM table

The warehouse (local disk) cache is different from the previous two: it's tied to a specific running virtual warehouse and caches raw data pages read from storage in that warehouse's local SSD, speeding up subsequent queries that access the same data — but only while the warehouse remains running. This cache is lost when the warehouse suspends, which is one of the trade-offs to weigh against aggressive auto-suspend settings for warehouses with a lot of repeat-query traffic.

warehouse (local disk) cache

For the result cache to be used, the query must be byte-for-byte identical (including whitespace and case, though Snowflake normalizes some of this) and the underlying table data must not have changed since the cached result was produced — any DML on the source tables invalidates the cache for queries against them.