This article outlines the integration steps for Telmai with Snowflake.
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.
- 1.Basic authentication using username and passcode
- 2.Key pair authentication
- 3.Multi-Factor Authentication
- 4.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.
- 1.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.
- 4.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.
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:
220.127.116.11 18.104.22.168 22.214.171.124 126.96.36.199 188.8.131.52 184.108.40.206 220.127.116.11 18.104.22.168 22.214.171.124 126.96.36.199 188.8.131.52 184.108.40.206 220.127.116.11
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