Warehouse selection strategies in Snowflake - Step-by-Step Execution

Process Flow - Warehouse selection strategies

Query arrives

↓

Check user preference

↓

Is specific warehouse requested?

No→Use default warehouse

|Yes↓

Is requested warehouse available?

No→Return error or fallback

|Yes↓

Assign query to selected warehouse

↓

Execute query on warehouse

↓

Return results

This flow shows how Snowflake selects a warehouse to run a query, based on user request or default settings.

Execution Sample

Snowflake

USE WAREHOUSE WH_A;
SELECT * FROM SALES;

-- If no warehouse specified:
SELECT * FROM SALES;

This example shows selecting a specific warehouse before running a query, or running a query without specifying warehouse to use default.

Process Table

Step	Action	User Input	Warehouse Selected	Result
1	Query arrives	USE WAREHOUSE WH_A; SELECT * FROM SALES;	WH_A	Query assigned to WH_A
2	Execute query	Query on WH_A	WH_A	Results returned from WH_A
3	Query arrives	SELECT * FROM SALES;	DEFAULT_WH	Query assigned to default warehouse
4	Execute query	Query on DEFAULT_WH	DEFAULT_WH	Results returned from default warehouse
5	Query arrives	USE WAREHOUSE WH_X; SELECT * FROM SALES;	WH_X not found	Error or fallback triggered

💡 Execution stops after query runs or error if warehouse unavailable

Status Tracker

Variable	Start	After Step 1	After Step 3	After Step 5
Selected Warehouse	None	WH_A	DEFAULT_WH	Error (WH_X not found)

Key Moments - 3 Insights

What happens if the user does not specify a warehouse?

The system uses the default warehouse as shown in execution_table row 3 and 4.

What if the requested warehouse does not exist?

An error occurs or fallback happens, as shown in execution_table row 5.

Can a user switch warehouses between queries?

Yes, by issuing USE WAREHOUSE commands before queries, as shown in rows 1 and 3.

Visual Quiz - 3 Questions

Look at the execution_table, which warehouse is used when no warehouse is specified?

A DEFAULT_WH

B WH_A

C WH_X

D No warehouse

💡 Hint

Refer to execution_table rows 3 and 4 where no warehouse is specified.

At which step does an error occur due to warehouse unavailability?

A Step 2

B Step 3

C Step 5

D Step 1

💡 Hint

Check execution_table row 5 for error due to WH_X not found.

If the user changes warehouse from WH_A to DEFAULT_WH, what changes in variable_tracker?

A Selected Warehouse stays WH_A

B Selected Warehouse changes to DEFAULT_WH

C Selected Warehouse becomes None

D Selected Warehouse becomes WH_X

💡 Hint

Look at variable_tracker showing warehouse after steps 1 and 3.

Concept Snapshot

Warehouse selection in Snowflake:
- Use 'USE WAREHOUSE <name>' to pick a warehouse.
- If none specified, default warehouse runs queries.
- If warehouse unavailable, error or fallback occurs.
- Warehouse can be changed anytime before queries.

Full Transcript

When a query arrives in Snowflake, the system checks if the user specified a warehouse. If yes, it tries to use that warehouse. If the warehouse exists and is available, the query runs there. If not, an error or fallback happens. If no warehouse is specified, the default warehouse runs the query. Users can switch warehouses anytime by issuing 'USE WAREHOUSE' commands before queries.

Practice

1. What does the USE WAREHOUSE command do in Snowflake?

easy

A. It lists all available warehouses.

B. It switches the current warehouse to run queries.

C. It deletes an existing warehouse.

D. It creates a new warehouse.

Solution

1. Step 1: Understand the purpose of USE WAREHOUSE

   This command changes which warehouse is active for running queries.

2. Step 2: Compare with other commands

   Creating, deleting, or listing warehouses use different commands, not USE WAREHOUSE.

3. Final Answer:

   It switches the current warehouse to run queries. -> Option B

4. Quick Check:

   USE WAREHOUSE changes active warehouse [OK]

Hint: Remember: USE means switch active warehouse [OK]

Common Mistakes:

• Confusing USE with CREATE or DROP commands
• Thinking it lists warehouses
• Assuming it modifies warehouse size

2. Which of the following is the correct syntax to switch to a warehouse named COMPUTE_WH?

easy

A. USE WAREHOUSE COMPUTE_WH;

B. SWITCH WAREHOUSE COMPUTE_WH;

C. SELECT WAREHOUSE COMPUTE_WH;

D. CHANGE WAREHOUSE TO COMPUTE_WH;

Solution

1. Step 1: Recall the correct command syntax

   The command to switch warehouses is USE WAREHOUSE <name>;.

2. Step 2: Check each option

   Only USE WAREHOUSE COMPUTE_WH; matches the correct syntax exactly.

3. Final Answer:

   USE WAREHOUSE COMPUTE_WH; -> Option A

4. Quick Check:

   Correct syntax = USE WAREHOUSE [name]; [OK]

Hint: USE WAREHOUSE plus name switches warehouse [OK]

Common Mistakes:

• Using SWITCH or CHANGE instead of USE
• Adding extra keywords like TO or SELECT
• Missing semicolon at end

3. Given the following commands run in order:

USE WAREHOUSE SMALL_WH;
SELECT CURRENT_WAREHOUSE();
USE WAREHOUSE LARGE_WH;
SELECT CURRENT_WAREHOUSE();

What will be the output of the two CURRENT_WAREHOUSE() calls?

medium

A. SMALL_WH then LARGE_WH

B. LARGE_WH then SMALL_WH

C. SMALL_WH twice

D. LARGE_WH twice

Solution

1. Step 1: Analyze the first USE WAREHOUSE command

   The first command sets the active warehouse to SMALL_WH, so the first CURRENT_WAREHOUSE() returns SMALL_WH.

2. Step 2: Analyze the second USE WAREHOUSE command

   The second command switches the active warehouse to LARGE_WH, so the second CURRENT_WAREHOUSE() returns LARGE_WH.

3. Final Answer:

   SMALL_WH then LARGE_WH -> Option A

4. Quick Check:

   Warehouse switches reflect in CURRENT_WAREHOUSE() [OK]

Hint: Each USE WAREHOUSE changes current warehouse immediately [OK]

Common Mistakes:

• Assuming warehouse does not change after second USE
• Mixing order of outputs
• Thinking CURRENT_WAREHOUSE() returns session name

4. You run the command USE WAREHOUSE BIG_WH but get an error saying the warehouse does not exist. What is the most likely cause?

medium

A. The command requires a semicolon at the end.

B. You need to restart your Snowflake session.

C. You must create a database before switching warehouses.

D. The warehouse name is misspelled or does not exist.

Solution

1. Step 1: Understand the error message

   The error says the warehouse does not exist, so the name is likely wrong or missing.

2. Step 2: Evaluate other options

   Restarting session or creating a database is unrelated; semicolon missing usually causes syntax error, not 'does not exist'.

3. Final Answer:

   The warehouse name is misspelled or does not exist. -> Option D

4. Quick Check:

   Nonexistent warehouse name causes error [OK]

Hint: Check warehouse name spelling if 'does not exist' error appears [OK]

Common Mistakes:

• Restarting session unnecessarily
• Confusing warehouse with database
• Ignoring error message details

5. You have two warehouses: SMALL_WH with 1 cluster and LARGE_WH with 4 clusters. You want to run a heavy query quickly but also save costs on light queries. Which strategy is best?

hard

A. Always use LARGE_WH for all queries to ensure speed.

B. Use SMALL_WH only and increase its size when needed.

C. Use SMALL_WH for light queries and switch to LARGE_WH for heavy queries.

D. Create a new warehouse for each query type.

Solution

1. Step 1: Understand warehouse sizes and costs

   SMALL_WH is cheaper and good for light work; LARGE_WH is bigger and faster but costs more.

2. Step 2: Choose strategy balancing speed and cost

   Switching warehouses based on query load saves cost and improves performance.

3. Final Answer:

   Use SMALL_WH for light queries and switch to LARGE_WH for heavy queries. -> Option C

4. Quick Check:

   Match warehouse size to query load for cost and speed [OK]

Hint: Match warehouse size to query weight, switch as needed [OK]

Common Mistakes:

• Using large warehouse always wastes cost
• Trying to resize warehouses dynamically
• Creating many warehouses unnecessarily