1. Objective:

To filter duplicate records in a source, solution described below uses a lookup transformation with
dynamic cache enabled. System generated port NewLookupRow is used to identify new records.

2. Source definitions:

Source is EMP table as shown in figure 1. This can be a flat file source also depending on the
requirement.


Figure 1: Relational source EMP (Oracle)

3. Target definitions:

Target is copy of source i.e. another table with same structure as of source. All distinct records found
in source table will be inserted into target table EMP_TARGET. All duplicate records will be discarded.

4. Mapping:

Figure 2 shows the mapping designed to filter duplicate records in source.

                             Figure 2: Mapping logic

4.1 Transformations used
LK_target
Lookup transformation with dynamic cache properties enabled is used to identify if the incoming
record already exists in target. If yes then value of NewLookupRow port will be 0. If incoming record is



Lookup condition is EMPNO=IN_EMPNO. This condition depends on the key to identify duplicate
records. In my example I am just looking for duplicate empno. Dynamic Lookup Cache property for
this lookup transformation is enabled.
FL_new_rows
This filter passes only new rows to the target. Filter condition is NewLookupRow = 1.



We get the unique records in Target table.