Lookup Stage

1)The  ‘Lookup’ stage  is a processing stage  that can have more  than one  input  links and one output  link, as well as one  reject  link.  

2)Lookup Failure options 

Continue, Drop, Fail, Reject 

3)If the lookup fails to find a matching key column, one of these actions can be taken: 

Fail: the lookup Stage reports an error and the job fails immediately. 

This is the default. 

Drop:The input row with the failed lookup(s) is dropped 

Continue:The input row is transferred to the output, together with the successful table entries.The failed table entry(s) are not transferred, resulting in either default output values or null output values depends on datatype. 

Reject:The input row with the failed lookup(s) is transferred to a second output link, the reject  link.

4)Sparse lookup can be used if the input data is smaller than the reference data.

5)Joins have better performance when the reference data is huge. Avoid lookups in such cases. 

6)Lookup Stage does not need sorted input data where as for Join stage and Merge stage input data should be sorted.

7)Please find the below link to find the difference between Normal Lookup and sparse Lookup-NormalvsSparse