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.
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 userCREATEROLEIFNOTEXISTS identifier($role_name);CREATEUSERIFNOTEXISTS identifier($user_name)password= $user_passworddefault_role = $role_name;GRANTROLE identifier($role_name) TO USER identifier($user_name);-- Grants the base SNOWFLAKE database to the rolegrant imported privileges ondatabase"SNOWFLAKE"torole identifier($role_name);-- This will create a warehouse if the chosen warehouse does not existsCREATE warehouse IFNOTEXISTS identifier($warehouse_name)warehouse_size = xsmallwarehouse_type =standardauto_suspend =5auto_resume = trueinitially_suspended = truemax_concurrency_level =30statement_timeout_in_seconds =300statement_queued_timeout_in_seconds =1200;-- This grants the role access to the warehousegrant USAGE on warehouse identifier($warehouse_name) torole 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.
setdatabase_name='changethisdatabase'; -- The database to grant access toset role_name ='DECUBEROLE'; -- or differently if you modified it previously-- Read-only access to databasegrant USAGE ondatabase identifier($database_name) torole identifier($role_name);grant USAGE on all schemas indatabase identifier($database_name) torole identifier($role_name);grant USAGE on future schemas indatabase identifier($database_name) torole identifier($role_name);grantSELECTon all tables indatabase identifier($database_name) torole identifier($role_name);grantSELECTon future tables indatabase identifier($database_name) torole identifier($role_name);grantSELECTon all views indatabase identifier($database_name) torole identifier($role_name);grantSELECTon future views indatabase identifier($database_name) torole identifier($role_name);
4. Steps 3 needs to be repeated for every database that needs to be monitored.