Snowflake

Connecting DinMo to Snowflake: Step-by-Step Guide

  • Step 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;

We currently offer connection to Snowflake using a user/password or Key Pair.

Snowflake will progressively decommission the user / password method to ensure greater security. For all new source set-ups, we strongly recommend using the Key Pair method. More information on the official Snowflake documentation.

  • Step 2: On Snowflake, create a DinMo user and grant it with the required permissions: This is done by running the script below. Make sure to 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. Make sure to adapt your code according to the authentification method you chose:

    • If you chose user/password, make sure to uncomment related lines in the code below (lines 48 and 51)

    • If you chose Key Pair, make sure to run the lines of code proposed by DinMo after generating your public key

    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 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 DINMO_SEGMENTS;
CREATE SCHEMA IF NOT EXISTS DINMO_STATS;
CREATE SCHEMA IF NOT EXISTS DINMO_PREDICTIONS;

-- 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 DINMO_SEGMENTS TO ROLE identifier($dinmo_access_role_name);
GRANT ALL ON SCHEMA DINMO_STATS TO ROLE identifier($dinmo_access_role_name);
GRANT ALL ON SCHEMA DINMO_PREDICTIONS TO ROLE identifier($dinmo_access_role_name);

-- Create DinMo's user

-- SET user_password='<strong password>'; -- Uncomment this line if you have chosen the Password authentification method.

CREATE USER IF NOT EXISTS identifier($dinmo_service_user)
-- PASSWORD=$user_password -- Uncomment this line if you have chosen the Password authentification method.
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);

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

  2. Select Snowflake from the list of available sources.

  3. Provide the required information:

  • The account identifier can be found in your Snowflake URL or by following the official documentation of Snowflake.

    • Note: 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.

  • Choose the authentication method you wish to use, either user/password or Key Pair.

    • If you chose Password, uncomment the two lines of code referring to the password (lines 48 and 51) and enter the password in DinMo

    • If you chose “Key Pair”, click on “Generate a Public Key”. Simply copy the block of code and run it in your Snowflake console, without changing anything.

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

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

  2. 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.

    Troubleshooting

    • Connection Test Fails: Verify the host, port, database, user, and password are correct.

    • Privilege Errors: Ensure DINMO_USER has USAGE and SELECT privileges on the necessary schemas and tables.

    • Access Issues with Recreated Tables:

      • Confirm that default privileges are set for the user who creates the tables (e.g., the dbt user).

      • Ensure the ALTER DEFAULT PRIVILEGES command was executed by the correct user.

    • Missing Create View On Schema: This type of error can occur if the technical datasets have been created by a different role/user. In that case, you can manually run these lines of code: 

      GRANT CREATE VIEW ON SCHEMA <database>.DINMO_SEGMENTS TO ROLE DINMO_ACCESS_ROLE;
GRANT CREATE VIEW ON SCHEMA <database>.DINMO_STATS TO ROLE DINMO_ACCESS_ROLE;
GRANT CREATE VIEW ON SCHEMA <database>.DINMO_DELTA_STORAGE TO ROLE DINMO_ACCESS_ROLE;
GRANT CREATE VIEW ON SCHEMA <database>.DINMO_PREDICTIONS TO ROLE DINMO_ACCESS_ROLE;

    • If using multiple databases, make sure to always use fully qualified table locations (e.g., database.schema.table)."

PreviousPostgreSQLNextDatabricks

Last updated