Posted in Warehouse

Creating a Snowflake Warehouse

   November 17, 2023

A virtual warehouse, often referred to simply as a “warehouse”, is a cluster of compute resources in Snowflake. Creating a Warehouse is a fairly simple task. Here is an example of the Syntax:


CREATE OR REPLACE WAREHOUSE Warehouse_name_wh WITH
COMMENT = 'Lets build a warehouse'
    WAREHOUSE_TYPE = 'standard'
    WAREHOUSE_SIZE = 'xsmall' 
    MIN_CLUSTER_COUNT = 1 
    MAX_CLUSTER_COUNT = 2 
    SCALING_POLICY = 'standard'
    AUTO_SUSPEND = 60
    AUTO_RESUME = true
    INITIALLY_SUSPENDED = true;

Warehouse Type: Warehouses are required for queries, as well as all DML operations, including loading data into tables. Snowflake supports Standard (most-common) or Snowpark-optimized Warehouse Types.

Warehouse Size: Size specifies the amount of compute resources available per cluster in a warehouse. Snowflake supports X-Small through 6X-Large sizes.

Minimum and Maximum Cluster Count: With multi-cluster warehouses, Snowflake supports allocating, either statically or dynamically, additional clusters to make a larger pool of compute resources available.

Scaling Policy: Specifies the policy for automatically starting and shutting down clusters in a multi-cluster warehouse running in Auto-scale mode.

Auto Suspend: By default, Auto-Suspend is enabled. Snowflake automatically suspends the warehouse if it is inactive for the specified period of time, in our case 60 seconds.

Auto Resume: By default, auto-resume is enabled. Snowflake automatically resumes the warehouse when any statement that requires a warehouse is submitted and the warehouse is the current warehouse for the session.

Initially Suspended: Specifies whether the warehouse is created initially in the ‘Suspended’ state.

For further information on Snowflake Warehouses please visit the Snowflake Warehouse Documentation

  1. The Virtual Warehouses in Snowflake are Compute machines which internally uses Amazon EC2 machines or Azure VM machines. The user need not know these internal details and can created warehouses with T-Shirt sizes like X-SMALL, SMALL, MEDIUM, LARGE, X-LARGE, 2X-LARGE, 3X-LARGE and 4X-LARGE.
  2. Each compute cluster consists of one or more servers with multi-core CPU(s) and hyper-threading enabled that can execute multiple tasks in parallel.
  3. The number of maximum possible parallel process or queries in a Snowflake compute cluster depends on the numbers of processing threads available in the compute machines used for the cluster.