Marketing Cloud: Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

Question

Everyday we have an automation running to import new magento customers from an FTP file to our contact DataBase. Today, for the first time we had this error message.

Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'SystemData'. The duplicate key value is ([HIDEN EMAIL]@gmail.com).

And this is the query :

select
s.Magento_ID as Magento_ID,
case
when r.[Marketing Cloud_ID] is null or r.[Marketing Cloud_ID] = '' then s.EmailAddress 
else r.[Marketing Cloud_ID] end as [Marketing Cloud_ID],
s.EmailAddress as Email
from Import_Magento_Account_All s
left join SystemData r
on
s.Magento_ID = r.Magento_ID

Is checked and the supposed duplicated email is not in our data model. I'm really stuck any help will be much appreciated

Thank you!

Answer 1

I know you said there are no duplicate emails, but it might be best to set up a dedupe subquery to validate this:

SELECT a.*
FROM (
    select
    s.Magento_ID as Magento_ID,
    case
    when r.[Marketing Cloud_ID] is null or r.[Marketing Cloud_ID] = '' then s.EmailAddress 
    else r.[Marketing Cloud_ID] end as [Marketing Cloud_ID],
    s.EmailAddress as Email,
    ROW_NUMBER() OVER (PARTITION BY s.EmailAddress ORDER BY s.EmailAddress) AS rn
    from Import_Magento_Account_All s
    left join SystemData r
    on
    s.Magento_ID = r.Magento_ID
) a
WHERE a.rn = 1

The above will only pull records where the email address exists once. As from your comment, I am assuming the Marketing_Cloud_ID is only emails currently, this should resolve the issue.