1)The first input link to lookup stage is called the ‘Primary’ link. Other links are called ‘Lookup’ links. When lookup links are from a stage that is other than a database stage, all data from the lookup link is read into memory. Then, for each row from the primary link, the lookup is performed. If the source of lookups is a database, there can be two types of lookups:
Normal lookup:
All the data from the database is read into memory, and then lookup is performed.
Sparse lookup: For each incoming row from the primary link, the SQL is fired on database at run time.
2)Sparse lookups can be used if the input data is smaller than the reference data.
