How to Control Cache Behavior in Snowflake

How does cache behavior work in Snowflake and how can I control it?

Snowflake lets you speed up or bypass query execution by leveraging or disabling its result, warehouse, and metadata caches.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What kinds of cache does Snowflake use?

Snowflake maintains a result cache (final result sets), warehouse cache (micro-partitions in local SSD), and metadata cache (file headers and statistics). Each layer accelerates repeat queries without extra cost.

How do I tell Snowflake to reuse or ignore cached results?

Set the USE_CACHED_RESULT session parameter. TRUE (default) lets identical SELECT statements retrieve data from the result cache instantly.FALSE forces full re-execution, useful during data validation.

Is there a way to see whether a query hit the cache?

Query the QUERY_HISTORY view or the UI. The BYTES_SCANNED field will be 0 when the result cache is used, and CACHE_HIT appears in the UI.

How do warehouse caches affect performance?

When a virtual warehouse is warm, Snowflake keeps recently accessed micro-partitions in SSD. Subsequent queries touching the same data avoid remote storage, lowering latency.Suspend or resize actions clear this cache.

Can I warm the warehouse cache manually?

Run a fast SELECT COUNT(*) or SELECT 1 from large tables right after the warehouse resumes. This brings data into SSD before user traffic starts.

How do metadata caches help external stages?

Snowflake stores file listings and statistics for external S3/GCS stages for about 60 minutes.Use ALTER STAGE … REFRESH or the VALIDATE function to refresh the cache when new files arrive.

Best practices for cache control

Leave USE_CACHED_RESULT on in production dashboards.
Disable it only for ad-hoc data freshness checks.
Keep warehouses running during peak periods to preserve the SSD cache.
Use CLUSTER BY to align frequently filtered columns with micro-partitions.

Example: verify fresh order totals without cache

Disable cached results, run the query, then turn caching back on so later reports are fast.

ALTER SESSION SET USE_CACHED_RESULT = FALSE; SELECT customer_id, SUM(total_amount) FROM Orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days' GROUP BY customer_id; ALTER SESSION SET USE_CACHED_RESULT = TRUE;

Common mistakes

Forgetting to re-enable caching

Leaving USE_CACHED_RESULT = FALSE in a long-lived session makes every query expensive.Always reset to TRUE after testing.

Assuming warehouse cache survives suspension

Suspending a warehouse empties its SSD cache. Schedule warm-up queries or avoid frequent stop-start cycles for critical workloads.

Need-to-know FAQs

Why How to Control Cache Behavior in Snowflake is important

How to Control Cache Behavior in Snowflake Example Usage


-- Fetch repeat buyers without using cached results
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT c.id, c.name, COUNT(o.id) AS order_cnt
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 1;
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

How to Control Cache Behavior in Snowflake Syntax


-- Enable or disable result cache at the session level
ALTER SESSION SET USE_CACHED_RESULT = { TRUE | FALSE };

-- Warm warehouse cache with a simple read
SELECT COUNT(*) FROM Orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Refresh metadata cache for external stage
ALTER STAGE new_orders REFRESH;

/* Example: Disable cache, get fresh result */
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT * FROM Customers WHERE created_at >= CURRENT_DATE - INTERVAL '1 day';
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

Common Mistakes

Leaving USE_CACHED_RESULT = FALSE, causing every query to bypass the result cache. Fix by resetting the parameter to TRUE once testing is done.
Expecting warehouse cache to persist after suspending the warehouse. Fix by keeping critical warehouses running or issuing warm-up queries on resume.