Snowflake

Telmai integrates with Snowflake to monitor data, identifying anomalies such as outliers and drifts while processing data outside of your data warehouse (DW) architecture to reduce the monitoring load on your Snowflake DW.

This guide outlines the steps for integrating Telmai with Snowflake.

Creating a connection

Authentication Options

Telmai supports only key pair-based connectivity to Snowflake, offering enhanced security compared to basic authentication. Below are the detailed steps to integrate Snowflake with Telmai using this method.

Setting up Authentication

Role and User Creation: If you have an existing user with the necessary permissions to access the database, schema, table, or view, skip to Step 2. However, the best practice is to create a separate role and user specifically for Telmai.
- Role: Telmai requires permissions for database connections, schema and table listings, metadata retrieval, and data selection in tables and views. These are managed through SELECT, USAGE, and MONITOR privileges in Snowflake. For references on creating roles and assigning appropriate privileges, please refer to 1, 2, and 3
- User: Create a user account for Telmai to access Snowflake. Assign the user to the role created above. Detailed steps for creating a user can be found here.
Generate an unencrypted key pair and assign it to the user following the instructions here
Assign the default role and warehouse for the user as described here
Once the role, user, and key pair are configured, enter the following parameters in the Telmai Connection Wizard:
- Snowflake Account
- Snowflake Warehouse
- Snowflake Database
- Snowflake Schema
- Snowflake Username
- Snowflake Private Key

Snowflake Configuration Template Script To simplify configuration, you can use a Telmai Template Script, enter your parameters, and execute in the Snowflake console.

Generate key pair as described here
Once the key is generated, format it to create a single-line Base64 string for usage. If your public key file is rsa_key.pem, use the commands below to produce a single-line string.

// For mac OS
grep -v "PUBLIC KEY" rsa_key.pub | tr -d '\n' > rsa_public_key_base64.txt
grep -v "PRIVATE KEY" rsa_key.p8 | tr -d '\n' > rsa_private_key_base64.txt

// For Windows
(Get-Content rsa_key.pub | Where-Object {$_ -notmatch 'PUBLIC KEY'}) -join '' |
    Set-Content -NoNewline rsa_public_key_base64.txt
(Get-Content rsa_key.p8 | Where-Object {$_ -notmatch 'PRIVATE KEY'}) -join '' |
    Set-Content -NoNewline rsa_private_key_base64.txt

Create role for Telmai to read the data. The script below lists the commands to be run. Fill in the right values for the fields which are marked.

-- Create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
-- The following part also needs to be found and changed in script to include your generated public key: "set rsa_public_key = 'MI...'"
set role_name = 'TELMAI_ROLE';
set user_name = 'TELMAI_USER';
set user_password = 'P@SSw0rd'; -- Change this password
set warehouse_name = 'COMPUTE_WH'; -- Change to your warehouse name
set database_name = 'TESTDB'; -- Change this database name to your database
set db_schema_name = 'TESTSCHEMA'; -- Change this schema name to your schema
set db_table_name = 'SUPPLIER'; -- Change this table name to your table

-- Change role for user / role steps
use role accountadmin;

-- Create role for telmai
create role if not exists identifier($role_name);

-- Create a user for telmai
create user if not exists identifier($user_name)
  password = $user_password
  default_role = $role_name
  default_warehouse = $warehouse_name;

-- Set public key for user
alter user identifier($user_name) set rsa_public_key = 'MI...';

grant role identifier($role_name) to user identifier($user_name);

-- Grant telmai role access to warehouse
grant USAGE on warehouse identifier($warehouse_name) to role identifier($role_name);
grant USAGE on database identifier($database_name) to role identifier($role_name);
grant USAGE on schema identifier($db_schema_name) to role identifier($role_name);
grant SELECT on table identifier($db_table_name) to role identifier($role_name);

-- The following statement if all schema tables need to be accessible from telmai
grant SELECT on ALL TABLES in schema identifier($db_schema_name) to role identifier($role_name);

Run the updated script in your Snowflake console.

Whitelisting Telmai IP’s

In some cases, Snowflake's security requires whitelisting the IPs from which connection to the database is allowed. For this purpose, Telmai provides a list of static IPs from which connections can be made. Please refer to Telmai IP list.

Please make sure you are whitelisting those IP addresses. These IPs are for the SaaS version of the product, which is running in the US West. They will be provided separately upon request for the private cloud or deployment in other regions.

Snowflake instances are open to every IP address by default, so no action is required. However, if you have set up network policies to restrict the IP addresses communicating with the Snowflake instance, you'll need to modify these policies to allow the Telmai IP address. There are two types of network policies:

Account level: Apply to all users unless overridden by a user-level policy.
User level: Apply only to specific users and override account-level policies.

Please refer to the Snowflake documentation for more information on modifying network policies.