warehouses

Creates, updates, deletes, gets or lists a warehouses resource.

Overview

Name	warehouses
Type	Resource
Id	snowflake.warehouse.warehouses

Fields

The following fields are returned by SELECT queries:

list_warehouses

A Snowflake virtual warehouse

Name	Datatype	Description
name	string	A Snowflake object identifier. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive. (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$, example: TEST_NAME)
auto_resume	string	Specifies whether to automatically resume a warehouse when a SQL statement is submitted to it
auto_suspend	integer (int32)	time in seconds before auto suspend
available	string (Percentage)	Percentage of the warehouse compute resources that are provisioned and available.
budget	string	Comment representing budget for warehouse.
comment	string (comment)	Specifies a comment for the warehouse
created_on	string (date-time)	Date and time when the warehouse was created.
enable_query_acceleration	string	Specifies whether to enable the query acceleration service for queries that rely on this warehouse for compute resources
initially_suspended	string	Specifies whether the warehouse is created initially in the Suspended state
is_current	boolean	Whether the warehouse is in use for the session. Only one warehouse can be in use at a time for a session. To specify or change the warehouse for a session, use the USE WAREHOUSE command.
is_default	boolean	Whether the warehouse is the default for the current user.
kind	string (warehouse)
max_cluster_count	integer (int32)	Specifies the maximum number of clusters for a multi-cluster warehouse
max_concurrency_level	integer (int32)	Object parameter that specifies the concurrency level for SQL statements executed by a warehouse cluster
min_cluster_count	integer (int32)	Specifies the minimum number of clusters for a multi-cluster warehouse
other	string (Percentage)	Percentage of the warehouse compute resources that are in a state other than available, provisioning, or quiescing.
owner	string (role name)	Role that owns the warehouse.
owner_role_type	string	The type of role that owns the object.
provisioning	string (Percentage)	Percentage of the warehouse compute resources that are in the process of provisioning.
query_acceleration_max_scale_factor	integer (int32)	Specifies the maximum scale factor for leasing compute resources for query acceleration. The scale factor is used as a multiplier based on warehouse size
queued	integer (int32)	Number of SQL statements that are queued for the warehouse.
quiescing	string (Percentage)	Percentage of the warehouse compute resources that are executing SQL statements, but will be shut down once the queries complete.
resource_monitor	string	A Snowflake object identifier. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive. (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$, example: TEST_NAME)
resumed_on	string (date-time)	Date and time when the warehouse was last started or restarted.
running	integer (int32)	Number of SQL statements that are being executed by the warehouse.
scaling_policy	string	Scaling policy of warehouse, possible scaling policies: STANDARD, ECONOMY
size	string	[Deprecated] names of size: X-Small, Small, Medium, Large, X-Large, 2X-Large, 3X-Large, 4X-Large, 5X-Large, 6X-Large
started_clusters	integer (int32)	Number of clusters currently started.
state	string	The state of warehouse, possible states: STARTED, STARTING, DYNAMIC, SUSPENDED, RESIZING, RESUMING, SUSPENDING
statement_queued_timeout_in_seconds	integer (int32)	Object parameter that specifies the time, in seconds, a SQL statement can be queued on a warehouse before it is canceled by the system
statement_timeout_in_seconds	integer (int32)	Object parameter that specifies the time, in seconds, after which a running SQL statement is canceled by the system
target_statement_size	string	Names of size: X-Small, Small, Medium, Large, X-Large, 2X-Large, 3X-Large, 4X-Large, 5X-Large, 6X-Large
type	string	[Deprecated] Type of warehouse, possible types: STANDARD, SNOWPARK-OPTIMIZED
updated_on	string (date-time)	Date and time when the warehouse was last updated, which includes changing any of the properties of the warehouse or changing the state (STARTED, SUSPENDED, RESIZING) of the warehouse.
wait_for_completion	string	When resizing a warehouse, you can use this parameter to block the return of the ALTER WAREHOUSE command until the resize has finished provisioning all its compute resources
warehouse_credit_limit	integer (int64)	Credit limit that are can be executed by the warehouse.
warehouse_size	string	Size of warehouse, possible sizes: XSMALL, SMALL, MEDIUM, LARGE, XLARGE, XXLARGE, XXXLARGE, X4LARGE, X5LARGE, X6LARGE
warehouse_type	string	Type of warehouse, possible types: STANDARD, SNOWPARK-OPTIMIZED

