Snowflake Caching Validation

user_18556276 0
Viewed 84

Is the cache valid once it has been retrieved, even after the warehouse has been suspended?

For example, the same query used in BI that is provided to multiple users is executed each time a user visits. The cache is available in this case, but is it still possible to use the cache when the warehouse is stopped? If not available, the perception is that there is a speed benefit in a system where the uptime of the warehouse is charged for, but on the other hand, there is no benefit on the cost side.

Is the cache valid once it has been retrieved, even after the warehouse has been suspended?

snowflake-cloud-data-platform
2 Answers

@Himanshu gave good explanation. Adding how it looks in query profiler for all three scenarios -

enter image description here

Metadata Cache - For any query such as - select count(*) from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CATALOG_SALES;

enter image description here

user_139153130

Are you taking of the Warehouse cache, it gets purged once the Warehouse is stopped and restarted.

Snowflake has the following different cache available,

a. Metadata cache – hold object information+ Statistics ( it is also called Metadata layer or Service layer or cloud service layer)

b. Result cache – last 24hrs of your result, the query result cache is retained for a maximum of 31 days after being generated. (it is also called Result set cache or 24 hrs. result cache or query result cache)

c. Warehouse cache – hold data locally as long as warehouse is running. (When Warehouse is suspended the cache is purged and cache is not purged when resumed) (It is also called local cache or SSD cache or raw data cache or data cache) (Users cannot see each other’s result but the result cache can re-use one users result cache and present it to other user)

I think as you are using the query you are talking of the Result cache, the cache is used by Snowflake if

  1. The Query is same syntactically
  2. If the user has permission to the tables
  3. If the data in the tables used in the query has not changed
  4. The query dose nit contains time function like CURRENTTIMESTAMP() and the query dose not use UDF.
  5. The table micropartions have not been changed or re-clustered.
user_112279190
Related