Result caching layers in Snowflake - Step-by-Step Execution

Process Flow - Result caching layers

User Query Submitted

↓

Check Result Cache

↓

Return Cached

↓

Store Result in Cache

↓

Return Result

When a query runs, Snowflake first checks if the result is cached. If yes, it returns the cached result instantly. If not, it runs the query, stores the result in cache, then returns it.

Execution Sample

Snowflake

SELECT * FROM sales WHERE date = '2024-06-01';
-- First run: cache miss, query executes
-- Second run: cache hit, result returned instantly

This query fetches sales data for June 1, 2024. The first time it runs, Snowflake executes it and caches the result. The second time, it returns the cached result without re-executing.

Process Table

Step	Action	Cache Status	Query Execution	Result Returned
1	User submits query	Check cache for query result	No execution yet	No result yet
2	Cache check	Cache miss (no cached result)	Execute query on data	No result yet
3	Query execution	N/A	Query runs and result generated	No result yet
4	Store result	Result stored in cache	Execution complete	No result yet
5	Return result	Cache updated	No new execution	Result returned to user
6	User submits same query again	Check cache for query result	No execution yet	No result yet
7	Cache check	Cache hit (cached result found)	Skip execution	Return cached result instantly

💡 Execution stops after returning cached result on second query run

Status Tracker

Variable	Start	After Step 2	After Step 4	After Step 7
Cache Status	Empty	Miss	Hit (after storing result)	Hit
Query Execution	Not started	Running	Completed	Skipped
Result Returned	None	None	Generated	Cached result

Key Moments - 2 Insights

Why does the query run only once even if executed twice?

Because after the first run (see execution_table step 4), the result is stored in cache. On the second run (step 7), Snowflake finds the cached result and returns it without re-executing.

What happens if the underlying data changes after caching?

Snowflake invalidates the cache automatically when data changes, so the next query run will be a cache miss and re-execute the query (not shown in this trace).

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the cache status?

A Cache miss

B Cache hit

C Cache empty

D Cache invalid

💡 Hint

Refer to execution_table row with Step 2 under 'Cache Status'

At which step does Snowflake store the query result in cache?

A Step 3

B Step 5

C Step 4

D Step 7

💡 Hint

Look at execution_table rows describing storing result in cache

If the query is changed slightly, what will happen to the cache status on next run?

A Cache hit because similar query

B Cache miss because query is different

C Cache hit but returns old data

D Cache cleared manually

💡 Hint

Cache keys depend on exact query text, see concept_flow for cache check logic

Concept Snapshot

Result caching layers in Snowflake:
- When a query runs, Snowflake checks if the exact result is cached.
- If cached, it returns the result instantly (cache hit).
- If not cached, it runs the query, stores the result, then returns it (cache miss).
- Cache invalidates automatically if underlying data changes.
- This speeds up repeated queries and saves compute resources.

Full Transcript

This visual execution trace shows how Snowflake handles result caching. When a user submits a query, Snowflake first checks if the result is already cached. If the cache is empty or does not have the result (cache miss), Snowflake executes the query on the data, generates the result, stores it in the cache, and then returns the result to the user. On subsequent runs of the same query, Snowflake finds the cached result (cache hit) and returns it immediately without re-executing the query. Variables like cache status, query execution state, and result returned change step-by-step as shown. Key moments include understanding why the query runs only once and how cache invalidation works when data changes. The quiz questions test understanding of cache status at different steps and behavior when queries change. This caching layer improves performance by avoiding repeated query execution for identical queries.

Practice

1. What does Snowflake's result caching do when you run the same query multiple times within 24 hours?

easy

A. Deletes the previous result and returns an error

B. Runs the query again and stores a new result

C. Requires manual cache refresh before returning results

D. Returns the cached result instantly without rerunning the query

Solution

1. Step 1: Understand Snowflake's caching behavior

   Snowflake stores query results for 24 hours to speed up repeated queries.

2. Step 2: Identify the effect of caching on repeated queries

   When the same query runs again within 24 hours, Snowflake returns the cached result instantly without rerunning the query.

3. Final Answer:

   Returns the cached result instantly without rerunning the query -> Option D

4. Quick Check:

   Result caching = instant cached return [OK]

Hint: Same query within 24h returns cached result instantly [OK]

Common Mistakes:

• Thinking query always reruns
• Assuming cache expires immediately
• Believing manual refresh is needed

2. Which of the following is the correct way to disable result caching for a single query in Snowflake?

easy

A. ALTER SESSION SET DISABLE_CACHE = TRUE;

B. ALTER SESSION SET USE_CACHED_RESULT = FALSE;

C. ALTER SESSION SET NO_CACHE_RESULT = TRUE;

D. ALTER SESSION SET SKIP_CACHE = TRUE;

Solution

1. Step 1: Recall how to disable result caching

   Snowflake uses ALTER SESSION SET USE_CACHED_RESULT = FALSE to disable caching for the session.

2. Step 2: Match the correct syntax

   The correct syntax is ALTER SESSION SET USE_CACHED_RESULT = FALSE;

3. Final Answer:

   ALTER SESSION SET USE_CACHED_RESULT = FALSE; -> Option B

4. Quick Check:

   Disable cache param = USE_CACHED_RESULT = FALSE [OK]

Hint: Use ALTER SESSION SET USE_CACHED_RESULT = FALSE to disable caching [OK]

Common Mistakes:

• Using wrong parameter names
• Incorrect ALTER SESSION syntax
• Confusing with other cache parameters

3. Consider this sequence of queries run in Snowflake:
SELECT COUNT(*) FROM sales;
Immediately followed by:
SELECT COUNT(*) FROM sales;
What will happen on the second query?

medium

A. Snowflake returns the cached result from the first query

B. Snowflake returns partial results from cache

C. Snowflake returns an error about duplicate queries

D. Snowflake reruns the query and returns fresh results

Solution

1. Step 1: Identify caching behavior for repeated queries

   Snowflake caches query results for 24 hours and returns cached results if the same query runs again.

2. Step 2: Apply to the given queries

   The second SELECT COUNT(*) query is identical and runs immediately, so Snowflake returns the cached result.

3. Final Answer:

   Snowflake returns the cached result from the first query -> Option A

4. Quick Check:

   Repeated query = cached result return [OK]

Hint: Repeated query returns cached result instantly [OK]

Common Mistakes:

• Assuming query always reruns
• Expecting partial or error results
• Confusing cache with data freshness

4. You executed this command in Snowflake:
ALTER SESSION SET USE_CACHED_RESUTL = FALSE;
But you notice subsequent queries still return cached results. What is the likely cause?

medium

A. Result caching cannot be disabled in Snowflake

B. The query is too complex to cache

C. The USE_CACHED_RESULT parameter is misspelled or incorrect

D. The cache expired before the query ran

Solution

1. Step 1: Verify the USE_CACHED_RESULT parameter usage

   The parameter must be spelled exactly USE_CACHED_RESULT and set to FALSE to disable caching.

2. Step 2: Identify why caching still happens

   If caching still occurs, the parameter might be misspelled or incorrectly specified.

3. Final Answer:

   The USE_CACHED_RESULT parameter is misspelled or incorrect -> Option C

4. Quick Check:

   Correct parameter spelling and value disables cache [OK]

Hint: Check parameter spelling and syntax to disable caching [OK]

Common Mistakes:

• Assuming caching can't be disabled
• Misspelling the parameter name
• Ignoring exact parameter syntax

5. You want to ensure a query always returns fresh data and never uses cached results in Snowflake. Which approach correctly achieves this while minimizing cost?

hard

A. Execute ALTER SESSION SET USE_CACHED_RESULT = FALSE; before the query to disable caching

B. Manually clear the entire result cache before running the query

C. Run the query multiple times to refresh the cache

D. Use a different warehouse to bypass caching

Solution

1. Step 1: Identify how to disable result caching per session

   Snowflake allows disabling result caching using ALTER SESSION SET USE_CACHED_RESULT = FALSE before the query.

2. Step 2: Evaluate other options for cost and effectiveness

   Manually clearing cache or switching warehouses is inefficient and costly; running multiple times does not guarantee fresh data.

3. Final Answer:

   Execute ALTER SESSION SET USE_CACHED_RESULT = FALSE; before the query to disable caching -> Option A

4. Quick Check:

   Use USE_CACHED_RESULT = FALSE for fresh data [OK]

Hint: Use ALTER SESSION SET USE_CACHED_RESULT = FALSE for fresh data without extra cost [OK]

Common Mistakes:

• Trying to clear cache manually
• Switching warehouses unnecessarily
• Relying on repeated runs to refresh data