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.

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now