In this article we've documented answers to the questions we frequently get from Snowflake users and 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:

Snowflake UI auto suspend details

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.

How do I combine my Snowflake data with other data sources, prepped and ready for analysis?

Analyzing data across your business solutions is simplified with Mitto by Zuar. You can automate your ELT/ETL processes and have data flowing from hundreds of potential sources (like Snowflake), into a single destination (like Snowflake). Transport, warehouse, transform, model, report, and monitor: it's all managed by Mitto. You can learn more about Mitto here.

Next Steps: Learn about...

How to create Date Dimensions Table, Calendar Table, Date Scaffold, or Date Spine in Snowflake
Use Case: I’m using Snowflake as my data warehouse and I have a client tablewith sales data but my visual analytics isn’t looking correct because there aredays with no data. How do I fill in the dates so that I have a record for everydate even if there is no data? Answer: Create a Date Dimensio…
Snowflake vs Redshift vs BigQuery: Comparisons & How to Choose | Zuar
Unsure which data warehouse is best for your organization? We compare different aspects of Snowflake, Amazon Redshift, and Google BigQuery.
Zuar | Cresco Labs Customer Success Story
Find out from the Cresco Labs Team why Zuar is the right choice for efficient data strategy and staging.