0

We have a peculiar case where we are joining 3 different data extensions (i.e. Consent_DE, Address DE and Channels DE) via SQL in Automation Studio. We have Global_ID as unique customer identifier across all three DEs. This Global ID is repeated multiple times only in the Consent DE because one person can have 2 consent lines tied to him/her. This is just how the data lands in our SFMC from another system.

We use CustomerKey (located in Address DE) as Primary Key, which has a '1-to-1' relationship with GlobalID. We also filter the query on Consent = either 'Products' or 'Services'. Now, here's what happens: if we go for MC overwrite without primary key, in the resulting DE we get duplicated records (duplicated Customer_Keys); specifically, in the duplicate records everything's identical except for one field - encryptedID - which is generally different value for "Products" vs "Services" (in the Consent DE). In contrast, if we enable CustomerKey as PrimaryKey, then we get perfect results, filtered properly on the Consent (i.e. we don't get the encrypted_ID from 'Services' consent record). Why is this happening / why are we getting duplicates?

Here's the code:

SELECT DISTINCT ch.First_Name, ch.Last_Name, a.Customer_Key, a.Email, cs.Consent, cs.Encrypted_ID, cs.Global_ID, cs.Source FROM Channels AS ch INNER JOIN Consent AS cs ON ch.Global_ID = cs.Global_ID INNER JOIN Address AS a ON ch.Global_ID = a.Global_ID WHERE ch.Email='true' AND cs.Consent ='Products' AND a.Address_Type ='Digital' AND a.Inactive = 'false'

0

A primary key is an identified Unique ID. So basically it is the definition that differentiates a record from being an 'add' or an 'update'.

So when you do an overwrite without a primary key, it just adds all records into the DE, including those with the same CustomerKey, because you did not identify it as a primary key.

When you change it to contain a primary key, it then has the identifier defined and will use 'update' on any records that have more than one CustomerKey.

I do want to note that DISTINCT does not work like many think it does. It requires each field you list after it to be exactly the same in value for it to recognize it as a duplicate.

For example DISTINCT FirstName will ignore all other records with Susan as Firstname beyond the first one. Where DISTINCT FirstName, LastName will ignore all other records with Susan as Firstname ANDNix as Lastname. But will allow for records that have Susan in First name, with different last name and vice versa.

If you want to be able to 'deduplicate' inside the SQL query instead of adding pkeys to the DE, you can utilize the SQL GROUP BY statement on the field you want to deduplicate on.

try something like:

SELECT ch.First_Name, ch.Last_Name, a.Customer_Key, a.Email, cs.Consent, cs.Encrypted_ID, Max(cs.Global_ID), cs.Source 
FROM Channels AS ch 
INNER JOIN Consent AS cs ON ch.Global_ID = cs.Global_ID 
INNER JOIN Address AS a ON ch.Global_ID = a.Global_ID 
WHERE ch.Email='true' AND cs.Consent ='Products' AND a.Address_Type ='Digital' AND a.Inactive = 'false'
GROUP BY a.Customer_Key

Notice that I added Max() to Global_ID. This is because to use GROUP BY, you need to have an aggregate function. This would not change anything though as your Global_ID is 1:1 with Customer_Key.

| improve this answer | |
  • Thanks for a really nice answer. My guess here would be that when doing overwrite without PK, MC does not take into account the stated filter (Consent='Products'). Instead, it fetches data, specifically encrypted_id, from both consent lines/records (products and services) that appear for one global_id record, in the Consent DE. Thus, customer_key appears twice for the same record in the resulting DE, with just a different encrypted_id, evreything else's identical. It's super weird; however, adding PK indeed makes MC execute the 3 joins properly without producing unecessary duplicates. – nix9247 Dec 2 '19 at 16:00
  • I think the issue is that you are getting a false positive. It sounds like the overwrite with the pkey is updating with the proper 'Products' in 'Consent' by chance as that is the last record in the duplicates. I think this sounds like there may be an issue in the WHERE condition logic or the foreign keys on the joins that I would review. – Gortonington Dec 2 '19 at 20:36
  • I'm running out of options here, as all filters seems legit. So I will have to resort to deduping, i.e. adding one additional step and creating a staging DE in which I will filter the Consent DE on 'Products' and then proceed with the 3 joins in the second step. – nix9247 Dec 3 '19 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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