Can SCD implementation in talend be used for synching up data between source and destination tables. The source and destination tables has master and child tables with parent child relationships. Any changes(insert/update/delete) on source's parent and child tables needs to be sycnhed up with destination parent and child tables. Source table shall have an indication KEY column for identifying changed data Any inputs on this will be appreciated.
Hi gorotman, Thanks for your reply. Please find the example scenario. 1. Source db has below tables - employee ( master table) - employee_address and employee_salary ( child tables) emp_id as acts a key column . employee master has a column named " change_data" which shall have identification for change data "I",'U' and "D" and the child tables in source will not have any identifcation column for identifyig the changed data 2. Destination db also has employee,employee_address and employee_salary table with similar data structure as that of source db. On daily basis the employee,employee_address and employee_salary tables in destination db needs synched up with change data from the source db.data has to be synched in both parent and child tables
Can we achieve this via SCD ? I believe SCD Type 1 is capable of performing updates on dest table . Having said that in our case the change data flag is available only in master table so is this SCD implemtation capable of updating child records also along with the parent records
Hi ushamu, yes, it is possible to manage your scenario in TOS, but I think you can use a simple tMap->tMSSqlOutput (or ever db output component for your db). You already have source rows marked with correct operation (I,U,O), so use this column. This is the example: 1- input is your source master table 2- tMap with filter expression for output flows:
insert=> row1.change_data.equals("I") update=> row1.change_data.equals("U") delte=> row1.change_data.equals("O") 3- for every output choose the correct action on rows (insert, update, delete) If you can use "change_data" column to apply insert/update/delete operations to child rows, you can reuse same logic, but use these tables as primary and do a lookup to master table in tMap component. At the end, you need to reset "change_data" column? or do you have a date to filter rows processed?