fetch_warehouse

A Snowflake virtual warehouse

Name	Datatype	Description
name	string	A Snowflake object identifier. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive. (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$, example: TEST_NAME)
auto_resume	string	Specifies whether to automatically resume a warehouse when a SQL statement is submitted to it
auto_suspend	integer (int32)	time in seconds before auto suspend
available	string (Percentage)	Percentage of the warehouse compute resources that are provisioned and available.
budget	string	Comment representing budget for warehouse.
comment	string (comment)	Specifies a comment for the warehouse
created_on	string (date-time)	Date and time when the warehouse was created.
enable_query_acceleration	string	Specifies whether to enable the query acceleration service for queries that rely on this warehouse for compute resources
initially_suspended	string	Specifies whether the warehouse is created initially in the Suspended state
is_current	boolean	Whether the warehouse is in use for the session. Only one warehouse can be in use at a time for a session. To specify or change the warehouse for a session, use the USE WAREHOUSE command.
is_default	boolean	Whether the warehouse is the default for the current user.
kind	string (warehouse)
max_cluster_count	integer (int32)	Specifies the maximum number of clusters for a multi-cluster warehouse
max_concurrency_level	integer (int32)	Object parameter that specifies the concurrency level for SQL statements executed by a warehouse cluster
min_cluster_count	integer (int32)	Specifies the minimum number of clusters for a multi-cluster warehouse
other	string (Percentage)	Percentage of the warehouse compute resources that are in a state other than available, provisioning, or quiescing.
owner	string (role name)	Role that owns the warehouse.
owner_role_type	string	The type of role that owns the object.
provisioning	string (Percentage)	Percentage of the warehouse compute resources that are in the process of provisioning.
query_acceleration_max_scale_factor	integer (int32)	Specifies the maximum scale factor for leasing compute resources for query acceleration. The scale factor is used as a multiplier based on warehouse size
queued	integer (int32)	Number of SQL statements that are queued for the warehouse.
quiescing	string (Percentage)	Percentage of the warehouse compute resources that are executing SQL statements, but will be shut down once the queries complete.
resource_monitor	string	A Snowflake object identifier. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive. (pattern: ^"([^"]|"")+"|[a-zA-Z_][a-zA-Z0-9_$]*$, example: TEST_NAME)
resumed_on	string (date-time)	Date and time when the warehouse was last started or restarted.
running	integer (int32)	Number of SQL statements that are being executed by the warehouse.
scaling_policy	string	Scaling policy of warehouse, possible scaling policies: STANDARD, ECONOMY
size	string	[Deprecated] names of size: X-Small, Small, Medium, Large, X-Large, 2X-Large, 3X-Large, 4X-Large, 5X-Large, 6X-Large
started_clusters	integer (int32)	Number of clusters currently started.
state	string	The state of warehouse, possible states: STARTED, STARTING, DYNAMIC, SUSPENDED, RESIZING, RESUMING, SUSPENDING
statement_queued_timeout_in_seconds	integer (int32)	Object parameter that specifies the time, in seconds, a SQL statement can be queued on a warehouse before it is canceled by the system
statement_timeout_in_seconds	integer (int32)	Object parameter that specifies the time, in seconds, after which a running SQL statement is canceled by the system
target_statement_size	string	Names of size: X-Small, Small, Medium, Large, X-Large, 2X-Large, 3X-Large, 4X-Large, 5X-Large, 6X-Large
type	string	[Deprecated] Type of warehouse, possible types: STANDARD, SNOWPARK-OPTIMIZED
updated_on	string (date-time)	Date and time when the warehouse was last updated, which includes changing any of the properties of the warehouse or changing the state (STARTED, SUSPENDED, RESIZING) of the warehouse.
wait_for_completion	string	When resizing a warehouse, you can use this parameter to block the return of the ALTER WAREHOUSE command until the resize has finished provisioning all its compute resources
warehouse_credit_limit	integer (int64)	Credit limit that are can be executed by the warehouse.
warehouse_size	string	Size of warehouse, possible sizes: XSMALL, SMALL, MEDIUM, LARGE, XLARGE, XXLARGE, XXXLARGE, X4LARGE, X5LARGE, X6LARGE
warehouse_type	string	Type of warehouse, possible types: STANDARD, SNOWPARK-OPTIMIZED

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Optional Params	Description
list_warehouses	select	endpoint	like	Show a list of warehouse filtered by pattern. Equivalent to SHOW WAREHOUSE in SQL.
fetch_warehouse	select	name, endpoint		Describes the warehouse, show information of the chosen warehouse. Equivalent to DESCRIBE WAREHOUSE in SQL.
create_warehouse	insert	endpoint	createMode	Create a virtual warehouse. Equivalent to CREATE WAREHOUSE in SQL.
create_or_alter_warehouse	replace	name, endpoint		Create a (or alter an existing) warehouse. Even if the operation is just an alter, the full property set must be provided.
delete_warehouse	delete	name, endpoint	ifExists	Removes the specified virtual warehouse from the system. Equivalent to DROP WAREHOUSE in SQL.
resume_warehouse	exec	name, endpoint	ifExists	Bring current warehouse to a usable ‘Running’ state by provisioning compute resources if current warehouse is suspended.
suspend_warehouse	exec	name, endpoint	ifExists	Remove all compute nodes from a warehouse and put the warehouse into a ‘Suspended’ state if current warehouse is not suspended.
rename_warehouse	exec	name, endpoint	ifExists	Specifies a new identifier for the warehouse; must be unique for current account.
abort_all_queries_on_warehouse	exec	name, endpoint	ifExists	Aborts all the queries currently running or queued on the warehouse.
use_warehouse	exec	name, endpoint		[Deprecated] Specifies the active/current warehouse for the session.
enable_warehouse	exec	name, endpoint	ifExists	Enable an adaptive warehouse and put the warehouse into a ‘enabled’ state, if the warehouse is not enabled.
disable_warehouse	exec	name, endpoint	ifExists	Disable an adaptive warehouse and put the warehouse into a ‘disabled’ state, if the warehouse is not disabled.

Parameters

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

Name	Datatype	Description
endpoint	string	Organization and Account Name (default: orgid-acctid)
name	string	Identifier (i.e. name) for the resource.
createMode	string	Query parameter allowing support for different modes of resource creation. Possible values include: - errorIfExists: Throws an error if you try to create a resource that already exists. - orReplace: Automatically replaces the existing resource with the current one. - ifNotExists: Creates a new resource when an alter is requested for a non-existent resource.
ifExists	boolean	Query parameter that specifies how to handle the request for a resource that does not exist: - true: The endpoint does not throw an error if the resource does not exist. It returns a 200 success response, but does not take any action on the resource. - false: The endpoint throws an error if the resource doesn't exist.
like	string	Query parameter to filter the command output by resource name. Uses case-insensitive pattern matching, with support for SQL wildcard characters.

SELECT examples

list_warehouses

Show a list of warehouse filtered by pattern. Equivalent to SHOW WAREHOUSE in SQL.

SELECT
name,
auto_resume,
auto_suspend,
available,
budget,
comment,
created_on,
enable_query_acceleration,
initially_suspended,
is_current,
is_default,
kind,
max_cluster_count,
max_concurrency_level,
min_cluster_count,
other,
owner,
owner_role_type,
provisioning,
query_acceleration_max_scale_factor,
queued,
quiescing,
resource_monitor,
resumed_on,
running,
scaling_policy,
size,
started_clusters,
state,
statement_queued_timeout_in_seconds,
statement_timeout_in_seconds,
target_statement_size,
type,
updated_on,
wait_for_completion,
warehouse_credit_limit,
warehouse_size,
warehouse_type
FROM snowflake.warehouse.warehouses
WHERE endpoint = '{{ endpoint }}' -- required
AND like = '{{ like }}';

fetch_warehouse

Describes the warehouse, show information of the chosen warehouse. Equivalent to DESCRIBE WAREHOUSE in SQL.

SELECT
name,
auto_resume,
auto_suspend,
available,
budget,
comment,
created_on,
enable_query_acceleration,
initially_suspended,
is_current,
is_default,
kind,
max_cluster_count,
max_concurrency_level,
min_cluster_count,
other,
owner,
owner_role_type,
provisioning,
query_acceleration_max_scale_factor,
queued,
quiescing,
resource_monitor,
resumed_on,
running,
scaling_policy,
size,
started_clusters,
state,
statement_queued_timeout_in_seconds,
statement_timeout_in_seconds,
target_statement_size,
type,
updated_on,
wait_for_completion,
warehouse_credit_limit,
warehouse_size,
warehouse_type
FROM snowflake.warehouse.warehouses
WHERE name = '{{ name }}' -- required
AND endpoint = '{{ endpoint }}' -- required;

INSERT examples

create_warehouse

Create a virtual warehouse. Equivalent to CREATE WAREHOUSE in SQL.

INSERT INTO snowflake.warehouse.warehouses (
data__name,
data__warehouse_type,
data__warehouse_size,
data__wait_for_completion,
data__max_cluster_count,
data__min_cluster_count,
data__scaling_policy,
data__auto_suspend,
data__auto_resume,
data__initially_suspended,
data__resource_monitor,
data__comment,
data__enable_query_acceleration,
data__query_acceleration_max_scale_factor,
data__max_concurrency_level,
data__statement_queued_timeout_in_seconds,
data__statement_timeout_in_seconds,
data__type,
data__size,
data__warehouse_credit_limit,
data__target_statement_size,
endpoint,
createMode
)
SELECT 
'{{ name }}' --required,
'{{ warehouse_type }}',
'{{ warehouse_size }}',
'{{ wait_for_completion }}',
{{ max_cluster_count }},
{{ min_cluster_count }},
'{{ scaling_policy }}',
{{ auto_suspend }},
'{{ auto_resume }}',
'{{ initially_suspended }}',
'{{ resource_monitor }}',
'{{ comment }}',
'{{ enable_query_acceleration }}',
{{ query_acceleration_max_scale_factor }},
{{ max_concurrency_level }},
{{ statement_queued_timeout_in_seconds }},
{{ statement_timeout_in_seconds }},
'{{ type }}',
'{{ size }}',
{{ warehouse_credit_limit }},
'{{ target_statement_size }}',
'{{ endpoint }}',
'{{ createMode }}'
;

Manifest

# Description fields are for documentation purposes
- name: warehouses
  props:
    - name: endpoint
      value: string
      description: Required parameter for the warehouses resource.
    - name: name
      value: string
      description: >
        A Snowflake object identifier. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.
        
    - name: warehouse_type
      value: string
      description: >
        Type of warehouse, possible types: STANDARD, SNOWPARK-OPTIMIZED
        
    - name: warehouse_size
      value: string
      description: >
        Size of warehouse, possible sizes: XSMALL, SMALL, MEDIUM, LARGE, XLARGE, XXLARGE, XXXLARGE, X4LARGE, X5LARGE, X6LARGE
        
    - name: wait_for_completion
      value: string
      description: >
        When resizing a warehouse, you can use this parameter to block the return of the ALTER WAREHOUSE command until the resize has finished provisioning all its compute resources
        
      valid_values: ['true', 'false']
    - name: max_cluster_count
      value: integer
      description: >
        Specifies the maximum number of clusters for a multi-cluster warehouse
        
    - name: min_cluster_count
      value: integer
      description: >
        Specifies the minimum number of clusters for a multi-cluster warehouse
        
    - name: scaling_policy
      value: string
      description: >
        Scaling policy of warehouse, possible scaling policies: STANDARD, ECONOMY
        
    - name: auto_suspend
      value: integer
      description: >
        time in seconds before auto suspend
        
    - name: auto_resume
      value: string
      description: >
        Specifies whether to automatically resume a warehouse when a SQL statement is submitted to it
        
      valid_values: ['true', 'false']
    - name: initially_suspended
      value: string
      description: >
        Specifies whether the warehouse is created initially in the Suspended state
        
      valid_values: ['true', 'false']
    - name: resource_monitor
      value: string
      description: >
        A Snowflake object identifier. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.
        
    - name: comment
      value: string
      description: >
        Specifies a comment for the warehouse
        
    - name: enable_query_acceleration
      value: string
      description: >
        Specifies whether to enable the query acceleration service for queries that rely on this warehouse for compute resources
        
      valid_values: ['true', 'false']
    - name: query_acceleration_max_scale_factor
      value: integer
      description: >
        Specifies the maximum scale factor for leasing compute resources for query acceleration. The scale factor is used as a multiplier based on warehouse size
        
    - name: max_concurrency_level
      value: integer
      description: >
        Object parameter that specifies the concurrency level for SQL statements executed by a warehouse cluster
        
    - name: statement_queued_timeout_in_seconds
      value: integer
      description: >
        Object parameter that specifies the time, in seconds, a SQL statement can be queued on a warehouse before it is canceled by the system
        
    - name: statement_timeout_in_seconds
      value: integer
      description: >
        Object parameter that specifies the time, in seconds, after which a running SQL statement  is canceled by the system
        
    - name: type
      value: string
      description: >
        [Deprecated] Type of warehouse, possible types: STANDARD, SNOWPARK-OPTIMIZED
        
    - name: size
      value: string
      description: >
        [Deprecated] names of size: X-Small, Small, Medium, Large, X-Large, 2X-Large, 3X-Large, 4X-Large, 5X-Large, 6X-Large
        
    - name: warehouse_credit_limit
      value: integer
      description: >
        Credit limit that are can be executed by the warehouse.
        
    - name: target_statement_size
      value: string
      description: >
        Names of size: X-Small, Small, Medium, Large, X-Large, 2X-Large, 3X-Large, 4X-Large, 5X-Large, 6X-Large
        
    - name: createMode
      value: string
      description: Query parameter allowing support for different modes of resource creation. Possible values include: - `errorIfExists`: Throws an error if you try to create a resource that already exists. - `orReplace`: Automatically replaces the existing resource with the current one. - `ifNotExists`: Creates a new resource when an alter is requested for a non-existent resource.

REPLACE examples

create_or_alter_warehouse

Create a (or alter an existing) warehouse. Even if the operation is just an alter, the full property set must be provided.

