Skip to main content

Snowflake

The Enterpret integration with Snowflake allows you to power analyses on Enterpret using feedback exported to Snowflake databases.

Written by Jack Divita

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: When the feedback event happened in the source system (business / event time).

  • ERC_ROW_CREATED_AT: When the row landed or was updated in the warehouse (ingestion time). Enterpret reads this column on every sync to decide what is "new since the last watermark," so it must be a persisted column on a base table — not a query-time computed expression. See Troubleshooting below for the full requirements and the common view-pattern pitfall.

  • 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 ",
    },
    {
      "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;

Note: The password is only required for the initial user creation in Snowflake and will not be used by Enterpret for authentication. You do not need to share this password with Enterpret. The actual authentication will be handled through the RSA key pair method described in the Authentication section.

You have now set up ENTERPRET_USER having access only to the needed resources through the ENTERPRET_USER_ROLE associated with it.Additionally, you'll need to whitelist Enterpret's IP addresses to allow data ingestion: - 3.140.143.140

3.134.21.149 These IP addresses must be whitelisted in your Snowflake network policies to allow Enterpret to access and ingest data from your Snowflake tables. Contact your Snowflake administrator if you need help configuring network policies.

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

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

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

Validate Your Connection

After you save your Snowflake integration, Enterpret automatically schedules the first data pull, which can take up to 24 hours to complete. To avoid waiting on that first cycle to discover a misconfiguration, you can use the Validate Connection button on the integration detail page to confirm your setup immediately.

The button appears on your Snowflake integration while it is in the ONBOARDING state, right next to the data ingestion progress bar.

What the validate button checks

Clicking Validate Connection runs a schema introspection query against the Snowflake table you configured. In a single check, it verifies:

  • Account name — your Snowflake account is reachable and spelled correctly.

  • RSA key authentication — the public key you added to ENTERPRET_USER matches the private key Enterpret holds.

  • Database, schema, and table — the objects exist and the configured names are correct.

  • Role and warehouse — the role has the required privileges and the warehouse is running (not suspended).

  • ERC column schema — the table contains the required columns: ERC_ID, ERC_ROW_CREATED_AT, and at least one of ERC_TICKET_DATA, ERC_SURVEY_DATA, or ERC_REVIEW.

This works even when the table is empty — the check inspects column metadata directly, so you don't need to land any rows first.

Verifying the sample data preview

If your table already has rows, Validate Connection also returns a Sample Data Preview (5 rows) so you can sanity-check what Enterpret will see on the first ingestion. Use this preview to confirm:

  • The expected ERC_ columns are present (ERC_ID, ERC_CREATED_AT, ERC_ROW_CREATED_AT, and your data column — ERC_TICKET_DATA, ERC_SURVEY_DATA, or ERC_REVIEW).

  • ERC_ID values are unique and look like the IDs from your source system.

  • ERC_CREATED_AT reflects when the feedback event happened in the source system, not when the row landed in Snowflake.

  • ERC_ROW_CREATED_AT reflects when the row was loaded or last updated in Snowflake. If every row shows the same "right now" value, see the Troubleshooting section above — your column is likely computed at query time and will silently break ingestion.

  • Any extra columns (e.g. USER_PLAN, TAGS, ACCOUNT_TIER) are populated as expected — these will be ingested as metadata.

Error messages

If the validation fails, a red error banner is shown at the bottom of the page with a human-readable message describing exactly what to fix. For example:

Code: ErrCodeBadConfig, Msg: snowflake fetcher: validate connection: Code: ErrCodeBadConfig, Msg: Could not connect to Snowflake — please validate the entered connection details

Common error scenarios and what to check

  • "Could not connect to Snowflake — please validate the entered connection details" — The account name, user, role, database, schema, or warehouse is incorrect. Re-check each field against your Snowflake admin console.

  • Snowflake authentication failed — The RSA public key on ENTERPRET_USER does not match the key Enterpret is holding. Re-copy the public key from the Enterpret integration page and re-run ALTER USER ENTERPRET_USER SET RSA_PUBLIC_KEY = '...'.

  • Snowflake account not found — The account identifier is incorrect. If your URL is https://example.snowflakecomputing.com, the account name is example, not the full URL.

  • Snowflake object not found — The configured database, schema, or table does not exist or your role does not have access. Verify the names and the GRANT statements from the role setup section above.

  • Snowflake warehouse not found or is suspended — The configured warehouse name is wrong, or the warehouse is suspended. Resume it or update the integration with a running warehouse.

  • Snowflake connection blocked by network policy — Your Snowflake network policy is blocking Enterpret. Allowlist Enterpret's IP addresses (3.140.143.140, 3.134.21.149) in your Snowflake network policy.

  • Snowflake connection timed out — Either the account name is unreachable, or the network policy is silently dropping the connection. Verify the account name first, then check the network policy.

Fix the underlying issue based on the message and click Validate Connection again. Once validation passes, the next scheduled ingestion will pick up your data automatically. If you still see errors, please reach out to support.

Handling Updated Records:

When updating existing records in your Snowflake table, keep in mind:

  • ERC_ID: This field serves as the unique identifier for each record. It should remain constant for any given record you wish to update.

  • ERC_ROW_CREATED_AT: This timestamp field helps Enterpret track updates to existing records. When updating a record:

    • Update this timestamp to reflect when the row was modified

    • Enterpret will use this field to identify and process updated records

    • The most recent timestamp will be used to determine the latest version of the record

For example, if you update a record with an existing ERC_ID:

  • Keep the same ERC_ID

  • Update the ERC_ROW_CREATED_AT to the current timestamp

  • Update any other fields as needed

Enterpret will automatically detect the updated record based on the new ERC_ROW_CREATED_AT timestamp and process the changes accordingly.

Troubleshooting:

Ingestion silently stalled — ERC_ROW_CREATED_AT not advancing

Symptoms. New rows are landing in your Snowflake source, but Enterpret has stopped picking them up. MIN(ERC_ROW_CREATED_AT) and MAX(ERC_ROW_CREATED_AT) over your table or view return the same value, and that value is "right now."

Why it happens. Enterpret's incremental fetcher uses ERC_ROW_CREATED_AT as a high-watermark on every sync, pulling rows where ERC_ROW_CREATED_AT > last_watermark. If the column is computed at query time instead of being persisted on the row, every row resolves to the same "right now" value at fetch time, the watermark never advances, and no rows come through.

Requirements for ERC_ROW_CREATED_AT

For incremental sync to work, the column must satisfy all of the following:

  • Persisted on a base table — value materialized once on insert/update and frozen afterwards.

  • Set to the current timestamp when a new row is inserted or an existing row is updated.

  • Stable for unchanged rows — no re-evaluation per query.

  • Never in the past for newly arriving rows.

Common pitfall: sysdate() or CURRENT_TIMESTAMP() inline in a view

❌ Never define ERC_ROW_CREATED_AT this way:

-- DO NOT do this
CREATE VIEW VW_FEEDBACK AS
SELECT
  id          AS ERC_ID,
  event_time  AS ERC_CREATED_AT,
  sysdate()   AS ERC_ROW_CREATED_AT,   -- re-evaluates every query, breaks ingestion
  ...
FROM base_table;

✅ Instead, project a persisted load/update timestamp from the underlying base table (e.g. _loaded_at, updated_at, or your loader's sync timestamp):

-- Correct
CREATE VIEW VW_FEEDBACK AS
SELECT
  id          AS ERC_ID,
  event_time  AS ERC_CREATED_AT,
  _loaded_at  AS ERC_ROW_CREATED_AT,   -- persisted on the base table
  ...
FROM base_table;

How to verify

Run this against your table or view:

SELECT MIN(ERC_ROW_CREATED_AT), MAX(ERC_ROW_CREATED_AT) FROM <your_table_or_view>;

The values should span a real time range that reflects when rows were actually loaded into your warehouse. If MIN equals MAX equals the current time on every run, the column is broken — fix the underlying view or insert logic before re-running the sync.

Related Articles
Zendesk Chat
Kustomer
Snowflake - Users & Accounts
Data Export API
Hubspot User and Account Integration
Did this answer your question?