In continuation of previous post related to Caching, Below are different Caching States of Snowflake Virtual Warehouse:
a) Cold
b) Warm
c) Hot:
- Run from cold: Starting Caching states, meant starting a new VW (with no local disk caching), and executing the query.
- Run from warm: Which meant disabling the result caching, and repeating the query. This makes use of the local disk caching, but not the result cache.
- Run from hot: Which again repeated the query, but with the result caching switched on.
![](https://cloudyard.in/wp-content/uploads/2021/04/Cold.jpg)
Total execution time is 4.2s, 0% of data is scanned from Cache and 87% of Remote Disk IO.
Run from Warm:
The same Query is executed with an additional column p_name. Select the needed columns from the Local disk Cache and the new column from the Remote Disk S3.
![](https://cloudyard.in/wp-content/uploads/2021/04/Hot.jpg)
![](https://cloudyard.in/wp-content/uploads/2021/04/Warm.jpg)
Typically, query results are reused if all of the following conditions are met:
- The user executing the query has the necessary access privileges for all the tables used in the query.
- The new query syntactically matches the previously-executed query.
- The table data contributing to the query result has not changed.
- The query does not include functions that must be evaluated at execution (e.g. CURRENT_TIMESTAMP()).
- The table’s micro-partitions have not changed (e.g. been re-clustered or consolidated) due to changes to other data in the table.
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?
Tuning Tips:
- Auto-Suspend: By default, Snowflake auto-suspend a virtual warehouse after 10 minutes of idle time. To illustrate the point, consider these two extremes:
- Suspend after 60 seconds.
- Suspend Never: Cache will always be warm, but pay for compute resources, even if nobody is running any queries.
- Scale up for large data volumes: Scaling up the VW ,if sequence of large queries perform against massive data volume.
- Scale down – but not too soon: Once large task completed, Reduce costs by scaling down or even suspending the virtual warehouse.
To have understanding of Caching from scratch, please click here.