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

1 Prepare 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 that mParticle can use to access your warehouse.

Step-by-step instructions for how to complete these two requirements for each supported provider can be found in the Warehouse Sync developer docs.

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.

Mapping requirements for user and event data

Warehouse Sync can be used to ingest user data or events data.

User data mapping

If you are creating a user data pipeline, your SQL data model maps your ingested data to fields in the mParticle JSON schema.

Event data mapping

If you are creating an event data pipeline, you must complete an additional mapping step after creating your data model.

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

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

If you selected User Attributes & Identities Only, then the data model you created in step 4 maps your ingested data to their corresponding fields in mParticle.

Verify that each column under Column in Warehouse maps to the correct attribute under mParticle User Profile Attribute.

To make changes your user data mapping:

1. Click Back.
2. Modify and rerun your SQL query according to how you want your user data to map to mParticle attributes.
3. After making changes to, and re-running, your SQL query, click Next.
4. Review your new mappings, and click Next.

Events

If you selected Events, then you must manually create mappings between each ingested field and its corresponding field in mParticle.

To create a mapping:

1. Click Add Mapping to add a new mapping.

2. In the New Mapping modal, 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.