Appreciate your good stuffs.. I know it is very late comment to this post. However I like to share my opinion and get your thoughts as well.
1. From your scenario, my view of the incremental data means the data that has been amented/inserted since certain time. according to this logic, the extracted source data in this article is not incremental one. it is FULL SET. if my assumption / definition is not correct, please advise me.
2. Lookup transformation with "cache" will degrade the performance for huge amounts of data due to memory usage. my opinion is that it can not be used for transactional tables (or fact tables). But surely it helps for small lookups table ( or dimension tables)
3. Lookup transformation with "no cache" again degrade a lot when we use the FULL DATA SET as it make millions of calls to the database
4. my option is "use the MERGE JOIN " route for delete operation by selecting only key colunms and compare them to identify the missing ones..
to be honest , I am a LOOKUP TRANSFORAMTION lover, but no other go for DELETE OPERATION as I am aware of. Requesting you provide me any other route/ logic
looking forward to hear your thoughts..
Thanks again for such a wonderful series,
Have a good day!!!