So, I am seeing a very odd behaviour.
When I run this query to dump all click data view data in a data extension, I see the record count going up...it goes into x millions and then suddenly the count starts going down and goes back to 0.
Does it mean that I am hitting some kind of limit of DE (even though no limit is defined in salesforce documentation for data extensions)?
OR it is not possible to dump all 6 months data from a data view in a data extension?
FROM _Click WITH (NOLOCK)
In other data views when I try to run the query, I am also getting this error:
ERROR: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object
In order for me to run query, I HAVE to define primary key in data extension. So in example of SENT_DATA_VIEW_DUMP_DE
I have defined the following as primary keys: AccountID JobID ListID BatchID EventDate
How come a combination of these columns be same in more than one row? eventdata technically includes timestamp too so there is no way a record or sent can have exact same timestamp. There has to be some different in seconds or minutes.
Thank you @Admin-Springgs So based on my understanding, I have modified the query like this and trying to do UPDATE (not append or overwrite) and getting error "Invalid column name 'TriggeredSendDefinitionObjectID'."
Even though I have checked the column name is correct in target data extension.
,case when IsUnique = 1 then 'True' else 'False' end as IsUnique
, row_number() over(partition by JobID, SubscriberID order by EventDate desc) ranking
from _Click WITH (NOLOCK)
jobid is not null
and eventDate is not null
and SubscriberID is not null
and ListID is not null
and BatchID is not null
where x.ranking = 1