Snowflake SQL Tutorial 155 - CREATE WAREHOUSE Moved My Session And Broke My Own Demo

Snowflake SQL · Episode 155

In this post we are going to cover CREATE WAREHOUSE in Snowflake.

A warehouse is the compute. It is the thing that actually runs a query and reads rows, and creating one is a single statement with a handful of options.

It also does something the statement never mentions. It moves your session onto the new warehouse, every time, and that side effect broke this episode's own capture three queries running.

We will start with the smallest create there is and build it up step by step. Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 155 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's see what we are working with
2. Let's create one with almost nothing in it
3. Now let's write every option out
4. Now the column I had been looking straight past
5. So what happens if we drop the one we are standing on?
6. And then it broke my own capture, three queries running
7. So let's ask that properly, with the session kept safe
8. Now the options themselves, starting with size
9. And the numbers: zero, thirty, and minus sixty
10. What happens when the name is already taken
11. Can we have a name with spaces in it?
12. One thing that is not where you would look for it
13. One last thing: let's put the account back

Let's see what we are working with

There is no sample table in this post. Level 13 is about the account itself, so what we need to see first is the warehouses that came with the account.

Where we are running

-- There is no new table in this post.
-- Everything here runs on the ACCOUNT rather than on data.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;
USE WAREHOUSE COMPUTE_WH;

-- One table from an earlier post is read later on, purely as something to
-- count. Nothing in this post writes to it.
--   DEMO_DB.PUBLIC.SALES_RAW

Query

SHOW WAREHOUSES;

Result3 rows · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
COMPUTE_WH	STARTED	STANDARD	X-Small	1	1	1	0	0	Y	Y	300	true	100	0	0	0	2026-09-21 18:36:58.764000-07:00	2026-10-06 11:05:18.848000-07:00	2026-10-06 11:05:18.848000-07:00	ACCOUNTADMIN		true	8	null	1	0	0	0	283799204	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
SNOWFLAKE_LEARNING_WH	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	N	300	true					2026-09-21 18:33:47.899000-07:00	2026-09-21 18:33:47.899000-07:00	2026-09-21 18:33:47.913000-07:00	ACCOUNTADMIN	Created by Snowflake during account provisioning	true	8	null	0	0	0	1	283799128	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
SYSTEM$STREAMLIT_NOTEBOOK_WH	SUSPENDED	STANDARD	X-Small	1	10	0	0	0	N	N	60	true					2026-09-21 18:33:46.101000-07:00	2026-09-21 18:33:46.115000-07:00	2026-09-21 18:33:46.136000-07:00	ACCOUNTADMIN		true	8	null	0	0	0	1	283799044	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Three warehouses, and 38 columns about them. COMPUTE_WH is started and the two Snowflake put there itself are suspended. That is the whole list, so everything else you see in this post is ours.

And this is where the session is pointing before we touch anything.

Query

SELECT CURRENT_DATABASE()   AS THE_DATABASE,
       CURRENT_SCHEMA()     AS THE_SCHEMA,
       CURRENT_ROLE()       AS THE_ROLE,
       CURRENT_WAREHOUSE()  AS THE_WAREHOUSE,
       CURRENT_VERSION()    AS THE_VERSION;

Result1 row · 5 columns

THE_DATABASE	THE_SCHEMA	THE_ROLE	THE_WAREHOUSE	THE_VERSION
DEMO_DB	PUBLIC	ACCOUNTADMIN	COMPUTE_WH	10.36.101

Reading the grids

That grid is wide, and a few of its cells are blank rather than saying anything. A blank cell marked empty holds an empty string, which is what SHOW uses for “nothing here”. A cell that reads NULL is a real SQL NULL. They are different values and the grids on this page keep them apart.

Let's create one with almost nothing in it

Here is close to the smallest CREATE WAREHOUSE there is. A name, and one option.

Create

CREATE OR REPLACE WAREHOUSE WHDEFAULTS_T155
    INITIALLY_SUSPENDED = TRUE;

Result1 row · 1 column

status
Warehouse WHDEFAULTS_T155 successfully created.

Read the statement rather than the block name. It carries INITIALLY_SUSPENDED = TRUE, so the suspended state below was asked for. Everything else in the grid was decided for us.

Query

SHOW WAREHOUSES LIKE 'WHDEFAULTS\\_T155';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WHDEFAULTS_T155	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	Y	600	true					2026-10-06 11:10:01.252000-07:00	2026-10-06 11:10:01.301000-07:00	2026-10-06 11:10:01.331000-07:00	ACCOUNTADMIN		true	8	null	0	0	0	1	283799284	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

So here is what Snowflake chose on its own: size X-Small, auto_suspend of 600 seconds, auto_resume true, one cluster minimum and one maximum, and a STANDARD scaling policy.

Ten minutes of idle before it switches itself off, and it will switch itself back on when something needs it. Those are the two numbers worth remembering out of that grid.

One claim this grid cannot support

The state in that row is SUSPENDED, and that is not a default. The statement asked for it. So this grid does not tell you what state a warehouse starts in when you say nothing at all, and the next section asks that question properly.

Now let's write every option out

This one spells out everything, so you can see the shape of the statement. Size, type, auto suspend, auto resume, initially suspended, both cluster counts, scaling policy and a comment.

Create

CREATE OR REPLACE WAREHOUSE WH_T155
    WAREHOUSE_SIZE      = XSMALL
    WAREHOUSE_TYPE      = STANDARD
    AUTO_SUSPEND        = 60
    AUTO_RESUME         = TRUE
    INITIALLY_SUSPENDED = TRUE
    MIN_CLUSTER_COUNT   = 1
    MAX_CLUSTER_COUNT   = 1
    SCALING_POLICY      = STANDARD
    COMMENT             = 'episode 155 - never used for work';

Result1 row · 1 column

status
Warehouse WH_T155 successfully created.

And we can read the SHOW above back as ordinary rows, which is a pattern worth having. RESULT_SCAN over LAST_QUERY_ID() turns the last result into a table we can select from.

Query

SELECT "name", "state", "size", "auto_suspend", "auto_resume"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

name	state	size	auto_suspend	auto_resume
WH_T155	SUSPENDED	X-Small	60	true

Suspended, as asked. Now the question the section before could not answer. Here is one with no INITIALLY_SUSPENDED clause at all.

Create

CREATE OR REPLACE WAREHOUSE WHBIG_T155
    WAREHOUSE_SIZE = XSMALL
    AUTO_SUSPEND   = 60
    COMMENT        = 'episode 155 - no INITIALLY_SUSPENDED clause';

Result1 row · 1 column

status
Warehouse WHBIG_T155 successfully created.

Query

SELECT "name", "state", "size", "auto_suspend", "auto_resume"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

name	state	size	auto_suspend	auto_resume
WHBIG_T155	RESUMING	X-Small	60	true

RESUMING. So left to itself a new warehouse starts coming up, and if you do not want that you have to say INITIALLY_SUSPENDED = TRUE yourself.

Here are all three of ours side by side.

Query

SELECT "name", "state", "size", "auto_suspend", "auto_resume", "comment"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result3 rows · 6 columns · scroll →

name	state	size	auto_suspend	auto_resume	comment
WHBIG_T155	STARTED	X-Small	60	true	episode 155 - no INITIALLY_SUSPENDED clause
WHDEFAULTS_T155	SUSPENDED	X-Small	600	true
WH_T155	STARTED	X-Small	60	true	episode 155 - never used for work

Two started and one suspended, with the auto suspend each one was given. The empty comment on the middle row is an empty string, because that warehouse was created without a comment.

And DESCRIBE WAREHOUSE is a real statement. Watch what it gives you.

Query

DESCRIBE WAREHOUSE WH_T155;

Result1 row · 3 columns

created_on	name	kind
2026-10-06 11:10:02.347000-07:00	WH_T155	WAREHOUSE

Created on, name, kind. Three columns, and that is the entire answer. So SHOW WAREHOUSES is the one that knows things, and DESCRIBE is almost a formality here.

