Creating a Warehouse in Snowflake

Create Warehouse

What does it do?

Used to create a Virtual Warehouse which can be used to execute queries.

Required Privileges

Your current role needs to have the following privileges:

CREATE WAREHOUSE

💡

By default, only the SYSADMIN role has the privileges needed to create databases, and it's best practice to use this role to create virtual warehouses. The ACCOUNTADMIN role could also be used to create warehouses, but this must be avoided.

show warehouses

CREATE WAREHOUSE

Creates a Virtual Warehouse with a sensible set of parameters. See below for notes.

create or replace warehouse sales_data_loading_wh with
   warehouse_size      = SMALL
   auto_resume         = true
   initially_suspended = true
   min_cluster_count   = 1
   max_cluster_count   = 1
   auto_suspend        = 60
   comment             = 'Sales warehouse purely for Data Loading using COPY'
   statement_timeout_in_seconds = 28800;

The above command creates a warehouse with the following parameters:

Parameter	Recommended Value	Description	Comments
warehouse_size	SMALL	Indicates the size of the warehouse.	Larger warehouses can process larger data volumes more quickly but *may* cost more.
auto_resume	true	Indicates whether the warehouse should automatically resume when a query is submitted.	If this value is set to FALSE, you must execute an ALTER WAREHOUSE RESUME command before executing a query. This would be very unusual.
initially_suspended	true	Indicates the warehouse should be created but not immediately started.	This is a sensible default unless the warehouse needs to execute queries immediately.
min_cluster_count	1	Defines the number of clusters (sets of machines) to deploy initially.	In around 99.9% of cases, leave this as one to avoid a significant increase in cost.
max_cluster_count	1	Defines how many clusters may be created to cope with increasing users executing concurrent queries.	Unless you expect many concurrent queries on the warehouse, leave this as 1.
auto_suspend	60	The number of idle time seconds to wait before suspending the warehouse.	Sixty seconds is a best practice setting in about 99% of cases.
comment	Warehouse Purpose	Documents why this warehouse was built	It should be mandatory for every warehouse
statement timeout in seconds	28800	The number of seconds a query can execute before being automatically terminated.	The default is 3 days. This setting allows each query to run for 8 hours and avoids runaway queries.