I apologize in advance as this question is likely to be more on the theoretical side, with (hopefully) a fair bit of discussion.

This is the scenario: We have Salesforce and a multiple local provisioning systems (short history - multiple companies were merged). As part of an effort to standardize, we setup an ETL that pulled the data from all of the local systems, transformed it and uploaded it to Salesforce. Currently we use Salesforce mainly as a Ticketing system and there is no write-back to the Local Provisioning system. The data is refreshed in Salesforce every 2 hours.

The function that the data in Salesforce provides is that as customer calls or emails come in, they are matched against Contact information (pulled from the local provisioning systems).

Our Marketing team is currently wanting to implement Salesforce Marketing cloud, which pulls data from the Contacts table.

Now you have the background - here is the issue:

Since we have a lot of 'duplicate' data - the 2 most common scenarios are:

Person A has an account with Brand X and Brand Y. The ETL sees 2 unique contact objects, assigned to 2 different brands, 2 different accounts with 2 different products and imports them as 2 separate contacts, even though the relevant details (Name, Email, Phone number) is the same.

Person A has multiple accounts within a Brand. Sometimes this can be due to having multiple businesses or they are a reseller but aren't using a Reseller login or whatever - in which case the ETL sees multiple unique contacts (as within the Provisioning system, the contacts have a unique and different primary Key) - and as above, the ETL sees this is multiple unique contacts, belonging to different accounts, having different product sets.

This causes an issue with the Match rates for case generation and it also potentially causes an issue with the Marketing tool that we want to use - in particular an Individual with Multiple accounts might receive multiple copies of Marketing material.

The Marketing team want to just De-Dupe the Contact data, however whilst I have a few theories as to how to do that, that would either:

1: Lose relationships between the local systems and Salesforce - There are some ancillary issues that may be generated here since Products are bound to contact information and when a case is created, the associated products are presented to the Agent.

2: Create some form of relationship mapping to try and preserve things - this may get extremely messy and may end up crossing Brand/Account boundries, which IMO could create Legal/Privacy issues if anything went wrong. Not to mention it creates something that is not pulled from the Source provisioning system.

Currently, I've designed this process with the mantra that what is in the source system must be replicated to Salesforce, but with what I'm being asked to do, will break that.

So - to sum up and ask a question: Are there any 'rules' about De-duplication and Data Integrity I should be aware of or reference? How would you architect such a requirement? Any Pitfalls or 'gotchas' or similar I should be aware of? Should I even pursue this course of action?


I'm looking more for some guiding principles as opposed to a specific answer - I appreciate that it's a bit broad, however Breadth is what I need right now, I should be able to work on the detail, but right now I need some help on the big picture.

First answer of find out what the business needs is a good one - so I'm going to sit down with the Marketing team and other stakeholders and see what we get.

More information and general principles/advice like that is appreciated.


There's no real hard rules when it comes to data cleanup/deduplication as it usually depends on these factors:

The quality of your dataset(s)

You don't mention quality being an issue so I won't say any more on this but usually matching data from different systems can be the biggest hurdle to tackle. e.g. David in one system might be Dave in another.

What the business needs

You already have a list of requirements and that's guided you so far. Now you've hit something that will impact the quality of the output. What would be beneficial is to sit down with the process and data owners and tell them the concerns you have with either method. It's not a solely DBA/Developer issue to solve and the other stakeholders can then be part of the solution. It help will avoid those "Why did you do that TheDemonLord! That's not what we wanted" accusations down the line when you thought you were doing the right thing but someone else disagrees!

You can then define the logic and rules around the dedupe effort together e.g. If a person has an account in with Brands X & Y then we will use the details from the most recent purchase etc...

How much effort can you devote to cleaning or transforming the data

Like any task there's a clear time/quality equation to deal with. You can easily get trapped in an ever growing list of manual fixes to have a perfect dedupe / extract process that captures all you need it to. This is another one to solve with the business. It's not your job to find every error and fix every issue. Define a process for highlighting and logging any accounts that might not fit your extract model. Then decide who fixes it, where and when.

As you first said, it is quite theoretical, and that's why the questions you uncover as you build it should be solved by everyone who is using the system.

  • The ETL process has a mechanism for emailing certain staff members a spreadsheet of invalid entries - when they get pulled into Salesforce they get replaced - so for example - if there are invalid characters in a first name - this gets logged and emailed and the entry into salesforce is 'firstname'. I should also add that despite the Companies size (~150 employees) there's a lot of functions that are being done by other people - for example I'm technically a Sysadmin and we don't have a DBA, but I know a fair bit with MSSQL so, I do what needs to be done. – TheDemonLord Aug 28 '19 at 0:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.