Step 4: Unifying historical and new data

After Hightouch Events is collecting data, decide how to combine new events with historical data from your previous provider. Most migrations need this step so dashboards, models, and destinations keep a continuous event history. (If you're rebuilding your data model or want a fresh start, you can skip it.)

Plan your unification strategy

Before you begin, consider the following:

1. Time frame: Based on your use cases, decide how far back you need to unify data (for example, 1 year, 2 years, or all historical data). Your decided time frame affects processing time and storage requirements and ensures you have enough historical context for your analyses.
2. Data volume: Assess the volume of historical data you need to unify. This could impact processing time and storage costs and may influence your choice of unification method.
3. Schema differences: Identify any differences between your old and new event schemas if you haven't in a prior step. If there are changes, this will inform any necessary data transformations during the unification process.
4. Storage considerations: Determine where the unified data will be stored (for example, an existing or new table in your data warehouse). Your decision can affect query performance, how you update downstream destinations, and may have cost implications.

You have a few options for where the unified data lives: the previous provider's tables, the Hightouch tables, or a separate unified table or view. During validation, keep Hightouch data in its own schema so you can compare it against your historical data without modifying either set. For the long-term unified dataset, many teams use a separate unified table or view. In some warehouses, you may also choose to backfill historical rows into the Hightouch event tables after validation, but treat that as an implementation choice rather than the default recommendation.

Union historical and new data

To keep your analytics continuous for data consumers, union the historical Segment data with the new Hightouch Events data.

Your process will depend on where you've decided to store the unioned data and any needed data transformations. We've sketched in SQL here for inserting 2 years of historical data from a prior provider (in this case Segment) into the Hightouch tables with minimal transformation.

In this particular case, we're only going to insert events from Segment that don't have a context_migration_id, since any dual-tracked event already exists in the Hightouch tables. The context_migration_id column only exists when your event storage destination writes context fields as columns; if it stores context as JSON, extract the value from the JSON column instead.

-- Insert historical data from Segment to Hightouch identifies table
INSERT INTO HIGHTOUCH.identifies (
  id, anonymous_id, user_id, timestamp, email, name, context_migration_id
)
SELECT
  s.id, s.anonymous_id, s.user_id, s.timestamp, s.email, s.name, s.context_migration_id
FROM SEGMENT.identifies s
WHERE s.context_migration_id IS NULL
  AND s.timestamp >= DATEADD(day, -730, CURRENT_DATE());  -- Adjust time frame as needed

Modify this for table names and to include any traits or other properties you need. Repeat similar INSERT statements for tracks, pages, and other relevant tables, mapping the columns appropriately.

If there are differences between Segment and Hightouch schemas, you may need to transform the data during insertion. If you are migrating from Segment, for example, the schemas are quite similar and should only require basic SQL transformations, if any.

The example above uses Segment table and column names to keep it concrete. The approach is the same for any source—substitute your provider's names. See the RudderStack and mParticle guides for platform-specific notes.

Validate the unified dataset

We've previously validated that Hightouch Events is collecting an equivalent volume of events with matching values. For peace of mind, after unifying the de-duped data, you can perform some basic validation steps:

1. Check event counts: Ensure the total event count makes sense based on the historical data and new Hightouch events.

SELECT
    DATE_TRUNC('day', timestamp) as date,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as user_count
FROM HIGHTOUCH.tracks
GROUP BY 1
ORDER BY 1;

2. Verify data continuity: Look for any unexpected drops or spikes in event volume, especially around the migration period.
3. Compare with Segment data: Run a comparison query to ensure all historical data was properly migrated.
4. Sample data: Manually review a sample of events from different periods to ensure data format and content consistency.

By following these steps, you'll create a unified dataset in your Hightouch tables that combines your historical Segment data with new events from Hightouch Events. This approach ensures data continuity and provides a single source of truth for all your event data.

Remember to adjust the SQL queries as needed to match your specific schema and table names. Also, consider running these operations in batches if you're dealing with a large volume of historical data.

In the next section, you'll update downstream applications and destinations so each consumer reads from Hightouch Events.