Now the column I had been looking straight past

Every grid above has a column called is_current, and on the warehouse that had just been created it reads Y. Nothing in any of those statements said to use it.

So let's ask it on purpose, in a second capture, with nothing else going on. First, where the session is.

Query

SELECT CURRENT_WAREHOUSE() AS THE_WAREHOUSE,
       CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_VERSION()   AS THE_VERSION;

Result1 row · 3 columns

THE_WAREHOUSE	THE_ROLE	THE_VERSION
COMPUTE_WH	ACCOUNTADMIN	10.36.101

COMPUTE_WH. Now create one warehouse and do absolutely nothing else. No USE WAREHOUSE anywhere.

Create

CREATE OR REPLACE WAREHOUSE WHTRAP_T155B
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = 60
    INITIALLY_SUSPENDED = TRUE
    MAX_CLUSTER_COUNT   = 1
    COMMENT             = 'episode 155b - created, and never USEd';

Result1 row · 1 column

status
Warehouse WHTRAP_T155B successfully created.

Query

SELECT CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

Result1 row · 1 column

THE_WAREHOUSE
WHTRAP_T155B

The warehouse we just made. We never said to use it.

And the catalogue agrees.

Query

SELECT "name", "state", "is_current", "is_default", "auto_resume"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

name	state	is_current	is_default	auto_resume
WHTRAP_T155B	SUSPENDED	Y	N	true

Query

SELECT "name", "state", "is_current", "is_default"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 4 columns

name	state	is_current	is_default
COMPUTE_WH	STARTED	N	Y

is_current is Y on the new one and N on COMPUTE_WH. Have a look at is_default too, because it did not move: COMPUTE_WH is still the default warehouse. Current and default are two different things.

The side effect

CREATE WAREHOUSE points your session at the new warehouse. Nothing in the statement says so, nothing warns you, and the only place it shows is the is_current column and CURRENT_WAREHOUSE().

So what happens if we drop the one we are standing on?

That is the obvious next question, and the answer is worth knowing before it happens to you.

Drop

DROP WAREHOUSE WHTRAP_T155B;

Result1 row · 1 column

status
WHTRAP_T155B successfully dropped.

Query

SELECT CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

Result1 row · 1 column

THE_WAREHOUSE
NULL

NULL. The session is not put back where it started. It is left pointing at nothing at all.

I tried to prove that mattered with a count, which is the test I would reach for first.

Query

SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.PUBLIC.SALES_RAW;

Result1 row · 1 column

HOW_MANY
8

It answered. With no warehouse at all. So that probe proves nothing, and we will come back to why in two sections' time.

Repair

USE WAREHOUSE COMPUTE_WH;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

Result1 row · 1 column

THE_WAREHOUSE
COMPUTE_WH

USE WAREHOUSE is the way back, and it is the statement to reach for the moment CURRENT_WAREHOUSE() surprises you.

And then it broke my own capture, three queries running

This is the part I would quite like to skip, so here it is first. Back in the main capture I created a warehouse with AUTO_RESUME = FALSE and INITIALLY_SUSPENDED = TRUE.

Create

CREATE OR REPLACE WAREHOUSE WHNORESUME_T155
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = 60
    AUTO_RESUME         = FALSE
    INITIALLY_SUSPENDED = TRUE;

Result1 row · 1 column

status
Warehouse WHNORESUME_T155 successfully created.

Which, given what you have just read, stood my session on a warehouse that cannot wake up. Nothing complained yet.

The next thing the file did was ask a warehouse for its parameters.

Query

SHOW PARAMETERS IN WAREHOUSE WH_T155;

Result5 rows · 6 columns · scroll →

key	value	default	level	description	type
ENABLE_USE_STABLE_PATH	true	false	SYSTEM	Add support for stable path	BOOLEAN
FALLBACK_WAREHOUSE				If not null, the warehouse to use as a fallback for statements that timed out	STRING
MAX_CONCURRENCY_LEVEL	8	8		Maximum number of SQL statements a warehouse cluster can execute concurrently before queuing them. Small SQL statements count as a fraction of 1.	NUMBER
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS	0	0		Timeout in seconds for queued statements: statements will automatically be canceled if they are queued on a warehouse for longer than this amount of time; disabled if set to zero.	NUMBER
STATEMENT_TIMEOUT_IN_SECONDS	172800	172800		Timeout in seconds for statements: statements are automatically canceled if they run for longer; if set to zero, max value (604800) is enforced.	NUMBER

