# Snowflake

## Supported Capabilities

{% tabs %}
{% tab title="Supported Capabilities" %}
**General**

* **Metadata** — metadata extraction and display of asset information (tables, columns, schemas). Types collected: Schema, Table, Column, View
* **Sync Tags** — syncs Snowflake tags to assets in the Catalog
* **Sync Objects Descriptions** — syncs object descriptions from Snowflake to the Catalog
* **Profiling** — data profiling on the Profiler tab
* **Preview** — sample data preview
* **Data Quality** — data quality monitoring and observability
* **External Table** — external tables, enabling queries on data in external storage (e.g., S3, ADLS) without loading it into the database
* **View Table** — view tables, which are virtual tables based on SQL queries

**Data Quality Monitors**

* Freshness
* Volume
* Field Health
* Custom SQL
* Schema Drift

**Lineage**

* **View Table Lineage** — tracks virtual tables (views) and their data dependencies
* **External Table Lineage** — tracks the relationship between raw files in cloud storage (S3/ADLS) and the virtualized relational schema in your data platform
* **SQL Query Lineage** — maps data movement through SQL queries (SELECT, JOIN, INSERT, etc.)
  {% endtab %}

{% tab title="Not Supported" %}
**General**

* Configurable Collection
* Stored Procedure

**Data Quality Monitors**

* Job Failure

**Lineage**

* Foreign Key Lineage
* Stored Procedure Lineage
  {% endtab %}
  {% endtabs %}

Below are the steps to Connect snowflake to Decube with key pair authentication method.

### Key Pair

Refer to the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/key-pair-auth) for more information on how to generate a key pair. Please provide only the **unencrypted version** of private keys as the key is encrypted on Decube's end. The following credentials are required upon adding new connection:

* Username
* Public Key File
* [Account Identifier](#account-identifier)
* Warehouse Name
* Role Name

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-9d863039fa909b2f4b7c7d16ef34c87e029ffead%2Fimage.png?alt=media" alt=""><figcaption><p>Snowflake</p></figcaption></figure>

The `source name` will be for you to differentiate and recognize particular sources within the decube application.

### Prerequisite

To ensure a smooth experience configuring the connection.

1. The user `decubeuser` and role `decuberole` is created and given the proper privileges for monitoring.
2. Steps 4 or 5 below needs to be repeated for every `database` that needs to be monitored.

### Account Identifier

If your Snowflake UI differ, please refer to [Snowflake Documentation](https://docs.snowflake.com/en/user-guide/admin-account-identifier#format-1-preferred-account-name-in-your-organization) on how to get your Account Identifier.

Account Identifier can be found by clicking on your profile icon in Snowflake and going to account details.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-ffc4dc4cacd4ff92b26511ab79eea04d4ae76f9f%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

Copy the Account Identifier which is shown below. This should look like `<org-name>-<account-name>`

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-65469e074ebd250869c6244d76aa78c2ecc409a7%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

### Configuring User, Role and Privileges

1\. On a Snowflake worksheet, copy the commands below and modify as necessary. We have the user called `DECUBEUSER` and role called DECUBEROLE

```sql
set role_name = 'DECUBEROLE';
set user_name = 'DECUBEUSER';
set public_key = 'changethispublickey'; -- Change this to the public key you generated

-- !!Choose an existing warehouse name if you don't want to create a new warehouse!!
set warehouse_name = 'DECUBE_WH';

-- Creates the role and user and grant the role to the user
CREATE ROLE IF NOT EXISTS identifier($role_name);
CREATE USER IF NOT EXISTS identifier($user_name) DEFAULT_ROLE = $role_name;
ALTER USER identifier($user_name) SET RSA_PUBLIC_KEY = $public_key;
GRANT ROLE identifier($role_name) TO USER identifier($user_name);

-- Grants the base SNOWFLAKE database to the role
grant imported privileges on database "SNOWFLAKE" to role identifier($role_name);

-- This will create a warehouse if the chosen warehouse does not exists
CREATE warehouse IF NOT EXISTS identifier($warehouse_name)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 5
auto_resume = true
initially_suspended = true
max_concurrency_level = 30
statement_timeout_in_seconds = 300
statement_queued_timeout_in_seconds = 1200;

-- This grants the role access to the warehouse
grant USAGE on warehouse identifier($warehouse_name) to role identifier($role_name);
```

2\. The `source` type for the database has to be known. To get this information, from Snowflake dashboard click on *`Data`* -> *`Databases`.* On the left panel, a list of `Databases` can be seen along with `Source`.

3\. If `Source` if *local*, modify `database_name`, copy into a worksheet and run the commands.

```sql
set database_name = 'changethisdatabase'; -- The database to grant access to
set role_name = 'DECUBEROLE'; -- or differently if you modified it previously

-- Read-only access to database
grant USAGE on database identifier($database_name) to role identifier($role_name);
grant USAGE on all schemas in database identifier($database_name) to role identifier($role_name);
grant USAGE on future schemas in database identifier($database_name) to role identifier($role_name);
grant SELECT on all tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on future tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on all views in database identifier($database_name) to role identifier($role_name);
grant SELECT on future views in database identifier($database_name) to role identifier($role_name);

-- Only if external tables are to be ingested into Decube
grant SELECT on all external tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on future external tables in database identifier($database_name) to role identifier($role_name);

```

4\. Step 3 needs to be repeated for every `database` that needs to be monitored.