Snowsql MERGE Statement insert issue

Question

I migrating tsql code to snowsql and have ran into an issue with MERGE statements. Process is vetted and tested on legacy system i.e. tsql sql server BUT basic validation needs to pass before code is ready for UAT/ Prod testing. That said. Business logic gets encapsulated into 12 different procs that all call MERGE statements to insert data into same reporting table. Final thoughts before jumping into the tech side of it; my data on snowflake is doubled or tripled in some cases

||Sandbox_Table             ||Sql Server            
BUSINESS_DATE|DEAL_AMT|ORG|BUSINESS_DATE|DEAL_AMT|ORGANIZATION_ID|Difference
5/26/2021|78,313,906.29|2|5/26/2021|78,317,995.16|2|(4,088.87)
5/26/2021|86,578,148.23|3|5/26/2021|95,122,255.15|3|(8,544,106.92)

ON (tgt.ORG_ID = src.ORG_ID and tgt.LOCATION_ID = src.LOCATION_ID and tgt.BUSINESS_DATE = src.BUSINESS_DATE and tgt.REGISTER_ID = src.REGISTER_ID and tgt.TRANSACTION_NO = src.TRANSACTION_NO and tgt.rtrans_lineitm_seq = src.rtrans_lineitm_seq AND CAST(tgt.create_date  AS TIMESTAMP_NTZ(9)) = CAST(src.create_date  AS TIMESTAMP_NTZ(9)) 
AND tgt.mod_seq_no = src.mod_seq_no and IFNULL(tgt.reasoncode,'') = IFNULL(src.reasoncode,'') )
WHEN NOT MATCHED THEN INSERT

Data types ORG_ID (NUMBER 38,0) // int on sql server
LOCATION_ID (NUMBER 38,0) // int on sql server
REGISTER_ID (NUMBER 38,0) // int on sql server
TRANSACTION_NO  (NUMBER 38,0) // int on sql server
rtrans_lineitm_seq  (NUMBER 38,0) // int on sql server
create_date  TIMESTAMP_NTZ(9) // datetime  on sql server
mod_seq_no    (NUMBER 38,0) // int on sql server
reasoncode VARCHAR(500) // nvarchar on sql server

Per above not mateched insert values. I'm not doing any updates or deletes. Late arrive transactions will also be inserted AND NO DATA IS ALLOWED TO BE DELETED. I was suggested by team-mates to check for NULL ; as in version before I did not. But still per above values do not seem to match. Am I missing something too obvious? Thanks for help in advance.

Sample Code of PROCS per request

// proc 1
// truncate table IREPORT_TABLE_A;

// select * from IREPORT_TABLE_A order by TRANS_NO

