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.