Five rows, and that worked perfectly well. Now the very next statement reads those same rows back as rows.

Query

SELECT "key", "value", "default", "level"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "key";

Query failed — Snowflake returned an error

000608 (57P03): Warehouse 'WHNORESUME_T155' is suspended.  Use 'alter warehouse WHNORESUME_T155 resume;' command if you need to use it again.

000608, and read the name in it. It is the warehouse I never meant to be on. The message even tells me the fix: alter warehouse ... resume.

That is not a staged failure. That is this episode's capture dying on a warehouse its own CREATE moved it onto. And it happened again twice more before the file moved on to something else.

Query

SELECT "key", "value", "default", "level"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "key";

Query failed — Snowflake returned an error

000608 (57P03): Warehouse 'WHNORESUME_T155' is suspended.  Use 'alter warehouse WHNORESUME_T155 resume;' command if you need to use it again.

Query

SELECT "name", "state", "size", "auto_suspend", "auto_resume", "type"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Query failed — Snowflake returned an error

000608 (57P03): Warehouse 'WHNORESUME_T155' is suspended.  Use 'alter warehouse WHNORESUME_T155 resume;' command if you need to use it again.

Three queries, the same code, the same warehouse name. But look at what that accident separates, because it is the clearest thing in this post.

• SHOW PARAMETERS IN WAREHOUSE answered. Five rows, with no usable warehouse at all.
• The RESULT_SCAN that reads those same rows did not. 000608.

A SHOW is metadata and needs no compute. Reading actual rows does. Which is also why that count earlier proved nothing: a COUNT is answered from metadata too.

Which of the two tests was worth anything

The accident is the real evidence in this episode, and the test I wrote on purpose was the useless one. If you want to know whether a session can compute, run something that has to read rows.

So let's ask that properly, with the session kept safe

Here is the same warehouse in the second capture, where the session is put back on COMPUTE_WH straight after the create.

Create

CREATE OR REPLACE WAREHOUSE WHNORESUME_T155B
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = 60
    AUTO_RESUME         = FALSE
    INITIALLY_SUSPENDED = TRUE
    MAX_CLUSTER_COUNT   = 1
    COMMENT             = 'episode 155b - auto resume off';

Result1 row · 1 column

status
Warehouse WHNORESUME_T155B successfully created.

Repair

USE WAREHOUSE COMPUTE_WH;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

Result1 row · 1 column

THE_WAREHOUSE
COMPUTE_WH

Good. Now we can look at the thing itself without standing on it.

Query

SELECT "name", "state", "auto_suspend", "auto_resume", "is_current"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

name	state	auto_suspend	auto_resume	is_current
WHNORESUME_T155B	SUSPENDED	60	false	N

Suspended, auto_resume false, and is_current N, because we moved off it. Now let's point at it deliberately and ask it for something.

Use

USE WAREHOUSE WHNORESUME_T155B;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT COUNT(*) AS HOW_MANY FROM DEMO_DB.PUBLIC.SALES_RAW;

Result1 row · 1 column

HOW_MANY
8

And there is the same count answering again, from a warehouse that is asleep and cannot wake itself. Twice now, and the same number both times. That is the proof that this particular probe never touched compute at all.

The only way that warehouse ever starts is by hand.

Alter

ALTER WAREHOUSE WHNORESUME_T155B RESUME;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT "name", "state", "auto_resume"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 3 columns

name	state	auto_resume
WHNORESUME_T155B	RESUMING	false

RESUMING, with auto_resume still false. It came up because we said so, and it will not do that again on its own.

Alter

ALTER WAREHOUSE WHNORESUME_T155B SUSPEND;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT "name", "state"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 2 columns