MERGE INTO IREPORT_TABLE_A tgt
USING ( 
select to_varchar(concat(ORG_ID,STORE_NO,to_varchar(replace(to_date(CREATE_DATE),'-','')),TRANS_NO,REG_NO,trans_seq,mod_nbr) ) as Unique_id
,*
from (
select 2000 as ORG_ID
,TH.STORE_NO as  STORE_NO
,TH.Date as CREATE_DATE
,TH.TRANSACTION_NO as TRANS_NO
,case when left(TH.POS_TERMINAL_NO,2) = 'NP' then 1 
when left(TH.POS_TERMINAL_NO,2) = 'NS' then 2
when left(TH.POS_TERMINAL_NO,2) = 'NT' then 3 else 0 end as REG_NO
,TSE.LINE_NO as trans_seq
,ROW_NUMBER() over (partition by TSE.STORE_NO,TSE.Date,TSE.TRANSACTION_NO,TSE.POS_TERMINAL_NO order by TSE.STORE_NO,TSE.Date,TSE.TRANSACTION_NO,TSE.POS_TERMINAL_NO,TSE.DISCOUNT_AMOUNT ) AS mod_nbr
,CAST(CONCAT(trim(substring(TH.DATE,0,charindex(':',TH.DATE)-4)), ' ' ,substring(TH.Time,charindex(':',TH.Time)-2,length(TH.Time)))AS TIMESTAMP_NTZ(9)) as create_date
,trim(CONCAT('LOYALTY',' ',ifnull(TIE.Information,''))) AS rtl_price_mod_reasoncode
,IFNULL(PERIODIC_DISC_GROUP,TIE.Information) AS discount_code
,IFNULL(TSE.DISCOUNT_AMOUNT,0) AS deal_amt
,null as deal_id
,TH.STAFF_ID as create_user
,null as sales_agt_com
,null as serial_number
from TRANS_HEAD as TH
 join TRANS_ENTRY TSE on TSE.TRANSACTION_NO = TH.TRANSACTION_NO and TSE.STORE_NO = TH.STORE_NO and TSE.POS_TERMINAL_NO = TH.POS_TERMINAL_NO
left join TRANS_INFORMATION TIE on TIE.TRANSACTION_NO = TH.TRANSACTION_NO and TIE.STORE_NO = TH.STORE_NO and TIE.POS_TERMINAL_NO = TH.POS_TERMINAL_NO and TIE.LINE_NO = TSE.LINE_NO
where TH.TRANSACTION_TYPE = 2
    and TH.ENTRY_STATUS not in (1,3)
    and TIE.TRANSACTION_TYPE = 1        
    and TIE.INFOCODE = 'LOYALTY'
     and TIE.INFORMATION not in ('PPP EXCLUSIVE','PPP Points Discount')
       // and TH.STORE_NO = 7010 and to_date(TH.Date) = '2021-06-02' and TH.TRANSACTION_NO = 20004144 
      and (( TH.STORE_NO = 7010 and to_date(TH.Date) = '2021-06-01' and TH.TRANSACTION_NO in( 20012155, 20012156,20012185 ) )
       or ( TH.STORE_NO = 7010 and to_date(TH.Date) = '2021-06-02' and TH.TRANSACTION_NO in( 20004144, 20004187,20012292 ) ) )
) as a
) AS src
ON (to_varchar(concat(tgt.ORG_ID,tgt.STORE_NO,to_varchar(replace(to_date(tgt.CREATE_DATE),'-','')),tgt.TRANS_NO,tgt.REG_NO,tgt.trans_seq,tgt.mod_nbr) ) = src.Unique_id
   )
WHEN NOT MATCHED THEN INSERT (tgt.ORG_ID
,tgt.STORE_NO
,tgt.CREATE_DATE
,tgt.TRANS_NO
,tgt.REG_NO
,tgt.trans_seq
,tgt.mod_nbr
,tgt.create_date
,tgt.rtl_price_mod_reasoncode
,tgt.discount_code
,tgt.deal_amt
,tgt.deal_id
,tgt.create_user
,tgt.sales_agt_com
,tgt.serial_number )
values (src.ORG_ID
,src.STORE_NO
,src.CREATE_DATE
,src.TRANS_NO
,src.REG_NO
,src.trans_seq
,src.mod_nbr
,src.create_date
,src.rtl_price_mod_reasoncode
,src.discount_code
,src.deal_amt
,src.deal_id
,src.create_user
,src.sales_agt_com
,src.serial_number )

// proc 2 //////////////////////////////////////////////////////////////////
// select * from IREPORT_TABLE_A order by TRANS_NO

MERGE INTO IREPORT_TABLE_A tgt
USING ( 
select to_varchar(concat(ORG_ID,STORE_NO,to_varchar(replace(to_date(CREATE_DATE),'-','')),TRANS_NO,REG_NO,trans_seq,mod_nbr) ) as Unique_id
,*
from (  
SELECT  2000 as ORG_ID
,TH.STORE_NO as  STORE_NO
,TH.Date as CREATE_DATE
,TH.TRANSACTION_NO as TRANS_NO
,case when left(TH.POS_TERMINAL_NO,2) = 'NP' then 1 
when left(TH.POS_TERMINAL_NO,2) = 'NS' then 2
when left(TH.POS_TERMINAL_NO,2) = 'NT' then 3 else 0 end as REG_NO
,TSE.LINE_NO as trans_seq
,ROW_NUMBER() over (partition by TSE.STORE_NO,TSE.Date,TSE.TRANSACTION_NO,TSE.POS_TERMINAL_NO order by TSE.STORE_NO,TSE.Date,TSE.TRANSACTION_NO,TSE.POS_TERMINAL_NO,TSE.DISCOUNT_AMOUNT ) AS mod_nbr
,CAST(CONCAT(trim(substring(TH.DATE,0,charindex(':',TH.DATE)-4)), ' ' ,substring(TH.Time,charindex(':',TH.Time)-2,length(TH.Time)))AS TIMESTAMP_NTZ(9)) as create_date
,'PERIODIC DISCOUNT' as rtl_price_mod_reasoncode
,IFNULL(TSE.PERIODIC_DISC_GROUP,'') AS discount_code
,case when TSE.PERIODIC_DISC_GROUP IS NOT NULL then  TSE.PERIODIC_DISCOUNT else 0 end as Deal_Amt
//case when IFNULL(TSE.PERIODIC_DISC_GROUP,'') <> '' then TSE.PERIODIC_DISCOUNT else 0 end as Deal_Amt
,Null as deal_id
,TH.STAFF_ID as create_user
,null as sales_agt_com
,null as serial_number
from TRANS_HEAD as TH
join TRANS_ENTRY TSE on TSE.TRANSACTION_NO = TH.TRANSACTION_NO and TSE.STORE_NO = TH.STORE_NO and TSE.POS_TERMINAL_NO = TH.POS_TERMINAL_NO
where TH.TRANSACTION_TYPE = 2
    and TH.ENTRY_STATUS not in (1,3)
    // and IFNULL(TSE.PERIODIC_DISC_GROUP,'') <> '' // commented on purpose to see if zero amounts are logged as well.
      //  and TH.STORE_NO = 7010 and to_date(TH.Date) = '2021-06-02' and TH.TRANSACTION_NO = 20004144 
     and ( ( TH.STORE_NO = 7010 and to_date(TH.Date) = '2021-06-01' and TH.TRANSACTION_NO in( 20012155, 20012156,20012185 ) )
       or ( TH.STORE_NO = 7010 and to_date(TH.Date) = '2021-06-02' and TH.TRANSACTION_NO in( 20004144, 20004187,20012292 ) ) )
) as a
) AS src
ON (to_varchar(concat(tgt.ORG_ID,tgt.STORE_NO,to_varchar(replace(to_date(tgt.CREATE_DATE),'-','')),tgt.TRANS_NO,tgt.REG_NO,tgt.trans_seq,tgt.mod_nbr) ) = src.Unique_id
   )
WHEN NOT MATCHED THEN INSERT (tgt.ORG_ID
,tgt.STORE_NO
,tgt.CREATE_DATE
,tgt.TRANS_NO
,tgt.REG_NO
,tgt.trans_seq
,tgt.mod_nbr
,tgt.create_date
,tgt.rtl_price_mod_reasoncode
,tgt.discount_code
,tgt.deal_amt
,tgt.deal_id
,tgt.create_user
,tgt.sales_agt_com
,tgt.serial_number )
values (src.ORG_ID
,src.STORE_NO
,src.CREATE_DATE
,src.TRANS_NO
,src.REG_NO
,src.trans_seq
,src.mod_nbr
,src.create_date
,src.rtl_price_mod_reasoncode
,src.discount_code
,src.deal_amt
,src.deal_id
,src.create_user
,src.sales_agt_com
,src.serial_number )

// select * from IREPORT_TABLE_A order by TRANS_NO
// proc 3 /////////////////////////////////////////// line 448

