Snowflake Solutions Expertise and
Community Trusted By

Enter Your Email Address Here To Join Our Snowflake Solutions Community For Free

Snowflake Solutions Community

Using a Warehouse (Snowflake)

577 viewsConnecting to SnowflakeVirtualwarehouses
0

Using a Warehouse (Snowflake)

Alejandro Penzini Answered question December 19, 2023
0

Executing Queries and DML Statements:

Warehouse Requirements: Running queries and DML statements in Snowflake requires two conditions:

-A running warehouse.
-The warehouse specified as the current warehouse for the session.

Session Scope: Each Snowflake session can only have one current warehouse at a time.
Warehouse Selection: Use the USE WAREHOUSE command to set or change the current warehouse for the session.
Query Processing: Once set, queries and DML statements within the session are processed by the current warehouse. You can view the warehouse used for each query/statement in the Classic Console's History and Worksheets pages.

Delegating Warehouse Management:

Default Access: The ACCOUNTADMIN role holds default privileges to manage all warehouses in the account (alter, suspend, describe, etc.).
Custom Role Delegation: For finer control, use the GRANT command to grant the MANAGE WAREHOUSES privilege to a custom role. This grants equivalent privileges (MODIFY, MONITOR, OPERATE) to all account warehouses.

Example: The following demonstrates delegating warehouse management to a custom role named manage_wh_role:

1. Create and Own a New Warehouse:

SQL
CREATE ROLE create_wh_role;
GRANT

CREATE WAREHOUSE ON ACCOUNT TO ROLE create_wh_role;
GRANT ROLE create_wh_role TO ROLE SYSADMIN;
CREATE ROLE manage_wh_role;
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE manage_wh_role;
GRANT ROLE manage_wh_role TO ROLE SYSADMIN;
Use code with caution. Learn more

2. Use manage_wh_role to modify "test_wh" owned by create_wh_role:

SQL
USE ROLE manage_wh_role;
ALTER WAREHOUSE test_wh ...;

Delegating Warehouse Management with Roles:

This example demonstrates creating two roles to manage Snowflake warehouses:

1. create_wh_role:

This role has the CREATE WAREHOUSE privilege, allowing it to:

Create and own new warehouses.
Grant ownership of warehouses to other roles.

2. manage_wh_role:

This role has the MANAGE WAREHOUSES privilege, granting it comprehensive control over all warehouses in the account, regardless of ownership. This includes the ability to:

Suspend and resume warehouses.
Resize warehouses.
Describe warehouse properties.

Scenario:

Create both roles and grant them the necessary privileges:
SQL
CREATE ROLE create_wh_role;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE create_wh_role;
GRANT ROLE create_wh_role TO ROLE SYSADMIN;

CREATE ROLE manage_wh_role;
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE manage_wh_role;
GRANT ROLE manage_wh_role TO ROLE SYSADMIN;
Use code with caution. Learn more

Create a new warehouse with create_wh_role:
SQL
USE ROLE create_wh_role;
CREATE OR REPLACE WAREHOUSE test_wh WITH WAREHOUSE_SIZE = XSMALL;
Use code with caution. Learn more

Switch to manage_wh_role and manage the warehouse:
SQL
USE ROLE manage_wh_role;

ALTER WAREHOUSE test_wh SUSPEND; -- Suspend the warehouse
ALTER WAREHOUSE test_wh SET WAREHOUSE_SIZE = SMALL; -- Resize the warehouse
DESC WAREHOUSE test_wh; -- Describe the warehouse properties

Alejandro Penzini Answered question December 19, 2023
You are viewing 1 out of 1 answers, click here to view all answers.

Sign in with google.com

To continue, google.com will share your name, email address, and profile picture with this site.

Harness the Power of Data with ITS Solutions

Innovative Solutions for Comprehensive Data Management

Feedback on Q&A