Data Conversion in ETL Package

Highlighted
Four Stars

Data Conversion in ETL Package

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.


Accepted Solutions
Community Manager

Re: Data Conversion in ETL Package

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. 


All Replies
Community Manager

Re: Data Conversion in ETL Package

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. 

Four Stars

Re: Data Conversion in ETL Package

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?

Community Manager

Re: Data Conversion in ETL Package

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.

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads