Re-running query that dedupes a column with ROW_NUMBER returning increasingly more records

Question

I've been banging my head against this problem for quite some time. I have a query that is automated to run twice a day and selects order records that fit a certain criteria. It puts them in a data extension that is the entry source for a journey. I then have decision splits that look at a few fields that could have changed to take the contact out or keep them in the journey. Said decision splits look at the target DE/entry source DE as it's own Attribute Group, which is why I have the query set to update.

I consistently get the error that I'm violating the primary key constraint because our SF is set up so that one Order ID (primary key) can be present in the synched data extension I'm pulling from if that individual ordered multiple products at the same time. Because of this, I have no truly unique fields.

I tried correcting this with ROW_NUMBER, but it returns a larger amount of records each time it's run, which is concerning. I would imagine this is due to the rows being generated upon each run of the query and even adding a single record to the mix will throw off everything.

Any help would be greatly appreciated.

Select

x.OrderItem_OrderId,
x.OrderItem_Product_Type__c,
x.OrderItem_ETA__c,
x.OrderItem_Product2Id,

x.Product2_Name,
x.Product2_Wood_Type__c,
x.Product2_Electrial_Requirements__c,
x.Product2_Exterior_Dimensions__c,
x.Product2_Interior_Dimensions__c,
x.Product2_Family,
x.Product2_Item_Weight__c,
x.Product2_Image_URL__c,
x.Product2_Assembly_Guide_Link__c,
x.Product2_Assembly_Video_Link__c,
x.Product2_Manual_Link__c,
x.Product2_of_Shipping_Boxes__c,
x.Product2_id,

x.Order_ID,
x.Order_Order_Number__c,
x.Order_AccountID,
x.Order_Contact__c,
x.Order_Phone__c,
x.Order_Email__c,
x.Order_Status,
x.Order_EffectiveDate,
x.Order_Amount_Required__c,
x.Order_Payment_Type__c,
x.Order_Payment_Term__c,
x.Order_Monthly_Installment__c,
x.Order_ETA__c,
x.Order_Shipping_Via__c,
x.Order_Ship_to_Contact__c,
x.Order_ShippingCity,
x.Order_ShippingCountry,
x.Order_ShippingPostalCode,
x.Order_ShippingState,
x.Order_ShippingStreet,
x.Order_Ready_Date__c,
x.Order_Ready_to_Ship__c,
x.Order_Tracking__c,
x.Order_OrderNumber

From
(SELECT
OrderItem.OrderId as [OrderItem_OrderId],
OrderItem.Product_Type__c as [OrderItem_Product_Type__c],
OrderItem.ETA__c as [OrderItem_ETA__c],
OrderItem.Product2Id as [OrderItem_Product2Id],

Product2.Name as [Product2_Name],
Product2.Wood_Type__c as [Product2_Wood_Type__c],
Product2.Electrical_Requirements__c as [Product2_Electrial_Requirements__c],
Product2.Exterior_Dimensions__c as [Product2_Exterior_Dimensions__c],
Product2.Interior_Dimensions__c as [Product2_Interior_Dimensions__c],
Product2.Family as [Product2_Family],
Product2.Item_Weight__c as [Product2_Item_Weight__c],
Product2.Image_URL__c as [Product2_Image_URL__c],
Product2.Assembly_Guide_Link__c as [Product2_Assembly_Guide_Link__c],
Product2.Assembly_Video_Link__c as [Product2_Assembly_Video_Link__c],
Product2.Manual_Link__c as [Product2_Manual_Link__c],
Product2.of_Shipping_Boxes__c as [Product2_of_Shipping_Boxes__c],
Product2.id as [Product2_id],

Orders.id as [Order_ID],
Orders.Order_Number__c as [Order_Order_Number__c],
orders.AccountId as [Order_AccountID],
orders.Contact__c as [Order_Contact__c],
orders.Phone__c as [Order_Phone__c],
orders.Email__c as [Order_Email__c],
orders.Status as [Order_Status],
orders.EffectiveDate as [Order_EffectiveDate],
orders.Amount_Required__c as [Order_Amount_Required__c],
orders.Payment_Type__c as [Order_Payment_Type__c],
orders.Payment_Term__c as [Order_Payment_Term__c],
orders.Monthly_Installment__c as [Order_Monthly_Installment__c],
orders.ETA__c as [Order_ETA__c],
orders.Shipping_Via__c as [Order_Shipping_Via__c],
orders.Ship_to_Contact__c as [Order_Ship_to_Contact__c],
orders.ShippingCity as [Order_ShippingCity],
orders.ShippingCountry as [Order_ShippingCountry],
orders.ShippingPostalCode as [Order_ShippingPostalCode],
orders.ShippingState as [Order_ShippingState],
orders.ShippingStreet as [Order_ShippingStreet],
orders.Ready_Date__c as [Order_Ready_Date__c],
orders.Ready_to_Ship__C as [Order_Ready_to_Ship__C],
orders.Tracking__c as [Order_Tracking__c],
orders.OrderNumber as [Order_OrderNumber],
orders.Master_Record_Type__c as Order_Master_Record_Type__c,
orders.Delivery_Message__c as Order_Delivery_Message__c,
orders.Sales_Ops_Approved__c as Order_Sales_Ops_Approved__c,
orders.Sales_Ops_Approved_Date_Time__c as Order_Sales_Ops_Approved_Date_Time__c,

ROW_NUMBER() OVER (PARTITION BY orders.ID ORDER BY orders.ID) as RN

FROM Order_Salesforce as orders
Join Orderitem_Salesforce as OrderItem
On Orders.id = OrderItem.OrderId
Join Product2_salesforce as Product2
On Orderitem.Product2Id = Product2.ID
) x

