Result Reuse and Caching in Snowflake
π What is Result Reuse?β
β¨ Result Reuse means that when the exact same SQL query is executed again, Snowflake can return the stored result set from the Result Cache instead of re-executing the query.
β‘ Why it matters?
- π« No redundant computation
- β‘ Instant responses (milliseconds)
- π° Zero Snowflake credits used
β³ Validity Window: Results are cached for 24 hours and reused only if:
- π SQL text is identical
- π Underlying data has not changed
- π Session context (role, warehouse, timezone, etc.) is the same
π Result Cache = storage mechanism
π Result Reuse = behavior of serving cached results
π¦ What is Caching?β
Caching in Snowflake is the broader process of storing results, metadata, and data blocks to make queries faster.
ποΈ Types of Cachingβ
1οΈβ£ π Result Cache
- Stores final query results (used in Result Reuse).
- Shared across users & sessions for 24 hours.
- β Free β No warehouse cost.
2οΈβ£ π Metadata Cache
- Stores micro-partition statistics (min/max values, row counts, file locations).
- Enables partition pruning β fewer partitions scanned.
3οΈβ£ πΎ Local Disk Cache (Data Cache)
- Each warehouse caches data blocks on SSD when reading from cloud storage.
- Subsequent queries in the same warehouse can reuse cached blocks.
- β Cleared when the warehouse is suspended.
π Example Scenarioβ
π€ User A runs:
SELECT customer_id, SUM(amount)
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY customer_id;
π’ Snowflake executes the query β stores result in Result Cache.
π₯ User B runs the same query within 24 hours.
- β Outcome:
- π Result is returned instantly.
- π° No warehouse credits consumed.
β‘ If User B changes the query (e.g., new date range):
- β Result Cache not reused.
- β Snowflake may still use Metadata Cache (partition pruning) and Local Disk Cache (if blocks exist in SSD).
π Key Differences: Result Reuse vs Cachingβ
| π Feature | π Result Reuse (via Result Cache) | π¦ Caching (Metadata + Local Disk) |
|---|---|---|
| π― Scope | Final query results | Underlying data blocks & metadata |
| β±οΈ Duration | 24 hours | Until warehouse suspension / metadata refresh |
| π° Cost | β 0 credits (no warehouse) | β‘ Uses warehouse (credits apply) |
| π οΈ Use Case | Identical queries | Similar queries, overlapping data |
| π₯ Shared? | β Across users/sessions | β Warehouse-specific |
π§© Example: Putting It All Togetherβ
-- First run
SELECT SUM(amount)
FROM sales_transactions
WHERE trans_date BETWEEN '2025-09-01' AND '2025-09-10';
πΉ First Run β Warehouse scans partitions (uses Metadata Cache) + stores blocks in Local Disk Cache. πΉ Second Run (same SQL) β Snowflake skips compute and uses Result Cache β β‘ instant, π° free. πΉ Different Query (new date range) β Metadata & Local Cache may help, but warehouse credits still used.
π§ Visual Flow: How Snowflake Uses Cachesβ
π Use Casesβ
π BI Dashboards β Multiple users refreshing the same dashboard β results reused instantly. π Static Reports β Daily/weekly summaries β cached results avoid reprocessing. π§ͺ Training & Development β Developers rerunning queries β saves credits.
π Best Practicesβ
- β Make dashboard queries identical (avoid tiny text differences).
- β
Avoid volatile functions (
NOW(),RANDOM(),CURRENT_TIMESTAMP). - β Use Result Reuse for repeated static queries.
- β Benefit from multi-user cache sharing β one run helps everyone.
β οΈ Limitationsβ
β Cache invalidates when data changes. β Expires after 24 hours. β No manual control (canβt force expiry or extension). β Not a substitute for query optimization.
π Advantagesβ
β¨ Instant results for identical queries. β¨ Zero cost (no warehouse used). β¨ Concurrency boost β many users can share results. β¨ Automatic β no setup needed.
π§ Disadvantagesβ
β οΈ Not effective for dynamic queries (results always change). β οΈ Can hide slow performance (query looks fast due to cache). β οΈ Temporary β only valid for 24 hours.
π Roles & Privilegesβ
- π« No special role needed to benefit from Result Reuse.
-
To analyze usage via history views:
-
QUERY_HISTORY,WAREHOUSE_LOAD_HISTORY -
Require
MONITORor higher privileges (e.g.,ACCOUNTADMIN).
-
Why Result Reuse & Caching Matter
βοΈ Result Reuse = β‘ instant identical query responses βοΈ Caching = π¦ faster access + reduced compute cost βοΈ Perfect for dashboards, reports, and static workloads β Not ideal for dynamic, frequently changing queries