Configure your Snowflake account by performing the following steps:
Database and Table setup:
Create a new database called ENTERPRET_SHARED_DATA.
In the database’s PUBLIC schema, create a new table to export data to Enterpret.
Note: All the columns that begin with `ERC_` are mandatory columns. You can create any number of additional columns in the table and they will be ingested as metadata, for e.g TAGS and USER_PLAN columns in the following example.
Table Structure for Ticket Data
create or replace TABLE ENTERPRET_SHARED_DATA.PUBLIC.CUSTOMER_FEEDBACK_TICKETS (
ERC_ID VARCHAR(40) NOT NULL,
ERC_CREATED_AT TIMESTAMP_NTZ(9) NOT NULL,
ERC_ROW_CREATED_AT TIMESTAMP_NTZ(9) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
ERC_TICKET_DATA OBJECT NOT NULL,
USER_PLAN VARCHAR(40),
TAGS ARRAY,
constraint PK1 primary key (ERC_ID)
);
Table Structure for Surveys
create or replace TABLE ENTERPRET_SHARED_DATA.PUBLIC.CUSTOMER_FEEDBACK_SURVEYS (
ERC_ID VARCHAR(40) NOT NULL,
ERC_CREATED_AT TIMESTAMP_NTZ(9) NOT NULL,
ERC_ROW_CREATED_AT TIMESTAMP_NTZ(9) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
ERC_SURVEY_DATA OBJECT NOT NULL,
USER_PLAN VARCHAR(40),
TAGS ARRAY, constraint PK1 primary key (ERC_ID)
);
Table Structure for Reviews
create or replace TABLE ENTERPRET_SHARED_DATA.PUBLIC.CUSTOMER_FEEDBACK_REVIEWS (
ERC_ID VARCHAR(40) NOT NULL,
ERC_CREATED_AT TIMESTAMP_NTZ(9) NOT NULL,
ERC_ROW_CREATED_AT TIMESTAMP_NTZ(9) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
ERC_REVIEW TEXT NOT NULL,
USER_PLAN VARCHAR(40),
TAGS ARRAY,
constraint PK1 primary key (ERC_ID)
);
ERC_ID: Unique ID for the Feedback Record.
ERC_CREATED_AT: The creation timestamp of the feedback.
ERC_ROW_CREATED_AT: The creation timestamp of the row in the table; helps Enterpret track what data to ingest.
ERC_REVIEW: Text field, that contains the review data for the review table.
ERC_TICKET_DATA: A JSON object containing the ticket data adheres to the following contract.
message TicketData {
Conversation conversation = 1;
}
message Conversation {
repeated ConversationMessage msgs = 1;
}
message ConversationMessage {
string text = 1;
string actor = 2; // allowed values are "user", "agent", and "bot"
optional string actorID = 3;
}
Sample:
{
"conversation": {
"msgs": [
{
"text": "Hi need some help",
"actor": "user"
},
{
"text": "Please describe your issue",
"actor": "agent",
"actorID": "1234125"
}
]
}
}
ERC_SURVEY_DATA: An object containing the Survey responses, which adheres to the following contract
message Survey {
repeated SurveyQA responses = 1;
}
message SurveyQA {
string question = 1;
optional string answer = 2;
repeated string selectedOptions = 3;
}
Sample:
{
"responses": [
{
"question": "How likely is it that you would recommend us to a friend or colleague?",
"selectedOptions": [
"10"
]
},
{
"question": "Did Bre make your experience with us memorable?\n",
"answer": "Bre was great...but it took 4 separate requests to get it finally done....but thanks...I greatly appreciate your help ",
"selectedOptions": [
"5"
]
},
{
"question": "We love to hear it! What did Bre do to make your experience awesome?\n",
"selectedOptions": [
"Helpful",
"Polite"
]
}
]
}
Setup Role, User, and Permissions:
For restricting the permission to only the needed table or a secure view, we need to create a user and role for Enterpret and grant it the required permissions to the relevant resources. You can run the following queries in a Snowflake Worksheet for the ENTERPRET_SHARED_DATA database.
CREATE ROLE ENTERPRET_USER_ROLE;
CREATE USER ENTERPRET_USER password = '<STRONG_PASSWORD>';
GRANT ROLE ENTERPRET_USER_ROLE TO USER ENTERPRET_USER;
GRANT USAGE ON WAREHOUSE <WAREHOUSE_NAME> TO ENTERPRET_USER_ROLE;
GRANT USAGE ON DATABASE ENTERPRET_SHARED_DATA TO ROLE ENTERPRET_USER_ROLE;
GRANT USAGE ON SCHEMA PUBLIC TO ROLE ENTERPRET_USER_ROLE;
GRANT SELECT ON CUSTOMER_FEEDBACK_TICKETS TO ROLE ENTERPRET_USER_ROLE;
You have now set up ENTERPRET_USER having access only to the needed resources through the ENTERPRET_USER_ROLE associated with it.
Authentication:
For authentication, Enterpret generates a public-private RSA key pair and exposes the public-key in the product, which customers need to add to the `ENTERPRET_USER` that they created in the previous steps.
This can be done by running the following command.
ALTER USER ENTERPRET_USER SET RSA_PUBLIC_KEY = '<KEY_SHARED_BY_ENTERPRET>'
Add Snowflake Integration to Enterpret:
After setting up your Snowflake account, go to https://dashboard.enterpret.com/
Click “Sign in with Google” or log in with your work email.
Once you have logged in successfully, navigate to the integrations page using the icon on the menu bar to the left.
Click on the “+ New Integration” button.
Choose “Snowflake” from the presented options by clicking the “Continue” button in front of it.
Fill in the details in the form presented with the credentials you created in the section above and click on Connect.
Details of the fields:
Account Name: Name of your Snowflake account.
Database: Name of the Snowflake database.
Schema: Name of the Snowflake schema.
Warehouse: Name of the snowflake warehouse.
Table: Name of the table from which to ingest data.
User (Optional): Name of the Enterpret user created in the section above.
Role (Optional): Name of the Enterpret user role created in the section ab
You should now be able to see the Snowflake Connection in your list of integrations.
Finally, copy the public key mentioned below the integration and run the following command on Snowflake.
ALTER USER ENTERPRET_USER SET RSA_PUBLIC_KEY = '<KEY_SHARED_BY_ENTERPRET>'
You have successfully set up the Snowflake integration.