Add Snowflake as a data source in Pendo Predict

This article explains how to connect your Snowflake instance to Pendo Predict so you can use Snowflake as a source when building predictive models.

Pendo Predict supports two access patterns for Snowflake:

• Read access - Predict can discover approved databases, schemas, tables, views, and columns, and read data from approved tables or views.
• Read/write access - Predict can read, insert, and update records in a target Snowflake table.

Pendo Predict authenticates to Snowflake using key-pair authentication. Your Snowflake administrator assigns the public key to the Snowflake user, and Predict uses the matching private key to authenticate.

Requirements

After your Snowflake administrator completes the setup, configure the Snowflake connection in Pendo Predict with the following values:

Note: The role configured in Predict must also be granted to the Snowflake user. Granting a role to a user allows that user to perform the operations allowed by the privileges granted to the role.(Snowflake Documentation)

Recommended Snowflake objects

Pendo recommends creating a dedicated Snowflake service user and role for Predict instead of reusing a personal or administrative account. Snowflake defines TYPE = SERVICE users for services or applications that interact with Snowflake without human intervention. (Snowflake Documentation)

Example names used in this article:

• PENDO_PREDICT_USER
• PENDO_PREDICT_ROLE

You can replace these names with values that match your organization’s naming standards.

Access summary

(Snowflake Documentation)

Step 1: Generate a key pair

Generate a private/public key pair for the Predict Snowflake user.

To generate an unencrypted private key:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

To generate an encrypted private key with a passphrase:
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Copy the generated public  and private keys. Use the public key when creating or updating the Snowflake user. Use the matching private key when configuring the connection in Pendo Predict. If the private key is encrypted, enter the passphrase in Predict. Snowflake notes that the passphrase protects the private key and is not sent to Snowflake. 

Note: Note - when copying the public key, make sure to exclude the PEM headers ( copy the value between the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----).(Snowflake Documentation)

Step 2: Create a dedicated Snowflake user and role

Run the following commands using a Snowflake role with permission to create users, roles, and grants.

-- Step 1: Create the dedicated, restricted role
CREATE ROLE IF NOT EXISTS PENDO_PREDICT_ROLE;
-- Step 2: Create the dedicated service user
CREATE USER IF NOT EXISTS PENDO_PREDICT_USER
  TYPE = SERVICE
  DEFAULT_ROLE = PENDO_PREDICT_ROLE
  DEFAULT_WAREHOUSE = <WAREHOUSE_NAME> -- optional 
  RSA_PUBLIC_KEY = '<PUBLIC_KEY_WITHOUT_PEM_HEADERS>';
GRANT ROLE PENDO_PREDICT_ROLE TO USER PENDO_PREDICT_USER;

(Snowflake Documentation)

Step 3: Grant access to source data

Minimal read privileges

Predict requires the following Snowflake privileges for read access:

Snowflake requires the role to have privileges on the parent database and schema to operate on objects inside a schema. Snowflake also defines warehouse USAGE as the privilege that enables query execution on the warehouse. (Snowflake Documentation)

Grant read access to specific tables and views

Use this option when Predict should read only selected tables.

-- Step 3: Grant data container access (Usage permissions)
GRANT USAGE ON WAREHOUSE <WAREHOUSE_NAME> TO ROLE PENDO_PREDICT_ROLE;
GRANT USAGE ON DATABASE <DB_NAME> TO ROLE PENDO_PREDICT_ROLE;
GRANT USAGE ON SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE PENDO_PREDICT_ROLE;
-- Step 4: Grant read permissions on a specific table/object
GRANT SELECT ON TABLE <DB_NAME>.<SCHEMA_NAME>.<TABLE_NAME> TO ROLE PENDO_PREDICT_ROLE;
GRANT SELECT ON VIEW <DB_NAME>.<SCHEMA_NAME>.<VIEW_NAME>
  TO ROLE PENDO_PREDICT_ROLE;

Repeat the schema and table grants for each table Predict should access.

Optional: Grant read access to all current and future tables in a schema

Use this option only when Predict should be allowed to read every current and future table/view in a schema.

GRANT SELECT ON ALL TABLES IN SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE PENDO_PREDICT_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE PENDO_PREDICT_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE PENDO_PREDICT_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE PENDO_PREDICT_ROLE;

Snowflake notes that GRANT SELECT ON ALL TABLES/VIEWS IN SCHEMA grants access to existing tables/views only. To grant access to tables/views created later, use GRANT SELECT ON FUTURE TABLES/VIEWS IN SCHEMA. (Snowflake Documentation)

Optional: Grant write access to target table(s)

Use this section if Predict should write results back to Snowflake.

Predict requires read/write access to the target table. This allows Predict to read existing records, insert new records, and update existing records.

Minimal write privileges

Grant write access to a target table

GRANT USAGE ON WAREHOUSE <WAREHOUSE_NAME> TO ROLE PENDO_PREDICT_ROLE;
GRANT USAGE ON DATABASE <DB_NAME> TO ROLE PENDO_PREDICT_ROLE;
GRANT USAGE ON SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE PENDO_PREDICT_ROLE;
GRANT SELECT, INSERT, UPDATE ON TABLE <DB_NAME>.<SCHEMA_NAME>.<TABLE_NAME> TO ROLE PENDO_PREDICT_ROLE;

Repeat the schema and table grants for each table Predict should write to.

Step 4: Configure the connection in Pendo Predict

1. Click the Sources->Data connector on the Pendo Predict app.

   

2. Select New Source, then choose Snowflake.

   

3. Enter a name that other users will recognize easily

   

    

4. Add your Snowflake user name and private key, Snowflake account address and the name of the database you want to work with. Before saving the connection in Predict:
   • The Snowflake role must either be configured as the user’s default role or entered in the Predict connection settings.
   • The warehouse must either be configured as the user’s default warehouse or entered in the Predict connection settings

5. Select Authorize to connect Snowflake to Pendo Predict.

   

Step 2. Manage the Snowflake source

As the owner of a data source, you can manage its access and ensure the connection is functioning correctly. This includes sharing the source with additional users and testing the connection.

• To test the connection, select the More options icon (three dots), then choose Test connection.

• To share the source, select the More options icon, then choose Share. When shared, other users can use the Salesforce source when building predictive models.