0

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. enter image description here

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.

1
  • 1
    Please update your question with a sample of the query. Jun 3, 2021 at 17:23

1 Answer 1

2

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.

2
  • Thanks Adam. Yeah I saw Eliot's video saying the nolock doesn't matter anymore, but had not gotten around to removing it. Thanks for this suggestion, although I'm still unsure why it's failing sometimes, but not always. I opened a support case, but they said they don't troubleshoot data... Oh well, I guess I'll chalk it up to another of SFMC's oddities... Jun 4, 2021 at 14:01
  • this is what support said help.salesforce.com/… 1. When the results of SQL Query retrieves duplicate records(ID field) and the target data extension has a primary key(ID field), if the duplicate record is being inserted for the first time into target data extension it will throw an error. 2. When the results of SQL Query retrieves duplicate records and the target data extension has a primary key, if the duplicate record already exists in target data extension it will not throw an error. Jul 27, 2021 at 16:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .