Set up Snowflake for integration with Zenskar

🐕‍🦺 Setup guide

In this guide, we will explore how to set up Snowflake for integration with Zenskar. The outcome of this guide will be a SQL script that does the following for integration with Zenskar:

• Create a Snowflake role
• Create a Snowflake user
• Create a Snowflake warehouse
• Create a Snowflake database
• Grant the role and user appropriate privileges and access

⚙️ Define identifiers

Define some identifiers to refer to Snowflake objects in the SQL script.

In Snowflake SQL statements, in addition to referring to objects by name (see Identifier Requirements), you can also use a string literal, session variable, bind variable, or Snowflake Scripting variable to refer to an object. For example, you can use a session variable that is set to the name of a table in the FROM clause of a SELECT statement.

To use an object name specified in a literal or variable, use IDENTIFIER().

IDENTIFIER( { string_literal | session_variable | bind_variable | snowflake_scripting_variable } )

<string_literal>: string identifying the name of the object:

• The string must either be enclosed by single quotes ('name') or start with a dollar sign ($name).
• The string literal can be a fully-qualified object name (e.g. 'db_name.schema_name.object_name' or $db_name.schema_name.object_name).

set zenskar_role = '<zenskar_role>';
set zenskar_username = '<zenskar_user>';
set zenskar_warehouse = '<zenskar_warehouse>';
set zenskar_database = '<zenskar_database>';
set zenskar_schema = '<zenskar_schema>';
set zenskar_password = '<password>';

📖

Identifiers and placeholders

In the above snippet, the structure of the statement is set IDENTIFIER = 'PLACEHOLDER';. For example, in the statement set zenskar_role = '<zenskar_role>';, zenskar_role is the identifier whereas <zenskar_role> is the placeholder. Replace the placeholder with appropriate values before executing the SQL script.

⚙️ Create a role for Zenskar

Create a role for Zenskar in Snowflake. Only a user assigned with the securityadmin role can create a new role.

SECURITYADMIN (aka Security Administrator)

Role that can manage any object grant globally, as well as create, monitor, and manage users and roles. More specifically, this role:

• Is granted the MANAGE GRANTS security privilege to be able to modify any grant, including revoking it.
• Inherits the privileges of the USERADMIN role via the system role hierarchy (i.e. USERADMIN role is granted to SECURITYADMIN).

The following script snippet uses the securityadmin role privileges to create a role dedicated to Zenskar (identifier($zenskar_role)). Further, the snippet grants the privileges of the sysadmin role to the newly created Zenskar role.

SYSADMIN (aka System Administrator)

Role that has privileges to create warehouses and databases (and other objects) in an account.

If, as recommended, you create a role hierarchy that ultimately assigns all custom roles to the SYSADMIN role, this role also has the ability to grant privileges on warehouses, databases, and other objects to other roles.

 use role securityadmin;
 create role if not exists identifier($zenskar_role);
 grant role identifier($zenskar_role) to role SYSADMIN;

⚙️ Create a user for Zenskar

Create a user dedicated to Zenskar.

 create user if not exists identifier($zenskar_username)
 password = $zenskar_password
 default_role = $zenskar_role
 default_warehouse = $zenskar_warehouse;

To facilitate querying immediately after a session is initiated, Snowflake supports specifying a default warehouse for each individual user. The default warehouse for a user is used as the warehouse for all sessions initiated by the user.

A default warehouse can be specified when creating or modifying the user, either through the web interface or using CREATE USER/ALTER USER.

⚙️ Grant Zenskar role to Zenskar user

The Zenskar role we configured earlier, must be assigned to the Zenskar user.

grant role identifier($zenskar_role) to user identifier($zenskar_username);

⚙️ Create a warehouse and a database for Zenskar

Only a sysadmin has privileges to create warehouses and databases:

use role sysadmin;

📖

Definition

A virtual warehouse is a cluster of compute resources. A warehouse is needed to execute certain types of SQL statements because it provides resources such as CPU, memory, and local storage.

 create warehouse if not exists identifier($zenskar_warehouse);

📖

Definition

All data in Snowflake is maintained in databases. Each database consists of one or more schemas, which are logical groupings of database objects, such as tables and views. Snowflake does not place any hard limits on the number of databases, schemas (within a database), or objects (within a schema) you can create.

  create database if not exists identifier($zenskar_database);

⚙️ Grant usage access on the warehouse to Zenskar

The following steps grant usage access on warehouse to the Zenskar role.

grant USAGE  
 on warehouse identifier($zenskar_warehouse)  
 to role identifier($zenskar_role);

⚙️ The final annotated SQL script

The final annotated SQL script is shown below. You can replace the placeholders with real values and execute the script in your Snowflake environment.

-- set all required identifiers
set zenskar_role = '<zenskar_role>';
set zenskar_username = '<zenskar_user>';
set zenskar_warehouse = '<zenskar_warehouse>';
set zenskar_database = '<zenskar_database>';
set zenskar_schema = '<zenskar_schema>';
set zenskar_password = '<password>';

 begin;

 -- create zenskar role
 use role securityadmin;
 create role if not exists identifier($zenskar_role);
 grant role identifier($zenskar_role) to role SYSADMIN;

 -- create zenskar user
 create user if not exists identifier($zenskar_username)
 password = $zenskar_password
 default_role = $zenskar_role
 default_warehouse = $zenskar_warehouse;

 grant role identifier($zenskar_role) to user identifier($zenskar_username);

 -- change role to sysadmin to create warehouse and database
 use role sysadmin;

 -- create zenskar warehouse
 create warehouse if not exists identifier($zenskar_warehouse);

 -- create zenskar database
 create database if not exists identifier($zenskar_database);

 -- grant zenskar warehouse access
 grant USAGE
 on warehouse identifier($zenskar_warehouse)
 to role identifier($zenskar_role);

 commit;

 begin;

 USE DATABASE identifier($zenskar_database);

 -- create schema for zenskar data
 -- CREATE SCHEMA IF NOT EXISTS identifier($zenskar_schema);

 commit;