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

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

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 return 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 primaey 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 return 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

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