Snowflake

Connecting DinMo to Snowflake: Step-by-Step Guide

1. Optional: Create a dedicated DinMo Warehouse on Snowflake to better manage the resources allocated. To do that, run the following script on your Snowflake environment.

   CREATE warehouse IF NOT EXISTS identifier('DINMO_WAREHOUSE')
   warehouse_size = xsmall 
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = TRUE
   initially_suspended = TRUE;

2. On Snowflake, create a DinMo user and grant it with the required permissions: This is done by running the script below. Edit the variables < > at the beginning of the script with your Snowflake connection information. If you created a dedicated warehouse in step 1, put its name DINMO_WAREHOUSE on the corresponding part of the script. Remember the user name, and password. Then, run the script on your Snowflake environment. Do not hesitate to adapt the script in order to restrict the permissions of the DinMo user to specific tables and schemas, instead of giving it full access to the entire database.

   -- Edit the variables between < > with your snowflake connection information
   SET INPUT_ROLE='ACCOUNTADMIN'; -- or any other role with the needed permissions
   SET dinmo_service_user='DINMO_SERVICE_USER'; -- a specific role with the minimal permissions is created for DinMo to operate
   SET user_password='<strong password>';
   SET dinmo_access_role_name='DINMO_ACCESS_ROLE'; -- you can modify this name
   
   
   -- If you previously created a warehouse in step 1, you can replace 'COMPUTE_WH' with its name
   SET COMPUTE_WAREHOUSE='COMPUTE_WH';
   
   -- Specify the database where your data to be synced is stored, the database where DinMo will store technical data,, and define a default schema
   SET DATA_MODEL='<database>'; -- the database in which your data is stored
   SET default_namespace='<database.schema>'; -- this schema should be located in the database mentioned above
   SET DINMO_INTERNAL_DATABASE='DINMO_DB'; -- this database will be created by the script. There, DinMo will store technical data. You can modify this name. 
   
   -- Set role for grants
   USE ROLE identifier($INPUT_ROLE);
   
   -- Create a role for DinMo
   CREATE ROLE IF NOT EXISTS identifier($dinmo_access_role_name);
   
   -- Grant permissions to the role
   GRANT USAGE ON WAREHOUSE identifier($COMPUTE_WAREHOUSE) TO ROLE identifier($dinmo_access_role_name);
   GRANT USAGE ON DATABASE identifier($DATA_MODEL) TO ROLE identifier($dinmo_access_role_name);
   GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier($DATA_MODEL) TO ROLE identifier($dinmo_access_role_name);
   GRANT SELECT ON ALL TABLES IN DATABASE identifier($DATA_MODEL) TO ROLE identifier($dinmo_access_role_name);
   GRANT SELECT ON ALL VIEWS IN DATABASE identifier($DATA_MODEL) TO ROLE identifier($dinmo_access_role_name);
   GRANT USAGE ON FUTURE SCHEMAS IN DATABASE identifier($DATA_MODEL) TO ROLE identifier($dinmo_access_role_name);
   GRANT SELECT ON FUTURE TABLES IN DATABASE identifier($DATA_MODEL) TO ROLE identifier($dinmo_access_role_name);
   GRANT SELECT ON FUTURE VIEWS IN DATABASE identifier($DATA_MODEL) TO ROLE identifier($dinmo_access_role_name);
   
   -- Create the database with the schemas DinMo will use
   CREATE DATABASE IF NOT EXISTS identifier($DINMO_INTERNAL_DATABASE);
   USE DATABASE identifier($DINMO_INTERNAL_DATABASE);
   CREATE SCHEMA IF NOT EXISTS DINMO_DELTA_STORAGE;
   CREATE SCHEMA IF NOT EXISTS DINMOSEGMENTS;
   CREATE SCHEMA IF NOT EXISTS _DINMOSTATS;
   
   -- Grant full permissions only to these DinMo schemas
   GRANT ALL PRIVILEGES ON DATABASE identifier($DINMO_INTERNAL_DATABASE) TO ROLE identifier($dinmo_access_role_name);
   GRANT ALL ON SCHEMA DINMO_DELTA_STORAGE TO ROLE identifier($dinmo_access_role_name);
   GRANT ALL ON SCHEMA DINMOSEGMENTS TO ROLE identifier($dinmo_access_role_name);
   GRANT ALL ON SCHEMA _DINMOSTATS TO ROLE identifier($dinmo_access_role_name);
   
   -- Create DinMo's user
   CREATE USER IF NOT EXISTS identifier($dinmo_service_user)
   PASSWORD=$user_password
   DEFAULT_ROLE=$dinmo_access_role_name
   DEFAULT_WAREHOUSE=$COMPUTE_WAREHOUSE
   DEFAULT_NAMESPACE=$default_namespace;
   
   -- Assign the role to the user
   GRANT ROLE identifier($dinmo_access_role_name) TO USER identifier($dinmo_service_user);

3. On DinMo, go to the workspace settings, then to the Source tab, and click "Add New Source".

4. Select Snowflake from the list of available sources.

5. Provide the required information:

   

   • The account identifier can be found in your Snowflake URL. For example, if your account URL is https://xxx.europe-west2.gcp.snowflakecomputing.com/, you should enter "xxx.europe-west2.gcp" as the account identifier. Remember to exclude "https://" and ".snowflakecomputing.com/" from the URL.

   • Enter the name of the Snowflake database containing the business data you wish to sync to external platforms using DinMo. If you have multiple databases, you can create multiple sources for each one of them.

   • Enter the name of the Snowflake database DinMo will use to store technical data. If you did not modify the script in step 2, it should be DINMO_DB.

   • Fill in the appropriate Warehouse: COMPUTE_WH is the default Warehouse on Snowflake, but you could also decide to use a specific one, for instance, if you went through step 1, DINMO_WAREHOUSE.

   • Enter the role you entered in the script above. If you did not change the name of the role in the script, it should be DINMO_ACCESS_ROLE.

   • Enter the credentials (user and password) used to connect to the Snowflake user account. These are the ones created in the script in step 1.

   • Finally, give a name to your source. This name will be displayed on DinMo.

6. Once the informations are filled, hit "Continue".

7. Test the connection to ensure the entered information is valid. If the test is successful, click on "Continue" to proceed with the creation of the source. If not, double-check the information entered on the previous page to ensure accuracy.