Result caching layers in Snowflake - Commands & Configuration

Introduction

When you run queries in Snowflake, it can save the results so it doesn't have to do the same work again. This saves time and computing power by using cached results instead of re-running the full query.

When you run the same query multiple times and want faster results.

When you want to reduce the cost of repeated data analysis.

When you have dashboards that refresh often but the underlying data does not change.

When you want to improve performance for reports used by many users.

When you want to avoid unnecessary load on your Snowflake warehouse.

Commands

Run a simple query to check the current time and see if result caching is working by running it again quickly.

Terminal

SELECT CURRENT_TIMESTAMP();

Expected Output

CURRENT_TIMESTAMP()

2024-06-01 12:00:00.000

Run the same query again immediately to get the result from cache, which is faster and uses less compute.

Terminal

SELECT CURRENT_TIMESTAMP();

Expected Output

CURRENT_TIMESTAMP()

2024-06-01 12:00:00.000

Check how much result caching Snowflake has used recently to confirm caching is active.

Terminal

SELECT SYSTEM$RESULT_CACHE_USAGE();

Expected Output

RESULT_CACHE_HITS

1

Turn off result caching for the session to force queries to run fresh, useful for testing or when you want the latest data.

Terminal

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

Expected Output

No output (command runs silently)

Run the query again with caching turned off to see the fresh result and confirm caching is disabled.

Terminal

SELECT CURRENT_TIMESTAMP();

Expected Output

CURRENT_TIMESTAMP()

2024-06-01 12:00:01.500

Key Concept

If you remember nothing else from this pattern, remember: Snowflake saves query results automatically and reuses them to speed up repeated queries without extra cost.

Common Mistakes

Expecting result caching to work when the underlying data has changed.

Snowflake invalidates cached results if the data changes, so the cache won't be used and the query runs fresh.

Understand that result caching only works when data and query text are unchanged.

Turning off caching globally without knowing it increases compute costs.

Disabling caching causes all queries to run fully every time, which uses more resources and costs more.

Disable caching only when necessary, like for testing or debugging.

Summary

Run a query once to store its result in Snowflake's cache.

Run the same query again to get fast results from the cache without extra compute.

Use session settings to enable or disable caching as needed for fresh data or testing.