name	state
WHNORESUME_T155B	SUSPENDING

SUSPENDING rather than SUSPENDED. Those states are moments, not settings, and a SHOW run a second later can read either one.

While we are here, this is the grid that shows where a warehouse parameter's value came from.

Query

SELECT "key", "value", "default", "level"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "key";

Result5 rows · 4 columns

key	value	default	level
ENABLE_USE_STABLE_PATH	true	false	SYSTEM
FALLBACK_WAREHOUSE
MAX_CONCURRENCY_LEVEL	8	8
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS	0	0
STATEMENT_TIMEOUT_IN_SECONDS	172800	172800

Five parameters. One has a level of SYSTEM, and the rest have an empty level, which means nobody set them and we are looking at the defaults. We will use that column properly in the next post.

Now the options themselves, starting with size

There are three ways to write the same size, and it is worth knowing they all work. The first one, XSMALL with no hyphen and no quotes, is in the statements above. Here is the long form, quoted and hyphenated.

Create

CREATE OR REPLACE WAREHOUSE WHSIZE_T155
    WAREHOUSE_SIZE      = 'X-SMALL'
    AUTO_SUSPEND        = 60
    INITIALLY_SUSPENDED = TRUE;

Result1 row · 1 column

status
Warehouse WHSIZE_T155 successfully created.

Accepted. Now here is a reader of mine that failed, and it is worth a look because this mistake is easy to make.

Query

SELECT "name", "size" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier '"name"'

000904, invalid identifier "name". RESULT_SCAN reads the statement before it, and the statement before it was a CREATE, whose only column is status. There was no "name" to read.

So let's ask it the way that works.

Query

SHOW WAREHOUSES LIKE 'WHSIZE\\_T155';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WHSIZE_T155	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	Y	60	true					2026-10-06 11:10:07.251000-07:00	2026-10-06 11:10:07.271000-07:00	2026-10-06 11:10:07.294000-07:00	ACCOUNTADMIN		true	8	null	0	0	0	1	72652554725	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

And the third spelling, in lower case.

Create

CREATE OR REPLACE WAREHOUSE WHSIZE_T155
    WAREHOUSE_SIZE      = xsmall
    AUTO_SUSPEND        = 60
    INITIALLY_SUSPENDED = TRUE;

Result1 row · 1 column

status
Warehouse WHSIZE_T155 successfully created.

Query

SHOW WAREHOUSES LIKE 'WHSIZE\\_T155';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WHSIZE_T155	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	Y	60	true					2026-10-06 11:10:08.090000-07:00	2026-10-06 11:10:08.122000-07:00	2026-10-06 11:10:08.157000-07:00	ACCOUNTADMIN		true	8	null	0	0	0	1	72652554805	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

All three spellings were accepted, and all three are stored as X-Small, with a capital X and a hyphen. So Snowflake normalises it, and the string you get back is not always the string you wrote.

Now a size that is not a size at all.

Create

CREATE OR REPLACE WAREHOUSE WHBADSIZE_T155
    WAREHOUSE_SIZE      = ENORMOUS
    INITIALLY_SUSPENDED = TRUE;

Query failed — Snowflake returned an error

001421 (22023): SQL compilation error:
invalid type of property 'ENORMOUS' for 'WAREHOUSE_SIZE'

Query

SHOW WAREHOUSES LIKE 'WHBADSIZE\\_T155';

Result0 rows · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables

001421, invalid type of property, and nothing was created. That empty grid is the half people forget to check.

And the numbers: zero, thirty, and minus sixty

AUTO_SUSPEND is in seconds, and the first value I tried is more interesting than I expected. Zero.

Create

CREATE OR REPLACE WAREHOUSE WHZERO_T155
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = 0
    INITIALLY_SUSPENDED = TRUE;

Result1 row · 1 column

status
Warehouse WHZERO_T155 successfully created.

Query

SELECT "name", "auto_suspend", "auto_resume"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier '"name"'

That is the same RESULT_SCAN mistake as before, reading a CREATE. Here it is asked properly.

Query

SHOW WAREHOUSES LIKE 'WHZERO\\_T155';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WHZERO_T155	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	Y	0	true					2026-10-06 11:10:09.050000-07:00	2026-10-06 11:10:09.050000-07:00	2026-10-06 11:10:09.077000-07:00	ACCOUNTADMIN		true	8	null	0	0	0	1	72652554809	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Stored as zero. Not refused, and not quietly rewritten into something sensible.

Now thirty seconds, which is below the minute most guidance suggests.

Create

CREATE OR REPLACE WAREHOUSE WHSHORT_T155
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = 30
    INITIALLY_SUSPENDED = TRUE;

Result1 row · 1 column

status
Warehouse WHSHORT_T155 successfully created.

Query

SHOW WAREHOUSES LIKE 'WHSHORT\\_T155';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WHSHORT_T155	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	Y	30	true					2026-10-06 11:10:10.081000-07:00	2026-10-06 11:10:10.081000-07:00	2026-10-06 11:10:10.107000-07:00	ACCOUNTADMIN		true	8	null	0	0	0	1	283799288	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Also stored, exactly as written.

And then minus sixty, which I had marked in my file as a statement I expected to fail.

Create

CREATE OR REPLACE WAREHOUSE WHNEG_T155
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = -60
    INITIALLY_SUSPENDED = TRUE;

Result1 row · 1 column

status
Warehouse WHNEG_T155 successfully created.

It did not fail. And I have to stop there, because I never asked what that stored value became. So I am not going to tell you. It was accepted, the drop at the end of the file found it, and that is all this capture knows.

What happens when the name is already taken

Three statements, three different behaviours. First CREATE WAREHOUSE IF NOT EXISTS, on a name that exists, with a size written into it.

Create

CREATE WAREHOUSE IF NOT EXISTS WH_T155
    WAREHOUSE_SIZE = XSMALL;

Result1 row · 1 column

status
WH_T155 already exists, statement succeeded.

Statement succeeded. Now look at whether anything in that statement actually happened.

Query

SHOW WAREHOUSES LIKE 'WH\\_T155';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WH_T155	STARTED	STANDARD	X-Small	1	1	1	0	0	N	Y	60	true	100	0	0	0	2026-10-06 11:10:02.347000-07:00	2026-10-06 11:10:04.394000-07:00	2026-10-06 11:10:04.395000-07:00	ACCOUNTADMIN	episode 155 - never used for work	true	8	null	1	0	0	0	72652554565	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Query

SELECT "name", "size", "comment" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 3 columns

name	size	comment
WH_T155	X-Small	episode 155 - never used for work

The comment is still the first one's. So IF NOT EXISTS did not update anything. The whole options list in the second statement was ignored, silently.

Now the plain form, with no IF NOT EXISTS and no OR REPLACE.

Create

CREATE WAREHOUSE WH_T155
    WAREHOUSE_SIZE = XSMALL;

Query failed — Snowflake returned an error

002002 (42710): SQL compilation error:
Object 'WH_T155' already exists.

002002, the object already exists. Which is the refusal you want when you did not mean to touch an existing warehouse.

And CREATE OR REPLACE, which is a different thing again.

Create

CREATE OR REPLACE WAREHOUSE WHSHORT_T155
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = 120
    INITIALLY_SUSPENDED = TRUE
    COMMENT             = 'replaced in episode 155';

Result1 row · 1 column

status
Warehouse WHSHORT_T155 successfully created.

Query

SELECT "name", "auto_suspend", "comment"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier '"name"'

Another RESULT_SCAN reading a CREATE. Here it is asked properly.

Query

SHOW WAREHOUSES LIKE 'WHSHORT\\_T155';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WHSHORT_T155	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	Y	120	true					2026-10-06 11:10:15.754000-07:00	2026-10-06 11:10:15.754000-07:00	2026-10-06 11:10:15.818000-07:00	ACCOUNTADMIN	replaced in episode 155	true	8	null	0	0	0	1	72652554733	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

A new auto suspend and a new comment, because REPLACE does not keep anything. It is a new object with the old name.

Statement	On a name that already exists
CREATE WAREHOUSE	002002, already exists
CREATE WAREHOUSE IF NOT EXISTS	Succeeds, keeps the existing one, ignores every option you wrote
CREATE OR REPLACE WAREHOUSE	Succeeds, new object, nothing carried over

Can we have a name with spaces in it?

We can, but only one way. Here it is unquoted first.

Create

CREATE OR REPLACE WAREHOUSE MY WAREHOUSE T155
    WAREHOUSE_SIZE = XSMALL;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 41 unexpected 'T155'.

001003, a syntax error at the word after the space. Now the same name in double quotes.

Create

CREATE OR REPLACE WAREHOUSE "my warehouse t155"
    WAREHOUSE_SIZE      = XSMALL
    INITIALLY_SUSPENDED = TRUE;

Result1 row · 1 column

status
Warehouse my warehouse t155 successfully created.

Query

SHOW WAREHOUSES LIKE 'my warehouse t155';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
my warehouse t155	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	Y	600	true					2026-10-06 11:10:16.951000-07:00	2026-10-06 11:10:16.951000-07:00	2026-10-06 11:10:16.968000-07:00	ACCOUNTADMIN		true	8	null	0	0	0	1	72652554569	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Accepted, and SHOW finds it with the spaces intact and the lower case preserved. A quoted identifier is stored exactly as you wrote it, which also means you have to quote it every time you refer to it again.

And one last refusal: an option that does not exist. AUTO_SUSPENDED, with a D on the end.

Create

CREATE OR REPLACE WAREHOUSE WHBADOPT_T155
    WAREHOUSE_SIZE   = XSMALL
    AUTO_SUSPENDED   = 60
    INITIALLY_SUSPENDED = TRUE;

Query failed — Snowflake returned an error

001420 (22023): SQL compilation error:
invalid property 'AUTO_SUSPENDED' for 'WAREHOUSE'

001420, invalid property. Worth comparing with the size refusal earlier: 001421 was an invalid value for a real property, and this is an invalid property.

One thing that is not where you would look for it

Four blocks in this capture went looking for warehouses in INFORMATION_SCHEMA, the way you would for a table. Here is the first.

Query

SELECT WAREHOUSE_NAME, WAREHOUSE_SIZE, AUTO_SUSPEND, AUTO_RESUME
  FROM DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES
 ORDER BY WAREHOUSE_NAME;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES.

002003, the object does not exist. Here it is again, asking for one warehouse by name.

Query

SELECT WAREHOUSE_NAME, WAREHOUSE_SIZE, WAREHOUSE_TYPE, AUTO_SUSPEND, AUTO_RESUME,
       MIN_CLUSTER_COUNT, MAX_CLUSTER_COUNT, SCALING_POLICY
  FROM DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES
 WHERE WAREHOUSE_NAME = 'WHDEFAULTS_T155';

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES.

A warehouse is an account-level object. It does not live in a database, so there is no per-database view of it. SHOW WAREHOUSES is how you list them, and that is why every grid in this post uses it.

One last thing: let's put the account back

Every warehouse this post created is dropped here. Here are two of the drops, and the rest are the same statement with different names.

Drop

DROP WAREHOUSE IF EXISTS WHDEFAULTS_T155;

Result1 row · 1 column

status
WHDEFAULTS_T155 successfully dropped.

Drop

DROP WAREHOUSE IF EXISTS "my warehouse t155";

Result1 row · 1 column

status
my warehouse t155 successfully dropped.

And now the check at the end of the file.

Query

SHOW WAREHOUSES;

