[FEATURE REQUEST] - Extract Unique Records by External ID

[PotSeht]

Sometimes, we may be working with an org that has poor data quality, primarily, duplicate records. Take the below example of three accounts, each with the same name. Importantly, there is no other writeable fields that can be used to distinguish one from another.

We may use this to seed data with production data, but disregard duplicated records.

Take the below example of three accounts with the same name.

If I run an Upsert using externalId : Name from this environment to a sandbox, the three records migrate across. However, upon running the same command again, the result is five records.

I believe this is because at the point of the second run, the source and target is matching against one of the accounts, but not the other two. So it updates one record, and creates two others, which at least seems to be true in some form from the isolated test I ran below.

It would be helpful to only extract unique values based on the external ID supplied, so that only one record is migrated across and all child records use that single key to relate to. E.g 3 accounts with 1 contact each, would result in the contacts all consolidating under the one unique account upon migration.

My line of thinking was a parameter against ScriptObject that basicallly retrieves the first hit of each key and disregards the rest when querying.

That being said Im unsure if my use case is common enough to warrant this feature request, please consider it though! 🙂

[hknokh]

Hello, @PotSeht

Thank you for your feature request.
I will review it as soon as possible and provide updates as they become available.

Cheers

[github-actions]

This case has been marked as 'to-be-closed', since it has no activity for the 3 days.
It will be automatically closed in another 3 days of inactivity.

[hknokh2]

Hello!

Thank you for your suggestion.

While this could be a useful improvement in some cases, I have a couple of concerns:

1. The main purpose of an external ID is to uniquely distinguish between records. If that’s not sufficient, you can use Composite External ID Keys to ensure uniqueness. Therefore, maintaining the uniqueness of external IDs is a responsibility to address at your end.

2. This enhancement is quite complex and could lead to unexpected regressions, which I would prefer to avoid.

For these reasons, I will not be implementing this feature request.

Best regards.

[PotSeht]

Hello @hknokh

I appreciate considering the feature and understand your stance. I have been using composite external keys which is definetely helpful, although ultimately it does require looking at the data (we have clients where multiple external references may be duplicated for some bizarre reason).

I have started performing a check such as SELECT COUNT(ID), EXTID FROM SOBJECT GROUP BY EXTID HAVING COUNT(ID) > 1 which is a good indicator of whether the dataset contains duplicates across the one or more keys.