> ## Documentation Index
> Fetch the complete documentation index at: https://zenskar.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Set up Snowflake for integration with Zenskar

> This guide provides a step-by-step SQL script to set up Snowflake for integration with Zenskar, including creating roles, users, warehouses, and databases, and granting necessary privileges.

## 🐕🦺 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](https://docs.snowflake.com/en/sql-reference/identifiers) to refer to Snowflake objects in the SQL script.

<Info>
  In Snowflake SQL statements, in addition to referring to objects by name (see [Identifier Requirements](https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html)), you can also use a string literal, session variable, bind variable, or [Snowflake Scripting variable](https://docs.snowflake.com/en/developer-guide/snowflake-scripting/variables.html) 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.
</Info>

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

```sql theme={null}
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).

```sql theme={null}
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>';
```

<Info>
  ### 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.
</Info>

## ⚙️ Create a role for Zenskar

[Create a role](https://docs.snowflake.com/en/user-guide/security-access-control-overview) for Zenskar in Snowflake. Only a user assigned with the `securityadmin` role can create a new role.

<Info>
  ### 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).
</Info>

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.

<Info>
  ### 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.
</Info>

```
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.

```sql theme={null}
create user if not exists identifier($zenskar_username)
password = $zenskar_password
default_role = $zenskar_role
default_warehouse = $zenskar_warehouse;
```

<Info>
  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.
</Info>

## ⚙️ Grant Zenskar role to Zenskar user

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

```sql theme={null}
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:

```sql theme={null}
use role sysadmin;
```

<Info>
  ### 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.
</Info>

```sql theme={null}
create warehouse if not exists identifier($zenskar_warehouse);
```

<Info>
  ### 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.
</Info>

```sql theme={null}
create database if not exists identifier($zenskar_database);
```

## ⚙️ Grant usage access on the warehouse to Zenskar

The following steps [grant usage access](https://docs.snowflake.com/en/user-guide/security-access-control-privileges) on warehouse to the Zenskar role.

```sql theme={null}
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.

```sql theme={null}
-- 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;
```