Data Sync handling for Visitor or Account ID merges

When Pendo merges Visitor or Account IDs, also known as remapping, it rewrites your historical data in Pendo so that it's associated with the new IDs. Data Sync doesn't rewrite data that it has already delivered to your destination. After a merge, your destination holds old IDs in the data delivered before the merge and new IDs in the data delivered after it.

This article explains which exported data a merge affects, how to manage your exports while the merge runs, and how to reconcile the data already in your destination.

To learn more about the merge process itself, including the mapping file format and supported merge types, see Merge Visitor or Account IDs.

This guidance applies to all Data Sync destinations. Table, file, and column names differ by destination. For the names that apply to yours, see Data Sync schema definitions.

How a merge affects Data Sync

Data Sync doesn't detect an ID merge. Pendo rewrites your historical data internally but doesn't deliver the rewritten data to your destination. During and after a merge, Data Sync doesn't:

• Reissue or replace data that it has already delivered.
• Delete records associated with old IDs.
• Revise the export manifest in cloud storage destinations. Earlier entries stay as they are, and nothing in the manifest shows that a merge happened.

When visitors are merged, Pendo marks the merged visitor record as updated, so the next ongoing metadata updates export delivers it with the new ID. Records for the old IDs stay in your destination.

Your destination ends up with a split history: old IDs in exports delivered before the merge, and new IDs in exports delivered after it. Your team is responsible for reconciling this split, using one of the approaches in Reconcile your destination.

Note: Identity mapping, which connects anonymous visitors to identified visitors, is a separate process and is already represented in your exports. An ID merge doesn't change how identity mapping data is exported.

Data affected by a merge

Every export that contains a Visitor ID or Account ID is affected. The following table shows where those IDs appear.

Event data is the largest volume of affected data. Every event row contains both IDs, so a merge of a few thousand visitors can affect hundreds of millions of event rows. Plan your reconciliation around event data first.

Where metadata is delivered as one row for each visitor or account and field, each row contains the ID and is affected by a merge. In cloud storage destinations, the metadata schema file contains field definitions only, with no IDs, so it isn't affected. The values themselves are in the visitor and account files.

Visitor and account data include a hash of the ID, which is derived from the ID and changes with it. You can't calculate the hash yourself. If you join, partition, or deduplicate on the hash, take the new values from visitor or account data exported after the merge.

Before the merge starts

Complete these tasks before Pendo starts the merge:

• Keep your mapping file. The CSV file of old and new IDs that you give Pendo is the same mapping you use to reconcile your destination.
• Export your custom metadata. Custom metadata isn't carried over to the new IDs, and Data Sync exports reflect the current state of your data in Pendo. Custom metadata lost in the merge is missing from your exports until you restore it. For more information, see Restore custom metadata.
• Decide how to handle your exports during the merge. For your options, see the next section.

Manage your exports during the merge

You can pause Data Sync while the merge runs or let it continue. The best approach differs for event data and for account and visitor metadata. Regardless of approach, you still need to reconcile your destination afterward, because your data from before the merge contains the old IDs.

Event data

You can turn off the event export, pause loading on your side, or continue as normal. All three approaches are recoverable. If you turn off the export and miss a period of data, you can retrieve it later. For a cloud storage destination, create a one-time export for the missing dates. For a warehouse destination, contact Pendo Support to backfill them.

Turning off an event export stops Pendo from creating new retroactive exports for it. Retroactive exports that are already queued are still delivered, so they stop within about 24 hours.

Because event data is recoverable, base your choice on your reporting needs:

• Pause to get a single cutover point to reconcile against. Your data is stale until the merge completes.
• Continue to keep your data current. Your destination has a period in which both old and new IDs are present.

Account and visitor metadata

In warehouse destinations, account and visitor data keeps syncing as normal during the merge. No action is needed until you reconcile.

In cloud storage destinations, account and visitor exports are available as a one-time export, which delivers every account or visitor in your subscription, or as an ongoing metadata updates export, which delivers only the accounts or visitors updated in the previous 24 hours. There's no export for a date range, so you can only fill a gap in the ongoing export by reloading all account or visitor data.

To avoid a gap, leave the export running in Pendo and pause loading on your side. Don't turn off or pause the account or visitor export in Data Sync. While loading is paused:

• Record your position. Pendo doesn't track which exports you've loaded. Record the counter value of the last export you loaded from the export manifest, so you know where to resume.
• Keep the pause under 30 days. The export manifest lists exports from a rolling 30-day window, and older exports are no longer listed. Most merges complete within two weeks, but larger merges can take longer, so check how many exports are waiting rather than waiting for the merge to complete.

If you keep loading during the merge

If you continue to load exports while the merge runs, expect the following until you reconcile:

• Exports delivered after the merge starts contain the new IDs. Records you've already loaded keep the old IDs.
• Records associated with old IDs stop updating, but aren't deleted.
• Counts of distinct visitors or accounts are too high, because the same visitor or account appears under two IDs.

Mapping IDs at query time handles this mixed period without extra work, because records that already have the new ID pass through the mapping unchanged. If you update IDs in place instead, make sure you can run the process more than once with the same result. Take particular care with many-to-one merges, where records with the new ID might already be in your destination.

Important: Don't start reconciling until Pendo confirms that the merge is complete. Data delivered while Pendo is still rewriting historical data can overwrite changes you make.

