Cost optimization strategies in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to set the warehouse size to a smaller option for cost savings.

Snowflake

ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = '[1]';

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

A2X-Large

BLarge

CXSMALL

DMedium

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a larger warehouse size increases costs unnecessarily.

Using invalid warehouse size names causes errors.

Setting the warehouse size to 'XSMALL' reduces compute costs by using fewer resources.

2fill in blank

medium

Complete the code to enable auto-suspend after 5 minutes of inactivity.

Snowflake

ALTER WAREHOUSE my_warehouse SET AUTO_SUSPEND = [1];

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

A0

B600

C60

D300

Attempts:

3 left

💡 Hint

Common Mistakes

Setting AUTO_SUSPEND to 0 disables auto-suspend, increasing costs.

Using too long a suspend time delays cost savings.

Setting AUTO_SUSPEND to 300 seconds (5 minutes) helps save costs by suspending idle warehouses.

3fill in blank

hard

Fix the error in the query to limit data scanned for cost optimization.

Snowflake

SELECT * FROM sales_data WHERE transaction_date [1] '2023-01-01';

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

A>=

B==

C!=

D<>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' is invalid in SQL for comparison.

Using '<>' or '!=' filters out the date, not what is needed.

Using '>=' filters data from 2023-01-01 onwards, reducing scanned data and cost.

4fill in blank

hard

Fill both blanks to create a clustering key on the 'customer_id' column for cost-efficient queries.

Snowflake

ALTER TABLE sales_data CLUSTER BY ([1]([2]));

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

AHASH

Bcustomer_id

CDATE

DRANGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using DATE as a function causes syntax errors.

Clustering by wrong columns reduces effectiveness.

Using HASH(customer_id) clusters data by customer ID, improving query performance and reducing costs.

5fill in blank

hard

Fill all three blanks to create a materialized view that refreshes automatically to save compute costs.

Snowflake

CREATE MATERIALIZED VIEW [1] AS SELECT [2] FROM sales_data WHERE region = '[3]';

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

Amv_sales_region

BSUM(amount)

CUS

DCOUNT(*)

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(*) instead of SUM(amount) changes the aggregation meaning.

Incorrect region value causes no data to be stored.

Creating a materialized view named 'mv_sales_region' with SUM(amount) for region 'US' precomputes data, saving compute costs on repeated queries.