Click data view wont' save in data extension

Question

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)

UPDATE:

In other data views when I try to run the query, I am also getting this error:

SENT_DATA_VIEW_DUMP_DE

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

Answer 1

Typically if Query Activities fail, it's one of these 4 things:

1. Primary key violation -- your query results in duplicate rows not allowed by the primary key
2. Inserting a null value into a non-nullable field
3. Inserting a value too long for the field (truncation)
4. Timeout -- if your query doesn't complete within the 30 minute timeout window, it'll error out.

SFMC Support can tell you what the error is from the server log.

Having gone down this route before with the _Click data view and primary key violations, you'll need to de-duplicate the rows using row_number(). Something like this:

select
  x.ClientID
, x.SendID
, x.SubscriberKey
, x.EmailAddress
, x.SubscriberID
, x.ListID
, x.EventDate
, x.BatchID
, x.TriggeredSendExternalKey
, x.eventType
, x.isUnique
, x.URL
, x.Alias
, x.SendURLID
, x.URLID
from (

    select
          e.AccountID as ClientID
        , e.Jobid as SendID
        , e.SubscriberKey
        , e.SubscriberKey as EmailAddress
        , e.SubscriberID
        , e.ListID
        , e.EventDate
        , e.BatchID
        , e.TriggeredSendCustomerKey as TriggeredSendExternalKey
        , 'Click' as eventType
        , case
            when e.isUnique = 1 then 'True'
            else 'False'
          end as isUnique
        , e.URL
        , left(e.linkName,500) as Alias
        , 0 as SendURLID
        , 0 as URLID
        , row_number() over(partition by e.jobid, e.subscriberid order by e.eventDate desc) ranking
    from _Click e
    where
    e.jobid is not null
    and e.eventDate is not null
    and e.SubscriberID is not null
    and e.ListID is not null
    and e.BatchID is not null
    and e.subscriberid not in (
        select
        t.SubscriberID
        from Tracking_Click t
        where t.SendID = e.jobid
        and t.subscriberid = e.subscriberid
        and t.eventDate = e.eventDate
        and t.isUnique = 'True'
    )
) x
where x.ranking = 1
/* name: Tracking_Click */
/* target: Tracking_Click */
/* action: update */