Implementing SCD2 on Redshift. Logic Not working

[sushantk19]

Presently, I am trying to implement SCD2 without SCD component. But facing the below issue.Its not even Inserting data in my Target table. Please see the screenshots attached. I already have 2 country ids in my target. Source  has 253 unique country ids, so as per the Insert update logic it should insert 251 and update 2 records.

[manodwhb]

@sushantk19 , can you change join model to leftouter in tmap and check the data?

[sushantk19]

@manodwhb : I am trying that. Inserts are working fine now(after updating the Catch Lookup inner join to "true". Just that Running sequence no is not getting generated in my insert flow.

My requirement is also the same as others, if the Data doesn't exist in Target Table, then insert "I" with todays date and future end date, if it exists then the earlier records end date should be todays date and current records start_date should be "todays date" and end_date should be "future_date".

Please find the job design attached.

expected output should be like below:

Country_id	CountryCode	Skey	Start_date	End_date	Flag	Insert for first time
1	AF	10	2020-04-28	9999-12-31	I
Country_id	CountryCode	Skey	Start_date	End_date	Flag	History data
1	AF	10	2020-04-28	2020-04-29	I
1	AG	11	2020-04-29	9999-12-31	U

Job_Design_SCD2.png
SCD_Insert.png
SCD_update.png

[manodwhb]

@sushantk19 ,for the update flow you need to check for other than country_id you need to check for other columns data changed or not.are you implementing scd 1 or 2?

[sushantk19]

@manodwhb : SCD2. what changes are needed in My expressions/Job?

[manodwhb]

@sushantk19 ,then you need do not equals county_id with lookup id and you need compare for the other non-key columns to check is it new or update.

[sushantk19]

@manodwhb  Thanks for quick reply.

I have created a separate job just to test my update logic. It has two flows one for end dating y earlier record and other one for inserting my new record identified as "update". Later I will merge this code with insert flow.

But keeps running without succeeding, there are hardly 253 records in source. what could be possible bottleneck? how do i make this job run faster? please see the job design attached. how much should an update take ?

Update_SCD_Logic.png