Result caching layers in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

How does Snowflake's result cache behave after data changes?

Snowflake caches query results to speed up repeated queries. What happens to the cached results if the underlying table data changes?

A The cached results are automatically invalidated and not used for subsequent queries.

B The cached results are used only if the query is run within 5 minutes of the data change.

C The cached results are updated automatically to reflect the new data.

D The cached results remain and are returned until manually cleared.

💡 Hint

Think about data consistency and cache freshness.

Snowflake invalidates cached results automatically when the underlying data changes to ensure queries return fresh data.

❓ Architecture

intermediate

Which layer in Snowflake caches query results for all users?

Snowflake has multiple caching layers. Which cache layer stores results that can be reused by any user running the same query?

A Local disk cache on the user's client machine

B Warehouse cache specific to a virtual warehouse

C Result cache shared across all users and warehouses

D Metadata cache inside the database catalog

💡 Hint

Consider which cache is global and query-specific.

The result cache stores the results of queries and is shared globally across all users and warehouses, enabling reuse of identical query results.

❓ security

advanced

What security consideration applies to Snowflake's result cache?

Snowflake caches query results globally. What security feature ensures users only see cached results they are authorized to access?

A Result cache requires manual approval before sharing results.

B Result cache stores results only for queries run by the same user.

C Result cache encrypts data with user-specific keys.

D Result cache respects role-based access control and only returns results if the user has permission.

💡 Hint

Think about how Snowflake enforces data access controls.

Snowflake enforces role-based access control on cached results, so users only receive cached data if they have permission to see it.

✅ Best Practice

advanced

How can you maximize the benefit of Snowflake's result cache in your queries?

To get the most speed from Snowflake's result cache, what practice should you follow when writing queries?

A Use SELECT * to ensure all columns are cached.

B Write queries with consistent syntax and avoid unnecessary changes.

C Run queries from different warehouses to increase cache hits.

D Disable caching to always get fresh data.

💡 Hint

Think about how query text affects cache matching.

Snowflake's result cache matches queries exactly by text. Writing queries consistently helps reuse cached results.

🧠 Conceptual

expert

What is the maximum duration Snowflake retains results in the result cache?

Snowflake keeps query results in the result cache for a limited time. What is the maximum time a result can stay cached if the underlying data does not change?

A 24 hours

B 90 days

C 7 days

D Indefinitely until cache is manually cleared

💡 Hint

Consider Snowflake's default cache retention policy.

Snowflake retains query results in the result cache for up to 24 hours if the underlying data remains unchanged.