Use of Database lookup in Pentaho Kettle

Database Lookup is used to map the columns of source and destination and get the surrogate key of lookup tables.

For example,

1. My source table (dbo.Employee) contains employee details including DepartmentID
2. My destination table (DimEmployee) including a column DepartmentKey.
3. DepartmentKey is the SurrogateKey in the table dbo.DimDepartment with the following columns: (DepartmentKey, DepartmentID, DepartmentName,...).
4. Now if you want to load the department key into Dimemployee table, we need to map the DepartmentID of the source with DepartmentID of the DimDepartment table (lookup table) to get the DepartmentKey as output.

This can be achieved in Pentaho, create a transformation as shown below: