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.