Skip to main content
All CollectionsIntegrations
Snowflake - Users & Accounts
Snowflake - Users & Accounts

The Enterpret integration with Snowflake allows you to power analyses using user & account data exported to Snowflake databases.

Team Enterpret avatar
Written by Team Enterpret
Updated over 6 months ago

Configure your Snowflake account by performing the following steps:

Database and Table setup:

Create a new database called ENTERPRET_SHARED_DATA (if it does not already exist).

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/ACCOUNT_PLAN columns in the following example.

Table Structure for User Data

create or replace TABLE ENTERPRET_SHARED_DATA.PUBLIC.USERS ( 
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(),
USER_PLAN VARCHAR(40),
TAGS ARRAY,
constraint PK1 primary key (ERC_ID)
);

Table Structure for Account Data

create or replace TABLE ENTERPRET_SHARED_DATA.PUBLIC.ACCOUNTS ( 
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(),
ACCOUNT_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.

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; // Use a different role name if this already exists for another integration

CREATE USER ENTERPRET_USER password = '<STRONG_PASSWORD>'; // If it does not already exist
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:

  1. After setting up your Snowflake account, go to https://dashboard.enterpret.com/

  2. Click “Sign in with Google” or log in with your work email.

  3. Once you have logged in successfully, navigate to the integrations page using the icon on the menu bar to the left.

  4. Click on the “+ New Integration” button.

  5. Choose “Snowflake” from the presented options by clicking the “Continue” button in front of it.

  6. Select the kind of integration (User/Account) you want to set up.

  7. 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

  8. You should now be able to see the Snowflake Connection in your list of integrations.

  9. 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.

Did this answer your question?