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?
SELECT * 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.
SELECT x.AccountID ,x.OYBAccountID ,x.JobID ,x.ListID ,x.BatchID ,x.SubscriberID ,x.SubscriberKey ,x.EventDate ,x.Domain ,x.URL ,x.LinkName ,x.LinkContent ,x.IsUnique ,x.TriggeredSendDefinitionObjectID ,x.TriggeredSendCustomerKey FROM ( SELECT AccountID ,OYBAccountID ,JobID ,ListID ,BatchID ,SubscriberID ,SubscriberKey ,EventDate ,Domain ,URL ,LinkName ,LinkContent ,case when IsUnique = 1 then 'True' else 'False' end as IsUnique ,TriggeredSendDefinitionObjectID ,TriggeredSendCustomerKey , row_number() over(partition by JobID, SubscriberID order by EventDate desc) ranking from _Click WITH (NOLOCK) where jobid is not null and eventDate is not null and SubscriberID is not null and ListID is not null and BatchID is not null ) x where x.ranking = 1