Snowflake - Virtual Warehouse

 Snowflake Virtual Warehouse :

It is the second layer of the snowflake. It takes care of query Processing. A virtual warehouse can be thought of a cluster of compute resources that are allocated on demand to process queries against a data warehouse. Can consider this as CPU / query processing capacity. 

It is used for Query processing. 

• Every Virtual ware house is a group of clusters. 
• A cluster is a group of servers.
• Every server provides CPU, storage and memory.

The size and number of instances can be scaled up or down dynamically to accommodate varying levels of query workload.

• Scale - in / Scale Out : When you increase clusters it is called it is called Scale Out and  decrying cluster is called scale -in. it happens in multi cluster
• Scale up / Down : It is increasing the capacity of a cluster itself not the number of clusters.

Who to Create a virtual warehouse :

A virtual warehouse can be created using snowflake sql command as shown in the sample code below:

CREATE WAREHOUSE my_warehouse

WITH

WAREHOUSE_SIZE = 'XSMALL' -- / Small / Medium / Large / X-Large / 2X-Large / 4X-Large / 6X-Large

WAREHOUSE_TYPE = 'STANDARD' -- / MULTI_CLUSTER / SINGLE_CLUSTER / VIRTUAL

AUTO_SUSPEND = 600

AUTO_RESUME = TRUE

INITIALLY_SUSPENDED = TRUE;

ObjectProperties ::=

  WAREHOUSE_TYPE = STANDARD | SNOWPARK-OPTIMIZED

  WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE | X5LARGE | X6LARGE

  MAX_CLUSTER_COUNT = <num>

  MIN_CLUSTER_COUNT = <num>

  SCALING_POLICY = STANDARD | ECONOMY

  AUTO_SUSPEND = <num> | NULL

  AUTO_RESUME = TRUE | FALSE

  INITIALLY_SUSPENDED = TRUE | FALSE

  RESOURCE_MONITOR = <monitor_name>

  COMMENT = '<string_literal>'

  ENABLE_QUERY_ACCELERATION = TRUE | FALSE

  QUERY_ACCELERATION_MAX_SCALE_FACTOR = <num>

we can set up multi cluster in Standard / Economy Mode.

Standard mode will not wait to scale out where as Economy Mode will wait for few seconds to check if any resources can get freed to allocate to new process.