topic Re: Implementing SCD2 on Redshift. Logic Not working in Talend Studio

Implementing SCD2 on Redshift. Logic Not working

sushantk19 — Sat, 16 Nov 2024 02:34:04 GMT

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.

Re: Implementing SCD2 on Redshift. Logic Not working

manodwhb — Tue, 28 Apr 2020 11:52:17 GMT

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

Re: Implementing SCD2 on Redshift. Logic Not working

sushantk19 — Tue, 28 Apr 2020 12:19:02 GMT

@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

Re: Implementing SCD2 on Redshift. Logic Not working

manodwhb — Tue, 28 Apr 2020 12:29:29 GMT

@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?

Re: Implementing SCD2 on Redshift. Logic Not working

sushantk19 — Tue, 28 Apr 2020 12:46:07 GMT

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

Re: Implementing SCD2 on Redshift. Logic Not working

manodwhb — Tue, 28 Apr 2020 13:06:28 GMT

@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.

Re: Implementing SCD2 on Redshift. Logic Not working

sushantk19 — Tue, 28 Apr 2020 14:23:28 GMT

@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