Snowflake Caching Performance Explained — A Clear, Practical Benchmark Story

-

Originally published on LinkedIn: https://www.linkedin.com/pulse/snowflake-caching-performance-explained-clear-story-mohapatra-p0ric/?trackingId=XOnrAVSWV7bM%2FOvYlOboOA%3D%3D

Migrated on: 2026-04-05

  • The query result cache is essential for repeated query performance. By reusing the results of recently run queries, it drastically reduces both time and resource consumption.

🛠️ How It Works: If the exact query is repeated within 24 hours and no changes occur in the underlying data, Snowflake will return the cached result. This optimization can save significant compute costs in repetitive reporting environments.

👩💻 Real-World Example: A marketing analyst rerunning customer engagement reports throughout the day would experience much faster query response times due to the query result cache, thus streamlining the reporting process unless the underlying data has changed.

  • Managing Warehouse Cache for Efficiency

The warehouse cache stores data in a running virtual warehouse's SSD. This is most beneficial when users or systems frequently access the same datasets during an active session.
Note that you will not get stale results from the warehouse cache. If the warehouse cache has been rendered obsolete by DML operations, then Snowflake will retrieve the most current version of the underlying data from the micropartions.

🔑 Key Features:

  • Data remains in the warehouse cache as long as the warehouse stays active.
  • The warehouse’s cache size and query pattern determine how effectively it can speed up operations.

The size of the warehouse determines how much warehouse cache it can store.

👩💻 Real-World Example: When data engineers execute complex queries repeatedly during a project, the warehouse cache ensures that data is fetched rapidly from local SSD instead of having to fetch data from micro-partitions in the storage layer (which is slower).

🤝 Important Trade-off: While keeping the warehouse active to retain the cache helps performance, it also increases costs, as running warehouses still incur charges. Fine-tuning auto-suspension is necessary to balance performance with cost efficiency.

When analysing large‑scale analytics workloads, understanding Snowflake’s caching layers is essential for optimizing performance. To demonstrate how Snowflake intelligently uses its caching mechanisms, a benchmark was executed on 1.5 billion rows (≈60 GB raw data) using a Medium warehouse (4 nodes). No performance tuning, clustering, or indexing was applied — the query was executed exactly as-is.

The test was performed three times:

  1. Cold Run – no cache
  2. Warm Run – local disk (SSD) cache
  3. Hot Run – result cache

1. Cold Run — First Execution (No Cache)

  • Warehouse started fresh → no local disk cache
  • Result cache disabled
  • Query scanned ~12 GB of compressed data (Snowflake only reads required columns)
  • Execution Time: ~20 seconds

Observation: Almost 50% of total time was consumed by remote/local disk I/O, while computation was only 2%. Since no partition pruning or clustering was applied, Snowflake had to scan all necessary micro‑partitions.

Even without any tuning, scanning billions of rows in 20 seconds is impressive.

2. Warm Run — Local Disk (SSD) Cache

  • Executed immediately after the cold run
  • Result cache still disabled
  • Most data served directly from local SSD cache
  • Execution Time: ~1.2 seconds (≈16× faster)

Observation: Around 99% of data came from Snowflake’s local disk cache. Disk I/O dropped to ~11%, drastically accelerating the query. This illustrates Snowflake’s highly efficient automatic caching.

3. Hot Run — Result Cache

  • Re-executed with result cache enabled
  • No change in data or SQL
  • Snowflake served the entire result directly from its metadata cache
  • Execution Time: a few milliseconds (≈2 ms)

Why so fast? Snowflake uses a “compiled result” cache. If the:

  • SQL text is identical, and
  • underlying micro-partitions have not changed

— Snowflake returns cached results instantly.

-- Results typically persist for 24 hours, and repeated executions keep extending this window (up to 30 days), assuming no changes.

This is especially powerful for dashboards and BI tools that refresh queries repeatedly.

  • Raw Data: 60 GB+
  • Rows: 1.5 billion
  • Compression: Snowflake returned only required columns, reducing scan to ~12 GB

Why This Matters

Unlike traditional databases where you must manage:

  • partitioning
  • indexing
  • compression
  • stats gathering
  • tuning strategies

Snowflake handles all caching automatically. Without any optimization effort, you immediately benefit from:

  • Intelligent micro‑partitioning
  • Columnar compression
  • Local SSD caching
  • Result caching

Article content

System Performance Tuning Best Practice

Clearly data caching makes a massive difference to Snowflake query performance, but what can you do to ensure maintain the performance when you cannot change the cache?

Here's a few best practice tips:-

  • Auto-Suspend: By default, Snowflake will auto-suspend a virtual warehouse (the compute resources with the SSD cache after 10 minutes of idle time.  Best practice?  Leave this alone.  Keep in mind, you should be trying to balance the cost of providing compute resources with fast query performance.  To illustrate the point, consider these two extremes:Suspend after 60 seconds: When the warehouse is re-started, it will (most likely) start with a clean cache, and will take a few queries to hold the relevant cached data in memory.  (Note:  Snowflake will try to restore the same cluster, with the cache intact, but this is not guaranteed).Suspend Never: And your cache will always be warm, but you will pay for compute resources, even if nobody is running any queries. However, provided you set up a script to shut down the server when  not being used, it may make sense.
  • Scale up for large data volumes: If you have a sequence of large queries to perform against massive (multi-terabyte) size data volumes, you can improve query performance by scaling up.  Simple execute a SQL statement to increase the virtual warehouse size, and new queries will start on the larger (faster) cluster.  While this will start with a clean (empty) cache, you should normally find performance doubles at each size, and this extra performance boost will more than out-weigh the cost of refreshing the cache.
  • Scale down - but not too soon: Once your large task has completed, you could reduce costs by scaling down or even suspending the virtual warehouse.  Be aware again however, the cache will start again clean on the smaller cluster.  By all means tune the warehouse size dynamically, but don't keep adjusting it, or you'll lose the benefit.

Reference-https://docs.snowflake.com/en/user-guide/performance-query-warehouse-cache

https://community.snowflake.com/s/article/Caching-in-the-Snowflake-Cloud-Data-Platform

Comments

Post a Comment