Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use Result Cache in Snowflake for Faster Queries

In Snowflake, result cache automatically stores the results of queries and reuses them when the same query runs again without data changes. You just run your query normally, and Snowflake returns cached results instantly if available, improving speed and saving compute resources.
📐

Syntax

Snowflake's result cache works automatically without special syntax. When you run a query, Snowflake checks if the exact same query result is cached and returns it if valid. You can control caching behavior using the USE_CACHED_RESULT parameter.

  • USE_CACHED_RESULT = TRUE: Enables using cached results (default).
  • USE_CACHED_RESULT = FALSE: Forces query to run fresh, ignoring cache.
sql
ALTER SESSION SET USE_CACHED_RESULT = TRUE;
-- Run your query normally
SELECT * FROM my_table WHERE id = 1;
💻

Example

This example shows how Snowflake uses result cache automatically. The first query runs normally and stores the result. The second query with the same text returns instantly from cache.

sql
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

-- First run: query executes and caches result
SELECT CURRENT_TIMESTAMP() AS run_time;

-- Second run: same query returns cached result instantly
SELECT CURRENT_TIMESTAMP() AS run_time;
Output
run_time ------------------------- 2024-06-01 12:00:00.000 2024-06-01 12:00:00.000
⚠️

Common Pitfalls

Common mistakes when expecting result cache to work include:

  • Changing data in the underlying tables invalidates the cache.
  • Using USE_CACHED_RESULT = FALSE disables caching.
  • Queries with non-deterministic functions (like CURRENT_TIMESTAMP()) do not cache results.
  • Minor query text differences (extra spaces, comments) cause cache misses.

Always ensure your query text is exactly the same and data is unchanged to benefit from caching.

sql
/* Wrong: disables cache */
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT * FROM my_table;

/* Right: enables cache */
ALTER SESSION SET USE_CACHED_RESULT = TRUE;
SELECT * FROM my_table;
📊

Quick Reference

FeatureDescription
Result CacheStores results of queries for 24 hours if data unchanged
USE_CACHED_RESULTSession parameter to enable or disable cache usage
Cache InvalidationOccurs when underlying data changes or query text differs
Non-cacheable QueriesQueries with volatile functions or session variables
BenefitSpeeds up repeated queries and saves compute credits
✅

Key Takeaways

Snowflake automatically caches query results for 24 hours if data is unchanged.
Use the session parameter USE_CACHED_RESULT to enable or disable result caching.
Exact query text and unchanged data are required to hit the cache.
Queries with volatile functions or session variables do not use result cache.
Result cache improves query speed and reduces compute costs for repeated queries.