Result caching layers in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to enable result caching for a query in Snowflake.

Snowflake

SELECT * FROM sales WHERE region = 'US' [1];

Drag options to blanks, or click blank then click option'

A/*+ RESULT_CACHE */

BUSE RESULT_CACHE

CENABLE RESULT_CACHE

DRESULT_CACHE = TRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to use SQL keywords like ENABLE or USE which are not valid for result caching hints.

Adding options outside of comment syntax.

In Snowflake, you can hint to use result caching by adding the comment hint /*+ RESULT_CACHE */ after the query.

2fill in blank

medium

Complete the code to check if result caching is enabled for a query in Snowflake.

Snowflake

SELECT query_id, result_caching FROM table(information_schema.query_history()) WHERE query_text LIKE '%[1]%';

Drag options to blanks, or click blank then click option'

ARESULT_CACHING

BSELECT

CRESULT_CACHE

DCACHE

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect keywords that do not appear in query_text.

Confusing column names with SQL keywords.

The query_text column contains the SQL text. To find queries using SELECT statements, you filter with '%SELECT%'.

3fill in blank

hard

Fix the error in the query to disable result caching for a specific query.

Snowflake

SELECT * FROM orders [1] RESULT_CACHE = FALSE;

Drag options to blanks, or click blank then click option'

A/*+ RESULT_CACHE = FALSE */

BNO RESULT_CACHE

CDISABLE RESULT_CACHE

DSET RESULT_CACHE = FALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to use SET or NO keywords which are invalid in this context.

Placing the hint outside of comment syntax.

To disable result caching for a query, use the comment hint syntax: /*+ RESULT_CACHE = FALSE */.

4fill in blank

hard

Fill both blanks to create a query that uses result caching and filters by region 'EU'.

Snowflake

SELECT * FROM sales [1] WHERE region [2] 'EU';

Drag options to blanks, or click blank then click option'

A/*+ RESULT_CACHE */

B=

C!=

D/*+ NO_RESULT_CACHE */

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong comment hint to disable caching.

Using '!=' instead of '=' for filtering the region.

The comment hint /*+ RESULT_CACHE */ enables result caching. The filter uses = to select region 'EU'.

5fill in blank

hard

Fill all three blanks to create a query that disables result caching, filters orders with amount greater than 100, and orders results by date.

Snowflake

SELECT * FROM orders [1] WHERE amount [2] 100 ORDER BY [3];

Drag options to blanks, or click blank then click option'

A/*+ RESULT_CACHE = FALSE */

B>

Corder_date

Damount

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong comment hint syntax.

Using '<' instead of '>' for filtering.

Ordering by the wrong column.

The comment hint disables result caching. The filter uses '>' to select amounts greater than 100. The results are ordered by 'order_date'.

Practice

1. What does Snowflake's result caching do when you run the same query multiple times within 24 hours?

easy

A. Deletes the previous result and returns an error

B. Runs the query again and stores a new result

C. Requires manual cache refresh before returning results

D. Returns the cached result instantly without rerunning the query

Solution

1. Step 1: Understand Snowflake's caching behavior

   Snowflake stores query results for 24 hours to speed up repeated queries.

2. Step 2: Identify the effect of caching on repeated queries

   When the same query runs again within 24 hours, Snowflake returns the cached result instantly without rerunning the query.

3. Final Answer:

   Returns the cached result instantly without rerunning the query -> Option D

4. Quick Check:

   Result caching = instant cached return [OK]

Hint: Same query within 24h returns cached result instantly [OK]

Common Mistakes:

• Thinking query always reruns
• Assuming cache expires immediately
• Believing manual refresh is needed

2. Which of the following is the correct way to disable result caching for a single query in Snowflake?

easy

A. ALTER SESSION SET DISABLE_CACHE = TRUE;

B. ALTER SESSION SET USE_CACHED_RESULT = FALSE;

C. ALTER SESSION SET NO_CACHE_RESULT = TRUE;

D. ALTER SESSION SET SKIP_CACHE = TRUE;

