Skip to main content

Warehouse

You can provide configuration to Snowflake Object Lifecycle Engine for the following operation with warehouse:

  • Manage the lifecycle of new and existing warehouses
  • Manage the grants of a warehouse

Usage

note

We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. Learn more about SOLE for Data Products which is currently available as a private preview.

warehouses:
<warehouse-name>:
<configuration-key>: <value>
grants:
<privilege>:
- <role-name>
- <role-name>

Supported parameters

The engine supports the parameters listed below.

Configuration KeyRequired/OptionalData Types and ValuesDescription
auto_resumeOptionalBooleanSpecifies whether to automatically resume a warehouse when a SQL statement (e.g. query) is submitted to it
auto_suspendOptionalIntegerSpecifies the number of seconds of inactivity after which a warehouse is automatically suspended
commentOptionalStringSpecifies a comment for the warehouse
deletedOptionalBoolean: True enables deletion prevention, False does nothingSpecifies what objects are allowed to be deleted
environmentOptionalStringSpecifies the environment in which the warehouse is managed. Regex can be provided as well.
grantsOptionalMap: See Supported Warehouse Grants to RolesList of Privileges and Roles to which privileges are granted on the current warehouse
initially_suspendedOptionalBooleanSpecifies whether the warehouse is created initially in the 'suspended' state.
Note: Setting this value will cause the warehouse to be recreated on each SOLE run.
manage_modeOptionalString: all (default), none, grantsConfigures what properties to manage for the warehouse.
See Changing Manage Mode before changing the value.
max_concurrency_levelOptionalNumberSpecifies the concurrency level for SQL statements (i.e. queries and DML) executed by a warehouse
max_cluster_countOptionalIntegerSpecifies the maximum number of server clusters for the warehouse
max_cluster_countOptionalIntegerSpecifies the maximum number of server clusters for the warehouse
namespacingOptionalString: both (default), none, prefix, suffixSpecifies whether prefix or suffix or both are to be added to warehouse name - doesn't apply to the default database.
resource_monitorOptionalStringSpecifies the name of a resource monitor that is explicitly assigned to the warehouse.
Caution: The resource monitors set for the account cannot have any warehouses referencing them.
scaling_policyOptionalString: STANDARD, ECONOMYSpecifies the policy for automatically starting and shutting down clusters in a multi-cluster warehouse running in auto-scale mode
statement_timeout_in_secondsOptionalNumberSpecifies the time, in seconds, after which a running SQL statement (query, DDL, DML, etc.) is canceled by the system
statement_queued_timeout_in_secondsOptionalNumberSpecifies the time, in seconds, a SQL statement (query, DDL, DML, etc.) can be queued on a warehouse before it is canceled by the system
wait_for_provisioningOptionalBooleanSpecifies whether the warehouse, after being resized, waits for all the servers to provision before executing any queued or new queries
warehouse_size, sizeOptionalString: XSMALL (default), X-SMALL, SMALL, MEDIUM, LARGE, XLARGE, XLARGE, X2LARGE, 2X-LARGE, XXXLARGE, X3LARGE, X-LARGE, X4LARGE, 4X-LARGE, X5LARGE, 5X-LARGE, X6LARGE, 6X-LARGESpecifies the size of the virtual warehouse

In-Place changes

Using non-uppercase values for warehouse_size leads to in-place changes when running a PLAN or APPLY for a warehouse.

The value of warehouse_size is converted to the uppercase equivalent when the object is created in Snowflake and the value is saved in the local state as well. Due to this, when subsequent pipelines are run, the engine assumes that a change has to be made to convert the uppercase value to the specified size.

To reduce the number of in-place changes, we suggest using uppercase values for the warehouse Size, such as XSMALL or 2X-LARGE, instead of xsmall or 2x-large.

Supported warehouse grants to roles

Following are the privileges you can grant to roles in the warehouse definition:

  • ALL PRIVILEGES
  • MODIFY
  • MONITOR
  • USAGE
  • OPERATE
  • OWNERSHIP

Examples

Warehouses with configured parameters

warehouses:
DEMO_WAREHOUSE_2:
comment: Test Comment
warehouse_size: X-SMALL
## Please comment out max_cluster_count, min_cluster_count and scaling_policy if you are using a standard snowflake account.
## Read more on snowflake account types here: https://docs.snowflake.com/en/user-guide/intro-editions.html
max_cluster_count: 2
min_cluster_count: 1
auto_suspend: 100
auto_resume: true
initially_suspended: true
scaling_policy: standard
statement_timeout_in_seconds: 23

DEMO_WAREHOUSE_3:
comment: Warehouse with Auto-resumed disabled
warehouse_size: X3LARGE
max_cluster_count: 5
auto_suspend: 300
auto_resume: false
## Please comment out max_cluster_count, min_cluster_count and scaling_policy if you are using a standard snowflake account.
## Read more on snowflake account types here: https://docs.snowflake.com/en/user-guide/intro-editions.html
scaling_policy: ECONOMY

Warehouses with MODIFY and MONITOR privileges granted only to the role SYSADMIN

This would implicitly revoke all grants on privileges USAGE and OPERATE.

warehouses:
DEMO_WAREHOUSE_4:
comment: Test Comment
warehouse_size: X-LARGE
auto_resume: true
initially_suspended: true
grants:
MODIFY:
- SYSADMIN
monitor:
- SYSADMIN

Warehouses all privileges revoked

warehouses:
DEMO_WAREHOUSE_5:
comment: Test Comment
warehouse_size: x-large
auto_resume: true
initially_suspended: true
grants: