19 October 2018        Add to Favorites   Report

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

Warehouse Size  Servers / Cluster
Credits / Hour
X-Small
11
Small
22
Medium
44
Large
88
X-Large 
1616
2X-Large 
3232
3X-Large 
6464
4X-Large 
128128
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




Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied