A dynamic cache is helpful when the source table contains a large amount of data or it contains duplicate primary keys.

The following example illustrates the advantage of using a dynamic cache rather than a static cache when the source table includes duplicate primary keys.

You want to update your payroll table with data from your Human Resources department. The payroll table includes the following data, where ID is the primary key:

ID	Name	Location
1	Abhi	USA
2	Alice	UK

You create a mapping with a Lookup transformation and use the payroll table for the target and the lookup cache. You configure the cache to be dynamic because you expect the Human Resources department's table to contain duplicate keys.

In the mapping, you specify the Human Resources department's table to be the source. The source table includes the following data:

ID	Name	Location
1	Abhi	India
2	Alice	UK
3	James	Japan
3	James	USA

You create a

mapping

task to update the payroll table. When the

mapping

task begins, it creates the cache file that contains the rows in the target table. As the task processes the rows, it flags the first row as an update and it updates the cache. It flags the third row as an insert and inserts the row in the cache. It flags the fourth row as an update because the row exists in the cache.

If you follow the same scenario using a static cache, the task flags the fourth row as an insert. The cache does not contain the row for James because it does not update as the task processes the rows. The target database produces an error because it cannot handle two rows with the same primary key.