Where x.Order_Master_Record_Type__c = 'Personal'
and (x.Order_ShippingCountry = 'United States' or x.Order_ShippingCountry = 'Canada')
and x.Order_Delivery_Message__c = 'FIFO'
and (x.Order_Sales_Ops_Approved__c = 1 or x.Order_Sales_Ops_Approved_Date_Time__c IS NOT NULL)
and x.OrderItem_Product_Type__c = 'Sauna'
and x.Order_Contact__c is not null

and RN = 1

Answer 1

Your partition should match the fields in the target data extension if you don't want to violate it.

My assumption is that your primary key is more than just Order_ID in your target Data Extension.

If for example, the target PK is Order_ID + Order_AccountID, then you can do something like this. I'm using the short-form of the row_number() over (partition), just because it's less typing.

SELECT top 1 with ties
  o.id as [Order_ID]
, o.Order_Number__c as [Order_Order_Number__c]
, o.AccountId as [Order_AccountID]
, o.Contact__c as [Order_Contact__c]
, o.Phone__c as [Order_Phone__c]
, o.Email__c as [Order_Email__c]
, o.Status as [Order_Status]
, o.EffectiveDate as [Order_EffectiveDate]
, o.Amount_Required__c as [Order_Amount_Required__c]
, o.Payment_Type__c as [Order_Payment_Type__c]
, o.Payment_Term__c as [Order_Payment_Term__c]
, o.Monthly_Installment__c as [Order_Monthly_Installment__c]
, o.ETA__c as [Order_ETA__c]
, o.Shipping_Via__c as [Order_Shipping_Via__c]
, o.Ship_to_Contact__c as [Order_Ship_to_Contact__c]
, o.ShippingCity as [Order_ShippingCity]
, o.ShippingCountry as [Order_ShippingCountry]
, o.ShippingPostalCode as [Order_ShippingPostalCode]
, o.ShippingState as [Order_ShippingState]
, o.ShippingStreet as [Order_ShippingStreet]
, o.Ready_Date__c as [Order_Ready_Date__c]
, o.Ready_to_Ship__C as [Order_Ready_to_Ship__C]
, o.Tracking__c as [Order_Tracking__c]
, o.OrderNumber as [Order_OrderNumber]
, o.Master_Record_Type__c as Order_Master_Record_Type__c
, o.Delivery_Message__c as Order_Delivery_Message__c
, o.Sales_Ops_Approved__c as Order_Sales_Ops_Approved__c
, o.Sales_Ops_Approved_Date_Time__c as Order_Sales_Ops_Approved_Date_Time__c
, i.OrderId as [OrderItem_OrderId]
, i.Product_Type__c as [OrderItem_Product_Type__c]
, i.ETA__c as [OrderItem_ETA__c]
, i.Product2Id as [OrderItem_Product2Id]
, p.Name as [Product2_Name]
, p.Wood_Type__c as [Product2_Wood_Type__c]
, p.Electrical_Requirements__c as [Product2_Electrial_Requirements__c]
, p.Exterior_Dimensions__c as [Product2_Exterior_Dimensions__c]
, p.Interior_Dimensions__c as [Product2_Interior_Dimensions__c]
, p.Family as [Product2_Family]
, p.Item_Weight__c as [Product2_Item_Weight__c]
, p.Image_URL__c as [Product2_Image_URL__c]
, p.Assembly_Guide_Link__c as [Product2_Assembly_Guide_Link__c]
, p.Assembly_Video_Link__c as [Product2_Assembly_Video_Link__c]
, p.Manual_Link__c as [Product2_Manual_Link__c]
, p.of_Shipping_Boxes__c as [Product2_of_Shipping_Boxes__c]
, p.id as [Product2_id]
FROM Order_Salesforce as o
Join Orderitem_Salesforce as i On i.OrderId = o.id
Join Product2_salesforce as p On p.ID = i.Product2Id 
Where 
o.Master_Record_Type__c = 'Personal'
and o.ShippingCountry in ('United States','Canada')
and o.Delivery_Message__c = 'FIFO'
and (o.Sales_Ops_Approved__c = 1 or o.Sales_Ops_Approved_Date_Time__c IS NOT NULL)
and i.Product_Type__c = 'Sauna'
and o.Contact__c is not null    
order by row_number() over (partition by o.id, o.accountId order by o.id desc)

I would also order by your Order_ID descending, since (also an assumption) you want to return the most recent order only.