Snowflake
This article outlines the integration steps for Telmai with Snowflake.
Introduction
Snowflake is a multi-cloud data warehouse optimized for analytics workloads and requiring little maintenance.
Telmai monitors the data in the Snowflake instance to identify anomalies like outliers and drifts. Telmai is designed to read your data once and process it outside your DW architecture to reduce any monitoring load on your Snowflake DW.
This article outlines the integration steps with Snowflake.
Authentication Options
Snowflake offers following authentication options (Reference)
Basic authentication using username and passcode
Key pair authentication
Multi-Factor Authentication
Federated Auth and SSO.
Telmai currently supports key pair-based connectivity to Snowflake which is considered as an enhanced authentication mechanism when compared to basic authentication. Below we will go in detail on how to integrate Snowlake and Telmai using this option.
Setting up Authentication
Role and User Creation: If you already have an existing user which you want to use pls skip to step #2. You can use the user as long as it has appropriate permissions to access the database/schema/table or view. However the best practice is to create a separate role and user for use by Telmai.
Role: Telmai requires permissions to connect to the database, list schemas, list tables and views in the schema, get metadata about the tables and views, select the data in tables and views. These are covered through SELECT, USAGE, MONITOR privileges in Snowflake. For references on how to create roles and assign appropriate privileges pls refer to 1, 2 and 3.
User: a username which would be used by Telmai to access Snowflake. For reference on how to create user pls refer to this. Assign the user to the role created above.
Create an unencrypted key-pair and assign the same to the user (here)
Assign the default role and warehouse for the user (here)
Now you have all needed parameters for connecting Telmai to Snowflake, which you can enter in 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 Snowflake console.
1: Generate key pair as described in here (Step 2)
2: Download script (download)
3: Modify the parameters in the script :
set user_password = '4556-$%%-oooo-#@'; -- 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
alter user identifier($user_name) set rsa_public_key = 'MII...'; -- Change to the public key to one generated in step 1 above
3: Run the update script in your snowflake console.
Whitelisting Telmai IP’s
In some cases Snowflake's security requires whitelisting of the IPs from which connection to the database is allowed. For this purpose Telmai provides list of static IPs from which connections can be made:
34.82.72.116 35.247.60.101 35.247.73.61 35.227.135.231 34.168.182.169 34.120.179.207 34.117.190.198 35.199.181.100 104.196.250.120 34.83.96.76 34.168.229.206 34.118.198.67 34.105.45.137
Please make sure you are whitelisting those IP addresses. These IPs are for the SaaS version of the product, running in the US West. For the private cloud or other regions deployment they will be provided separately upon request.
By default, Snowflake instances are open to every IP address, so no action is required. However, if you have set up network policies to restrict the IP addresses communicating with Snowflake instance, you'll need to modify these policies to allow the Telmai IP address. There are two types of network policies : account level and user level.
Account level policies apply to all users unless the policy is overridden by a user level policy. User level policies only apply to the users that the policy is assigned and user level policies will override account level policies. Please refer to the Snowflake documentation for more information on modifying network policies.
[TODO] Support for Change data capture and batch processing
Last updated
