Snowflake Warehouse: Types, Features, Creation, and Deletion

In this blog, you will gain a comprehensive understanding of Snowflake warehouses. We will discuss the following topics as mentioned below:

Post Content

Snowflake warehouse

Key characteristics and features of Snowflake warehouses

Types of snowflake warehouse

Who Can Create a Snowflake Warehouse?

Create a Snowflake warehouse using web UI

Creating a Snowflake warehouse using SQL Query in Snowflake worksheet

Drop (Delete) a Warehouse in Snowflake

Snowflake Warehouse – Virtual Warehouse:

A Snowflake warehouse is a virtual cluster of compute resources that is used to running SQL and Python queries, processing data, and performing analytics tasks.

Warehouses are a key concept in Snowflake’s architecture, as they allow Snowflake to scale to handle even the most demanding workloads.

Key characteristics and features of Snowflake warehouses include:

• Separation of Compute and Storage: Snowflake warehouses are designed with a separation of compute and storage. This means that compute resources (processing power) and data storage are independently scalable. You can adjust the size of your warehouse to meet your processing needs without affecting the stored data.
• Elastic Scalability: Snowflake warehouses can be easily scaled up or down based on workload demands. This elasticity allows you to allocate more or fewer resources as needed, ensuring optimal performance and cost-efficiency.
• Multi-Cluster Support: Snowflake supports the creation of multiple compute clusters. This means that you can have different warehouses with varying sizes and configurations to handle different workloads concurrently.
• Resource Isolation: Each Snowflake warehouse operates in isolation from others. This isolation ensures that one warehouse’s workload does not impact the performance of another warehouse, making it suitable for multi-tenant environments.
• Pause and Resume: Snowflake warehouses can be paused and resumed. When paused, no compute resources are consumed, and costs are reduced. You can resume a warehouse when you need to perform tasks or queries.
• Auto-Suspend and Auto-Resume: You can configure warehouses to automatically suspend after a period of inactivity and automatically resume when a query is submitted. This feature helps optimize costs by minimizing the use of resources during idle times.
• Concurrency: Snowflake warehouses support concurrent query execution. You can execute multiple queries in parallel within the same warehouse, improving query performance for analytical workloads.
• Role-Based Access Control: Access to warehouses is controlled through Snowflake’s role-based access control (RBAC) system. You can grant specific roles and users permission to use a warehouse.
• Query Optimization: Snowflake’s query optimizer optimizes query execution plans for better performance. It considers factors like data distribution, join conditions, and available resources to determine the most efficient way to execute queries.
• Integration: Snowflake warehouses seamlessly integrate with various data integration, business intelligence, and analytics tools, making it easy to connect your preferred tools to your Snowflake environment.

There are two types of warehouses in Snowflake:

• Standard warehouses: Standard warehouses are the most common type of warehouse. They are used for a variety of workloads, including OLTP, OLAP, and data science.
• Snowpark-optimized warehouses: Snowpark-optimized warehouses are designed for use with Snowpark, which is a framework for running Python code in Snowflake. Snowpark-optimized warehouses provide additional resources for running Python code, such as GPU acceleration.

Understanding Snowflake warehouse Creation and Privileges:

By default in Snowflake, only the SYSADMIN role or roles with higher privileges have the CREATE WAREHOUSE privilege. This is because the CREATE WAREHOUSE privilege grants users the ability to create critical resources known as warehouses.

As a result, only users with the highest level of privileges, including the SYSADMIN role, are permitted to create warehouses.

Switch roles, and you will notice that the + Warehouse button is only visible to SYSADMIN and ACCOUNTADMIN.

How to Create a Snowflake warehouse?

Here are the steps on how to create a warehouse using the Snowflake web UI:

Step-1: Log In to Snowflake: Log in to your Snowflake account using your credentials.

Step-2: Click on the Admin tab, then select Warehouses.

Step-3: Click on the + Warehouse button.

Step-4: Now, provide the following details:

• Enter a name for the warehouse: Provide a unique name for your warehouse. The name should be unique within your Snowflake account.
•  Select the size of the warehouse: Specify the size of the warehouse (e.g., small, medium, large) and configure auto-suspend and auto-resume settings. These settings determine the resources allocated to the warehouse and its behavior when idle.
• Select the type of warehouse.

Step-7: Click on the Create warehouse button.

The warehouse has been created successfully. You can check it under the Admin tab by clicking on Warehouses.

Creating a Snowflake warehouse using SQL Query in Snowflake worksheet:

To create a Snowflake warehouse using SQL in a Snowflake worksheet, you can use the following SQL script as an example. This script creates a new Snowflake warehouse:

Step-1: Add a SQL worksheet and write the following SQL script below:

CREATE OR REPLACE WAREHOUSE TEST_WH_SQL
WAREHOUSE_SIZE = 'X-SMALL'
WAREHOUSE_TYPE = 'STANDARD'
AUTO_SUSPEND = 1800 -- Set the auto-suspend time in seconds (e.g., 30 minutes)
AUTO_RESUME = TRUE; -- Automatically resume the warehouse when needed

Step-2 : You can check it under the Admin tab by clicking on Warehouses.

Drop (Delete) a Warehouse in Snowflake:

To drop a warehouse, use the DROP WAREHOUSE statement followed by the warehouse name you want to drop. Here’s the syntax:

DROP WAREHOUSE IF EXISTS warehouse_name;

Thank you for reading this post, and I hope you enjoyed it!