Snowflake Technical Integration Details

Overview

Integrating Snowflake and MaestroQA allows for customers to export data from MaestroQA and import data into MaestroQA.

For information on what can be exported to Snowflake, check this Data Dictionary. For information on the data ingestion process, check this Help Article.

Snowflake Admin Setup

First - you'll need MaestroQA's IPs to whitelist in Snowflake. You MaestroQA POC can provide these.

To let us send data to a new or existing database, you'll need to build a new user for us to login with, a new role with proper permissions, and a new schema that we can write to.

To use key pair authentication as described in the Snowflake documentation, first generate an encrypted private key with the following command, which stores the key in a file named 'rsa_key.p8':

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

Then generate the public key with the following command, which stores the key in a file named 'rsa_key.pub':

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Finally, generate the public key's fingerprint with the following command, and save the second line of the output to enter on the next page:

openssl rsa -pubin -in rsa_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64

Now, open a connection to your Snowflake instance and go through the following steps.

If you'd like to create a new database, run the following:

CREATE DATABASE MAESTRO_DB;

Then within that database, create a new schema

CREATE SCHEMA MAESTRO;

Next we'll create the user and role, followed by granting the required permissions:

CREATE USER MAESTRO_USER; 
ALTER USER MAESTRO_USER SET RSA_PUBLIC_KEY=<your_public_key>; 
CREATE ROLE MAESTRO_ROLE; 
REVOKE ALL PRIVILEGES ON DATABASE MAESTRO_DB FROM MAESTRO_ROLE; G
RANT USAGE ON DATABASE MAESTRO_DB TO MAESTRO_ROLE; 
GRANT USAGE ON WAREHOUSE <your_warehouse_name> TO MAESTRO_ROLE; 
GRANT ROLE MAESTRO_ROLE TO USER MAESTRO_USER; 
GRANT ALL ON SCHEMA maestro TO MAESTRO_ROLE; GRANT OWNERSHIP ON SCHEMA maestro TO ROLE MAESTRO_ROLE; 
-- the below operation is only needed if you don't have "auto resume" enabled on the warehouse 
GRANT OPERATE ON WAREHOUSE <your_warehouse_name> TO MAESTRO_ROLE;

Snowflake Details to Enter in MaestroQA

Once you've completed the steps above, any Admin in MaestroQA can enter the following details to complete the integration:

Account (https://<account>.<region>.snowflakecomputing.com)
Cloud Region ID
User (from steps above)
Warehouse (from steps above)
Database (from steps above)
Schema (from steps above)
User Role (from steps above)
Private Key (from steps above)
Encryption Password (from steps above)
Public Key Fingerprint (from steps above)