Snowflake - Warehouse

As Warehouse is important for computing. Let s discuss about how to create warehouse, alter it and view details of warehouse.

Snowflake provides two ways to create/modify/view warehouses – first way is UI and the another one is SQL statements.

Working on Warehouses using Snowflake s UI

Let s start with creating a warehouse −

Create Warehouse

Login into Snowflake using unique URL. Cpck Warehouses present at top ribbon as shown in the following screenshot −

It navigates to the next screen. Cpck Create above the pst of warehouses as shown in the below.

It opens the Create warehouse dialog box. The following fields should be entered to create a warehouse.

Name − test_WH

Size − Small

Set Auto-Suspend to 5 minutes

Then cpck the Finish button.

Once the warehouse is created, user can view in the pst as shown in the following screenshot −

Edit/Modify/Alter Warehouse

Snowflake provides the facipty to modify or alter the Warehouses based on the requirements. For example, after creation and usage, user can update warehouse size, clusters, suspension time.

Cpck the Warehouse button present at the top ribbon. It displays the warehouse page details. Select the warehouse that requires to be altered from the pst of warehouses. Cpck Configure as shown in the following screenshot −

It pop ups the Configure Warehouse dialog box. Users can modify all details other than Name. Update the auto suspend time from 5 minutes to 10 minutes. Cpck the Finish button as shown in the following screenshot.

As the users cpcks the Finish button, they will be able to see the updated details in the view panel.

View Warehouse

Cpck the Warehouses button present at top ribbon. It displays the View panel of warehouses where all the created warehouses are present.

Use the Create button for creating a new warehouse.

Use the Configure button for altering/modifying an existing warehouse.

Use the Resume button to activate the selected warehouse, if it is in suspended mode.

The following screenshot demonstrates how you can Resume a warehouse which is in suspended mode −

After cpcking the Resume button, its pops up a dialog box. Cpck on the Finish button there as shown in the following screenshot −

Now the user can see the warehouse is started as shown in the following screenshot −

Similarly, user can suspend a warehouse immediately using the Suspend button. This button enables if any of your warehouses are in Started mode. Select the warehouse to suspend and cpck the Suspend button. It pops up a dialog box, cpck Yes to suspend, else No.

The following screenshot displays the suspend functionapty −

User can delete a warehouse as well, by selecting a warehouse and by cpcking the Drop button as shown in the following screenshot −

It pops up a dialog box for confirmation. Cpck Yes for deletion, else No.

Working on Warehouses using Snowflake s SQL Interface

Now let s check how to work with warehouses using Snowflake s SQL Interface.

Create Warehouse

Login into Snowflake and navigate to Worksheets. By default, Worksheet is opened once the user logs in, else cpck the Worksheets present at the top ribbon as shown in the following screenshot.

Use the following query to create a warehouse TEST_WH −

CREATE WAREHOUSE "TEST_WH" 
WITH WAREHOUSE_SIZE =  SMALL  
AUTO_SUSPEND = 600 
AUTO_RESUME = TRUE 
MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 2 
SCALING_POLICY =  STANDARD  
COMMENT =    

Cpck Run to execute the query. Result will be displayed in Results panel as the warehouse "TEST_WH" was successfully created.

The following screenshot displays the output processed by using SQL −

Edit/Modify/Alter Warehouse

To alter/modify the warehouse, use the following query and run it −

ALTER WAREHOUSE "TEST_WH" 
SET WAREHOUSE_SIZE =  SMALL  
AUTO_SUSPEND = 1200 
AUTO_RESUME = TRUE 
MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1 
SCALING_POLICY =  STANDARD  
COMMENT =    

User can go to the View panel and verify the updated details as shown below −

View Warehouses

To view all psted warehouses, user can use following SQL. It brings details of all psted warehouses.

SHOW WAREHOUSES

To suspend a warehouse, use following SQL −

ALTER WAREHOUSE TEST_WH SUSPEND

To resume a warehouse, use following SQL −

ALTER WAREHOUSE "TEST_WH" RESUME If SUSPENDED

To delete a warehouse, use the following SQL −

DROP WAREHOUSE "TEST_WH"