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 following parameters in a warehouse are supported by the runner:

  • 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_size
    • 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_size
    • Data Type: Integer
  • 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
  • RESOURCE_MONITOR: Specifies the name of a resource monitor that is explicitly assigned to the warehouse.
    • Configuration Key: resource_monitor
    • Data Type: String
  • 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
  • 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    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    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:
Last updated on