Historical Data and Warehouse Sync

When ingesting historical event data into mParticle with Warehouse Sync, it is important to consider historical data handling, data quality, data retention, and platform limits. Historical data is processed differently by mParticle, so pipelines that ingest it have special considerations and requirements.

This guide outlines key points and best practices for handling historical and large-volume data ingestion.

There are two approaches to ingesting data with Warehouse Sync:

• Full syncs: Ingesting data in batches, on demand. Full pipelines re-read the entire result set every time they run and are well-suited for manual syncs of a specific set of data, small tables, or ad-hoc, on-demand replays/retries.
• Incremental syncs: Ingesting data incrementally, on a schedule. Incremental pipelines require an iterator column and only fetch rows whose iterator value is greater than the last successful run. This is the recommended approach for ongoing data syncs.

When ingesting historical data, you can use a combination of these two pipeline types to backfill old data and then switch to an ongoing incremental sync.

What is historical data?

As a general rule, any event with a timestamp older than 30 days is considered historical. This data requires special handling to ensure it is processed correctly and made available for long-term use cases like audience segmentation with extended lookback windows.

How mParticle processes historical data

Data that you flag as historical is handled differently from real-time data:

• No real-time forwarding: Historical events are not forwarded to any connected event, data warehouse, or audience outputs. This prevents old data from triggering real-time workflows in downstream systems.
• Optimized for historical workflows: The data is retained and can power features that rely on long-term activity (for example, audiences with extended look-back windows), though are subject to your account’s data retention policies.
• Delayed UI visibility: Because historical uploads bypass the real-time processing stream, these events and the profile updates they generate may take longer to appear in interfaces such as Live Stream or the User Activity view. Generally, historical data is available in the User Activity view within 24 hours of ingestion.
• Data retention limits still apply: mParticle enforces data retention policies that define how long your data remains available and if older data is made available for use. Before loading very old events, coordinate with your mParticle account team to ensure your retention configuration can accept them; otherwise, the data may be discarded during ingest.

All other processing rules for identity resolution and storage remain the same for historical data.

Types of historical data you can ingest

A core concept in mParticle is the relationship between user data (attributes and identities) and event data. Understanding this is critical for a successful historical data ingest.

Data is sent to mParticle in batches (multiple events batched together) to optimize throughput. Each batch contains event data, and provides context about the user in the form of user attributes and identities.

• Events are actions a user takes at a specific point in time. For example, a product_view event captures that a user looked at a product. Event data is captured and stored as events using mParticle’s event data format.
• User Attributes are properties that describe a user, like membership_tier: gold.
• User Identities are the identifiers used to recognize a user, like an email address or customer ID.

User attributes and identities are stored in a persistent User Profile, which creates a complete, 360-degree view of your user.

When ingesting historical data, you can include user attributes and identities in the same batch as your events. However, in many backfill scenarios, it’s more effective to ingest them separately. This is particularly true when your goal is to have the final user profile reflect the most recent information about a user, rather than the state they were in when a historical event occurred. For example, you can send a batch containing only user_attributes and user_identities to update a user’s profile to ensure that the profile is accurate and up-to-date, then ingest the users’ events when the profile reflects the most recent information.

Historical data ingestion limits and performance considerations

mParticle enforces service limits specific to Warehouse Sync, including limits on:

• The number of active pipelines per account
• The number of records ingested per interval type (hourly, daily, weekly, and monthly)
• The number of historical records ingested
• The number of database columns ingested

Warehouse Sync ingests 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 can be used by downstream features and sent to output integrations. 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. This is especially true for large or historical data loads that are processed differently from real-time data and connected outputs which have their own processing times.

Recommended best practices

• Review the Default Service Limits before ingesting a large volume of data.
• Estimate the total number of records you plan to ingest, both for the initial historical load and for ongoing scheduled runs. Consider whether you will need to re-ingest historical data for testing or validation, and how many new or updated records will be included in each subsequent interval.

• Based on your estimates, coordinate with your mParticle Customer Success team if you anticipate exceeding any limits.

  • Additionally, communicate your timeline so the large data load can be processed according to your requirements.

Distinguishing batch and event timestamps

When ingesting historical data, it’s essential to distinguish between batch timestamps and event timestamps:

• Batch timestamp: Indicates when the batch was created. Setting this is optional and should only be used if you need to control the order in which user attributes are synchronized—specifically, when updates may arrive out of order and must be processed according to the batch’s timestamp.
• Event timestamp: Represents when each event actually occurred. Event timestamps must not be later than the batch timestamp and are subject to your account’s data retention window. You can control how the platform processes events based on the event timestamp using the source_info.is_historical field.

Key points

• If you are ingesting event data, do not set the batch timestamp. Let mParticle use the event timestamps to determine event order and processing.
• If you need to update user attributes based on when they were last changed, set the batch timestamp to match the user attribute’s timestamp, and ingest these updates separately from event data.

This approach ensures that both event and profile data are processed accurately and in the correct order, while avoiding issues with data retention, forwarding, and audience availability.

Strategies for ingesting historical data

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.

How to create a pipeline for historical data

This section provides an overview of creating a Warehouse Sync pipeline specifically for ingesting historical data. For a complete, detailed walkthrough of how to create a Warehouse Sync pipeline, refer to the main Warehouse Sync setup guide.

1. Connect to your warehouse

The first step is to connect mParticle to your data warehouse. This process is the same for all Warehouse Sync pipelines.

2. Create a data model

Your data model is the SQL query that defines what data to pull from your warehouse. When ingesting historical data, your query needs special consideration.

Mark data as historical

To ensure mParticle correctly identifies and processes your historical data, you must explicitly flag it when creating your data model. The “Sync Historical Data” setting in your Warehouse Sync pipeline only controls how far back the pipeline reads from your warehouse; it does not automatically mark old data as historical.

Example SQL:

Add a CASE statement to your SQL query to create an is_historical column.

SELECT
  *,
  CASE
    WHEN event_timestamp < CURRENT_DATE - INTERVAL '30 days' THEN TRUE
    ELSE FALSE
  END AS is_historical
FROM your_table

Filter the data you ingest

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

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.

Iterator window (from / until)

Use the Sync Historical Data setting in the UI or 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.

Choose an iterator column

When using incremental pipelines in Warehouse Sync, you must specify an iterator column—a timestamp field (such as datetime, date, or Unix timestamp) that tracks 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).
• Use the iterator column to track which rows have been processed, ensuring reliable incremental updates.
• 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 mappings, 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.

3. Review your mappings

In the “Create data mapping” step of your Warehouse Sync setup, map the is_historical column from your SQL query to the source_info.is_historical field. You should also set a channel for the data. For more details on this process, see Field Transformations.

Example Field Transformation Mapping:

[
  {
    "mapping_type": "column",
    "source": "is_historical",
    "destination": "source_info.is_historical"
  },
  {
    "mapping_type": "static",
    "destination": "source_info.channel",
    "value": "server_to_server"
  }
]

4. Set your sync settings

Your sync settings determine whether your pipeline runs on a schedule (incremental) or on-demand (full), and over what time period. These settings work together with the WHERE clause in your data model to control what data is ingested.

• For full sync pipelines, you will typically trigger them on-demand for specific historical ranges defined in your SQL query.
• For incremental sync pipelines, you will set a schedule and an iterator window. For a historical backfill, set the from date to the beginning of your historical period. Subsequent runs will automatically pick up where the last run left off.

5. Final review

Before activating your pipeline, review all settings to ensure they match your intended historical ingestion strategy. It’s a best practice to start with a small test batch to validate your configuration before running a large backfill.

Best practices for large historical data backfills

Successfully ingesting large volumes of historical data requires thoughtful preparation. In addition to the specific practices embedded in the steps above, keep the following general guidelines in mind:

• Start small: Always begin with a small test batch. This allows you to validate your configuration, surface any issues early, and make adjustments before committing to a full-scale backfill.
• Batch your loads: Break your historical data into manageable segments (for example, by year or month). Batching makes it easier to monitor progress, isolate and troubleshoot errors, and prevents overwhelming your pipeline. Keep in mind that very large backfills can take several days, may not display intermediate progress in the mParticle UI, and often cannot be paused or stopped once started.

• Evaluate connected systems and features:

  • Event Tiers: Use Event Tiers to control which events are stored or evaluated. Not all events may be necessary for replays or analysis, especially if you use Audiences or Calculated Attributes. With Tiered Events, you can collect data without storing it, or store data without evaluating it.
  • Forwarding: Since historical data is not forwarded, consider which data is needed for your use case and what is not.
  • Audiences: Audiences rely on event timestamps for processing and retention. Avoid setting batch timestamps when ingesting events, as this can cause very old events to be excluded if the batch timestamp falls outside the audience’s retention window. Because audience calculations performed during ingest may be based on incomplete data, it’s best to set up your audience after the backfill is complete to ensure calculations are accurate and include the full historical dataset.
  • Calculated Attributes: Calculated Attributes behave similarly to audiences—calculations performed during ingest may be based on partial data, affecting accuracy until all data is loaded.
• Review connected outputs: If you use connected outputs, confirm they can handle the increased data volume. For audiences, consider configuring them after the backfill to ensure calculations include the full historical period.
• Stagger multiple sources: When syncing from multiple sources, schedule syncs at different times to avoid overloading your pipeline and to reduce processing delays.
• Ingest user profiles first: If you are loading both user and event data, always ingest user profiles before events. This ensures user information is current and available for accurate event processing.
• Let mParticle set batch timestamps: For event data, do not manually set the batch timestamp. Allow mParticle to use the event timestamps to maintain correct sequencing and processing.
• Coordinate with mParticle: Before starting a large backfill (especially for data older than 30 days) notify your mParticle customer success team. They can help you to prepare and monitor your ingest, and verify that your account limits are sufficient for the expected data volume.
• Choose the right pipeline type: Use incremental pipelines for ongoing syncs and seamless transition between loading your historical data and ongoing syncs, and reserve full pipelines for ad-hoc, on-demand replays where you want a complete refresh. Remember that a pipeline’s sync_mode can’t be changed after creation.
• Filter Intelligently: Combine SQL WHERE clauses in your data model with pipeline from/until iterator windows to precisely control which rows are ingested and avoid overlapping backfill runs.

Work with your customer success team

Your mParticle customer success team can:

• Advise on best practices for large ingests
• Help adjust account limits if necessary
• Provide guidance on error handling and observability

Engage your Customer Success team early in the planning process for large or unusual data ingests.

Additional Resources

• Warehouse Sync Documentation
• Default Service Limits
• Event Tiers
• Field Transformations