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.

Use the Warehouse Sync UI to configure a complete Warehouse sync, including:

• Establishing a connection between your warehouse and mParticle
• Defining how to transform and map data from your warehouse so it matches your data schema in mParticle
• Configuring when and how often data is ingested from your warehouse.

You can also use the UI to view the status of, edit, or delete, existing Warehouse Sync pipelines.

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.

Set up your data warehouse

Before you can ingest data from your warehouse, you must configure your warehouse to be accessible by mParticle by:

1. Whitelisting the mParticle IP address range in your warehouse so that mParticle can submit API requests
2. Create credentials and permissions mParticle can use to access your warehouse.

Create a new warehouse input

To begin the setup wizard:

1. Log into your mParticle account
2. Navigate to Setup > Inputs in the left hand nav bar and select the Feeds tab
3. Click the Add Feed Input button in the top right and select your data warehouse provider

You can also enter the setup wizard 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, or Amazon Redshift. For Google BigQuery, make sure to select the tile labeled Data Warehouse, not Audience.

After selecting your warehouse provider, the Warehouse Sync setup wizard opens where you enter your warehouse details, create your data model, review how data from your warehouse will map to data in mParticle, and set your sync schedule.

1 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.

2 Create data model

Your data model describes which columns from your warehouse to ingest into mParticle, and which mParticle user profile attributes each column maps to. Data models are written as SQL queries, but warehouse providers process SQL functions 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.

Data models and field transformations

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

Refer to the SQL query written for a hypothetical database that includes following tables:

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:

Steps to create a data model

1. Write a SQL query following the guidelines above 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.

3 Review mapping

Verify that the columns selected in your warehouse map to the correct attributes on a user profile in your mParticle account. Any columns or attributes using the reserved word identifiers are automatically be used by mParticle during the identity resolution process. Warehouse Sync supports the same user identifiers as other input methods and adheres to the identity strategy you’ve configured.

To change your data mapping, click Back, modify and rerun your SQL query, then click Next to return to Review mapping.

4 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.

4.1 Select environment

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

4.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.

4.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:

4.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.

5 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.