Solution

1. Step 1: Recall how to disable result caching

   Snowflake uses ALTER SESSION SET USE_CACHED_RESULT = FALSE to disable caching for the session.

2. Step 2: Match the correct syntax

   The correct syntax is ALTER SESSION SET USE_CACHED_RESULT = FALSE;

3. Final Answer:

   ALTER SESSION SET USE_CACHED_RESULT = FALSE; -> Option B

4. Quick Check:

   Disable cache param = USE_CACHED_RESULT = FALSE [OK]

Hint: Use ALTER SESSION SET USE_CACHED_RESULT = FALSE to disable caching [OK]

Common Mistakes:

• Using wrong parameter names
• Incorrect ALTER SESSION syntax
• Confusing with other cache parameters

3. Consider this sequence of queries run in Snowflake:
SELECT COUNT(*) FROM sales;
Immediately followed by:
SELECT COUNT(*) FROM sales;
What will happen on the second query?

medium

A. Snowflake returns the cached result from the first query

B. Snowflake returns partial results from cache

C. Snowflake returns an error about duplicate queries

D. Snowflake reruns the query and returns fresh results

Solution

1. Step 1: Identify caching behavior for repeated queries

   Snowflake caches query results for 24 hours and returns cached results if the same query runs again.

2. Step 2: Apply to the given queries

   The second SELECT COUNT(*) query is identical and runs immediately, so Snowflake returns the cached result.

3. Final Answer:

   Snowflake returns the cached result from the first query -> Option A

4. Quick Check:

   Repeated query = cached result return [OK]

Hint: Repeated query returns cached result instantly [OK]

Common Mistakes:

• Assuming query always reruns
• Expecting partial or error results
• Confusing cache with data freshness

4. You executed this command in Snowflake:
ALTER SESSION SET USE_CACHED_RESUTL = FALSE;
But you notice subsequent queries still return cached results. What is the likely cause?

medium

A. Result caching cannot be disabled in Snowflake

B. The query is too complex to cache

C. The USE_CACHED_RESULT parameter is misspelled or incorrect

D. The cache expired before the query ran

Solution

1. Step 1: Verify the USE_CACHED_RESULT parameter usage

   The parameter must be spelled exactly USE_CACHED_RESULT and set to FALSE to disable caching.

2. Step 2: Identify why caching still happens

   If caching still occurs, the parameter might be misspelled or incorrectly specified.

3. Final Answer:

   The USE_CACHED_RESULT parameter is misspelled or incorrect -> Option C

4. Quick Check:

   Correct parameter spelling and value disables cache [OK]

Hint: Check parameter spelling and syntax to disable caching [OK]

Common Mistakes:

• Assuming caching can't be disabled
• Misspelling the parameter name
• Ignoring exact parameter syntax

5. You want to ensure a query always returns fresh data and never uses cached results in Snowflake. Which approach correctly achieves this while minimizing cost?

hard

A. Execute ALTER SESSION SET USE_CACHED_RESULT = FALSE; before the query to disable caching

B. Manually clear the entire result cache before running the query

C. Run the query multiple times to refresh the cache

D. Use a different warehouse to bypass caching

Solution

1. Step 1: Identify how to disable result caching per session

   Snowflake allows disabling result caching using ALTER SESSION SET USE_CACHED_RESULT = FALSE before the query.

2. Step 2: Evaluate other options for cost and effectiveness

   Manually clearing cache or switching warehouses is inefficient and costly; running multiple times does not guarantee fresh data.

3. Final Answer:

   Execute ALTER SESSION SET USE_CACHED_RESULT = FALSE; before the query to disable caching -> Option A

4. Quick Check:

   Use USE_CACHED_RESULT = FALSE for fresh data [OK]

Hint: Use ALTER SESSION SET USE_CACHED_RESULT = FALSE for fresh data without extra cost [OK]

Common Mistakes:

• Trying to clear cache manually
• Switching warehouses unnecessarily
• Relying on repeated runs to refresh data