Comment on page
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 = xsmallwarehouse_type = standardauto_suspend = 60auto_resume = TRUEinitially_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 nameDINMO_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 informationSET INPUT_ROLE='ACCOUNTADMIN'; -- or any other role with the needed permissionsSET dinmo_service_user='DINMO_SERVICE_USER'; -- a specific role with the minimal permissions is created for DinMo to operateSET 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 nameSET 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 schemaSET DATA_MODEL='<database>'; -- the database in which your data is storedSET default_namespace='<database.schema>'; -- this schema should be located in the database mentioned aboveSET 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 grantsUSE ROLE identifier($INPUT_ROLE);-- Create a role for DinMoCREATE ROLE IF NOT EXISTS identifier($dinmo_access_role_name);-- Grant permissions to the roleGRANT 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 useCREATE 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 schemasGRANT 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 userCREATE USER IF NOT EXISTS identifier($dinmo_service_user)PASSWORD=$user_passwordDEFAULT_ROLE=$dinmo_access_role_nameDEFAULT_WAREHOUSE=$COMPUTE_WAREHOUSEDEFAULT_NAMESPACE=$default_namespace;-- Assign the role to the userGRANT 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.
Last modified 10d ago