Data Conversion in ETL Package

[Anonymous]

Hi Team,

I am considering Talend for one of my design. Where the business case is like below

Source and destination : Oracle DB

ETL run frequency : Daily

From source it will pick data from multiple tables(joined) and store into a destination table. In destination table there is a flag column. When a record is inserted in destination for first time the the flag will be insert.

Next time onwards when the ETL runs there will be a check. If any data is inserted first time flag will be insert. When there is modification on existing data then in destination table the row will be updated and flag will change to update. 

How can I achieve this ETL development using Talend. I want to know which feature of Talend can help me to do this lookup and runtime flag calculation? Also share link for those feature.

[Anonymous · Accepted solution]

There are several ways of achieving this. An easy way of doing this is to carry out a lookup on your destination prior to inserting/updating. Use this information to identify whether it is an insert or update, then change the value supplied to your flag column. There will be multiple things you will need to take into account when deciding upon how to efficiently do this. So this is a very high level suggestion. 

[Anonymous]

Thanks!

Apart from insert update I have another condition if a particular column value of source changes from Active To Inactive then the ChangeType column in destination will be updated as Delete.How to handle that?

Will all these 3 scenarios can be covered by a single tMap component?

Also, how does that commit happens? Will it commit records in bulk after the complete lookup and dataset processing is done?

[Anonymous]

You can commit on each row or on completion of the batch of records. If you potentially have the same record being inserted and updated in the same batch, you may want to carry out your lookup using "reload on each row" (lookup model). This will query your dataset for every row coming from your main source.

With regard to your new condition, you can check your new condition in the tMap. 

If there are multiple actions on the same record in the same batch, you will need to commit on each row.