Member-only story
Road to Snowflake SnowPro Core Certification: Cache & Query Performance
Twelfth Chapter: Cache and Query Performance
One of the most important concepts is the cache to improve the speed of our queries in Snowflake and optimize costs. In this chapter, we will study the existing ones (Metadata, Query Result, and Warehouse cache) and some tips to improve Snowflake’s performance.
- Metadata Cache
- Query Result Cache
- Warehouse Cache
- Complete example of the Snowflake caches
- How to improve Snowflake performance
- Typical exam questions on Snowflake caches
Remember that all the chapters from the course can be found in the following link:
METADATA CACHE
Metadata caching is maintained in Global Service Layer and contains Objects Information & Statistics. As we mentioned in the chapter about micro-partitions, Snowflake automatically stores different types of metadata to improve the compiling time and query optimization. This metadata information lasts for 64 days. This cache will help us perform operations like MIN, MAX, COUNT… Snowflake will NOT use the warehouses in these cases, so we don’t spend computing credits.
Remember that we also studied that you cannot copy the same file into Snowflake using the COPY INTO command unless you specify the option FORCE=TRUE during 64 days? Now you understand who is in charge of that.
Just as an example, we can have a 30TB table with 931 million rows and get the COUNT of all the values from the table in microseconds without performing anything:
QUERY RESULT CACHE
Have you ever tried to execute the same query twice, and the second time is way…
