appreciate if someone can share insight on below - Does snowflake result cache depends in anyway on the same warehouse being used or will queries running on same warehouse and using cache performs better versus queries from different warehouse? Also, does sub-queries benefit from result cache in snowflake?
2 Answers
Snowflake has the following cache and the Result cache dose not depends upon the Warehouse used.
Results Cache- it stores the results and is retained for 24 Hours.
Metadata cache - The metadata cache keep all the metadata information including the table count.
WH Cache - The WH Cache is used when VWH is running and you access the same underline data from the same WH
The WH cache may be reset if a running WH is suspended and then resumed because when it is resumed it may be running in a different server.
To answer your questions:
- Does snowflake result cache depends in anyway on the same warehouse being used or will queries running on same warehouse and using cache performs better versus queries from different warehouse?
No. Result Cache is independent to the virtual warehouse.
- Does sub-queries benefit from result cache in snowflake?
If you mean only the sub-query portion of a larger query, then no. Result cache cannot be used on partial optimisation. In fact, for Snowflake to utilise the result cache, the query must be syntactically identical as previously executed.
There is also a number of conditions to be met to use the result cache. Check out the documentation for more details: https://docs.snowflake.com/en/user-guide/querying-persisted-results.html
For other types of Caching, you may refer to the ff.:
Related