REPLACE snowflake.warehouse.warehouses
SET 
data__name = '{{ name }}',
data__warehouse_type = '{{ warehouse_type }}',
data__warehouse_size = '{{ warehouse_size }}',
data__wait_for_completion = '{{ wait_for_completion }}',
data__max_cluster_count = {{ max_cluster_count }},
data__min_cluster_count = {{ min_cluster_count }},
data__scaling_policy = '{{ scaling_policy }}',
data__auto_suspend = {{ auto_suspend }},
data__auto_resume = '{{ auto_resume }}',
data__initially_suspended = '{{ initially_suspended }}',
data__resource_monitor = '{{ resource_monitor }}',
data__comment = '{{ comment }}',
data__enable_query_acceleration = '{{ enable_query_acceleration }}',
data__query_acceleration_max_scale_factor = {{ query_acceleration_max_scale_factor }},
data__max_concurrency_level = {{ max_concurrency_level }},
data__statement_queued_timeout_in_seconds = {{ statement_queued_timeout_in_seconds }},
data__statement_timeout_in_seconds = {{ statement_timeout_in_seconds }},
data__type = '{{ type }}',
data__size = '{{ size }}',
data__warehouse_credit_limit = {{ warehouse_credit_limit }},
data__target_statement_size = '{{ target_statement_size }}'
WHERE 
name = '{{ name }}' --required
AND endpoint = '{{ endpoint }}' --required
AND data__name = '{{ name }}' --required;

DELETE examples

delete_warehouse

Removes the specified virtual warehouse from the system. Equivalent to DROP WAREHOUSE in SQL.

DELETE FROM snowflake.warehouse.warehouses
WHERE name = '{{ name }}' --required
AND endpoint = '{{ endpoint }}' --required
AND ifExists = '{{ ifExists }}';

Lifecycle Methods

resume_warehouse

Bring current warehouse to a usable ‘Running’ state by provisioning compute resources if current warehouse is suspended.

EXEC snowflake.warehouse.warehouses.resume_warehouse 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }};

suspend_warehouse

Remove all compute nodes from a warehouse and put the warehouse into a ‘Suspended’ state if current warehouse is not suspended.

EXEC snowflake.warehouse.warehouses.suspend_warehouse 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }};

rename_warehouse

Specifies a new identifier for the warehouse; must be unique for current account.

EXEC snowflake.warehouse.warehouses.rename_warehouse 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }} 
@@json=
'{
"name": "{{ name }}", 
"warehouse_type": "{{ warehouse_type }}", 
"warehouse_size": "{{ warehouse_size }}", 
"wait_for_completion": "{{ wait_for_completion }}", 
"max_cluster_count": {{ max_cluster_count }}, 
"min_cluster_count": {{ min_cluster_count }}, 
"scaling_policy": "{{ scaling_policy }}", 
"auto_suspend": {{ auto_suspend }}, 
"auto_resume": "{{ auto_resume }}", 
"initially_suspended": "{{ initially_suspended }}", 
"resource_monitor": "{{ resource_monitor }}", 
"comment": "{{ comment }}", 
"enable_query_acceleration": "{{ enable_query_acceleration }}", 
"query_acceleration_max_scale_factor": {{ query_acceleration_max_scale_factor }}, 
"max_concurrency_level": {{ max_concurrency_level }}, 
"statement_queued_timeout_in_seconds": {{ statement_queued_timeout_in_seconds }}, 
"statement_timeout_in_seconds": {{ statement_timeout_in_seconds }}, 
"type": "{{ type }}", 
"size": "{{ size }}", 
"warehouse_credit_limit": {{ warehouse_credit_limit }}, 
"target_statement_size": "{{ target_statement_size }}"
}';

abort_all_queries_on_warehouse

Aborts all the queries currently running or queued on the warehouse.

EXEC snowflake.warehouse.warehouses.abort_all_queries_on_warehouse 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }};

use_warehouse

[Deprecated] Specifies the active/current warehouse for the session.

EXEC snowflake.warehouse.warehouses.use_warehouse 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required;

enable_warehouse

Enable an adaptive warehouse and put the warehouse into a ‘enabled’ state, if the warehouse is not enabled.

EXEC snowflake.warehouse.warehouses.enable_warehouse 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }};

disable_warehouse

Disable an adaptive warehouse and put the warehouse into a ‘disabled’ state, if the warehouse is not disabled.

EXEC snowflake.warehouse.warehouses.disable_warehouse 
@name='{{ name }}' --required, 
@endpoint='{{ endpoint }}' --required, 
@ifExists={{ ifExists }};