Warehouse Connectors: Sync data from your data warehouse into Mixpanel

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 mixpanel dataset 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.jobUser
• roles/bigquery.dataViewer on the datasets and/or tables to sync. Gives 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.dataOwner on the mixpanel dataset. Gives Mixpanel read-write access to the mixpanel dataset.

  GRANT `roles/bigquery.dataOwner`
    ON SCHEMA `<gcp-project>`.`mixpanel`
    TO "<mixpanel-service-account>"

To connect to Snowflake you will need:

• Your Snowflake account identifier, which you can find in the URL of your Snowflake account (https://YOUR_ACCOUNT_NAME.snowflakecomputing.com/).
• A dedicated Mixpanel user account and role. The user account can use either key-pair or password authentication. If using key-pair authentication Mixpanel will generate a secure key-pair during the connection process. The public key will be provided during the setup process and the private key will be encrypted and stored securely.

  CREATE ROLE MIXPANEL_ROLE;
  # one of
  CREATE USER MIXPANEL PASSWORD='?????' DEFAULT_ROLE=MIXPANEL_ROLE;
  CREATE USER MIXPANEL RSA_PUBLIC_KEY='<mixpanel-provided-key>' DEFAULT_ROLE=MIXPANEL_IMPORT_ROLE;
  # then
  GRANT ROLE MIXPANEL_ROLE TO USER MIXPANEL;
• A Snowflake WAREHOUSE Mixpanel will use to unload data. We recommend creating a dedicated warehouse for Mixpanel to avoid impacting other workloads.

  CREATE WAREHOUSE MIXPANEL_WAREHOUSE WITH
    WAREHOUSE_SIZE = XSMALL # consider increasing for larger datasets
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;
  GRANT USAGE ON MIXPANEL_WAREHOUSE TO ROLE MIXPANEL_ROLE;
  GRANT MONITOR ON MIXPANEL_WAREHOUSE TO ROLE MIXPANEL_ROLE;
• A Snowflake STORAGE INTEGRATION Mixpanel will use to unload data. This integration can optionally restrict STORAGE_ALLOWED_LOCATIONS to the unique Mixpanel-managed bucket created to receive data for this connection.

  CREATE STORAGE INTEGRATION MIXPANEL_STORAGE_INTEGRATION
    TYPE = EXTERNAL_STAGE
    STORAGE_PROVIDER = 'GCS'
    ENABLED = TRUE
    STORAGE_ALLOWED_LOCATIONS = ("<mixpanel-provided-bucket-name>"); # optional
  GRANT USAGE ON INTEGRATION MIXPANEL_STORAGE_INTEGRATION TO MIXPANEL_ROLE;
• A new, empty MIXPANEL SCHEMA the Mixpanel user has the USAGE and CREATE STREAM permission for (if you are using Mirror).

  CREATE SCHEMA <database>.MIXPANEL;
  GRANT USAGE ON DATABASE <database> TO ROLE MIXPANEL_ROLE;
  GRANT USAGE ON SCHEMA <database>.MIXPANEL TO ROLE MIXPANEL_ROLE;
  GRANT CREATE STREAM ON SCHEMA <database>.MIXPANEL TO ROLE MIXPANEL_ROLE;
• The Mixpanel user needs the USAGE and SELECT permissions to have read-only access to any tables and views you plan to sync. Adjust this example to fine tune permissions.

  GRANT USAGE ON DATABASE <database> TO ROLE MIXPANEL_ROLE;
  GRANT USAGE ON ALL SCHEMAS IN DATABASE <database> TO ROLE MIXPANEL_ROLE;
  GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <database> TO ROLE MIXPANEL_ROLE;
  GRANT SELECT ON ALL TABLES IN DATABASE <database> TO ROLE MIXPANEL_ROLE;
  GRANT SELECT ON FUTURE TABLES IN DATABASE <database> TO ROLE MIXPANEL_ROLE;
  GRANT SELECT ON ALL VIEWS IN DATABASE <database> TO ROLE MIXPANEL_ROLE;
  GRANT SELECT ON FUTURE VIEWS IN DATABASE <database> TO ROLE MIXPANEL_ROLE;
  GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE <database> TO ROLE MIXPANEL_ROLE;
  GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN DATABASE <database> TO ROLE MIXPANEL_ROLE;

IP Allowed List

If you are using Snowflake Network policy to restrict access to your instance, you might need to add the following IP addresses to the allowed list.

34.31.112.201
34.147.68.192
35.184.21.33
35.225.176.74
35.204.164.122
35.204.177.251

Complete the following steps to get your Databricks connector up and running:

1. Navigate to Project Settings, then select Warehouse Sources.
2. Click on + Add Connection and select Databricks.
3. You should see a new page to create your databricks connector. In the first view, fill out the following fields before clicking Create Source:
   • ** Server Hostname ** - This is the hostname of your Databricks cluster. This can be found in your workspace URL, or by navigating to JDBC/ODBC connection settings
   • ** HTTP Path ** - This is the HTTP path of the cluster you would like to connect to. This can be found in your cluster JDBC/ODBC connection settings
   • ** Access Token ** - This is the Personal access token used to authenticate with your Databricks cluster. Here are the instructions on how to create an access token
4. In the second view, you should see that the credentials are validated and a source is added.

Using Service Principals If a service principal is used, you’ll want to follow these steps:

1. Create the service principal if it doesn’t exist.
2. Create a token for the service principal. You can do so through the Databricks cli:

databricks configure --token
databricks token-management create-obo-token <application_id_of_service_principal> 31536000 --comment "Mixpanel warehouse connector service principal token"

3. Copy the token generated for later use
4. Give the service principal Can Attach permissions to the cluster (in Cluster > Permissions)
   • Note the cluster needs to be a shared compute resource and not a single user cluster (unless the service principal created the cluster as well)
5. Give the service principal access to the catalogs you want to read in Mixpanel
6. When table access control is enabled in the account, the service principal also requires access to the files that will be read. You can add access like:

GRANT SELECT ON ANY FILE TO <application_id_of_service_principal>
GRANT MODIFY ON ANY FILE TO <application_id_of_service_principal>

7. During the setup processing Mixpanel, you can now use the token from the service principal when setting up the connection

IP Allowed List

If you are using IP Access List to restrict access to your instance, you might need to add the following IP addresses to the allowed list.

34.31.112.201
34.147.68.192
35.184.21.33
35.225.176.74
35.204.164.122
35.204.177.251

Complete the following steps to get your Redshift connector up and running:

1. Navigate to Project Settings, then select Warehouse Sources.
2. Click on + Add Connection and select Redshift.
3. You should see a new page to create your Redshift connector. In the first view, fill out the following fields before clicking Next:
   • AWS Account ID - This is the AWS account ID that can be found in the dropdown in the AWS Redshift Console.
   • Cluster ID - This is the name of the Redshift cluster.
4. In the second view, you will need to add the following.
   • AWS Region - The region code in which your Redshift cluster resides.
   • S3 Staging Bucket - This is the name of S3 staging bucket you need to create. We’ll use it to extract data from your Redshift tables before importing the data into Mixpanel.
   • Copy the command generated below in the AWS CLI to create the S3 Staging Bucket with the name you specified.
   • Database Name - Input the name of the Database where the tables you want to import are stored.
   • (Optional) Policy Name - This is an optional name for the policy, which contains role permissions that you need to grant the Mixpanel Service Account. After inputting a policy name, you can either copy paste the JSON in the AWS UI or copy paste the inline command line version that we generate and run in the AWS CLI.
   • Role Name - Input the name of the role. After inputting a role name, you can either copy paste the JSON in the AWS UI or copy paste the inline command line version that we generate and run in the AWS CLI. Running this command grants the Mixpanel Service Account the necessary permissions to read and export data from your Redshift tables.
   • Finally, attach the policy you created to the role you created by copy pasting the command in the AWS CLI.
   • Then, click Create Source.
5. In the third view, you should see a confirmation that your source was created. To establish the source connection, we need to ping your Redshift instance to actually create the service account user.
   • Grant Access to Schema - Enter the name of the schema you want to grant Mixpanel access to.
   • Copy the command generated and run in your Redshift worksheet. Once that command is run successfully, the connection will be established and you will be able to send data from Redshift tables to Mixpanel.

IP Allowed List

If you are using AWS PrivateLink to restrict access to your instance, you might need to add the following IP addresses to the allowed list.

34.31.112.201
34.147.68.192
35.184.21.33
35.225.176.74
35.204.164.122
35.204.177.251

Mirror takes BigQuery table snapshots 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:

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:

Until values are written to the new column:

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;

If you have a JSON column in the table/view which you map to JSON Properties in the import setup, as the whole JSON object for that column is used to calculate the checksum for the row, any changes to the JSON object will result in a change to the checksum, even if the change is to add new keys with null values. As such, consider conditional formatting when building the JSON object to only update it when a new non-null value is available for a row.

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:

Handling partitioned tables:

When syncing time partitioned or ingestion-time partitioned 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 partitioned 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.

Mirror uses Snowflake Streams to track changes to Snowflake tables or views. The only requirement to use Mirror on a Snowflake table or view is to enable change tracking:

ALTER TABLE <table> SET CHANGE_TRACKING = TRUE;
# or
ALTER VIEW <view> SET CHANGE_TRACKING = TRUE;

Mixpanel will create and manage the necessary STREAM objects in the MIXPANEL schema created in Step 1.

Considerations when using Mirror with Snowflake:

• Snowflake Streams only maintain change tracking metadata for a limited number of days determined by the larger of DATA_RETENTION_TIME_IN_DAYS and MAX_DATA_EXTENSION_TIME_IN_DAYS (default is 14 days for MAX_DATA_EXTENSION_TIME_IN_DAYS). Make sure that the Mixpanel sync does not go longer than this number of days without running. Mixpanel recommends leaving the default of 14 days to ensure that if Mixpanel loses access to the warehouse unexpectedly (e.g. a credentials change), there is time to correct the issue.
• Snowflake Streams do not work if a table is deleted and re-created with the same name. If using a tool like dbt to model data in Snowflake make sure to use an incremental model so that dbt does not replace the table each time it runs.
• Snowflake streams doesn’t capture changes when a column is deleted or renamed, so deletion of columns won’t be synced to Mixpanel.
• Snowflake has specific requirements when using Streams on Views that must be met when using Mirror with views.
• While Snowflake Streams are a very efficient way of tracking changes there are some performance implications when using Streams on VIEWs that contain JOINs. If you find yourself needing such a JOIN in event data we recommends considering if syncing the joined data as User Profiles, Group Profiles, or a Lookup Table would work instead.

Handling schema changes when using Mirror with Snowflake: Adding new, default-NULL columns to Mirror-tracked tables/views is fully supported.

ALTER TABLE <table> ADD COLUMN <column> VARCHAR DEFAULT NULL;

We recommend avoiding other types of schema changes. Snowflake streams only reflect changes to tables from DML statements. DDL statements that logically modify data (e.g. adding new columns with default values, dropping existing columns, or renaming columns) will be reflected in future data sent to Mixpanel but the Stream will not update historical data with changes caused by DDL statements.

Mirror uses Databricks Change Data Feed to track changes to Databricks tables. The only requirement to use Mirror on a Databricks table is to enable change data feed on those entities.

ALTER TABLE <table> SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

Considerations when using Mirror with Databricks:

• Mirror is not supported on views in Databricks
• Databricks Change Data Feed only mantains change history for a limited number of days determined by delta.logRetentionDuration (default is 30 days). Make sure that the Mixpanel sync does not go longer than this number of days without running. Mixpanel recommends leaving the default of 30 days to ensure that if Mixpanel loses access to the warehouse unexpectedly (e.g. a credentials change), there is time to correct the issue.
• Databricks Change Data Feed does not work if a table is deleted and re-created with the same name. If using a tool like dbt to model data in Databricks make sure to use an incremental model so that dbt does not replace the table each time it runs.
• While Databricks Change Data Feed works with adding new columns to the table, there are certain limitations when it comes to dropping columns or renaming columns which uses column mappings. In such scenarios Mixpanel, recommends deleting the sync (along with the data) and re-creating the sync.

Mirror takes Redshift table snapshots and runs queries to compute the change stream between two snapshots using the computed MD5 hash value for each row. Snapshots are stored in a staging schema created in Step 1.

• If two rows in Redshift table are identical across all columns, the md5 hash value 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 and are automatically cleaned-up. 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. These snapshots will incur some additional storage cost.

How changes are detected:

Changed rows are detected by computing MD5 hash for concatenated values of all columns except trailing NULL-valued columns. For example in the following table would use these per-row MD5:

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:

Until values are written to the new column:

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 hash value 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:

Considerations when using Mirror with Redshift:

• Currently only add-column schema update is supported.
• Geography, Geometry, Varbyte and hllsketch not supported currently. If you have a use-case that requires support any of these columns, please contact us for a feature request.
• Any new tables created after the permissions are granted, are not automatically accessible by mixpanel. Please re-grant necessary permissions to be able to setup mirror on the tables.
• _mp_row_hash, _mp_change_type are reserved column names used for tracking the computed hash value for each row, and update type to the row. Please ensure that this doesn’t conflict with the column names of the table.