Cost optimization strategies in Snowflake - Commands & Configuration

Introduction

Cloud data warehouses like Snowflake charge based on compute and storage usage. Cost optimization strategies help you reduce your bills by using resources efficiently without losing performance.

When you want to reduce your monthly Snowflake bill without affecting data availability.

When you notice your queries are running longer than expected and costing more.

When you want to pause compute resources during idle times to save money.

When you want to automatically scale resources up or down based on workload.

When you want to monitor and control your Snowflake usage to avoid surprises.

Commands

This command changes the size of the warehouse to XSMALL, which uses fewer compute resources and costs less.

Terminal

ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'XSMALL';

Expected Output

Statement executed successfully.

This sets the warehouse to automatically suspend after 5 minutes (300 seconds) of inactivity, saving costs when not in use.

Terminal

ALTER WAREHOUSE my_warehouse SET AUTO_SUSPEND = 300;

Expected Output

Statement executed successfully.

This enables the warehouse to automatically resume when a query is submitted, so you don't have to manually start it.

Terminal

ALTER WAREHOUSE my_warehouse SET AUTO_RESUME = TRUE;

Expected Output

Statement executed successfully.

This command checks the current configuration of the warehouse to verify your cost optimization settings.

Terminal

SELECT WAREHOUSE_NAME, WAREHOUSE_SIZE, STATE, AUTO_SUSPEND, AUTO_RESUME FROM INFORMATION_SCHEMA.WAREHOUSES WHERE WAREHOUSE_NAME = 'MY_WAREHOUSE';

Expected Output

WAREHOUSE_NAME | WAREHOUSE_SIZE | STATE     | AUTO_SUSPEND | AUTO_RESUME

MY_WAREHOUSE  | XSMALL         | SUSPENDED | 300          | TRUE

Key Concept

If you remember nothing else from this pattern, remember: automatically suspending and resuming warehouses and choosing the right size saves money without losing performance.

Common Mistakes

Not setting AUTO_SUSPEND, so warehouses keep running and costing money even when idle.

Warehouses consume compute credits while running, even if no queries are executed.

Always set AUTO_SUSPEND to a low value like 300 seconds to pause warehouses when idle.

Choosing a warehouse size that is too large for the workload.

Larger warehouses cost more credits per hour, increasing your bill unnecessarily.

Start with a smaller warehouse size and scale up only if performance is insufficient.

Disabling AUTO_RESUME and manually starting warehouses.

This causes delays and may lead to warehouses running longer than needed.

Enable AUTO_RESUME so warehouses start automatically when queries run.

Summary

Use ALTER WAREHOUSE commands to set warehouse size, auto suspend, and auto resume.

Auto suspend pauses warehouses after inactivity to save compute costs.

Auto resume starts warehouses automatically when queries run, avoiding manual steps.

Practice

1. Which Snowflake feature helps reduce costs by automatically stopping a warehouse when it's not in use?

easy

A. Auto-suspend

B. Multi-cluster warehouse

C. Data sharing

D. Continuous data protection

Solution

1. Step 1: Understand warehouse billing

   Snowflake charges for compute resources while warehouses run, even if idle.

2. Step 2: Identify cost-saving feature

   Auto-suspend stops the warehouse after inactivity, preventing unnecessary charges.

3. Final Answer:

   Auto-suspend -> Option A

4. Quick Check:

   Auto-suspend = stops warehouse when idle [OK]

Hint: Auto-suspend pauses compute to save money when idle [OK]

Common Mistakes:

• Confusing auto-suspend with auto-resume
• Thinking multi-cluster reduces cost automatically
• Assuming data sharing affects compute cost

2. Which of the following is the correct syntax to set a warehouse to auto-suspend after 5 minutes in Snowflake?

easy

A. ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = '5 minutes';

B. ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 5;

C. ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 300;

D. ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = TRUE;

Solution

1. Step 1: Understand AUTO_SUSPEND unit

   Snowflake expects AUTO_SUSPEND value in seconds, so 5 minutes = 300 seconds.

2. Step 2: Check syntax correctness

   ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 300; uses correct syntax and value (300 seconds). Others use wrong units or types.

3. Final Answer:

   ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 300; -> Option C

