Yes, there won't be any duplication in the file within itself because of the unique values(username).
However, regular importing the same CSV file with older data and new data, will make duplication in the target table.
Which is what I want to avoid (using runtime check).
'Checking for duplication' and 'avoiding duplication' are different things. The first suggests that duplicates may already exist, unlike the second.
I did, however, make a mistake when recommending the CS for this task. I meant the Lookup, my apologies. This will probably perform significantly faster than the JOIN. Configure the Lookup to use
SELECT <unique key> from TargetTable
after selecting 'Use results of an SQL query' in the Connection node.
Use FULL caching (cache mode), if you have sufficient memory.
and then match the unique key of the incoming rows against it, sending the unmatched rows to the target table.