Warehouse Sync

Warehouse Sync is mParticle’s reverse-ETL solution, allowing you to use an external warehouse as a data source for your mParticle account. By ingesting data from your warehouse using either one-time, on-demand, or recurring syncs, you can benefit from mParticle’s data governance, personalization, and prediction features without having to modify your existing data infrastructure.

Supported warehouse providers

You can use Warehouse Sync to ingest both user and event from the following warehouse providers:

• Amazon Redshift
• Google BigQuery
• Snowflake
• Databricks

Warehouse Sync setup overview

1. Prepare your data warehouse before connecting it to mParticle
2. Create an input feed in your mParticle account for your warehouse
3. Connect your warehouse to your new mParticle input feed
4. Specify the data you want to ingest into mParticle by creating a SQL data model

5. Map your warehouse data to fields in mParticle

   • For user data pipelines, this mapping is done by your data model
   • For event data pipelines, you must complete an additional data mapping step
6. Configure when and how often data is ingested from your warehouse

Warehouse Sync API

All functionality of Warehouse Sync is also available from an API. To learn how to create and manage syncs between mParticle and your data warehouse using the API, visit the mParticle developer documentation.

Warehouse Sync setup tutorial

Prerequisites

Before beginning the warehouse sync setup, obtain the following information for your mParticle account. Depending on which warehouse you plan to use, you will need some or all of these values:

• Your mParticle region ID, or pod: either US1, US2, AU1, or EU1

• Your Pod AWS Account ID that correlates with your mParticle region ID:

  • US1: 338661164609
  • US2: 386705975570
  • AU1: 526464060896
  • EU1: 583371261087
• Your mParticle Org ID
• Your mParticle Account ID

How to find your mParticle Org, Account, Region, and Pod AWS Account IDs

To find your Org ID, Account ID, Pod ID, and Pod AWS Account ID:

1. Log into the mParticle app at app.mparticle.com

2. From your web browser’s toolbar, navigate to view the page source for the mParticle app.

   • For example, in Google Chrome: Navigate to View > Developer > View Page Source in the toolbar. In the resulting source for the page, find and save the values for accountId, orgId, podName, and podId, as shown in the screenshot below.

1 Prepare your data warehouse

Work with your warehouse administrator or IT team to ensure your warehouse is reachable and accessible by mParticle.

1. Whitelist the mParticle IP address range so your warehouse will be able to accept inbound API requests from mParticle.
2. Ask your database administrator to perform the following steps in your warehouse to create a new role that mParticle can use to access your database. Select the correct tab for your warehouse (Snowflake, Google BigQuery, Amazon Redshift, or Databricks) below.

-- Create a unique user for mParticle
CREATE USER {{user_name}} WITH PASSWORD '{{unique_secure_password}}'

-- Grant schema usage permissions to the new user
GRANT USAGE ON SCHEMA {{schema_name}} TO {{user_name}}

-- Grant SELECT privilege on any tables/views mP needs to access to the new user
GRANT SELECT ON TABLE {{schema_name}}.{{table_name}} TO {{user_name}}

{
    "Statement": [
        {
            "Action": "sts:AssumeRole",
            "Effect": "Allow",
            "Resource": "arn:aws:iam::{{mp_pod_aws_account_id}}:role/ingest-pipeline-data-external-{{mp_org_id}}-{{mp_acct_id}}",
            "Sid": ""
        }
    ],
    "Version": "2012-10-17"
}

2 Create a new warehouse input

1. Log into your mParticle account
2. Navigate to Setup > Inputs in the left-hand nav bar and select the Feeds tab
3. Under Add Feed Input, search for and select your data warehouse provider.

You can also create a new warehouse input from the Integrations Directory:

1. Log into your mParticle account, and click Directory in the left hand nav.
2. Search for either Google BigQuery, Snowflake, Amazon Redshift, or Databricks.

After selecting your warehouse provider, the Warehouse Sync setup wizard will open where you will:

1. Enter your warehouse details
2. Create your data model
3. Create any necessary mappings between your warehouse data and mParticle fields
4. Enter your sync schedule settings

3 Connect warehouse

The setup wizard presents different configuration options depending on the warehouse provider you select. Use the tabs below to view the specific setup instructions for Amazon Redshift, Google BigQuery, and Snowflake.

4 Create data model

Your data model describes which columns from your warehouse to ingest into mParticle, and which mParticle fields each column should map to. While mParticle data models are written in SQL, all warehouse providers process SQL slightly differently so it is important to use the correct SQL syntax for the warehouse provider you select.

For a detailed reference of all SQL commands Warehouse Sync supports alongside real-world example SQL queries, see Warehouse Sync SQL reference.

Steps to create a data model

1. Write a SQL query following the guidelines outlined below and the Warehouse Sync SQL reference. Make sure to use SQL commands supported by your warehouse provider.
2. Enter the SQL query in the SQL query text box, and click Run Query.
3. Click Next.

mParticle submits the SQL query you provide to your warehouse to retrieve specific columns of data. Depending on the SQL operators and functions you use, the columns selected from your database are transformed, or mapped, to user profile attributes in your mParticle account.

If you use an attribute name in your SQL query that doesn’t exist in your mParticle account, mParticle creates an attribute with the same name and maps this data to the new attribute.

mParticle automatically maps matching column names in your warehouse to reserved mParticle user attributes and device ids. For example, if your database contains a column named customer_id, it is automatically mapped to the customer_id user identifier in mParticle. For a complete list of reserved attribute names, see Reserved mParticle user or device identity column names.

Example database and SQL query

Below are several example data tables and a SQL query that creates a data model based on them:

Table name: mp.demo.attr

Table name: mp.demo.calc

Table name: mp.demo.favs

Example SQL query:

SELECT
    a.date_updated,
    a.customer_id,
    a.firstname AS "$firstname",
    c.propensity_to_buy AS "propensity_to_buy",
    ARRAY_AGG(f.value) WITHIN GROUP (ORDER BY f.value ASC) AS "favorite_categories"
FROM mp.demo.attr a
JOIN mp.demo.calc c ON a.customer_id = c.customer_id
JOIN mp.demo.favs f ON a.customer_id = f.customer_id
GROUP BY
    a.date_updated,
    a.customer_id,
    a.firstname,
    c.propensity_to_buy

The SQL query above performs several selections and operations on the data in the sample tables:

1. The columns date_updated, customer_id, and firstname are selected from the database table mp.demo.att.
2. The column firstname maps to the mParticle attribute firstname using the AS SQL operator. date_updated and customer_id are automatically mapped to their corresponding reserved attributes in mParticle.
3. The column propensity_to_buy in the table mp.demo.calc maps to the mParticle attribute propensity_to_buy.
4. The aggregate function ARRAY_ARG() creates an array named favorite_categories containing each user’s favorite categories in ascending order from the table mp.demo.favs.

Note that the query joins the three tables on their shared customer_id column. The result of the query as it exists in mParticle would then be:

5 Create data mapping

After creating a data model that specifies which columns in your warehouse you want to ingest, you must map each column to its respective field within mParticle with a data mapping.

To create a data mapping, first use the dropdown menu titled Type of data to sync to select either User Attributes & Identities Only or Events, depending on whether you want to ingest user data or event data.

User Attributes & Identities Only

Create a user data mapping

You must always create a user_attributes or user_identities object to contain user attribute and identity mappings.

1. Click Add Mapping.

2. Under Mapping Type, select Object.
3. Under Field in mParticle, select user_attributes if you want to map user attributes, or user_identities if you want to map user identities.

4. Click Save.
5. Within your new user_attributes or user_identities object, click the + button.

6. Under Mapping Type, select Column.
7. Under Column in warehouse select the name of the column that contains your user attribute or user identity field.
8. Under Field in mParticle enter the name of the user attribute or identity you want to map your warehouse field to.

9. Click Save.
10. Repeat steps 5-9 for each attribute or identity you want to map.

Events

You must always create an events object to contain your event attribute mappings.

1. Click Add Mapping.

2. Under Mapping Type, select Object.
3. Under Field in mParticle, select events.

4. Click Save.
5. Within the new events object, click the + button to add a new mapping.

6. Select the type of mapping you want to use from the Mapping Type dropdown:

   • Column - maps a column from your database to a field in mParticle
   • Static - maps a static value that you define to a field in mParticle
   • Ignore - prevents a field that you specify from being ingested into mParticle

The next steps vary depending on the mapping type you select:

