ALTER WAREHOUSE command to suspend, resume, resize a virtual warehouse in Snowflake

Snowflake uses Virtual warehouses to execute your SQL Queries as well as for your ETL Batch loads. You can suspend the Virtual warehouses once you are done with them to save some $$$. Here are the commands to suspend or resume a virtual warehouse

Suspend / Resume a virtual warehouse:

Example

ALTER WAREHOUSE MARKETING_TEAM_WH SUSPEND;

Syntax

ALTER WAREHOUSE [ IF EXISTS ] [ <name> ] { SUSPEND | RESUME [ IF SUSPENDED ] }

Increase / Decrease the Warehouse size:

Snowflake uses the T-shirt sizes for hardware, you can resize the hardware on the fly using the below commands. Snowflake charges/bills per second

ALTER WAREHOUSE MARKETING_TEAM_WAREHOUSE
SET
  WAREHOUSE_SIZE = XLARGE
  MAX_CLUSTER_COUNT = 5
  MIN_CLUSTER_COUNT = 1
  SCALING_POLICY = STANDARD
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  MAX_CONCURRENCY_LEVEL = 100
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 600
  STATEMENT_TIMEOUT_IN_SECONDS = 6000;

How to kill all queries running in a warehouse:

ALTER WAREHOUSE [ IF EXISTS ] [ <name> ] ABORT ALL QUERIES

victor

posted on 19 Oct 18