Lookup transformation - returns values from a database table or a flat file associated with a given input value.
In SQL terminology a lookup may be considered as a sub-query.
There are the following Lookup transformation types:
Dynamic Lookup (connected) - when the lookup table is also the target, the data may go out of sync with the target table loaded in memory. The Dynamic Lookup transformation allows for the synchronization of the target lookup in-memory table with its physical table in a database.
Dynamic Cache properties worth looking at: Dynamic Lookup Cache (boolean, use onlywith the lookup cache enabled), 'Insert Else Update' or 'Update Else Insert'.
Unconnected lookup - used when a lookup is not needed for each record, the lookup data is fetched at the point the mappings requires it. It doesn't contain links from and to other transformation. Data lookup is performed only for those rows which require it, which can significantly improve performance of the ETL process.
Unconnected lookup function can be invoked within any transformation that supports expressions.