How do you avoid duplicates during data migration?

Required tools

• Source data in Excel — From all the systems to be migrated.
• Defined key — Organization number for businesses.

Before migration

1. 1.Clean up the source, not the target

   Merge duplicates where the data is currently stored. After migration, the records will have new IDs, relationships and activities will be split between both records, and merging them will require significantly more work.

2. 2.Standardize spelling

   AS, A/S, AS. with a full stop, uppercase and lowercase letters. Standardize before comparing; otherwise, you will only find a fraction of the duplicates.

3. 3.Create an alternate key in Dataverse

   Define the organization number as an alternate key on the company table. This allows the import to match existing records instead of creating new ones.

During and after

1. 4.Migrate from one source at a time

   If the data comes from three systems, handle one at a time and check after each one. That way, you’ll know which source introduced the problem.

2. 5.Use update instead of create when the key exists

   With an alternate key in place, the second and third sources can update existing records instead of creating new ones. This is the mechanism that prevents duplicates across sources.

3. 6.Run a duplicate check after each source

   Don't wait until the end. Duplicates found early can be corrected before relationships and activities are linked to the wrong record.

4. 7.Set up continuous duplicate checking before deploying to production

   Migration is a one-time job, but new duplicates will start appearing from day one of operation. The rules should be active when users are granted access.