How to de-duplicate records using a SQL query
All about how to de-dupe records using SQL.
Recently was asked the question - what are the options/practices for using SQL to deduplicate records?
This is one of those discussions that will be of interest to those that find questions like this interesting, so if you like to put records into tables and you find that SQL is part of your workflow for doing that then read on.
If de-duping with SQL is not part of your day-to-day, then don't despair because the discussion is designed to be easy an ease read for both beginner and expert alike.
This discussion is split into three parts:
- Simple example of using SQL to transform a current to future state.
- Advanced real world how to SQL de-duplication in practice use cases.
- Suggestions, questions and comments.
If you prefer to watch visit the YouTube, otherwise read on.
Simple example of using SQL to transform a current to future state
For the purpose of demonstration an example will be used to explore records in a table with similar colours. In practice, duplicates may contain account, name, email, phone and address details which we will explore in advanced SQL use cases.
The current (or initial) state is a table with many duplicate records each containing a colour
Our current or initial state is a table with many records containing many duplication records.
To help us visualise this consider a table with many records where the many duplicate records each contains a colour.
In the pictured example there are sixteen records representative of eight colours consisting:
- Brown records occurring once.
- Grey records occurring once.
- Blue records occurring twice.
- Green records occurring four times.
- Purple records occurring twice
- Red records occurring trice.
- Orange records occurring twice.
- Yellow records occurring once.
Future (or desired) state of table with one record per colour
Once again, to help us visualise this consider a table where there is only one record for each of the eight colours.
In our future or desired state there is a table with eight records each containing only one colour.
Other way of considering the future state is that there is a table containing no duplicate records for any one colour.
SQL used for transformation of the simple current to future states
From a SQL point of view the query required will be as follows.
The result of this statement will be that only one record for each colour will be projected into the destination table.
Advanced SQL deduplication use cases
Before we dive into the how, let's look at the why do we find this deduplication requirement exists ...
Well it goes something like this ... person one has a way of doing things, then person two comes along and does it their way, and then person three joins the party and so on and so forth. When this get's applied to sending email campaigns and everyone is selecting different fields to base the SubscriberKey on then things can quickly become very chaotic with respect to how the marketing platform determines the individual subscriber.
Now that we have that out of the way.
Generally are three reasons for deduplication:
- Improve the customer experience - make it so that only one personalised email is sent to the customer instead of two, three or more.
- Ensure that the customer does not get spammed - if there are duplicate SubscriberKeys for the same account, and the subscriber has opted out of one, then the subscriber may need to be opted out of all.
- Reduce costs - with most marketing cloud platforms using a per contact pricing model it makes sense to remove any unnecessary duplicates.
For the purposes of this discussion we will from an implementation point of view consider that the result of the de-duplication processes needs to update a destination table. Once the records are in the destination table then some other next action can be taken, for example updating subscriber opt-in/out status, deleting contacts and updating external platforms updated accordingly which will be explored separately.
The previous "simple" example was used for the purpose of demonstration to explored the SQL for deduplicating records with similar colours.
In practice, whilst duplicate records are often more complex as they may contain the same or similar account, email and opt-in/out in different fields the procedure is much the same as deduplicating similar colours.
The following is a de-duping process flow to demonstrate this.
In the case of deduplicating consider four records that contain the same values in each of the [Source].email and [Source].ContactID fields.
For this more advanced use case then two fields will be considered for candidates of the deduplication.
The result will be a destination table containing two rows with the field dedupeKey having the values:
So for the purposes of this discussion, hopefully there is some insight into how to recover from potential duplication complexity chaos to easy de-duplicated order.
Future discussions will dive into how to deduplicate contacts, updating a single source of truth and pricing models used by marketing clouds.
Suggestions and questions all welcome
Have you got something that can help others or a question? Feel very welcome to share your thoughts in the comments.