Caching types: Caching States in Snowflake

Read Time:1 Minute, 46 Second

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.

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.

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:
1. Suspend after 60 seconds.
2. 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.