Result caching layers in Snowflake - Mini Project: Build & Apply

Result caching layers

📖 Scenario: You work as a data engineer managing a Snowflake data warehouse. Your team wants to improve query performance by using result caching layers effectively. You will create a simple table, configure caching settings, and write queries to observe caching behavior.

🎯 Goal: Build a Snowflake setup that demonstrates how result caching layers work by creating a table, setting a caching configuration variable, running a query that benefits from caching, and finally enabling result caching explicitly.

📋 What You'll Learn

Create a table named sales_data with specified columns and data

Create a variable cache_enabled to control caching behavior

Write a query selecting from sales_data using the caching variable

Enable result caching explicitly in the query using a query hint

💡 Why This Matters

🌍 Real World

Result caching in Snowflake helps speed up repeated queries by storing previous results, reducing compute costs and improving user experience.

💼 Career

Understanding and configuring result caching is important for data engineers and analysts to optimize data warehouse performance and cost.

1

Create the sales_data table

Write a Snowflake SQL statement to create a table called sales_data with columns id (integer), product (string), and amount (integer). Insert three rows with values: (1, 'Pen', 10), (2, 'Notebook', 20), and (3, 'Eraser', 5).

Snowflake

-- Create the sales_data table and insert data
-- Your code here

Need a hint?

Use CREATE OR REPLACE TABLE to define the table and INSERT INTO to add rows.

2

Create a caching control variable

Create a Snowflake session variable named cache_enabled and set it to true to indicate that result caching should be used.

Snowflake

CREATE OR REPLACE TABLE sales_data (id INT, product STRING, amount INT);
INSERT INTO sales_data VALUES (1, 'Pen', 10), (2, 'Notebook', 20), (3, 'Eraser', 5);
-- Create the cache_enabled variable
-- Your code here

Need a hint?

Use the SET command to create a session variable.

3

Write a query using the caching variable

Write a SELECT query to get all columns from sales_data. Use the session variable cache_enabled in a comment to indicate caching is enabled (simulate caching control).

Snowflake

CREATE OR REPLACE TABLE sales_data (id INT, product STRING, amount INT);
INSERT INTO sales_data VALUES (1, 'Pen', 10), (2, 'Notebook', 20), (3, 'Eraser', 5);
SET cache_enabled = true;
-- Write a SELECT query using the cache_enabled variable in a comment
-- Your code here

Need a hint?

Write a simple SELECT query and add a comment referencing cache_enabled.

4

Enable result caching explicitly in the query

Modify the SELECT query to include the Snowflake query hint /*+ RESULT_CACHE */ to explicitly enable result caching.

Snowflake

CREATE OR REPLACE TABLE sales_data (id INT, product STRING, amount INT);
INSERT INTO sales_data VALUES (1, 'Pen', 10), (2, 'Notebook', 20), (3, 'Eraser', 5);
SET cache_enabled = true;
-- cache_enabled is true
-- Add the RESULT_CACHE hint to the SELECT query
-- Your code here

Need a hint?

Add the query hint /*+ RESULT_CACHE */ immediately after SELECT.