Snowflake

Load your Amplitude event data into your Snowflake account. You can set up recurring syncs through the Amplitude UI, as well as manually start a sync of your historical data.

Considerations

• Depending on your company's network policy, you may need to add these IP addresses to your allowlist so Amplitude's servers can access your Snowflake instance:

  • Amplitude US IP addresses:
    • 52.33.3.219
    • 35.162.216.242
    • 52.27.10.221
  • Amplitude EU IP addresses:
    • 3.124.22.25
    • 18.157.59.125
    • 18.192.47.195

Limits

• Maximum running time for a single Snowflake SQL query is 12 hours.
• Each project can have only one Snowflake Export destination for each data type. This means one destination for events and one destination for merged IDs per project.

Reuse existing Snowflake credentials

When you create a new Snowflake export or import, Amplitude lets you select previously saved credentials from your organization instead of entering them again. Reusable credentials include connections from other exports, imports, and connections made in other projects (if you have permission).

To reuse existing credentials, select Use existing credentials at the credential entry step when creating a new connection. Amplitude lists all saved credentials from your organization. To reuse credentials from another project, you need permission to create Data Warehouse connections in both projects.

Set up the integration

Creating a recurring data export is a three-step process you can handle yourself. Each sync typically completes within five to ten minutes, though timing may vary depending on system load and data volume. This method also lets you watch jobs.

To set up a recurring export of your Amplitude data to Snowflake, follow these steps:

1. In Amplitude Data, click Catalog and select the Destinations tab.

2. In the Warehouse Destinations section, click Snowflake.

3. Under Export Data to Snowflake, choose which data to include in this export: Export events ingested today and moving forward, Export all merged Amplitude IDs, or export both. For events, you can also specify filtering conditions to export only events that meet certain criteria.

4. Review the Event table and Merge IDs table schemas and click Next.

5. In the Snowflake Credentials For Amplitude section, enter the following information:

   • Account Identifier: The account identifier on your Snowflake account, in the format <org-name>-<account-name>. For more information, refer to Snowflake's account identifier documentation{:target="_blank" rel="noopener noreferrer"}.
   • Warehouse: The warehouse Amplitude uses to load the data. Use a dedicated warehouse for Amplitude data to prevent load capacity issues with other Snowflake integrations. Sharing a warehouse can disrupt your other Snowflake operations.
   • Database: The database where Amplitude stores data. Dedicate this database specifically to Amplitude data.
   • Role: The role that the connection uses to write this data. The default value of role is AMPLITUDE only.
   • Username: The username Amplitude uses to connect to the Snowflake account.

   Amplitude offers password-based and key pair authentication for Snowflake.

To use password authentication, select the Password option and enter your password in the Password field. Key pair authentication (Recommended): To use key pair authentication, select the Key pair option and click Generate Key. To use key pair authentication, provide the organization and account names in the format ORGNAME-ACCOUNTNAME.

6. Copy the autogenerated SQL query and run it in Snowflake to give Amplitude the proper permissions.
7. Click Next. Amplitude attempts to upload test data using the credentials you entered. If the upload is successful, click Finish.

Amplitude automatically sends all future events to Snowflake.

From here, Amplitude generates micro-batch files and loads them to customer-owned Snowflake accounts on a best-effort basis. Exports typically run every 10 minutes, but may run less frequently depending on system load and data volume. You can typically see the data in your Snowflake accounts within 20 minutes after Amplitude receives the events, though timing may vary.

Export historical Amplitude data to Snowflake

To export your historical data from Amplitude into Snowflake, navigate to the integration, then click Export Data and select a date range.

This process can take from a single day to several weeks. It depends on your data volume, warehouse size, cluster count, network bandwidth, and the number of concurrent historical data exports you currently have, among other factors.

Reduce your Snowflake computation costs

To reduce Snowflake computation costs when receiving data from Amplitude, try these methods:

• Modify warehouse size and number of clusters for your dedicated Amplitude warehouse. For shorter export cadences (15 to 30 minutes), try starting with xsmall or small. Then upgrade as needed.
• When backfilling data into Snowflake, start with a small warehouse and upgrade as needed.
• You can also try reducing the auto suspend time to 60s. This option might not be available within the Snowflake UI, but you can manually set it through a direct Snowflake query.

Snowflake export format

Data locations

Amplitude exports data to your Snowflake account using the following naming convention:

For example, if your organization ID is 12345 and your project ID is 67890:

• Events: DB_12345.SCHEMA_67890.EVENTS_67890.
• Merged Users: DB_12345.SCHEMA_67890.MERGE_IDS_67890.

Event table

Event table schema

The Event table schema includes the following columns:

Event table clustering

The exported events table uses these clustering keys by default (in order):

1. TO_DATE(EVENT_TIME)
2. TO_DATE(SERVER_UPLOAD_TIME)
3. EVENT_TYPE
4. AMPLITUDE_ID

This optimizes query performance for time-based queries. You can modify the clustering keys to match your query patterns.

Merged User table

Merged User table schema

The Merged User table schema contains the following:

Merged User table clustering

Amplitude clusters the merged IDs table by DATE_TRUNC('HOUR', MERGE_SERVER_TIME). This optimizes queries that filter by when user merges occurred. You can modify the clustering keys to match your query patterns.