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.

Connecting to decube is as easy as providing us with credentials to your Snowflake instance. We require the following credentials:

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

The user decubeuser and role decuberole is created and given the proper privileges for monitoring.
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.

PreviousChangelog NextRedshift

Last updated 2 months ago

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

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