DSXchange

When i had discussion with my team mate he told look up stage should be used when the reference data is small.Not sure what could be the reason and even he do not know.Any thoughts on this

There is a discussion of Lookup versus Join in the documentation. Suggest you start there. You can also find discussions here if you search, for example this one.

Lookup loads the reference data set into memory, so that your lookups are performed in memory (at memory speed) and there is no need to sort the data (a hash table index is also created in memory). Join does require that both its inputs be sorted.

Then there is also sparse lookup, also documented.