Snowflake Caching Tips: Points to be consider Cloudyard

Read Time:2 Minute, 21 Second

Snowflake Caching : Imagine executing a query that takes 10 minutes to complete. Re run the same query later in the day while the underlying data hasn’t changed, We are wasting resources.

Snowflake caches the results of every query you ran. When a new query submit, It verify old queries and if a matching query exists, results are still cached. Therefore, it uses the cached result set instead of executing the query. This can greatly reduce query times because Snowflake retrieves the result directly from the cache.

Performance Tuning Tips:

Always use Limit Clause with Select * from table. Because VW pulls complete data from storage and execute the query and store all the results into Result Cache.
- This way charge for the computation even we use SELECT * to know only type or structure of data.
Suspended VW cause Local Disk cache purged ,So advisable to increase the suspend time limit at least 15-20 mins in Dev.
Share the VW when group of users are working on common tables. Once we get data inside the Local Disk and users belong to different VW can leverage the Local disk cache instead of getting data from Storage layer.
Never disable Cloud Service Result Cache layer: This layer holds the result of query and if other users execute the same query then instead of routing to VW and Storage layer , Get the output from Result Cache layer itself . There no computation cost involve.
What happens if underlying table gets updated: If result is stored in Result Cache layer and we have updated/delete the data from table and then exeute the Select Query , Snowflake does not use Result Cache, it sends request to VW layer which in turn pulls the daat from Storage layer ,Cache it at Local disk ,execute the query and stored the latest Output in Result Cache.

Hence it is advisable first perfom all DML operations and then execute the DDL to avoid the performance issues.

Important Points:

How long this Query result will be cached: Query result cached for 24 hours since its last execution.
- If we execute the query in 24 hours then result fetches from Cloud service layer else data from Storage layer.
Does Snowflake charge to store the result: No Snowflake stores the result for free for 24 hours and you can execute the queries within 24 hours with no cost i.e. Cache period for a query result in Result cache is 24 hrs. If i retrieve that result again at 23 rd hour, cached result will again stay there for 24 hours. but somehow you execute the query after 24 Snowflake charges the processing cost.
Result of already run query persist for 24 hours ,Every time the query runs it set the 24 hour window for another 24 hours till 31 days