0

I am trying to create an Automation to update sent dataview records daily.

The code I've tried is the one below, but it kept showing error messages with "Violation of PRIMARY KEY constraint. Cannot insert duplicate key'.

SELECT
    AccountID, 
    OYBAccountID, 
    JobID, 
    ListID, 
    BatchID,
    SubscriberID, 
    SubscriberKey, 
    EventDate
    AT TIME ZONE 'central standard time'
    AT TIME ZONE 'korea standard time'
    as EventDate,
    Domain, 
    TriggererSendDefinitionObjectID, 
    TriggeredSendCustomerKey

FROM _sent

WHERE CONVERT(date,DATEADD(DAY,-1,GETDATE()))<=EVENTDATE
      AND EVENTDATE<=CONVERT(date,GETDATE())

According to the query action log, the error is occuring as there are 4 records with same value on all fields, except for the 'EventDate' field. I've set the PK as 'subscriberkey+accountID+jobID+listID+batchID+SubscriberID+EventDate+Domain'.

It'll be a great help if anyone could give me a way to solve this error. Thank you.

1 Answer 1

1

A unique send event should be combination of subscriberkey (or ID, same thing in this respect) - jobid - listid - batchid. So you're doing the right thing in theory.

Still, I have seen behavior does occur on the data views, typically in _click or _open, where events are recorded that look exactly like one another. You actually see a difference in eventdate, but the approaches might anyway help you:

There are two ways around this that I've used:

A) If you want to know what's going on exactly, this one is insightful and simple but not necessarily great, functionally: use NO primary key on the target DE, that way you simply get all data out without any constraints. This comes with its own problems. E.g. if you directly export the data - then the problem of inexplicable duplicates typically goes to somebody else and you'll probably be asked to explain it to the person inheriting the question.

edit: a similar thing would be to just create a unique rowkey in your query, into a data extension with just one PK "rowkey" and an SQL that adds this (a GUID) to the SELECT:

SELECT
   newid() AS rowkey,
   // rest of select...
FROM

benefit is, having a PK makes a DE more useable, e.g. in imports you'll have all modes available, no PK means only "Overwrite" will work. end of edit

That approach does help to investigate what's going on though, e.g. you could compare the output's event dates on the duplicates; if they look the same at first glance, it might help to reformat them to see milliseconds; and if they're milliseconds apart, it's probably OK to deduplicate them. Which leads us to B).

B) In order to get it running - Make a simple decision what record to retrieve, like "the newest", "the oldest". Deduplicate using SQL ROW_Number(), partitioning by unique send events (subscriberkey-jobid-listid-batchid) according to your decision:

This example will take the newest record.

Replace the sorting parameter DESC with ASC and you will get the oldest.

SELECT 
subselect.AccountID, 
subselect.OYBAccountID, 
subselect.JobID, 
subselect.ListID, 
subselect.BatchID,
subselect.SubscriberID, 
subselect.SubscriberKey, 
subselect.EventDate,
subselect.Domain, 
subselect.TriggererSendDefinitionObjectID, 
subselect.TriggeredSendCustomerKey
FROM 
    (    
    SELECT
    AccountID, 
    OYBAccountID, 
    JobID, 
    ListID, 
    BatchID,
    SubscriberID, 
    SubscriberKey, 
    EventDate
    AT TIME ZONE 'central standard time'
    AT TIME ZONE 'korea standard time'
    as EventDate,
    Domain, 
    TriggererSendDefinitionObjectID, 
    TriggeredSendCustomerKey,
    ROW_NUMBER() OVER(Partition by SubscriberKey,BatchId,JobId,ListId ORDER BY EventDate DESC) AS Row_Number   
    FROM _Sent) subselect
WHERE subselect.Row_Number = 1
AND  CONVERT(date,DATEADD(DAY,-1,GETDATE()))<=EVENTDATE
      AND EVENTDATE<=CONVERT(date,GETDATE())
2
  • Thank you for a specific guide regarding the problem! I've tried the rownum query but as the records with error had different EventDate for each of them, it wasn't kind of working though. So, I've changed the 'EventDate'field as a text type with 100 length assigned for it, and the automation worked well. I guess this is a chronic problem often occuring in dataview automation. But, still, your answer was a great help for me! Thank you again
    – Lilyn
    Nov 25, 2021 at 4:12
  • great, thanks for the feedback. please mark the answer as accepted if it helped solved your problem :) Nov 25, 2021 at 7:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .