Choosing Suitable Key Mapping Fields

The key mapping fields for a table identify which field mappings should be used to uniquely identify the target record. To avoid situations such as duplicates or skipped records when performing a migration it is vital to follow the advice below.

When choosing key mappings, you need to consider both the values on the source or on the target. Note that the match is comparing the transformed value generated from a field mapping and the target value that existing records have. 

The recommendations below should help in understanding how to effectively use key mappings.

Choose fields that have a consistent value

• Only choose target fields that are unlikely to change value between migrations.
• The source values that are used to populate the target should also be constant. If a source value involved in the key mapping changes, a re-run of the project will generate a duplicate record instead of the intended update.

Choose fields that define a unique value

• If a single field is selected, then all target records should have a unique value for that field.
• If multiple fields are selected, then the combination of all key field values must be unique.
• Case sensitivity in matching will depend on the target server. It is usually recommended to initially assume that the matching will be case sensitive.
• If the key mapping is not unique, you may see skipped record in the report with an message 'Another record is being migrated with this key'. Do not ignore these, unless intentional.
• If in doubt about uniqueness of your key mapping fields, set the Multi Match option for the key mapping to log error to flag up any issues during testing.
• By default, if multiple target records are found because the key mappings are not unique, PB will update the first matching target record only - PB will NOT update all records. To avoid this, set the multiple match option to skip or error - preventing the record being migrated and flagging issues in the report.
• Sometimes, it is easiest to add a field on the target specifically for forming a simple relationship between the source and target. This field will be simple-mapped with the primary key or other unique field from the source table. The other advantage of this strategy is that it makes it easy to see which records on the target that Precision Bridge has migrated.

Choose fields that are indexed, if possible where the target table is large

• Not all platforms support indexing, but if they do, adding an index on the target for the key mapped fields, or selecting key mapped fields that are already indexed will improve performance.
• When multiple fields are used for key mapping, a single index on a high cardinality field included in the key mapping fields will usually be enough.
• Consider indexing if the target table has, or will have after migration completes, over 20,000 records.

If possible, choose fields that will never be NULL (empty)

• If nulls are possible, you will need to check the 'allow null key values' option on the key mapping. If you don't do this, you will get messages such as 'Entry contains a NULL key field value' and the record will error.
• Precision Bridge will match on a NULL value if there is a record or records on the target that have a null value for the field. This can sometimes lead to unexpected results, particularly if there is test data on the target where records are incomplete.
• If you do allow fields that can be null, the combination of the field values must still be unique, otherwise only the first matching will be migrated by default (see unique value points above).
• If you don't expect nulls, leave the 'allow null key values' disabled to flag source data or mapping logic issues as errors.

Watch out for truncation issues

• If the string value being pushed by Precision Bridge into the target is longer than the field can take, the target server may truncate it. This will typically not result in any error or warning, but if this field is the key mapped field, key mapping will not work and duplicates may occur because the value Precision Bridge is pushing to target is not the same as the value actually on the target.

• Where a simple mapping is used and the length of the source and target field is known, Precision Bridge will record a warning in the validation report for the mapping.

Changing Key Mappings may cause duplicates

• Be absolutely sure your key mappings are correct before migrating a large number of records. If your logic is incorrect it will be easier fix for a small number of records.
• If you do change the selection of key mappings after running the project for the first time, you may end up with duplicates. If possible, prevent this by deleting the previously migrated target records before running the migration again. Note that PB will not typically report such issues as the history of previous migrations is not considered.
• Changing the field mappings of key mappings themselves between project runs will also likely cause duplicated records.
• Duplicates can cause issues in later projects dependent on records in tables with duplicates. For example, if you migrate users with the key mapping set to correlate on email address, find this is invalid, then change to correlate on user id instead, you might create duplicates. A lookup in a later project to get the assignee user for a ticket might now not work as expected if the user now has 2 records.

Final thoughts

Good key mappings will simplify your project and avoid performance issues, duplicates or skipped records. Spend some time analysing the source and target data to identify which columns will be best to select as key mapping fields. This will pay dividends later on when you come to run your projects by avoiding duplicates, that may need to be manually removed, skipped records that can result in missing data following migration and confusing results.

Related Articles

Key Mappings (from quick start guide)

Introduction to Key Mappings