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'