Set up Pendo Data Sync to Snowflake

Use this article to connect Pendo Data Sync to Snowflake. You create a dedicated role, user, warehouse, and database in Snowflake, install a public key that Pendo generates, and enter the connection details in Pendo. Pendo copies the data into your Snowflake account, so your organization owns the data and pays for its storage and compute.

Pendo has two Snowflake destination types. This article is for the Snowflake destination, whose form asks for a Host, Port, Database, Schema, and Username. If your destination is labeled Snowflake (legacy), or its form asks for a Role, Organization name, Account name, and Compute warehouse, see Set up Pendo Data Sync to Snowflake (legacy).

Note: This Snowflake destination is available for Pendo subscriptions in the US and EU regions only.

How authentication works

Pendo connects to Snowflake with key-pair authentication. You don't share a password with Pendo.

• Pendo generates the key pair and keeps the private key. You only ever see the public key.
• You attach the public key to a Snowflake service user. Snowflake is phasing out passwords for service users, so key-pair authentication is the supported method.
• Pendo signs in as that user and uses its default role and default warehouse. You control what Pendo can reach through the grants on that role.

Before you begin

Before you begin, make sure that you:

• Are a Pendo subscription admin or have the Configure Data Sync permission.
• Have this Snowflake destination turned on for your subscription. Pendo enables it per subscription; contact your Pendo account representative to enable it.
• Can use the SECURITYADMIN and SYSADMIN roles in Snowflake, or have a Snowflake admin who can.
• Know your Snowflake account hostname. It ends in .snowflakecomputing.com, for example myorg-myaccount.snowflakecomputing.com.
• Know whether your Snowflake account uses network policies. If it does, you allow Pendo's IP address in Step 3.
• Use only letters, numbers, and underscores in the names you choose for the role, user, warehouse, database, and schema. Snowflake stores unquoted names in uppercase.

Each Pendo subscription needs its own Snowflake user, because each subscription has its own public key. You can share the role, warehouse, and database across subscriptions.

Step 1. Start the destination in Pendo

Pendo generates the public key when you open the destination form, so start in Pendo.

1. In Pendo, go to Settings > Data Sync.
2. In the Data Sync setup flow, choose Snowflake and select Select destination.
3. Under Enter the destination credentials, copy the Public key. You paste it into the script in Step 2.

Keep the setup flow open while you configure Snowflake. A key that you don't use expires after 30 days. If your key has expired, reopen the setup flow to get a new one and update the Snowflake user with it.

Step 2. Create the role, user, warehouse, and database in Snowflake

This script creates a dedicated role, service user, X-Small warehouse, and database for Pendo, and grants the role only what Data Sync needs.

1. In Snowflake, open a new SQL worksheet.
2. Copy the following script into the worksheet. Replace <PUBLIC_KEY_FROM_PENDO> with the key you copied in Step 1.
3. Optionally, change the names in the SET lines. To use a warehouse or database you already have, enter its name and the script skips creating it. Make a note of the user and database names; you enter them in Pendo in Step 4.
4. Run the script.

SET user_name      = 'PENDO_DATASYNC_USER';
SET role_name      = 'PENDO_DATASYNC_ROLE';
SET warehouse_name = 'PENDO_DATASYNC_WH';
SET database_name  = 'PENDO_DATASYNC_DB';

USE ROLE SECURITYADMIN;

-- Create the role for the transfer service
CREATE ROLE IF NOT EXISTS IDENTIFIER($role_name);
GRANT ROLE IDENTIFIER($role_name) TO ROLE SYSADMIN;

-- Create the service user with the public key from Pendo
CREATE USER IF NOT EXISTS IDENTIFIER($user_name)
  RSA_PUBLIC_KEY    = '<PUBLIC_KEY_FROM_PENDO>'
  DEFAULT_ROLE      = $role_name
  DEFAULT_WAREHOUSE = $warehouse_name
  TYPE              = SERVICE;
GRANT ROLE IDENTIFIER($role_name) TO USER IDENTIFIER($user_name);

USE ROLE SYSADMIN;

-- Create the warehouse and database for Pendo data
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($warehouse_name)
  WAREHOUSE_SIZE      = XSMALL
  WAREHOUSE_TYPE      = STANDARD
  AUTO_SUSPEND        = 60
  AUTO_RESUME         = TRUE
  INITIALLY_SUSPENDED = TRUE;
CREATE DATABASE IF NOT EXISTS IDENTIFIER($database_name);

-- Grant the role access to the warehouse and database
GRANT USAGE ON WAREHOUSE IDENTIFIER($warehouse_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE SCHEMA, MONITOR, USAGE ON DATABASE IDENTIFIER($database_name) TO ROLE IDENTIFIER($role_name);

Pendo creates the schema if it doesn't exist. To use a schema you already have, grant the role ALL PRIVILEGES on that schema instead of relying on CREATE SCHEMA.

The Pendo role must keep ownership of the tables and stages it creates in the schema. Don't grant FUTURE OWNERSHIP on tables in that schema to another role.

Step 3. Allow Pendo's IP address if you use network policies

Skip this step if your Snowflake account has no network policies. To check, run SHOW NETWORK POLICIES;. If it returns no rows, go to Step 4.

Add Pendo's IP address for your region to the network policy that applies to the Pendo user. You can update an existing policy, or create one and attach it to the Pendo user only.

USE ROLE SECURITYADMIN;
CREATE NETWORK POLICY PENDO_DATASYNC_POLICY
  ALLOWED_IP_LIST = ('136.114.218.147/32');
ALTER USER PENDO_DATASYNC_USER SET NETWORK_POLICY = PENDO_DATASYNC_POLICY;

Step 4. Complete the destination in Pendo

1. Return to the Snowflake destination form in Pendo.
2. Enter the Host. This is your Snowflake account hostname, with no protocol or path, for example myorg-myaccount.snowflakecomputing.com.
3. Leave Port as 443.
4. Enter the Database, Schema, and Username from Step 2. Enter only the schema name. Pendo creates the schema if it doesn't exist.
5. Select Validate, then continue to select data.

There's no warehouse or role field. Pendo uses the user's default warehouse and default role, which the script in Step 2 sets.

If validation fails, select View error to see the cause, correct it in place, and validate again. For common causes, see Troubleshooting in this article.

Step 5. Select data to sync

Choose the Pendo applications to sync. You can also choose account and visitor metadata. Data selection is optional during initial setup and can be completed later.

The first recurring sync begins overnight after the destination is configured and data is selected. Subsequent syncs run approximately every 24 hours. Each recurring sync incrementally loads new and updated rows, so full reloads aren't required.

Data lands as tables in the schema you entered in Step 4. For the available tables and fields, see Data Sync schema definitions.

Rotate the Snowflake key

Rotate the key when your security policy requires it. Your current key keeps working until you promote the new one, so syncs continue during rotation.

1. In Pendo, go to Settings > Data Sync.
2. Select Manage destination for your Snowflake destination.
3. Select Rotate key.
4. Select Generate new key. Pendo shows the New public key and the SQL that installs it. The new key expires in 30 days if you don't promote it.
5. In Snowflake, run the SQL shown under SQL to stage new key. It adds the new key to the free key slot on the Pendo user and leaves the current key in place.
6. In Pendo, select Promote new key. When you see Key rotation complete, Pendo connects with the new key.
7. In Snowflake, run the SQL shown under SQL to remove old key slot.

If promotion fails, the destination still uses the current key. Check that the staged key is installed on the Pendo user, then select Promote new key again.

Update or delete the destination

To change the host, port, database, schema, or username, go to Settings > Data Sync, select Manage destination, update the fields, and select Validate and update destination. The public key can't be edited here. To change it, rotate the key.

To delete the destination, select Manage destination > Delete destination. Deleting a destination turns off its recurring syncs, and they aren't recreated automatically. Don't delete the destination to fix a validation error; resolve the error in place instead.

Troubleshooting

Next steps

• For the tables and fields Pendo syncs, see Data Sync schema definitions.
• For an overview of destinations and sync timing, see Overview of Pendo Data Sync.