MERGE INTO IREPORT_TABLE_A tgt
USING ( 
select to_varchar(concat(ORG_ID,STORE_NO,to_varchar(replace(to_date(CREATE_DATE),'-','')),TRANS_NO,REG_NO,trans_seq,mod_nbr) ) as Unique_id
,ORG_ID
,   STORE_NO    
,CREATE_DATE    
,TRANS_NO   
,REG_NO 
,trans_seq  
,mod_nbr    
,create_date    
,rtl_price_mod_reasoncode   
,discount_code  
,deal_amt   
,deal_id    
,create_user    
,sales_agt_com  
,serial_number
 from (SELECT   2000 as ORG_ID
,TH.STORE_NO as  STORE_NO
,TH.Date as CREATE_DATE
,TH.TRANSACTION_NO as TRANS_NO
,case when left(TH.POS_TERMINAL_NO,2) = 'NP' then 1 
when left(TH.POS_TERMINAL_NO,2) = 'NS' then 2
when left(TH.POS_TERMINAL_NO,2) = 'NT' then 3 else 0 end as REG_NO
,TIE.ENTRY_LINE_NO as trans_seq
,ROW_NUMBER() over (partition by TH.STORE_NO,TH.Date,TH.TRANSACTION_NO,TH.POS_TERMINAL_NO order by TH.STORE_NO,TH.Date,TH.TRANSACTION_NO,TH.POS_TERMINAL_NO //,bc.[AmountOfDiscount] 
    ) AS mod_nbr
,CAST(CONCAT(trim(substring(TH.DATE,0,charindex(':',TH.DATE)-4)), ' ' ,substring(TH.Time,charindex(':',TH.Time)-2,length(TH.Time)))AS TIMESTAMP_NTZ(9)) as create_date
,TRIM(CONCAT('LOYALTY',' ',IFNULL(TIE.Information,''))) as rtl_price_mod_reasoncode
,Null AS discount_code //IFNULL(bc.[Description],'') AS discount_code
,0 AS deal_amt // (bc.[AmountOfDiscount]) as deal_amt
,Null as deal_id
,TH.STAFF_ID as create_user
,null as sales_agt_com
,null as serial_number
from TRANS_INFORMATION TIE
left join TRANS_HEAD as TH
        on TIE.TRANSACTION_NO = TH.TRANSACTION_NO
        and TIE.STORE_NO = TH.STORE_NO
        and TIE.POS_TERMINAL_NO = TH.POS_TERMINAL_NO
where TIE.Infocode = 'LOYALTY' 
and tie.Information = 'PPP Points Discount'
// and TH.STORE_NO = 7010 and to_date(TH.Date) = '2021-06-02' and TH.TRANSACTION_NO = 20004144 
        and (( TH.STORE_NO = 7010 and to_date(TH.Date) = '2021-06-01' and TH.TRANSACTION_NO in( 20012155, 20012156,20012185 ) )
        or ( TH.STORE_NO = 7010 and to_date(TH.Date) = '2021-06-02' and TH.TRANSACTION_NO in( 20004144, 20004187,20012292 ) ) )
) as a
where deal_amt is not null
) AS src
ON (to_varchar(concat(tgt.ORG_ID,tgt.STORE_NO,to_varchar(replace(to_date(tgt.CREATE_DATE),'-','')),tgt.TRANS_NO,tgt.REG_NO,tgt.trans_seq,tgt.mod_nbr) ) = src.Unique_id
   )
WHEN NOT MATCHED THEN INSERT (tgt.ORG_ID
,tgt.STORE_NO
,tgt.CREATE_DATE
,tgt.TRANS_NO
,tgt.REG_NO
,tgt.trans_seq
,tgt.mod_nbr
,tgt.create_date
,tgt.rtl_price_mod_reasoncode
,tgt.discount_code
,tgt.deal_amt
,tgt.deal_id
,tgt.create_user
,tgt.sales_agt_com
,tgt.serial_number )
values (src.ORG_ID
,src.STORE_NO
,src.CREATE_DATE
,src.TRANS_NO
,src.REG_NO
,src.trans_seq
,src.mod_nbr
,src.create_date
,src.rtl_price_mod_reasoncode
,src.discount_code
,src.deal_amt
,src.deal_id
,src.create_user
,src.sales_agt_com
,src.serial_number )

own find.. concating bunch of "keys" or business_keys (as keys are NOT enforced in Snowflake) I am able to get the merge proc to behave how TSQL one does. about 90-95% I still have a bit more testing around this. — junketsu

akshindesnowflake akshindesnowflake · Accepted Answer · 2021-06-03T04:23:13

Can you compare keys of Source table & Target Table by doing selecting Single Row at Source Table and Single Row at Target Table. It is highly likely you are having mismatch of one or more keys which is leading to Duplicate Inserts rather than Update. My Suspect would be create_date key column where which might reason to trigger duplication.

Can you please share some of sample source & target records which is leading to insert into target table.

Snowsql MERGE Statement insert issue

1 Answers