Skip to main content

Warehouse

Configuration can be provided to Snowflake Object Lifecycle Engine for the following operation with Warehouse:

  • Manage Lifecycle of new and existing Warehouse
  • Manage Grants of Warehouse

Supported Parameters

The engine supports the parameters listed below.

  • WAREHOUSE_SIZE: Specifies the size of the virtual warehouse
    • Configuration Key: warehouse_size, size
    • Data Type: String
    • Default: XSMALL
    • Possible Values:
      • XSMALL
      • X-SMALL
      • SMALL
      • MEDIUM
      • LARGE
      • XLARGE
      • XXLARGE
      • X2LARGE
      • 2X-LARGE
      • XXXLARGE
      • X3LARGE
      • 3X-LARGE
      • X4LARGE
      • 4X-LARGE
      • X5LARGE
      • 5X-LARGE
      • X6LARGE
      • 6X-LARGE
  • MAX_CLUSTER_COUNT: Specifies the maximum number of server clusters for the warehouse.
    • Configuration Key: max_cluster_count
    • Data Type: Integer
  • MIN_CLUSTER_COUNT: Specifies the minimum number of server clusters for the warehouse (only applies to multi-cluster warehouses).
    • Configuration Key: min_cluster_count
    • Data Type: Integer
      MAX_CLUSTER_COUNT, MIN_CLUSTER_COUNT and SCALING_POLICY are needed for non-standard snowflake editions.

      You should commented them out if you are using a standard snowflake edition. You can read more on snowflake account types here.

  • SCALING_POLICY: Specifies the policy for automatically starting and shutting down clusters in a multi-cluster warehouse running in Auto-scale mode.
    • Configuration Key: scaling_policy
    • Data Type: String
    • Possible Values:
      • STANDARD
      • ECONOMY
  • AUTO_SUSPEND: Specifies the number of seconds of inactivity after which a warehouse is automatically suspended.
    • Configuration Key: auto_suspend
    • Data Type: Integer
  • AUTO_RESUME: Specifies whether to automatically resume a warehouse when a SQL statement (e.g. query) is submitted to it.
    • Configuration Key: auto_resume
    • Data Type: Boolean
  • INITIALLY_SUSPENDED: Specifies whether the warehouse is created initially in the ‘Suspended’ state.
    • Configuration Key: initially_suspended
    • Data Type: Boolean
      Setting Initially suspended

      Note that setting this value will cause the warehouse to be recreated on each SOLE run.

  • RESOURCE_MONITOR: Specifies the name of a resource monitor that is explicitly assigned to the warehouse.
    • Configuration Key: resource_monitor
    • Data Type: String
      Resource Monitors set for Account

      Resource Monitors set for Account cannot have any Warehouses referencing it.

  • STATEMENT_TIMEOUT_IN_SECONDS: Object parameter that specifies the time, in seconds, after which a running SQL statement (query, DDL, DML, etc.) is canceled by the system.
    • Configuration Key: statement_timeout_in_seconds
    • Data Type: Number
  • WAIT_FOR_PROVISIONING: Object parameter that specifies whether the warehouse, after being resized, waits for all the servers to provision before executing any queued or new queries.
    • Configuration Key: wait_for_provisioning
    • Data Type: Boolean
  • COMMENT: Specifies a comment for the warehouse.
    • Configuration Key: comment
    • Data Type: String
  • MAX_CONCURRENCY_LEVEL:Object parameter that specifies the concurrency level for SQL statements (i.e. queries and DML) executed by a warehouse.
    • Configuration Key: max_concurrency_level
    • Data Type: Number
  • STATEMENT_QUEUED_TIMEOUT_IN_SECONDS: Object parameter that specifies the time, in seconds, a SQL statement (query, DDL, DML, etc.) can be queued on a warehouse before it is canceled by the system.
    • Configuration Key: statement_queued_timeout_in_seconds
    • Data Type: Number
  • NAMESPACING: Specify whether Prefix or Suffix or both are to be added to Warehouse Name[Doesn't apply to Default Database].
    • Configuration key: namespacing
    • Data Type: String
    • Possible Values:
      • none
      • prefix
      • suffix
      • both(Default)
  • ENVIRONMENT: Specify the environment in which the Warehouse is managed. Regex can be provided as well.
    • Configuration key: environment
    • Data Type: String
  • MANAGE_MODE: Configures what properties to manage for the Warehouse.
    • Configuration key: manage_mode
    • Data Type: String
    • Possible Values:
      • none
      • grants
      • all(Default)
  • GRANTS: List of Privileges and Roles to which privileges are granted to on the current Warehouse.
    • Configuration key: grants
    • Data Type: Map

In-Place Changes

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

Value of Warehouse Size is converted to upper-case equivalent when object is created in Snowflake and value is saved in local state as well. Due to this, when subsequent pipelines are run, the engine assumes that a change has to be made to convert Upper-Case value to specified size.

To reduce the number of in-place changes, we suggest to use Upper-Case values for Warehouse Size, such as XSMALL or 2X-LARGE, instead of xsmall or 2x-large.

Basic syntax

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

Supported Warehouse Grants to Roles

Following is the list of Privileges Grant to Roles that can be specified 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 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: