In this blog post we will be documenting common questions and answers we see in the field from Snowflake users and Snowflake account admins.

What's my current user, role, warehouse, database, etc?

SELECT CURRENT_USER();
SELECT CURRENT_ROLE();
SELECT CURRENT_WAREHOUSE();
SELECT CURRENT_DATABASE();

For more information see Snowflake's Context Functions documentation.

How do I use a specific role, warehouse, database, etc?

SHOW ROLES;
USE ROLE {role};

SHOW WAREHOUSES;
USE WAREHOUSE {warehouse};

SELECT * FROM INFORMATION_SCHEMA.DATABASES;
USE DATABASE {database};

How do I set my default warehouse?

Determine your current warehouse:

SELECT CURRENT_WAREHOUSE();

Alter your default warehouse:

ALTER USER {username} SET DEFAULT_WAREHOUSE = {warehouse};

How do I create a new warehouse?

Check if the warehouse already exists:

SHOW WAREHOUSES;
DESCRIBE WAREHOUSE {warehouse};

Create (or replace) the warehouse:

CREATE OR REPLACE WAREHOUSE ANALYTICS
    WITH WAREHOUSE_SIZE = 'X-SMALL'
    MAX_CLUSTER_COUNT = 1
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE
;

How to create a role that allows 'create warehouse'

use database analytics;
use warehouse analytics;
create role administrator;
grant create warehouse on account to role ADMINISTRATOR;
grant usage on database ANALYTICS to role ADMINISTRATOR;
show grants on role ADMINISTRATOR;
grant role ADMINISTRATOR to user <username>;
show grants on user <username>;

For more information see Snowflake's Create Warehouse documentation.

How do I set a custom Auto Suspend timeout on my warehouse?

The Snowflake UI has a set number of options for "Auto Suspend".

Snowflake automatically suspends the warehouse if it is inactive for the specified period of time.

However, with a simple SQL query you can set whatever timeout you need. The timeout value is in seconds.

ALTER WAREHOUSE IF EXISTS {warehouse} SET AUTO_SUSPEND = {seconds};

Below we are adjusting our "MITTO" warehouse to auto suspend after 60 seconds.

ALTER WAREHOUSE IF EXISTS MITTO SET AUTO_SUSPEND = 60;

Here's the result now in the Snowflake UI:

For more information see Snowflake's Alter Warehouse documentation.

How do I create a new database user?

SHOW ROLES;
USE ROLE ACCOUNTADMIN;
-- USE ROLE SECURITYADMIN;
CREATE USER {username} PASSWORD = '{password}' MUST_CHANGE_PASSWORD = TRUE;
-- Grant usage on a database and warehouse to a role
SHOW GRANTS TO ROLE {role};
GRANT USAGE ON WAREHOUSE {warehouse} TO ROLE {role};
GRANT USAGE ON DATABASE {database} TO ROLE {role};

Snowflake provides a full set of SQL commands for managing users and security. These commands can only be executed by users who are granted roles that have the OWNERSHIP privilege on the managed object. This is usually restricted to the ACCOUNTADMIN and SECURITYADMIN roles.

For more information see Snowflake's Create User documentation.