Snowflake

Adding Snowflake to your decube connections helps your team to find relevant datasets, understand their quality via incident monitoring and apply governance policies via our data catalog.

Supported Capabilities

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

Not Supported

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

Key Pair

Refer to the Snowflake documentation 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

• Warehouse Name

• Role Name

Snowflake

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

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

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

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.

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.