4. Quick Check:

   Auto-suspend time in seconds = 300 [OK]

Hint: Auto-suspend time is in seconds, not minutes [OK]

Common Mistakes:

• Using minutes instead of seconds
• Setting AUTO_SUSPEND to TRUE/FALSE
• Using string values instead of integers

3. Consider this Snowflake SQL snippet:

ALTER WAREHOUSE wh1 SET AUTO_SUSPEND = 600;
ALTER WAREHOUSE wh1 SET AUTO_RESUME = TRUE;

What is the effect on cost when no queries run for 15 minutes?

medium

A. Warehouse suspends after 10 minutes, no charges after that

B. Warehouse keeps running, charges continue for 15 minutes

C. Warehouse suspends immediately, no charges after query ends

D. Warehouse suspends after 15 minutes, charges continue until then

Solution

1. Step 1: Analyze AUTO_SUSPEND setting

   Warehouse suspends after 600 seconds (10 minutes) of inactivity.

2. Step 2: Understand AUTO_RESUME behavior

   Warehouse auto-resumes on new queries, but here no queries run for 15 minutes.

3. Step 3: Calculate cost impact

   Charges stop after suspension at 10 minutes; no charges for last 5 minutes idle.

4. Final Answer:

   Warehouse suspends after 10 minutes, no charges after that -> Option A

5. Quick Check:

   Auto-suspend 600s stops charges after 10 min idle [OK]

Hint: Auto-suspend time controls idle billing stop [OK]

Common Mistakes:

• Assuming warehouse suspends immediately after last query
• Thinking charges continue for full 15 minutes
• Confusing auto-resume with auto-suspend timing

4. You set a warehouse with AUTO_SUSPEND = 0 and AUTO_RESUME = TRUE. What issue might this cause?

medium

A. Warehouse auto-resume is disabled, queries fail

B. Warehouse never suspends, causing continuous charges

C. Warehouse suspends after default 10 minutes ignoring 0

D. Warehouse suspends immediately, causing query failures

Solution

1. Step 1: Understand AUTO_SUSPEND = 0 meaning

   Setting AUTO_SUSPEND to 0 disables suspension; warehouse runs continuously.

2. Step 2: Check AUTO_RESUME effect

   AUTO_RESUME TRUE allows auto start but does not suspend warehouse automatically.

3. Step 3: Identify cost impact

   Warehouse never suspends, so charges accrue continuously even if idle.

4. Final Answer:

   Warehouse never suspends, causing continuous charges -> Option B

5. Quick Check:

   AUTO_SUSPEND=0 means no suspend, costs continue [OK]

Hint: AUTO_SUSPEND=0 disables suspend, causing constant charges [OK]

Common Mistakes:

• Thinking 0 means immediate suspend
• Assuming AUTO_RESUME disables charges
• Believing default suspend time applies

5. A company wants to minimize Snowflake costs by scheduling warehouse usage only during business hours (9am-5pm). Which combination of strategies best achieves this?

hard

A. Set warehouse to always on and rely on auto-resume to start queries

B. Use multi-cluster warehouse to handle peak loads during business hours

C. Disable auto-suspend and manually suspend warehouse after hours

D. Use auto-suspend with 1-minute timeout and schedule warehouse start/stop tasks at 9am and 5pm

Solution

1. Step 1: Understand scheduling needs

   Warehouse should run only during business hours to save costs outside those times.

2. Step 2: Evaluate options

   Use auto-suspend with 1-minute timeout and schedule warehouse start/stop tasks at 9am and 5pm combines auto-suspend (quick suspend) and scheduled start/stop tasks to control usage precisely.

3. Step 3: Rule out other options

   Set warehouse to always on and rely on auto-resume to start queries wastes cost by always on; C is manual and error-prone; D increases cost for peak handling.

4. Final Answer:

   Use auto-suspend with 1-minute timeout and schedule warehouse start/stop tasks at 9am and 5pm -> Option D

5. Quick Check:

   Scheduled start/stop + auto-suspend = cost control [OK]

Hint: Schedule start/stop plus auto-suspend for tight cost control [OK]

Common Mistakes:

• Leaving warehouse always on wastes money
• Relying on manual suspend risks forgetting
• Using multi-cluster increases cost, not reduces