Result Cache Access in Snowflake Cloud Data Warehouse

Question

Snowflake offers various cache options and one of them is result cache. I understand that other users can use query result cache to access the result of repeated query (executed within 24 hours) but should they be in the same role OR users with all roles can access the cache results?

If the tool behavior has recently changed, what holds correct for Snowpro certification exam?

Answer 1

But should they be in the same role OR users with all roles can access the cache results?

The result cache should come into play well after access-authorization checks, so the role-membership of the authenticated user or service does not matter.

The cache is permitted for read in the same manner the table (or view) results are, and the Snowflake architecture separates the storage and cache from the compute layers, so the only rules that matter on its use are the ones defined in the documentation.

Answer 2

Typically, query results are reused if all of the following conditions are met:

1. The user executing the query has the necessary access privileges for all the tables used in the query.
   2. The new query syntactically matches the previously-executed query. T
   3. The table data contributing to the query result has not changed.
   4. The persisted result for the previous query is still available.
   5. Any configuration options that affect how the result was produced have not changed.
   6. The query does not include functions that must be evaluated at execution (e.g. CURRENT_TIMESTAMP()).
   7. The table’s micro-partitions have not changed (e.g. been re- clustered or consolidated) due to changes to other data in the table.

Ref : https://community.snowflake.com/s/article/Understanding-Result-Caching

Answer 3

I understood that the role accessing the cached results has the required privileges -

-- If the query was a SELECT query, the role executing the query must have the necessary access privileges for all the tables used in the cached query.

-- If the query was a SHOW query, the role executing the query must match the role that generated the cached results.