topic Re: SCD type 2 logic but not using SCD component. Any suggestions? in Data Quality

SCD type 2 logic but not using SCD component. Any suggestions?

Anonymous — Sat, 16 Nov 2024 06:16:23 GMT

Hi there,

I'm loading a csv file that consists of list of zipcodes that has been downloaded from the Internet. My keys are combination of Zipcode, City, State and Longitude. I would load it in Postgres Database. The requirement is to compare if these combinations already exists, check if there are changes, If there is, insert a new record of the recent changes and marked the old record as expired. Otherwise, if the combination doesn't exists yet, insert the record and marked it as a new record. I believe this is SCD type 2. As much as I want to use the SCD component, I'm afraid I'm not familiar with it and its behavior.

Any suggestion how can I implement this?

Thanks in advance!

Re: SCD type 2 logic but not using SCD component. Any suggestions?

akumar2301 — Fri, 22 Mar 2019 13:26:10 GMT

SCD component example

http://dwteam.in/scd-type2-talend-open-studio/

https://help.talend.com/reader/hm5FaPiiOP31nUYHph0JwQ/3Lyn4CR4M5Q2uOD8FWmOwg

Otherwise

https://community.talend.com/t5/Design-and-Development/Flag-not-updating-based-on-condition/m-p/26823#M5082

https://www.youtube.com/watch?v=dH567IFszu4

Re: SCD type 2 logic but not using SCD component. Any suggestions?

Anonymous — Mon, 25 Mar 2019 09:06:56 GMT

Implemenation of SCD is very simple in Talned

check below :

http://dwteam.in/scd-type2-talend-open-studio/

The logic that u need to build with out SCD also simple with tmap.

(source--main) tmap (target--lookup) ---> use inner join

take 3 out puts

1. matched records (one of these matched becomes existing record(use insert) with end date as -1 or high end value)

2. matched records(one of these matched becomes existingrecord(use update) with end date as startdate-1 )

3. un matched records(with inner join rejected link) --- will become new records(insert them to db)

let me know if you need more details

Re: SCD type 2 logic but not using SCD component. Any suggestions?

Anonymous — Mon, 25 Mar 2019 13:45:49 GMT

Thank you for your response.

I tried once again the SCD component. I have source keys with the combination of city, longitude, zipcode and state. I also created the following columns in my table: end_dtae, is_active, sg_key and start_date. I have type 0 fields and type 2 fields form my table as well. When I put sg_key as surrogate keys and selected table max+1 as creation, it gets me an error. But when I put the sg_key as source keys, it processed. Also, I tried to modify the state and load again, the first record still has the end_date as 2099, it should be the run date of the second record? and the is_active flag of the first record, still is true.

Any thoughts?

scd2.JPG

Re: SCD type 2 logic but not using SCD component. Any suggestions?

Anonymous — Wed, 27 Mar 2019 04:38:22 GMT

@mj_00 if your source keys are(basically primary key of your table) combination of city, longitude, zipcode and state then you need to select all of them in "SOURCE KEY" section

and if state is part of your primary key and if you modify it then it never updates the existing record because it considers that record as new record only (so this will be inserted as new record)

surrogate key is a natural key which always has unique value ...

don't forget to give kudos if that helps you

Re: SCD type 2 logic but not using SCD component. Any suggestions?

Anonymous — Wed, 27 Mar 2019 04:39:57 GMT

Check this vedio for full understanding of type 2 : https://www.youtube.com/watch?v=dH567IFszu4