When row is deleted, the end date in scd type 2 should get updated with that datetime details

Highlighted
Four Stars

When row is deleted, the end date in scd type 2 should get updated with that datetime details

Hi team,

 

I'm newbie to talend. I'm currently working on creating a flow to load data daily from data source to my scd type2 table. 

 

  1. If a new row based on primary key is loaded, it should insert new row.
  2. If there is change on existing row, the old row should be closed (start date, end date) and new row with end date as null should be created.
  3. If a row is deleted on primary key, then the end date for that row should be given.

 

Using tDBSCD, I'm able to solve cases 1,2. I need suggestions for case 3, where when a row is deleted, the end date is not getting updated with the time stamp.

Highlighted
Six Stars

Re: When row is deleted, the end date in scd type 2 should get updated with that datetime details

Since you are using SCD components, you might additionally need to perform a lookup of incoming data with existing records and can catch the deleted records in tMap using "Catch Inner Join Reject" option on tMap output side.

Note: Lookup join should be Inner Join for this case.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog