Set up Pendo Data Sync to Snowflake

Overview

Pendo Data Sync enables you to copy data tables directly from your Pendo subscription to your Snowflake account, with no developer resources required.

Pendo data is stored in your Snowflake environment, where you manage it. You're responsible for all storage and computing costs related to Data Sync.

Prerequisites

Before you can sync data to Snowflake, ensure you meet the following requirements:

• You have Pendo admin permissions to set up a destination.
• You have SECURITYADMIN, SYSADMIN, and ACCOUNTADMIN roles in Snowflake.
• If you're using Snowflake network policies to restrict IP addresses, allow traffic from Pendo public IP addresses.

Step 1. Access the public key

Before you set up the Snowflake destination, you need to retrieve the public key available in Pendo.

1. In Pendo, go to Settings > Data Sync.

2. Select Connect to destination.
3. Select Snowflake from the list of destinations, then select Next: Configure destination.

4. The Public key field is populated by default. Copy this to use later.

5. Leave the page to configure your Snowflake account.

Step 2. Create entities in Snowflake

Before Pendo can sync data to your Snowflake account, it needs a secure way to access and write data to your environment. In Snowflake, you will:

• Create a user that is associated with a public key generated by Pendo.

• Create a warehouse and database specific to be used exclusively by Pendo.

• Create a role that is granted the following permissions in Snowflake.

When a schema is created, PENDO_TRANSFER_ROLE is granted OWNERSHIP privileges. For ongoing syncs, the role only requires USAGE permission on the schema, so you can revoke OWNERSHIP after the schema is created.

The following table summarizes the permissions you need:

1. In Snowflake, create a SQL worksheet. Provide a descriptive name, for example Pendo-Data-Sync. For information on how to manage worksheets in Snowflake, see their documentation.
2. Copy the following SQL script into the worksheet to configure your role, user, warehouse, and database for the Pendo transfer service. The worksheet doesn't need to point to any database/schema. Modify the SQL script as needed:
   • Replace USERNAME, DATABASE_NAME and WAREHOUSE_NAME with the values of your choice. Make note of these names as you'll need to provide them later.
   • Because these are identifiers, they must start with a letter (A-Z, a-z) or an underscore (_) and contain only letters, underscores, and decimal digits (0-9), and dollar signs ($).
   • Snowflake automatically stores and resolves these identifiers as uppercase, regardless of casing input.
   • The USERNAME provided shouldn't conflict with the name of an existing user in your Snowflake account.
   • Replace <YOUR_PUBLIC_KEY> with the public key copied from Pendo in step 1.

SET role_name = 'PENDO_TRANSFER_ROLE'; -- DO NOT CHANGE - Pendo requires this specific role name
SET user_name = 'CUSTOM_USER_NAME'; -- replace CUSTOM_USER_NAME with a value of your choice - the name should not conflict with an existing user
SET database_name = 'CUSTOM_DATABASE_NAME'; -- replace CUSTOM_DATABASE_NAME with a value of your choice
SET warehouse_name = 'CUSTOM_WAREHOUSE_NAME'; -- replace CUSTOM_WAREHOUSE_NAME with a value of your choice

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 user for the transfer service
CREATE USER IF NOT EXISTS IDENTIFIER($user_name)
RSA_PUBLIC_KEY='<YOUR_PUBLIC_KEY>'; -- replace '<YOUR_PUBLIC_KEY>' with the public key provided on the Data Sync Destination configuration page

--Set default role and warehouse to new user
ALTER USER IDENTIFIER($user_name) SET DEFAULT_ROLE = $role_name;
ALTER USER IDENTIFIER($user_name) SET DEFAULT_WAREHOUSE = $warehouse_name;
ALTER USER IDENTIFIER($user_name) SET TYPE = service;

GRANT ROLE IDENTIFIER($role_name) TO USER IDENTIFIER($user_name);

USE ROLE sysadmin;

--Create the warehouse for the pendo transfer service
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($warehouse_name)
    warehouse_size = xsmall
    warehouse_type = standard
    auto_suspend = 60
    auto_resume = true
    initially_suspended = true;

--Create the database where the pendo data will transfer to
CREATE DATABASE IF NOT EXISTS IDENTIFIER($database_name);

--Grant service role access to the warehouse
GRANT USAGE, MONITOR ON WAREHOUSE IDENTIFIER($warehouse_name) TO ROLE IDENTIFIER($role_name);

--Grant service access to the database
GRANT CREATE SCHEMA, MONITOR, USAGE ON DATABASE IDENTIFIER($database_name) TO ROLE IDENTIFIER($role_name);

USE ROLE accountadmin;

--Grant service ability to create integration
GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE IDENTIFIER($role_name);

3. Run the script.

Snowflake warehouse size

The size of the Snowflake warehouse you use for Data Sync affects transfer performance. For most single‑subscription setups or initial testing, we recommend starting with an XS warehouse. If you’re syncing large volumes of data, multiple applications, or multiple Pendo subscriptions, consider using a larger warehouse or separate warehouses per subscription.

For more information about Snowflake warehouse sizing and behavior, see the Snowflake documentation.

Step 3. Complete destination setup in Pendo

Provide Pendo with the names of the user, warehouse and database you created in Step 2, as well as your Snowflake account and organization IDs.

1. In Pendo, go to Settings > Data Sync.
2. Select Connect to destination.
3. Select Snowflake, then select Next: Configure destination. This opens the page you copied the Public Key from in Step 1.
4. In the relevant fields, enter the following:
   • Username. The username created by the setup script. This should match the variable name USERNAME from Step 2.
   • Organization name. This can be found in Snowflake by viewing your account information.
   • Account name. This can be found in Snowflake by viewing your account information.
   • Database. The name of the database created by the setup script. This should match the variable name DATABASE_NAME from Step 2.
   • Compute warehouse. The name of the warehouse created by the setup script. This should match the variable name WAREHOUSE_NAME from Step 2.
5. Select Next: Validate destination. This may take several minutes.
6. Once validation is complete, select Next: Select data.
7. Select the data transfers you want to activate. Choose from account metadata, visitor metadata and any event data from your applications.

8. Select Connect destination to create the Snowflake destination in Pendo. This step may take several minutes.

9. To verify that data is being successfully transferred to Snowflake, use METADATA$START_SCAN_TIME to determine when the last transfer ran.

   For example, use the Snowflake query:

   SELECT METADATA$FILENAME, METADATA$FILE_LAST_MODIFIED, METADATA$START_SCAN_TIME FROM @PENDO_STAGE (file_format => PENDO_AVRO_FORMAT, pattern=>'.*[.]avro') LIMIT 100;

   This returns details about the most recent files loaded into your Snowflake environment.

Data transfers

Once your transfers are activated, Pendo begins preparing your Snowflake environment and scheduling regular data loads. The data transfer process works as follows:

• Pendo creates a Snowflake Storage Integration, and uses it to create External Stages for each activated transfer.

• Pendo creates schemas, tables, and procedures for each activated transfer in the database you provided.

• Pendo will activate your nightly data transfers. Recurring updates occur every night, incorporating data from the previous day, finalized data from eight days prior, as well as any retroactive updates.

• If you've chosen to sync accounts and visitors, a full sync of accounts, visitors, and associated metadata will sync into Snowflake. Every night, any new accounts or visitors or updates to existing metadata will sync.

• After the transfers are activated, the privileges to CREATE INTEGRATION and CREATE SCHEMA can be revoked from the user you provided. If you want to activate new transfers in the future, you’ll need to grant the user the CREATE SCHEMA privilege again.

• Data transfers are executed one app at a time. If you've enabled Data Sync for many apps, the transfer process can take longer as they are processed sequentially.

Note: You can sync up to 12 months of historical data per application. To set the start date within this range, go to Settings > Data Sync > Manage syncs and use the Available from column. To request more historical data, contact Pendo Support.

Update your Snowflake destination

If you want to update the user, database, or warehouse for your existing Snowflake destination, you need to re-run the setup script (Step 2) with the updated value(s) and then update the field(s) in Pendo.

1. Edit and run the setup script as outlined in Step 2 with your updated user, database or warehouse name.
2. In Pendo, go to Settings > Data Sync.
3. Select Manage destination.
4. Update the relevant fields in your destination.
5. Select Validate and update destination.

If you want to update your Snowflake organization or account name, you need to delete your Snowflake destination in Pendo and create a new one.

1. In Pendo, go to Settings > Data Sync.
2. Select Manage destination.
3. Select Delete destination.
4. Select Delete to confirm deletion of the destination.

After deletion, your data transfers are automatically deactivated. 

To set up a new Snowflake destination, repeat Steps 1 - 3.

Configuring Data Sync to Snowflake with multiple Pendo subscriptions

If you have multiple Pendo subscriptions and want to configure Data Sync to Snowflake for each subscription, you need to create a unique user in Snowflake for each Pendo subscription. This is required because each Pendo subscription generates its own unique public key and a Snowflake user should only be associated with one public key at a time (or two keys temporarily during key rotation).

However, you can share other Snowflake resources (role, database, warehouse) across subscriptions to simplify management and reduce costs.

Data from each Pendo subscription is isolated in separate schemas automatically created by Pendo as SUB_{SUBID}_APP_{APPID}.