Warehouse Sync API Overview

The Warehouse Sync API is mParticle’s reverse ETL solution that allows you to ingest user profile data or event data into mParticle from a data warehouse.

There are four stages to configuring Warehouse Sync:

1. Create a connection between mParticle and your database.
2. Create a data model that specifies what data you want to ingest.
3. Create a field transformation that maps your source data to fields in mParticle.
4. Create and configure the pipeline between your database and mParticle using your data model and field transformations. Your pipeline settings determine when, and how frequently, data will sync.

Full vs Incremental Pipelines

When you create a Warehouse Sync pipeline you must choose its sync_mode: full or incremental. This choice cannot be changed later.

full pipelines

• Each run returns the complete result set produced by your data model’s SQL.
• Ideal for small tables or occasional, ad-hoc backfills.
• Combine with a WHERE clause to narrow the slice of data when re-running the pipeline.
• Full pipelines may ingest duplicate data due to deduplication limits for large datasets. Without a timestamp to refer to, mParticle can’t track changes to data over time. A field that’s been changed back to a previous value might be mistakenly marked as a duplicate and therefore be discarded. For most use cases, incremental syncs provide better data accuracy and performance.

incremental pipelines

• Requires an iterator column and keeps track of the greatest iterator value that has been processed.
• The first execution ingests all rows where the iterator is after the from value you supply.
• Subsequent runs fetch only new or updated rows.
• Leave until blank to make the pipeline unbounded.
• Requires an iterator column and keeps track of the greatest iterator value that has been processed.

Iterator Columns and Timestamps

When using incremental pipelines in Warehouse Sync, you must specify an iterator column—a timestamp field (such as datetime, date, or Unix timestamp) that mParticle uses to track which rows have already been processed. This iterator column is essential for reliable incremental updates and should be distinct from your event timestamp field whenever possible.

Best practices for iterator columns:

• Use a dedicated iterator column (such as a system timestamp indicating when a row was inserted or updated) for incremental syncs. This is preferred because the time an event occurred (event timestamp) often differs from when the row was added or updated in your warehouse (iterator/system time).
• When using incremental pipelines in Warehouse Sync, you must specify an iterator column—a timestamp field (such as datetime, date, or Unix timestamp) that mParticle uses to track which rows have already been processed. This iterator column is essential for reliable incremental updates and should be distinct from your event timestamp field whenever possible.
• Use the event timestamp field for filtering in your SQL (for example, in a WHERE clause) and for mapping to the appropriate mParticle field.
• When setting up your field transformation, you may ignore the iterator column if it’s only used for sync tracking.
• You may use the event timestamp field as the iterator column if convenient, but this is not recommended for most use cases.
• You can offset the iterator column by a fixed amount to accommodate late-arriving data. For example, if data typically arrives in your warehouse one day after creation, set the pipeline’s delay field to 1d to account for this upstream processing time.

Filtering the Data You Ingest

Warehouse Sync provides two complementary ways to control which rows are pulled from your warehouse:

1. SQL WHERE clauses – Add predicates such as WHERE event_timestamp >= DATE '2023-01-01' or a bounded BETWEEN statement inside the query you save with the data model. The filter executes in your warehouse on every run. This is recommended for filtering on fields other than the iterator field, such as event timestamp, event type, or user region, and is useful for validating your pipeline by limiting the dataset to a small subset of data.
2. Iterator window (from / until) – Use the from and until fields on sync_mode when calling the API to set the minimum and maximum iterator values a pipeline will ever request. This is recommended for a seamless transition between your initial backfill and ongoing incremental syncs, and makes your pipeline runs more easily auditable via the pipeline status APIs.

A row must satisfy both the iterator window and your SQL to be ingested, giving you granular control over the time span and the business logic applied to your data.

Practical Patterns for Historical Ingestion

There are two common strategies for ingesting historical data efficiently:

1. Single incremental pipeline with an early from date – Set the from value to the start of the period you want to ingest, and leave until blank. The initial run will backfill the entire range, after which the pipeline will automatically switch to incremental updates on its schedule.
2. Separate historical full and incremental pipelines – Create a full On Demand pipeline and use a WHERE clause in your data model to limit the dataset (for example, WHERE event_timestamp BETWEEN '2024-01-01' AND '2024-06-30'). Trigger the pipeline whenever you need to ingest another slice, updating the clause between runs as needed. Once you have finished ingesting the historical data, disable the full pipeline and create a new incremental pipeline to sync ongoing new data on a regular schedule, setting the from value to the last date you ingested with the full pipeline.