Reconcile your destination

After Pendo confirms that the merge is complete, choose how to reconcile the data in your destination. Use the following table to choose an approach.

You can use a different approach for event data and for account and visitor data. For example, you can reload account and visitor data and map event data at query time.

Map IDs at query time

Keep the old IDs in your destination and resolve them to the new IDs when you query the data. This is usually the best approach for large event volumes.

1. Load your mapping file into your destination as a permanent table.
2. For each affected dataset, create a view that joins the dataset to the mapping table with a left join. The view returns the new ID where a match exists and the original ID where it doesn't.
3. Point your reports and downstream queries at the views instead of the underlying tables.

For example, a view over event data might look like the following. Replace the table and column names with the ones for your destination.

CREATE VIEW events_current_ids AS
SELECT e.*,
       COALESCE(m.new_id, e.visitor_id) AS current_visitor_id
FROM events e
LEFT JOIN id_mapping m
  ON m.old_id = e.visitor_id;

This approach has the following advantages:

• The work scales with the size of the mapping, typically thousands of rows, rather than with your event history, which can be billions of rows.
• No data is deleted. If the mapping is wrong or changes, you correct the mapping table.
• You keep a record of the IDs that were originally collected, which helps when you investigate a discrepancy.
• Many-to-one merges need no extra handling. Several old IDs resolve to one new ID and aggregate correctly.

The mapping table becomes a permanent part of your data model. Only the views reference it, so reports and other downstream consumers see a single ID column.

Note: In a many-to-one merge, several old visitor or account records resolve to one new record, and their metadata can conflict. The merged record holds the values that Pendo kept, so use the record delivered after the merge rather than combining the old records yourself.

Reload your data

Delete the affected data from your destination and load it again from Pendo. This approach needs no mapping logic and leaves no ongoing complexity, but its compute and time cost grows with your total data volume.

1. Delete the affected tables or data from your destination.
2. Request the data again. For a cloud storage destination, create a one-time export of the data you deleted. For a warehouse destination, contact Pendo Support.
3. For a cloud storage destination, load the export into your destination. Warehouse destinations load the data for you.

Reloading works especially well for account and visitor data, because it delivers every visitor or account as it exists after the merge, with its new ID.

Note: Event backfills have a time limit. For cloud storage destinations, a one-time export covers up to three calendar years. For warehouse destinations, contact Pendo Support for the range available to you. Map or update any event data older than the backfill can reach.

Update IDs in place

Update the historical records in your destination so that they contain the new IDs. This costs less than a full reload and needs no permanent mapping, so existing dashboards keep working without changes. It's also the most error-prone approach, and you can only undo it by reloading your data.

The steps depend on the type of merge.

One-to-one merges, where each old ID becomes one new ID:

1. Load your mapping file into your destination as a table.
2. In each affected dataset, update the ID columns from the mapping table. Include event data, which is the dataset most often missed.
3. Delete the mapping table.

Many-to-one merges, where several old IDs become one new ID:

1. Load your mapping file into your destination as a table.
2. Delete the records associated with the old IDs from your visitor and account data.
3. Load the merged visitors or accounts under their new IDs. The ongoing metadata updates export delivers merged visitors after the merge. To make sure you have every merged record, reload your visitor or account data.
4. In your event data, update the ID columns from the mapping table, as for a one-to-one merge. It's expected that several old IDs point to one new ID.
5. Delete the mapping table.

Warning: In a many-to-one merge, don't update visitor or account records in place, and don't combine them yourself. Pendo has already combined them under the new ID. Updating two old records to the same new ID creates two records with the same ID, which is the most common cause of duplicate-key errors after a merge.

Restore custom metadata

What you need to restore depends on where your metadata comes from:

• Web SDK and Segment.io metadata is carried over to the new ID. No action is needed.
• Salesforce and HubSpot metadata isn't carried over. After you update the integration to match records on the new IDs, the metadata repopulates on the integration's next sync.
• Custom metadata isn't carried over and isn't repopulated. Restore it from the export you made before the merge.

Before you upload your custom metadata export, replace the old IDs in it with the new IDs from your mapping file. A CSV upload that contains IDs that don't exist in Pendo creates a new, empty record for each one.

You can restore custom metadata with a CSV upload or through the metadata API. For steps and limits, see Manage custom metadata fields. Restored values are included in your next metadata export.

Tip: Each metadata API call must finish within five minutes, or it returns a 408 Request Timeout error. For large restores, split your records into batches that each finish within that limit.

Troubleshoot old IDs in exports after a merge

If old Visitor or Account IDs still appear in your exports after the merge is complete, a source outside your Pendo installation is usually still sending them. Common sources are metadata API calls, scheduled CSV uploads, and integrations whose ID mapping hasn't been updated. Each of these can recreate a record for an old ID after the merge.

These records have no events and no First Visit value. They also make counts of distinct visitors or accounts too high.

To remove them:

1. Update every integration, upload process, and pipeline that still sends the old IDs.
2. Contact Pendo Support to remove the empty records.

Important: If you remove the records before you update the source, they're created again the next time the source sends data.

Related articles

• Merge Visitor or Account IDs
• Data Sync account and visitor export handling
• Data Sync event export handling
• Data Sync schema definitions
• Overview of Pendo Data Sync