I am building replications of some system data views in order to be able to hold data that goes back further than 6 months (we are a seasonal business so want to look back at least 13 months).
I considered two approaches:
- Use an "Append" and query for records that happened YESTERDAY
- Use an "Update" and query for records over the last 7 days
I decided to use option 2 because, in the event the automation fails to run (which happens from time to time) then option 2 will automatically look back far enough to pick up the missed records and add them to the DE.
Option 1 would be quite fiddly to amend the WHERE clause to get the correct dates and not double up on records by accident.
The one requirement for option 2 is that I have the correct primary key setup so that records already in the DE get updated, rather than duplicated, when running the query.
I have achieved this for all the data views I'm using except for _Click. I am getting a primary key violation for this one.
The primary key combination I'm using for _Click is:
- JobID
- ListID
- BatchID
- SubscriberKey
- EventDate
I'm surprise about the violation as I would have thought EventDate would provide the required level of uniqueness given it measures down to the second. But I guess some people are clicking links twice within 1 second?
Can anyone think of another way to setup a primary key combination that would avoid any chance of a violation? Or will I simply have to use option 1 (ie: Append with specific date ranges) for getting _Click?
Thanks