Important notes:

• Full pipelines re-read the entire result set every time they run and are well-suited for small tables or ad-hoc, on-demand replays. They may ingest duplicate data due to deduplication limits for very large datasets. Use SQL WHERE clauses to ensure no overlap in data between runs.
• Incremental pipelines require an iterator column and only fetch rows whose iterator value is greater than the last successful run. The first run backfills everything from the from value you set; subsequent runs pick up new rows. Leave until blank to let the pipeline continue indefinitely.

Performance Considerations

By default, a Warehouse Sync Pipeline will ingest your data at a rate according to your account’s configured limits and expected data volumes. When the Warehouse Sync API or UI shows a “success” status, it means your data has been ingested and accepted to be processed by other mParticle systems, such as user activity view, audiences, calculated attributes, and connected outputs. Keep in mind that while recent non-historical data may be available quickly, some downstream features and integrations may require additional processing time before all ingested data is available end-to-end. This is especially true for large or historical data loads which are processed differently than real-time data and connected outputs which have their own processing times.

If you expect to ingest large volumes of data or have specific timing requirements, reach out to your Customer Success Manager (CSM) to review your configuration and ensure optimal pipeline performance.

Ingesting user profile data

User profile data includes information like what identities a user has (such as an email address, user name, or account number), custom attributes (such as subscription status), or device and demographic information. User data in mParticle is stored in user profiles which can be used to create segmented audiences that you can engage with using downstream marketing tools.

You can use warehouse sync to ingest user profile data from your database into mParticle, where it can be used to create new, or enrich existing, profiles that describe your users.

Ingesting events data

Event data describes the actions that your users take in your app, website, or product. This could include information about pages your users visit, videos played, products added to wishlists, and more.

Continue reading below for general information about how to access the Warehouse Sync API. For a step-by-step tutorial on how to start creating your first warehouse sync pipeline, go to the Warehouse Sync API Tutorial.

Prerequisites to Accessing the Warehouse Sync API

To authenticate when using the Warehouse Sync API, you will need a new set of API credentials.

1. From your mParticle account, hover your cursor over the Settings gear icon in the left hand nav and select Platform under settings.
2. Select the API Credentials tab and click +Add Credential.
3. Give your new credential a descriptive name, check the box next to Platform and select Admin from the Permissions dropdown menu.
4. Click Save, and copy the Client ID and Client Secret. You will use these when fetching an OAuth access token.

Authentication

After creating your new API credential by following the steps above, you can authenticate by issuing a POST request to mParticle’s SSO token endpoint.

https://sso.auth.mparticle.com/oauth/token

The JSON body of the request must contain:

• client_id - your Client ID that you saved when creating your new API credential
• client_secret - your Client Secret that you saved when creating your new API credential
• audience - set to a value of "https://api.mparticle.com"
• grant_type - set to a value of "client_credentials"

Curl Syntax

curl --request POST \
  --url https://sso.auth.mparticle.com/oauth/token \
  --header 'content-type: application/json' \
  --data '{"client_id":"...","client_secret":"...","audience":"https://api.mparticle.com","grant_type":"client_credentials"}'

Sample Raw HTTP Request

POST /oauth/token HTTP/1.1
Host: sso.auth.mparticle.com
Content-Type: application/json

{
  "client_id": "your_client_id",
  "client_secret": "your_client_secret",
  "audience": "https://api.mparticle.com",
  "grant_type": "client_credentials"
}

Using your Bearer Token

A successful POST request to the token endpoint will result in a JSON response as follows:

{
  "access_token": "YWIxMjdi883GHBBDnjsdKAJQxNjdjYUUJABbg6hdI.8V6HhxW-",
  "expires_in" : 28800,
  "token_type": "Bearer"
}

Subsequent requests to the API can now be authorized by setting the Authorization header as follows:

Authorization: Bearer YWIxMjdi883GHBBDnjsdKAJQxNjdjYUUJABbg6hdI.8V6HhxW-

Versioning

Once you have authenticated, the API resources can be accessed at https://api.mparticle.com/platform/v2/. Subsequent updates to the API that introduce breaking changes will be published with a new version number in the URL.

HTTP Methods

This API uses the HTTP methods GET, POST, PUT, and DELETE.

Headers

This API accepts and sometimes requires the following headers:

Request Bodies

All POST/PUT requests should send JSON as the Request Payload, with Content-Type set to application/json.

Limits

In addition to the standard default service limits, note the following limits specific to the Warehouse Sync API: