MC query dedupes records automatically

Question

I've noticed some quite odd behavior from running queries in automation studio.

Below is the query in question:

SELECT C.id
, CM.id
FROM ent.CampignMember_Salesforce CM 
JOIN ent.Contact_Salesforce C 
ON CM.ContactId=C.Id

We roughly have 100K contacts records in our environment and ~ 500k campign member records related to them.

Thus the expected result of this query should return 500k records. This is because in average for each contact there are 5 campign member record related to it.Therefore we can expect in average 5 duplicates records for each contact id.

The actual result is as follows:

1. When the target DE has no primary key field set the query returns 500k as expected.
2. However, When the target DE use contact id as the primary key - instead of getting an error of trying to insert duplicates value for the primary key field - the query runs without an error and chooses randomly 1 of the 5 records for each contact id. In this case the query returns 100k records only.

Note no changes done in query except for the target DE use of primary key.The query is set to OVERWRITE data on target DE.

Any explanation for this odd results? Shouldn't the query result not be affected by the settings of the target DE?

Thanks, Barak

Answer 1

So if I am understanding your question, the issue is that you have the primary key of ContactId, which limits the results to just a single contact record instead of including each of the correlated campaign records.

To get the 500k result, you would need to either include both ContactId and CampaignId as the primary keys or shift it to just show CampaignId as the primary key.

As to why this happens it is because the SQL will take the target's context into account when saving and/or running the query, which is why in a query activity you need to include both the SQL as well as the target DE you are looking to send results to.