Why optimization controls Snowflake costs - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select data from a table using the most cost-effective warehouse.

Snowflake

USE WAREHOUSE [1]; SELECT * FROM sales_data WHERE region = 'US';

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

ALARGE_WH

BSMALL_WH

CMEDIUM_WH

DXLARGE_WH

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a larger warehouse unnecessarily increases costs.

Using a smaller warehouse like SMALL_WH reduces compute costs while still processing the query efficiently.

2fill in blank

medium

Complete the code to create a clustered table to optimize query performance and cost.

Snowflake

CREATE TABLE orders_clustered CLUSTER BY ([1]) AS SELECT * FROM orders;

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

Acustomer_id

Bproduct_id

Corder_id

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Clustering by a unique ID like order_id does not improve filtering.

Clustering by order_date helps queries filter by date efficiently, reducing compute time and cost.

3fill in blank

hard

Fix the error in the query that causes unnecessary compute cost by avoiding a full table scan.

Snowflake

SELECT * FROM customers WHERE [1] = 'NY';

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

Acustomer_id

Bcustomer_name

Cstate_code

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Filtering by customer_name or email causes full scans and higher costs.

Filtering by state_code uses the clustered column to reduce scanned data and cost.

4fill in blank

hard

Fill both blanks to create a materialized view that reduces compute costs for frequent queries.

Snowflake

CREATE MATERIALIZED VIEW recent_orders AS SELECT * FROM orders WHERE order_date [1] CURRENT_DATE - INTERVAL '[2]' DAY;

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

A>

B<

C7

D30

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' selects older data, not recent, increasing compute costs.

Using order_date > CURRENT_DATE - INTERVAL '30' DAY creates a view of recent orders, speeding up queries and saving costs.

5fill in blank

hard

Fill all three blanks to optimize a query by selecting only necessary columns and filtering efficiently.

Snowflake

SELECT [1], [2] FROM sales WHERE region = '[3]';

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

Aproduct_id

Bsales_amount

CUS

Dcustomer_name

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting customer_name adds unnecessary data increasing cost.

Selecting only product_id and sales_amount for region US reduces data scanned and lowers costs.