During overwrite, Marketing Cloud picks first PK record instead of prompting PrimaryKey violation

Question

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'

Answer 1

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.