Skip to main content
Sign in Menu

Snowflake Authentication and Data Sync

AllVoices Support Team
  • Updated

Authentication Overview

To authenticate with Snowflake (JWT), you need:

  1. Subdomain - The subdomain for your Snowflake account, typically part of the account URL (e.g., xy12345 in xy12345.snowflakecomputing.com).
  2. Account Identifier - The unique identifier for your Snowflake account.
  3. User Name - The Snowflake username used to authenticate with the account.
  4. Private Key - The private RSA key in PEM format used to sign your JWT for authentication. It corresponds to the public key registered in Snowflake.

This guide will walk you through obtaining and generating these credentials within Snowflake.

Prerequisites:

  • You must have an account with Snowflake (JWT).

Instructions:

Step 1: Finding your Subdomain

  1. Log in to your snowflake account.
  2. Click on your account profile in the bottom-left corner, then under Account, select the account you wish to connect to.
  3. Click on View account details for the selected account.
  4. A pop-up window will appear displaying all your Account Details.
  5. Your Subdomain is the part before snowflakecomputing.com in the Account/Server URLfield. For example, in KXOPMLU-RE00572.snowflakecomputing.com, the subdomain is KXOPMLU-RE00572.

Step 2: Finding your Account Identifier

  1. From the same Account Details table above, your Account Identifier will be displayed within the Account Identifier field.

Step 3: Finding your User Name

  1. From the same Account Details table above, your User Name will be displayed within the User Name field.

Step 4: Generating your Private Key

  1. Generate the RSA private key with the following command:

openssl genrsa -out rsa_key.p8 2048

  1. Extract the corresponding public key:

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

  1. To view the Private Key, run the following:

cat rsa_key.p8

Step 5: Uploading your Public Key

  1. From your Snowflake account, navigate to Projects > Worksheets then click on the **+**button from the top right corner.
  2. Run the following SQL command to assign the Public Key to your user:

ALTER USER <your_username> SET RSA_PUBLIC_KEY = '<public_key_contents>';

Once you have your Subdomain, Account Identifier, User Name and Private Key:

  1. Open the integration form where you need to authenticate with Snowflake (JWT).
  2. Enter your Subdomain, Account Identifier, User Name and Private Key in their designated fields.
  3. Submit the form, and you should be successfully authenticated.

Data Sync Overview

Database setup is attached below which should be done after auth setup.

  1. Create a role for Allvoices to use called ALLVOICES_ROLE 
CREATE ROLE IF NOT EXISTS ALLVOICES_ROLE;

2. Create a small warehouse for Allvoices, the size of the warehouse can be adjusted according to need but we can start with x-small size

CREATE WAREHOUSE IF NOT EXISTS ALLVOICES_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

3. Create a database and schema for Allvoices to use

CREATE DATABASE IF NOT EXISTS ALLVOICES_SYNC;
CREATE SCHEMA IF NOT EXISTS ALLVOICES_SYNC.RAW;

4. Grant permission to the ALLVOICES_ROLE we created in step one to the resources we created above

GRANT USAGE ON WAREHOUSE ALLVOICES_WH TO ROLE ALLVOICES_ROLE;
GRANT USAGE ON DATABASE  ALLVOICES_SYNC TO ROLE ALLVOICES_ROLE;
GRANT USAGE ON SCHEMA    ALLVOICES_SYNC.RAW TO ROLE ALLVOICES_ROLE;

5. Create a landing table where Allvoices data will be stored

CREATE OR REPLACE TABLE ALLVOICES_SYNC.RAW.JSON_DATA (
  entity      STRING,
  id          STRING,
  updated_at  TIMESTAMP_NTZ,
  payload     VARIANT,
  loaded_at     TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

6. Grant permission to the Allvoices role on the table we just created above

GRANT INSERT, SELECT ON TABLE ALLVOICES_SYNC.RAW.JSON_DATA TO ROLE ALLVOICES_ROLE;

7. Tie the created role to the username that Allvoices uses for authentication

GRANT ROLE ALLVOICES_ROLE TO USER <username>;

8. (Optional) Specify how snowflake should cluster data in order to improve querying performance

ALTER TABLE ALLVOICES_SYNC.RAW.JSON_DATA
  CLUSTER BY (entity, DATE(updated_at));

 

Notes

  1. Your data of cases (all related attributes of case form and employees) along with aggregated charts data will sync into your snowflake table.
  2. Case data is like CSV export data in JSON where each attribute can be queried. 
  3. Admin can go to integration page and hit "refresh" to trigger data sync manually. Otherwise, system should trigger data sync every 24 hours. 

Related articles