Column mapping

1. Under Column in warehouse, select the name of the column in your database you are mapping fields from.
2. Under Field in mParticle, select the field in mParticle you are mapping fields to.
3. Click Save.

Static mapping

1. Under Input Value select either String, Number, or Boolean for the data type of the static value you are mapping.
2. In the entry box, enter the static value you are mapping.
3. Under Field in mParticle, select the field you want to map your static value to.
4. Click Save.

Ignore mapping

1. Under Column in warehouse, select the name of the column that you want your pipeline to ignore when ingesting data.
2. Click Save.

To add additional mappings, click Add Mapping. You must create a mapping for every column you selected in your data model.

When you have finished creating your mappings, click Next.

6 Set sync settings

The sync frequency settings determine when the initial sync with your database will occur, and how frequently any subsequent syncs will be executed.

6.1 Select environment

Select either Prod or Dev depending on whether you want your warehouse data sent to the mParticle development or production environments. This setting determines how mParticle processes ingested data.

6.2 Select input protection level

Input protection levels determine how data ingested from your warehouse can contribute to new or existing user profiles in mParticle:

• Create & Update: the default setting for all inputs in mParticle. This setting allows ingested user data to initiate the creation of a new profile or to be added to an existing profile.
• Update Only: allows ingested data to be added to existing profiles, but not initiate the creation of new profiles.
• Read Only: prevents ingested data from updating or creating user profiles.

To learn more about these settings and how they can be used in different scenarios, see Input protections.

6.3 Select sync mode and schedule

There are two sync modes: incremental and full.

• Incremental: Use this sync mode to only ingest data that has changed or been added between sync runs as indicated by your warehouse column you use as an iterator. The first run for incremental sync modes is always be a full sync.
• Full: Use this sync mode to sync all data from your warehouse each time you execute a sync run. Use caution when selecting this sync mode, as it can incur to very high costs due to the volume of data ingested.

The remaining setting options change depending on the mode you select from the Sync mode dropdown menu. Navigate between the two configuration options using the tabs below:

6.4 Sync historical data

The value you select for Sync Start Date determines how much old, historical data mParticle ingests from your warehouse in your initial sync. When determining how much historical data to ingest, mParticle uses to the column in your database you selected as the Timestamp field in the Create Data Model step.

After your initial sync begins, mParticle begins ingesting any historical data. If mParticle hasn’t finished ingesting historical data before the time a subsequent sync is due to start, the subsequent sync is still executed, and the historical data continues syncing in the background.

Historical data syncing doesn’t contribute to any rate limiting on subsequent syncs.

After entering your sync settings, click Next.

7 Review

mParticle generates a preview for Data Warehouse syncs that have been configured, but not yet activated. Use this page and the sample enriched user profiles to confirm the following:

• Your data model correctly maps columns from your database to mParticle attributes
• Your sync is scheduled to occur at the correct interval
• Your initial sync is scheduled to occur at the correct time
• Your initial sync includes any desired historical data in your warehouse

After reviewing your sync configuration, click Activate to activate your sync.

View and manage existing warehouse syncs

1. Log into your mParticle account.
2. Navigate to Setup > Inputs in the left hand nav bar and select the Feeds tab.

3. Any configured warehouse syncs are listed on this page, grouped by warehouse provider. Expand a warehouse provider to view and manage a sync.

   • To edit an existing sync, select it from the list under a warehouse provider. This loads the Warehouse Sync setup wizard, where you can modify your sync settings.
   • Connect a sync to an output by clicking the green + icon under Connected Outputs.
   • Configure rules for a sync by clicking the + Setup button under Rules Applied.
   • Delete a sync configuration by clicking the trash icon under Actions.
   • To add a new sync for a warehouse provider, click the + icon next to the provider.

Manage a sync

To view details for an existing sync, select it from the list of syncs. A summary page is displayed, showing the current status (Active or Paused), sync frequency, and a list of recent or in-progress syncs.

To pause a sync, click Pause Sync. Paused syncs will only resume running on their configured schedule after you click Resume.

To run an on-demand sync, click Run Sync under Sync Frequency.

Use the Data Model, Mapping, and Settings tabs to view and edit your sync configuration details. Clicking Edit from any of these tabs opens the respective step of the setup wizard where you can make and save your changes.