Prepare Snowflake for Winterface

Note: This article assumes that you were able to successfully create an account and are now being asked to set up your Winterface instance. If not, please see the previous article: Creating an Account.

We recommend getting these assets in Snowflake set up prior to completing your Winterface instance set up. While you can leverage existing USERS, ROLES, WAREHOUSES, and DATABASES, we recommend creating brand new assets as outlined below for the following reasons:

• Security - By creating separate and unique assets in Snowflake, you create an isolated region in your Snowflake instance. This ensures that permissions are explicit and thoughtfully managed. This will allow you to follow security best practices such as separation of concerns.
• Permissions - In addition to being more secure, by following the below instructions to create brand new assets, you can ensure Winterface has the exact permissions necessary to perform required functions. This ensures a seamless experience for Winterface users and reduces the possibility of issues occurring.
• Independent Workloads - By creating a separate warehouse specifically for Winterface, you can ensure there are no other processes that interfere with Winterface operations, ensuring maximum performance. Additionally, you can better understand and control spending on Winterface-related functions.

With the "why" out of the way, here is how to create the recommended assets:

Snowflake Role

The below snippet to be run in Snowflake creates a new role (WINTERFACE_ROLE) and gives SYSADMIN access to manage that role.

It requires SECURITYADMIN or equivalent privileges to run.

-- create role for Winterface
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS WINTERFACE_ROLE
    COMMENT = 'Winterface for Snowflake Admin Role';

GRANT ROLE WINTERFACE_ROLE TO ROLE SYSADMIN;

Snowflake User

By running the below in Snowflake, a new user is created with defaults set up to the WINTERFACE_ROLE role created above and the WINTERFACE_WH warehouse to be created below.

It also requires SECURITYADMIN or equivalent privileges to run.

Be sure to update the password before running.

-- create a user with WINTERFACE_ROLE
USE ROLE SECURITYADMIN;

CREATE USER IF NOT EXISTS WINTERFACE_USER 
    PASSWORD = '***********' 
    COMMENT = 'Winterface for Snowflake User'
    DEFAULT_ROLE = WINTERFACE_ROLE
    DEFAULT_WAREHOUSE = WINTERFACE_WH 
    MUST_CHANGE_PASSWORD = FALSE;
     
GRANT ROLE WINTERFACE_ROLE TO USER WINTERFACE_USER;

Snowflake Warehouse

A new warehouse is created with the below SQL. The below code uses an XSMALL warehouse size that autoscales to up to 2 clusters. Feel free to update these settings depending on your needs. The SQL also grants OPERATE and USAGE privileges on the warehouse to the WINTERFACE_ROLE.

It requires SYSADMIN or equivalent privileges to run.

-- create Winterface warehouse
USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS WINTERFACE_WH
    WITH WAREHOUSE_SIZE = 'XSMALL' 
        WAREHOUSE_TYPE = 'STANDARD' 
        AUTO_SUSPEND = 60 
        AUTO_RESUME = TRUE 
        MIN_CLUSTER_COUNT = 1 
        MAX_CLUSTER_COUNT = 2 
        INITIALLY_SUSPENDED = true
        COMMENT = 'Winterface for Snowflake Warehouse'
;

GRANT OPERATE,USAGE ON WAREHOUSE WINTERFACE_WH to role WINTERFACE_ROLE;

Snowflake Database

A distinct database is created with the below SQL. It also drops the PUBLIC schema in that database that can be created by default. Finally it grants all priviliges on the database to the WINTERFACE_ROLE.

It also requires SYSADMIN or equivalent privileges to run.

-- create Winterface database
USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS WINTERFACE_DB 
COMMENT = 'Winterface For Snowflake Database';

DROP SCHEMA IF EXISTS WINTERFACE_DB.PUBLIC;

GRANT OWNERSHIP ON DATABASE WINTERFACE_DB TO ROLE SYSADMIN REVOKE CURRENT GRANTS;
GRANT ALL PRIVILEGES ON DATABASE WINTERFACE_DB TO ROLE WINTERFACE_ROLE;