❄️ Snowflake Result Set Reuse – Previously Executed Query Results Are Automatically Reused

Every time you execute a SQL query in a traditional database, the system processes it from scratch — reading data from storage, performing joins, and computing results.

But Snowflake is smarter.

Thanks to its Result Set Reuse mechanism, Snowflake can automatically return results from a previous query if:

1. The SQL text is identical.
2. The underlying data has not changed.
3. The user and session permissions match.

This means that instead of re-running heavy computations, Snowflake simply retrieves the cached result — instantly.

The result? ⚡ Faster performance, 💰 lower compute costs, and 🔁 consistent query experience.

Let’s explore how this works, when it applies, examples, and how to prepare for interviews around this topic.

❄️ What Is Result Set Reuse in Snowflake?

Result Set Reuse is a query optimization feature that allows Snowflake to return results from a previous identical query without re-executing the entire query.

Snowflake maintains a Result Cache, which stores the final output of queries for a certain period (typically 24 hours).

If you run the same query again, and the underlying data has not changed, Snowflake retrieves the result from the cache.

⚙️ How It Works (Simplified Flow)

✅ Key Takeaway: Snowflake avoids unnecessary computation by checking whether it can reuse a previous query’s output safely.

🧠 Conditions for Result Set Reuse

Result set reuse occurs only if all these conditions are met:

You can check or modify this session setting:

SHOW PARAMETERS LIKE 'USE_CACHED_RESULT';
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

🧩 Example 1 – Basic Result Set Reuse

Let’s start with a simple demonstration.

-- Enable result caching
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

-- Create a sample table
CREATE OR REPLACE TABLE EMPLOYEES (
  ID INT, NAME STRING, DEPARTMENT STRING
);

INSERT INTO EMPLOYEES VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', 'IT'),
(3, 'Charlie', 'Finance');

-- Run a query
SELECT * FROM EMPLOYEES;

-- Run the same query again
SELECT * FROM EMPLOYEES;

✅ Explanation:

• The first query runs normally and stores results in the cache.
• The second identical query retrieves the same result from the Result Cache, without scanning the table again.

✅ Benefit: No compute cost for the second query — instant result!

🧩 Example 2 – Detecting When Cache Is Used

You can check whether Snowflake reused a cached result by using:

SELECT * FROM EMPLOYEES;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Or check query metadata:

SELECT QUERY_TEXT, BYTES_SCANNED, EXECUTION_STATUS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TEXT LIKE '%SELECT * FROM EMPLOYEES%'
ORDER BY START_TIME DESC;

✅ Observation:

• If BYTES_SCANNED = 0, it means Snowflake did not read data from storage — it reused the cached result.

🧩 Example 3 – Cache Invalidated by Data Change

If the underlying data changes, Snowflake invalidates the cache automatically.

-- Initial query - result cached
SELECT COUNT(*) FROM EMPLOYEES;

-- Insert new data
INSERT INTO EMPLOYEES VALUES (4, 'Diana', 'HR');

-- Run query again
SELECT COUNT(*) FROM EMPLOYEES;

✅ Result:

• The second query re-runs because the table changed.
• The previous cached result is invalidated.

💡 Tip: You can force bypassing the cache manually using:

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

🧠 Types of Query Caching in Snowflake

Result Set Reuse is part of a broader caching system:

Result Set Reuse specifically refers to using the Result Cache.

🧩 ** Snowflake Caching Layers**

✅ Note: Result Cache reuse occurs at the service layer, independent of warehouse compute.

💡 Benefits of Result Set Reuse

🧮 How Long Are Results Cached?

• Cached results persist for 24 hours by default.
• They are stored in the result cache layer, not in the user’s warehouse.
• Cached results are automatically purged after 24 hours or when the underlying data changes.

🧠 Memory Tricks to Remember for Interviews

💡 Mnemonic:

“If nothing changed, Snowflake doesn’t re-think!”

🎯 Why It’s Important to Learn Result Set Reuse

🧩 Example 4 – Real-World Dashboard Optimization

Imagine a Power BI dashboard refreshing every minute with:

SELECT REGION, SUM(SALES) FROM SALES_DATA GROUP BY REGION;

If data isn’t changing frequently, Snowflake reuses cached results for each refresh — avoiding hundreds of repeated queries.

✅ Outcome:

• Near-zero compute usage.
• Lightning-fast dashboard updates.

🧩 Example 5 – Using Result Cache with Different Users

Result cache is user-specific unless the same role and permissions apply.

-- User A
SELECT COUNT(*) FROM ORDERS;

-- User B with same role and access
SELECT COUNT(*) FROM ORDERS;

✅ Result: Both users benefit from the same cached result because roles and privileges align.

🧩 Example 6 – Disabling Result Cache for Benchmarking

When testing warehouse performance, disable caching to measure true query speed.

ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT * FROM SALES_DATA;

✅ Benefit: You get an accurate measure of warehouse compute performance.

📊 Query Cache Performance Analysis

You can analyze query caching behavior using:

SELECT
  QUERY_TEXT,
  EXECUTION_STATUS,
  BYTES_SCANNED,
  START_TIME,
  END_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TEXT ILIKE '%CUSTOMER%'
ORDER BY START_TIME DESC;

Look for:

• BYTES_SCANNED = 0 → Cached result reused.
• EXECUTION_STATUS = SUCCESS → Cache served result successfully.

🧠 Common Misconceptions

💡 Best Practices for Using Result Set Reuse

1. ✅ Enable Cached Results:

   ALTER SESSION SET USE_CACHED_RESULT = TRUE;

2. ⚙️ Avoid Randomized Queries: Queries using RANDOM() or CURRENT_TIMESTAMP invalidate caching.

3. 💾 Use Stable SQL Text: Formatting or comment changes alter cache matching.

4. 📊 Leverage for Dashboards: Reusing results improves BI performance dramatically.

5. 🧹 Monitor Caching Behavior: Use QUERY_HISTORY to identify which queries reuse cache.

🧠 Interview & Exam Preparation Guide

🔹 Common Questions:

1. What is Snowflake Result Set Reuse?
2. How is it different from Warehouse Cache?
3. When is the result cache invalidated?
4. How can you force disable caching?
5. How do you know if a query reused a cached result?
6. Does cache depend on the warehouse used?
7. Can result caching reduce compute credits?

🔹 Quick Recall Trick:

“Result Set Reuse = Remembering answers to identical questions.”

💻 SQL to Force Cache Refresh

ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT * FROM CUSTOMERS;
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

✅ Useful when you suspect outdated cached results.

🧩 ** Full Cache Flow**

📘 Real-World Analogy

Think of Snowflake’s Result Set Reuse like searching on Google. If you search for the same query twice within a short time, Google doesn’t recrawl the web — it returns the cached result faster.

Similarly, Snowflake keeps your last query’s result ready, saving time and cost.

💡 How to Remember This Concept Easily

🧠 Why It Matters

🏁 Conclusion

Snowflake’s Result Set Reuse is a perfect example of its intelligent cloud-native design.

It transforms how queries are processed — turning repeated executions into instant responses while saving both time and money.

For data engineers and analysts, understanding this concept is vital to designing efficient BI dashboards, cost-optimized warehouses, and certification success.

🌟 Final Thought

“The fastest computation is the one you don’t need to repeat — and Snowflake knows that.”