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

Data Quality
Capability

Freshness

Volume

Schema Drift

Field Health

Custom SQL

Job Failure

Catalog
Capability

Data Profiling

Data Preview

Data Recon
Capability

Add Recon

Connecting to decube is as easy as providing us with credentials to your Snowflake instance. There are currently 2 authentication options, Password and Key-pair authentication.

Password

  • username

  • password

  • account_name

  • warehouse_name

  • role_name

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

  • private_key upload

  • account_name

  • warehouse_name

  • role_name

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 Name

Copy the account url and it should look like

https://<account_number>.<region>.snowflakecomputing.com.

Your account name will be the <account_number>.<region>

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 user_password = 'changethispassword'; -- Change this password

-- !!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)
password = $user_password
default_role = $role_name;
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. Steps 3 needs to be repeated for every database that needs to be monitored.

Migrate from Password to Key-pair authentication

This section is applicable for you if you are currently using the Password authentication method to connect Snowflake to the decube platform before 31 March 2025 and will need to migrate to the Key-pair authentication method before the Password method is deprecated.

  1. You will need to generate the key-pair based on this section Key Pair.

  2. Go to My Account > Data sources tab. Click on Modify button.

  1. In the Modify Data Source, change the Authentication method selection from Passwordto Key-pair.

  1. Click on Upload file for Key-pair authentication to upload the file. Once the file has been uploaded, then click on Test this connection.

  1. Once the Test this connection operation is successful, you can now click on Modify button. The new credential will now be saved.

Last updated