Query running hourly failing with primary key violation intermittently

Question

I have a SQL query that is trying to pull three records and insert them into one. On table A, affiliation ID is the primary key, and a contact can have more than one affiliation. On table B, Contact ID is the primary key. So when my query runs, it may find a contact more than once, and try to insert it into Table B. Naturally, it fails. This is expected. What I CAN'T sort out, is that it only fails 4 times, and then runs fine the rest of the day, even though the duplicates remain. This is making no sense to me as the query just just keep failing all day. The query just ran and I checked and the duplicate values are still there, yet the query succeeded. We have noticed on some that the affiliation records are brand new the day they fail, but that doesn't help me much. Does anyone know if Marketing Cloud can resolve these types of errors on its own? It seems to be that may be what is happening.

The query producing the errors looks like this:

select
id
,   '1' as workgroup_a
FROM
eda_affiliations_master with (nolock)
where
workforce_group = 'A'

Also, I know I can solve this with a simple group by, but what I'm really after is the odd behavior of it failing only sometimes.

Answer 1

Queries created in Query Studio are wrapped inside other queries behind the scenes. I've seen database issues in the platform cause PK violations -- especially if the data extension has had any field revisions after it was first created. I'd open a support case.

My go-to for ensuring that PK violations do not occur is to partition by the target DE's primary key fields:

select top 1 with ties
id
, '1' as workgroup_a
FROM
eda_affiliations_master 
where
workforce_group = 'A'
order by row_number() over (partition by id order by newid())

This will only return 1 random (order by newid()) record per id in eda_affiliations_master.

Also the with (nolock) directive doesn't have any effect anymore.