Warehouse Connectors
With Warehouse Connectors you can sync data from data warehouses like Snowflake, BigQuery, Databricks, and Redshift to Mixpanel. By unifying business data with product usage events, you can answer many more questions in Mixpanel:
- What percentage of our Enterprise revenue uses the features we shipped last year?
- Did our app redesign reduce support tickets?
- Which account demographics have the best retention?
- We spent $50,000 on a marketing campaign, did the users we acquire stick around a month later?
Mixpanel's Mirror sync mode keeps the data in Mixpanel fully in sync with any changes that occur in the warehouse including updating historical events that are deleted or modified in your warehouse.
In this guide, we'll walk through how to set up Warehouse Connectors. The integration is completely codeless, but you will need someone with access to your DWH to help with the initial set up.
Getting Started
Step 1: Connect a warehouse
Navigate to Project Settings → Warehouse Sources (opens in a new tab). Select your warehouse and follow the instructions to connect it. Note: you only need to do this once.
The BigQuery connector works by giving a Mixpanel-managed service account permission to read from BigQuery in your GCP project. You will need:
- Your GCP Project ID, which you can find in the URL of Google Cloud Console (
https://console.cloud.google.com/bigquery?project=YOUR_GCP_PROJECT). - Your unique Mixpanel service account ID, which is is generated the first time you create a BigQuery connection in the Mixpanel UI
(e.g.
project-?????@mixpanel-warehouse-1.iam.gserviceaccount.com). - A new, empty
mixpaneldataset in your BigQuery instance (if you are using Mirror).
CREATE SCHEMA `<gcp-project>`.`mixpanel`
OPTIONS (
description = 'Mixpanel connector staging dataset',
location = '<same-as-the-tables-to-be-synced>',
);Grant the the Mixpanel service the following permissions:
roles/bigquery.jobUser- Allows Mixpanel to run BigQuery jobs to unload data.gcloud projects add-iam-policy-binding --member serviceAccount:<mixpanel-service-account> --role roles/bigquery.jobUserroles/bigquery.dataVieweron the datasets and/or tables to sync. Gives Mixpanel Mixpanel read-only access to the datasets.GRANT `roles/bigquery.dataViewer` ON SCHEMA `<gcp-project>`.`<dataset-to-be-synced>` TO "<mixpanel-service-account>"roles/bigquery.dataOwneron themixpaneldataset. Gives Mixpanel read-write access to themixpaneldataset.GRANT `roles/bigquery.dataOwner` ON SCHEMA `<gcp-project>`.`mixpanel` TO "<mixpanel-service-account>"
Step 2: Load a warehouse table
Navigate to Project Settings → Warehouse Data (opens in a new tab) and click +Event Table.
Select a table or view representing an event from your warehouse and tell Mixpanel about the table. Once satisfied with the preview, click run and we’ll establish the sync. The initial load may take a few minutes depending on the size of the table, we show you progress as it’s happening.
🎉 Congrats, you’ve loaded your first warehouse table into Mixpanel! From this point onward, the table will be kept in sync with Mixpanel. You can now use this event throughout Mixpanel’s interface.
Table Types
Mixpanel’s Data Model consists of 4 types: Events, User Profiles, Group Profiles, and Lookup Tables. Each have properties, which are arbitrary JSON. Warehouse Connectors lets you turn any table or view in your warehouse into one of these 4 types of tables, provided they match the required schema.
Events
An event is something that happens at a point in time. It’s akin to a “fact” in dimensional modeling or a log in a database. Events have properties, which describe the event. Learn more about Events here.
Here’s an example table that illustrates what can be loaded as events in Mixpanel. The most important fields are the timestamp (when) and the user id (who) — everything else is optional.
| Timestamp | User ID | Item | Brand | Amount | Type |
|---|---|---|---|---|---|
| 2024-01-04 11:12:00 | alice@example.com | shoes | nike | 99.23 | in-store |
| 2024-01-12 11:12:00 | bob@example.com | socks | adidas | 4.56 | online |
Here are more details about the schema we expect for events:
| Column | Required | Type | Description |
|---|---|---|---|
| Event Name | Yes | String | The name of the event. Eg: Purchase Completed or Support Ticket Filed. Note: you can specify this value statically, it doesn’t need to be a column in the table. |
| Time | Yes | Timestamp | The time at which the event occurred. |
| User ID | No | String or Integer | The unique identifier of the user who performed the event. Eg: 12345 or grace@example.com. |
| Device ID | No | String or Integer | An identifier for anonymous users, useful for tracking pre-login data. Learn more here |
| JSON Properties | No | JSON or Variant | A field that contains key-value properties in JSON format. If provided, Mixpanel will flatten this field out into properties. |
| All other columns | No | Any | These can be anything. Mixpanel will auto-detect these columns and attach them to the event as properties. |
User Profiles
A User Profile is a table that describes your users. It’s akin to a “dimension” in dimensional modeling or a relational table in a database. Learn more about User Profiles here.
Here’s an example table that illustrates what can be loaded as user profiles in Mixpanel. The only important column is the User ID, which is the primary key of the table.
| User ID | Name | Subscription Tier | |
|---|---|---|---|
| 12345 | grace@example.com | Grace Hopper | Pro |
| 45678 | bob@example.com | Bob Noyce | Free |
Profile History is in beta. While Profiles typically only store the state of a user as of now, Profile History enables storing the state of a user over time. When creating a User Profile sync, set the Table Type to “History Table” — this will require you to supply a Start Time column in the sync configuration. Request beta access here (opens in a new tab).
Group Profiles
A Group Profile is a table that describes an entity (most often an Account, if you’re a B2B company). They are functionally identical to User Profiles, just used for other non-User entities. Group Profiles are only available if you have the Group Analytics add-on. Learn more about Group Analytics here.
Here’s an example table that illustrates what can be loaded as group profiles in Mixpanel. The only important column is the Group Key, which is the primary key of the table.
| Group Key | Name | Domain | ARR | Subscription Tier |
|---|---|---|---|---|
| 12345 | Notion | notion.so | 45000 | Enterprise |
| 45678 | Linear | linear.so | 2000 | Pro |
Lookup Tables
A Lookup Table is useful for enriching Mixpanel properties (e.g. content, skus, currencies) with additional metadata. Learn more about Lookup Tables here (opens in a new tab). Do note the limits of lookup tables indicated here (opens in a new tab).
Here’s an example table that illustrates what can be loaded as lookup table in Mixpanel. The only important column is the ID, which is the primary key of the table that is eventually mapped to a Mixpanel property
| ID | Song Name | Artist | Genre |
|---|---|---|---|
| 12345 | One Dance | Drake | Pop |
| 45678 | Voyager | Daft Punk | Electronic |
Sync Modes
Warehouse Connectors regularly check warehouse tables for changes to load into Mixpanel. The Sync Mode determines which changes Mixpanel will reflect.
- Mirror (New) will keep Mixpanel perfectly in sync with the data in the warehouse. This includes syncing new data, modifying historical data, and deleting data that has been removed from the warehouse. Mirror is supported for Snowflake and BigQuery.
- Append will load new rows in the warehouse into Mixpanel, but will ignore modifications to existing rows or rows that have been deleted from the warehouse. We recommend using Mirror over Append for supported warehouses.
- Full will reload the entire table to Mixpanel each time it runs rather than tracking changes between runs. Full syncs are only supported for Lookup Tabls, User Profiles, and Group Profiles.
- One-Time will load the data from your warehouse into Mixpanel once with no ability to send incremental changer later. This is only recommended where the warehouse is being used as a temporary copy of the data being moved to Mixpanel from some other source and the warehouse copy will not be updated later.
Mirror
Mirror syncs work by having the warehouse compute which rows have been inserted, modified, or deleted and sending this list of changes to Mixpanel. Change tracking is configured differently depending on the source warehouse.
Mirror takes BigQuery table snapshots (opens in a new tab) and runs queries to compute the
change stream between two snapshot. Snapshots are stored in the mixpanel dataset created in Step 1.
Considerations when using Mirror with BigQuery:
- Mirror is not supported on views in BigQuery.
- If two rows in BigQuery are identical across all columns the checksums Mirror computes for each row will be the same and Mixpanel will consider them the same row causing only one copy to appear in Mixpanel. We recommend ensuring that one of your columns is a unique row ID to avoid this.
- The table snapshots managed by Mixpanel are always created to expire after 21 days. This ensures that the snapshots are deleted even if Mixpanel looses access to them unexpectedly. Make sure that the sync does not go longer than 21 days without running as each sync run needs access to the previous sync run's snapshot. (Under normal conditions Mirror maintains only one snapshot per-sync and removes the older run's snapshot as soon as it has been used by the subsequent sync run).
How changes are detected:
Changed rows are detected by checksumming the values of all columns except trailing NULL-valued columns. For example in the following table would use these per-row checksums:
| ID | Song Name | Artist | Genre | Computed checksum |
|---|---|---|---|---|
| 12345 | One Dance | Drake | NULL | CHECKSUM(12345, 'One Dance', 'Drake') |
| 45678 | Voyager | Daft Punk | Electronic | CHECKSUM(45678, 'Voyager', 'Daft Punk', 'Electronic') |
| 83921 | NULL | NULL | Classical | CHECKSUM(83921, NULL, NULL, 'Classical') |
Trailing NULL-values are excluded from the checksum to ensure that adding new columns does not change the checksum of existing rows. For example if a new column is added to the example table:
ALTER TABLE songs ADD COLUMN Tag STRING NULL;It would not change the computed checksums:
| ID | Song Name | Artist | Genre | Tag | Computed checksum |
|---|---|---|---|---|---|
| 12345 | One Dance | Drake | NULL | NULL | CHECKSUM(12345, 'One Dance', 'Drake') |
| 45678 | Voyager | Daft Punk | Electronic | NULL | CHECKSUM(45678, 'Voyager', 'Daft Punk', 'Electronic') |
| 83921 | NULL | NULL | Classical | NULL | CHECKSUM(83921, NULL, NULL, 'Classical') |
Until values are written to the new column:
| ID | Song Name | Artist | Genre | Tag | Computed checksum |
|---|---|---|---|---|---|
| 12345 | One Dance | Drake | NULL | tag1 | CHECKSUM(12345, 'One Dance', 'Drake', NULL, 'tag1') |
| 45678 | Voyager | Daft Punk | Electronic | tag2 | CHECKSUM(45678, 'Voyager', 'Daft Punk', 'Electronic', 'tag2') |
| 83921 | NULL | NULL | Classical | NULL | CHECKSUM(83921, NULL, NULL, 'Classical') |
Handling schema changes when using Mirror with BigQuery:
Adding new, default-NULL columns to Mirror-tracked tables/views is fully supported as described in the previous section.
ALTER TABLE <table> ADD COLUMN <column> STRING NULL;We recommend avoiding other types of schema changes on large tables. Other schema changes may cause the checksum of every row to change, effectively re-sending the entire table to Mixpanel. For example, if we were to remove the Genre column in the example above the checksum of every row would be different:
| ID | Song Name | Artist | Tag | Computed checksum |
|---|---|---|---|---|
| 12345 | One Dance | Drake | tag1 | CHECKSUM(12345, 'One Dance', 'Drake', 'tag1') |
| 45678 | Voyager | Daft Punk | tag2 | CHECKSUM(45678, 'Voyager', 'Daft Punk', 'tag2') |
| 83921 | NULL | NULL | NULL | CHECKSUM(83921) |
Handling partitioned tables:
When syncing time partitioned (opens in a new tab) or ingestion-time paritioned (opens in a new tab) tables Mirror will use partition metadata to skip processing partitions that have not changed between sync runs. This will make the computation of the change stream much more efficient on large paritioned tables where only a small percentage of partitions are update between runs. For example, in a day-partitioned table with two years of data, where only the last five days of data are normally updated only five partitions worth of data will be scanned each time the sync runs.
Append
Append syncs require an Insert Time column in your table. Mixpanel remembers the maximum Insert Time it saw in the previous run of the sync and looks for only rows that have an Insert Time greater than that. This is useful and efficient for append-only tables (usually events) that have a column indicating when the data was appended.
Full
Full syncs periodically make a snapshot of the source table and sync it entirely to Mixpanel. If a row has new properties in your warehouse, the corresponding profile in Mixpanel will be overridden with those new properties. This mode is available for all tables except events.
FAQ
What tables are valuable to load into Mixpanel?
Anything that is event-based (has a user_id and timestamp) and that you want to analyze in Mixpanel. Examples, by data source are:
- CRM: Opportunity Created, Opportunity Closed
- Support: Ticket Created, Ticket Closed
- Billing: Subscription Created, Subscription Upgraded, Subscription Canceled, Payment Made
- Application Database: Sign-up, Purchased Item, Invited Teammate
We also recommend loading your user and account tables, to enrich events with demographic attributes about the users and accounts who performed them.
How fast do syncs run?
Syncs have a throughput of ~30K events/second or ~100M events/hour.
I already track data to Mixpanel via SDK or CDP, can I still use Warehouse Connectors?
Yes! You can send some events (eg: web and app data) directly via our SDKs and send other data (eg: user profiles from CRM or logs from your backend) from your warehouse and analyze them together in Mixpanel.
How much does Warehouse Connectors cost?
The events generated by Warehouse Connectors are billed identically to all other events you track to Mixpanel. Learn more about Mixpanel’s event-based billing here (opens in a new tab). Otherwise, there is no special cost for using Warehouse Connectors.
What will be the cost impact of this on my DWH?
In short, very little. There are 3 major aspects of DWH cost: storage, compute, and network egress.
- Storage: None. We don't store anything additional in your warehouse, so there are no extra storage costs.
- Compute: Low. We make highly efficient queries to extract data from your warehouse. To be extra careful, we recommend provisioning the smallest possible warehouse for use with Warehouse Connectors (eg: X-Small in Snowflake (opens in a new tab)). If syncs run slowly with this size, you can scale up to trade compute cost for speed.
- Network Egress: Low. We only extract rows that have changed and leverage gzip compression to reduce bytes over the wire. Assuming an egress rate of $0.08 per GB and 100 compressed bytes per event, this is a cost of less than $0.01 per million events.
How can I get help setting up a warehouse sync?
Reach out (opens in a new tab) to our team — we’re happy to walk you through the set up. If you bring a data engineer who has credentials to access your warehouse, it takes < 10 minutes to get up and running.
Was this page useful?