join vs lookup

vamsi.4a6 · Post by **vamsi.4a6** » Tue Aug 19, 2014 12:02 am

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

chulett · Post by **chulett** » Tue Aug 19, 2014 11:25 am

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.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 19, 2014 2:20 pm

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.

qt_ky · Post by **qt_ky** » Tue Aug 19, 2014 2:22 pm

Then there is also sparse lookup, also documented.