Result cache is a powerful performance optimization feature in Snowflake that stores the results of previously executed queries, enabling instant retrieval when identical queries are run again. This mechanism significantly reduces compute costs and improves query response times by eliminating the n…Result cache is a powerful performance optimization feature in Snowflake that stores the results of previously executed queries, enabling instant retrieval when identical queries are run again. This mechanism significantly reduces compute costs and improves query response times by eliminating the need to re-execute queries against the underlying data.
When a query is executed in Snowflake, the system automatically caches the results for a period of 24 hours. If the same exact query is submitted again within this timeframe, and the underlying data has not changed, Snowflake retrieves the cached results instead of processing the query from scratch. This retrieval happens at no compute cost since virtual warehouses are not required to fetch cached results.
For the result cache to be utilized effectively, several conditions must be met. First, the query text must be identical, including whitespace and case sensitivity. Second, the underlying table data must remain unchanged since the original query execution. Third, the same role must be used, ensuring proper access control. Fourth, any configuration parameters affecting query results must remain consistent.
The result cache operates at the cloud services layer, making it accessible across all virtual warehouses within an account. This means if one user runs a query and another user with appropriate permissions runs the same query later, both can benefit from the cached results.
From a cost optimization perspective, result cache is invaluable for repetitive analytical workloads, dashboards, and reporting scenarios where users frequently execute similar queries. Organizations can achieve significant cost savings by reducing warehouse runtime and improving user experience through faster query responses.
It is important to note that result cache is enabled by default and can be controlled at the session level using the USE_CACHED_RESULT parameter. Understanding and leveraging result cache effectively is essential for optimizing both performance and costs in Snowflake environments, making it a key topic for SnowPro Core Certification preparation.
Result Cache in Snowflake: Complete Study Guide
What is Result Cache?
Result Cache is a Snowflake feature that stores the results of queries for 24 hours. When an identical query is executed again, Snowflake returns the cached result instead of re-executing the query against the data. This is one of Snowflake's three caching layers, alongside the metadata cache and local disk cache (warehouse cache).
Why is Result Cache Important?
Result Cache is crucial for several reasons:
• Cost Savings: Queries served from result cache consume zero compute credits because no virtual warehouse processing is required • Performance: Cached results are returned in milliseconds, providing near-instantaneous response times • Efficiency: Reduces unnecessary compute operations for repetitive queries • User Experience: Dashboard refreshes and repeated reports load extremely fast
How Result Cache Works
The result cache operates based on specific conditions:
Cache Hit Requirements: • The query must be syntactically identical (exact match) • The underlying table data must not have changed • The query must not include functions that evaluate at runtime (like CURRENT_TIMESTAMP()) • The user must have the necessary privileges on the objects • The cache entry must be within the 24-hour validity window
Key Characteristics: • Result cache is stored at the cloud services layer, not at the warehouse level • Results can be shared across different virtual warehouses for the same user • No warehouse needs to be running to retrieve cached results • Result cache is enabled by default at both the account and session level
Controlling Result Cache
You can control result cache behavior using:
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
This disables result cache usage for the current session. Account-level control is also available for administrators.
When Result Cache is Invalidated
The cache becomes invalid when: • Underlying data is modified (INSERT, UPDATE, DELETE, MERGE) • Table structure changes • 24 hours have passed since the cache was created • Micro-partitions accessed by the query are modified
Exam Tips: Answering Questions on Result Cache
1. Remember the 24-hour rule: Result cache persists for exactly 24 hours unless invalidated earlier
2. No compute costs: When questions mention cost optimization with identical queries, result cache is often the answer
3. Cloud Services Layer: Result cache lives in the cloud services layer, not at the warehouse level - this is frequently tested
4. Warehouse independence: A query cached from Warehouse A can serve results even when accessed from Warehouse B, or when no warehouse is running
5. Exact match requirement: Even a single space or character difference creates a new query that won't use the cache
6. Non-deterministic functions: Questions about CURRENT_DATE(), RANDOM(), or UUID functions - these prevent result cache usage
7. Default behavior: Result cache is ON by default - if asked about default settings, remember this
8. DML impact: Any data modification invalidates relevant cached results - watch for scenarios involving data changes
9. Distinguish from warehouse cache: Warehouse cache (local disk cache) stores raw data at the warehouse level, while result cache stores query results at the cloud services layer
10. Privilege requirements: Users must have appropriate access privileges for cached results to be used - security is never bypassed