Result3 rows · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
COMPUTE_WH	STARTED	STANDARD	X-Small	1	1	1	0	0	Y	N	300	true	100	0	0	0	2026-09-21 18:36:58.764000-07:00	2026-10-06 11:05:18.848000-07:00	2026-10-06 11:05:18.848000-07:00	ACCOUNTADMIN		true	8	null	1	0	0	0	283799204	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
SNOWFLAKE_LEARNING_WH	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	N	300	true					2026-09-21 18:33:47.899000-07:00	2026-09-21 18:33:47.899000-07:00	2026-09-21 18:33:47.913000-07:00	ACCOUNTADMIN	Created by Snowflake during account provisioning	true	8	null	0	0	0	1	283799128	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
SYSTEM$STREAMLIT_NOTEBOOK_WH	SUSPENDED	STANDARD	X-Small	1	10	0	0	0	N	N	60	true					2026-09-21 18:33:46.101000-07:00	2026-09-21 18:33:46.115000-07:00	2026-09-21 18:33:46.136000-07:00	ACCOUNTADMIN		true	8	null	0	0	0	1	283799044	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

The same three we started with, and nothing of ours. But look at the next grid, because this is the one that caught the whole episode.

Query

SELECT CURRENT_WAREHOUSE() AS THE_WAREHOUSE,
       CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA;

Result1 row · 4 columns

THE_WAREHOUSE	THE_ROLE	THE_DATABASE	THE_SCHEMA
NULL	ACCOUNTADMIN	DEMO_DB	PUBLIC

CURRENT_WAREHOUSE() is NULL. The block name says the session is exactly where it started, and it is not: it started on COMPUTE_WH. Every CREATE WAREHOUSE in the file had been moving the session along, and the last drop left it on nothing.

The second capture, which puts the session back by hand, ends properly.

Repair

USE WAREHOUSE COMPUTE_WH;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT "name", "state", "size", "is_current"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result3 rows · 4 columns

name	state	size	is_current
COMPUTE_WH	STARTED	X-Small	Y
SNOWFLAKE_LEARNING_WH	SUSPENDED	X-Small	N
SYSTEM$STREAMLIT_NOTEBOOK_WH	SUSPENDED	X-Small	N

Three warehouses, and is_current back on COMPUTE_WH where it belongs.

That capture ends with one more check, which also asks CURRENT_USER(). The grid is left off this page because it names the account's user, and the builder cannot drop a single column from a grid. Here is the statement on its own.

Query (result withheld)

SELECT CURRENT_USER()      AS THE_USER,
       CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA,
       CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

The role, database, schema and warehouse it returned are the same ones shown in the grids above.

Key points

• CREATE WAREHOUSE moves your session onto the new warehouse. Nothing in the statement says so, and is_current is where it shows.
• Dropping the warehouse the session is standing on leaves CURRENT_WAREHOUSE() as NULL. The session is not put back.
• is_current and is_default are different columns, and only the first one moved.
• The defaults Snowflake chose were X-Small, auto_suspend 600, auto_resume true, one cluster each way, and a STANDARD policy.
• A warehouse created with no INITIALLY_SUSPENDED clause came back RESUMING, so it starts coming up unless you say otherwise.
• SHOW is metadata and answers with no usable warehouse. Reading rows needs compute, and that is 000608.
• A COUNT(*) is answered from metadata, so it is the wrong probe for “can this session compute?”.
• Three spellings of a size all normalise to X-Small.
• AUTO_SUSPEND = 0 is stored as zero, 30 is allowed, and -60 was accepted.
• IF NOT EXISTS keeps the first warehouse and ignores every option in the second statement. OR REPLACE carries nothing over.
• A quoted name with spaces is legal and is stored exactly as written.
• DESCRIBE WAREHOUSE returns three columns and tells you almost nothing.
• Warehouses are account-level objects and are not in INFORMATION_SCHEMA.

Next in this series

In the next post we alter one: ALTER WAREHOUSE. Resizing, suspending and resuming, multi-cluster options, the level column that tells you where a value came from, and what UNSET actually leaves behind.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.36.101) running the demo script for this episode, across two captures. The side effect, the drop and the AUTO_RESUME = FALSE warehouse were re-asked in the second capture with the session kept on COMPUTE_WH, which is why those warehouse names end in T155B. One closing grid is withheld because it names the account's user; its statement is on the page. Every warehouse created here is dropped here, and COMPUTE_WH is used and read but never altered. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake CREATE WAREHOUSE documentation.