Why is my RowNumber partition creating duplicate rows on the same information

Question

I need to allow entrants into a journey in Journey Builder multiple times depending on the country of their booking and then preserve the entry and exit dates at a country level.

I have created a concatenated key in the sql query and set it as the primary key on the de. The GETDATE() should ensure it's uniqueness for each run of the query, which i then plan to use to match and isolate the specific exit dates using another query.

I normally use Row_Number to deduplicate and it works fine but in this case it is ignoring the country and keeps returning 2 rows both with the same country and a row_number of 1.

I have tried ordering by different things as i don't have a unique date, but the result is the same each time.

Query is below with comments.This SQL is for use in Marketing Cloud Automation Studio.

SELECT 

/*Construct the unique key*/
a.*,
Concat(a.ContactKey,'_',a.Country,'_',GETDATE()) as CompositeKey

FROM (SELECT

ids.ETA, 
ids.CUSTOMER_EMAIL as Email, 
ids.Shipment_Count, 
ids.Dep_FFE, 
ids.Port, 
ids.Country, 
/*Create Hashed Email*/
CONVERT(NVARCHAR(MAX), HASHBYTES('SHA2_256', CAST(ids.CUSTOMER_EMAIL as varchar(64))), 2) as Email_Hash,
cmm.ContactKey,
cmm.External_ID__c,
/*Deduplicate records by contact id*/
ROW_NUMBER() OVER (PARTITION BY ids.CUSTOMER_EMAIL, ids.Country ORDER BY ids.CUSTOMER_EMAIL DESC) AS RowNumber,
/*Set English as default*/
CASE WHEN cmm.Preferred_Language__c IS NOT NULL THEN cmm.Preferred_Language__c ELSE 'English' END as Preferred_Language__c  

FROM  Table1 as ids 
INNER JOIN Table2 as cmm on ids.Customer_Email = cmm.Email
WHERE ids.Customer_Email IS NOT NULL) as a 

LEFT JOIN Table4 as je on a.ContactKey = je.ContactKey and a.Country = je.Country
LEFT JOIN Table5 as cg on a.ContactKey = cg.ContactKey and a.Country = cg.Country
WHERE a.RowNumber = 1 

/*Check record is not already in Journey Entry or Control Group*/
AND (je.Country IS NULL or je.Journey_Exit_Date < DATEADD(minute,-10,GETDATE()))
AND (cg.Country IS NULL or cg.DateAdded_To_ControlGroup < DATEADD(minute,-10,GETDATE()))`/

Answer 1

I would replace your left joins with exists clauses, that way there's no unintended expansion of rows.

NOTE: Use caution with select * in the outermost SELECT. SFMC caches Data Extension columns names, so if you add a new column to your DE after your query has been created, it the query won't automatically recognize it.

SELECT

  a.ETA
, a.Email
, a.Shipment_Count
, a.Dep_FFE
, a.Port
, a.Country
, a.Email_Hash
, a.ContactKey
, a.External_ID__c
, a.RowNumber
, a.Preferred_Language__c
, Concat(a.ContactKey,'_',a.Country,'_',GETDATE()) as CompositeKey

FROM (

    SELECT
      ids.ETA
    , ids.CUSTOMER_EMAIL as Email
    , ids.Shipment_Count
    , ids.Dep_FFE
    , ids.Port
    , ids.Country
    , CONVERT(NVARCHAR(MAX), HASHBYTES('SHA2_256', CAST(ids.CUSTOMER_EMAIL as varchar(64))), 2) as Email_Hash
    , cmm.ContactKey
    , cmm.External_ID__c
    , ROW_NUMBER() OVER (PARTITION BY ids.CUSTOMER_EMAIL, ids.Country ORDER BY ids.CUSTOMER_EMAIL DESC) AS RowNumber
    , CASE
        WHEN cmm.Preferred_Language__c IS NOT NULL THEN cmm.Preferred_Language__c
        ELSE 'English'
      END as Preferred_Language__c
    FROM  Table1 as ids
    INNER JOIN Table2 as cmm on ids.Customer_Email = cmm.Email
    WHERE ids.Customer_Email IS NOT NULL

) as a
WHERE
a.RowNumber = 1
and exists (
  select top 1 je.*
  from Table4 as je
  where a.ContactKey = je.ContactKey
  and a.Country = je.Country
  and (je.Country IS NULL or je.Journey_Exit_Date < DATEADD(minute,-10,GETDATE()))
)
and exists (
   select top 1 cg.*
   from Table5 cg
   where a.ContactKey = cg.ContactKey
   and a.Country = cg.Country
   and (cg.Country IS NULL or cg.DateAdded_To_ControlGroup < DATEADD(minute,-10,GETDATE()))
)