Amplitude users now have the ability to load their Amplitude event data into their Snowflake account. You can set up recurring syncs through the Amplitude UI, as well as manually initiate a sync of your historical data.
NOTE: This feature is available to all paid customers irrespective of region.
Set up a recurring data export to Snowflake
Creating a recurring data export is a simple, three-step process you can handle yourself. Each sync will complete within five to ten minutes, though often it's much closer to real time. And this method allows you to watch jobs.
To set up a recurring export of your Amplitude data to Snowflake, follow these steps:
NOTE: You will need admin privileges in Amplitude, as well as a role that allows you to enable resources in Snowflake.
- Navigate to Sources and Destinations → Destinations.
- Under Add More Destinations …, click the Snowflake panel.
The Export Data to Snowflake page will open to the Getting Started tab. - Under Export Data to Snowflake, select the data you’d like to export.
NOTE: This is currently limited to events ingested today and in the future.
- Review the Event table and Merge IDs table schemas and click Next >. The Set Up Export tab will open.
- In the Snowflake Credentials For Amplitude section, enter the following information:
-
-
-
- Account Name: This is the account name on your Snowflake account. It’s the first part of your Snowflake URL, before ‘snowflakecomputing.com’
- Warehouse: The warehouse Amplitude will use to load the data. Ideally, this will be a warehouse dedicated to loading Amplitude data; this way, other regular Snowflake operations will not be disrupted.
- Database: The database where the data will be stored. Similarly, this database should also be dedicated specifically to Amplitude data.
- Username: The username Amplitude will use to connect to the Snowflake account.
- Password: The password associate with the username
-
-
NOTE: These credentials are case-sensitive, so keep that in mind.
- Next to the credentials section, Amplitude will dynamically create the query it will use to create Snowflake objects. To copy it to the clipboard, click Copy. You can now paste it into your Snowflake account.
- Click Next >. Amplitude will try to upload test data using the credentials you entered. If the upload is successful, click Finish.
All future events will automatically be sent to Snowflake.
From here, Amplitude generates micro-batch files at five-minute intervals and loads them to customer-owned Snowflake accounts directly every 10 minutes. You will be able to see the data in your Snowflake accounts within 20 minutes after Amplitude receives the events.
Export historical Amplitude data to Snowflake
To export your historical data from Amplitude into Snowflake, click Export Data and select a date range.
This process can take anywhere from a single day to several weeks, depending on your data volume, warehouse size, cluster count, network bandwidth, and number of concurrent historical data exports you currently have, among other factors.
Best practices: Ways you can reduce your Snowflake computation costs
If you are looking to reduce the Snowflake computation costs when receiving data from Amplitude, try these techniques:
NOTE: The effectiveness of these recommendations will depend on the frequency with which you export to your Snowflake instance.
- Modify warehouse size and number of clusters. 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 can be manually set via direct Snowflake query.
Snowflake Export Format
The Event table schema will contain the following:
|
Column |
Description |
|
amplitude_id NUMBER(38,0) |
The original Amplitude ID for the user. Use this field to automatically handle merged users.
|
|
adid VARCHAR(16777216) |
(Android) Google Play Services advertising ID (AdID).
|
|
amplitude_event_type |
Amplitude specific identifiers based on events Amplitude generates. This is a legacy field so event_type should suffice for all queries. |
|
amplitude_id |
An internal ID used to count unique users. |
|
app |
|
|
city |
City. "San Francisco" |
|
client_event_time |
Local timestamp (UTC) of when the device logged the event.
|
|
client_upload_time |
The local timestamp (UTC) of when the device uploaded the event. |
|
country |
Country."United States" |
|
data |
Dictionary where certain fields such as |
|
device_brand |
Device brand. |
|
device_carrier |
Device carrier. |
|
device_family |
Device family. |
|
device_id |
The device specific identifier. |
|
device_manufacturer |
Device manufacturer. |
|
device_model |
The device model. |
|
device_type |
Device type. |
|
dma VARCHAR(16777216) |
Designated marketing area (DMA). |
|
event_id NUMBER(38,0) |
A counter that distinguishes events. |
|
event_time |
Amplitude timestamp (UTC) which is the client_event_time adjusted by the difference between server_received_time and client_upload_time, specifically: event_time = client_event_time + (server_received_time - client_upload_time)
TWe use this timestamp is used to organize events on Amplitude charts. Note: If the difference between |
|
event_type |
The assigned type of event. |
|
followed_an_identify |
True if there was an identify event between this current SDK event and the last SDK event seen. |
|
groups |
Group types. See the Accounts documentation for more information. |
|
idfa |
(iOS) Identifier for Advertiser. |
|
ip_address |
IP address. "123.11.111.11" |
|
location_lat |
Latitude. |
|
location_lng |
Longitude. |
|
os_name |
OS name. |
|
os_version |
OS version. |
|
paying |
Note: The property value can be modified via the Identify API.
|
|
region |
Region. |
|
server_upload_time TIMESTAMP |
Amplitude timestamp (UTC) of when our servers received the event.
|
|
session_id |
The session start time in milliseconds since epoch. |
|
start_version |
App version the user was first tracked on. |
|
user_creation_time TIMESTAMP |
Event_time (UTC) of the user's first event.
|
|
user_id VARCHAR(16777216) |
A readable ID specified by you. Should be something that will not change; for that reason, using the user's email address is not recommended. |
|
uuid |
A unique identifier per row (event sent).
|
|
version_name |
The app version. |
The Merged User table schema will contain the following:
|
Column |
Description |
|
amplitude_id NUMBER(38,0) |
The Amplitude ID being merged into a user's original Amplitude ID. |
|
merge_event_time TIMESTAMP |
The time of the event a user's new Amplitude ID was associated with their original Amplitude ID. |
|
merge_server_time TIMESTAMP |
The server time of the event when a user's new Amplitude ID was associated with their original Amplitude ID. |
|
merged_amplitude_id NUMBER(38,0) |
The originally assigned Amplitude ID when the user is first created. |