Pushdown optimization is a concept using which you can push the transformation logic on the source or target database side. When you have a source as the database table, you can make use of a SQL override to remove the logic written in the transformation. When you use the SQL override, the session performance is enhanced, as processing the data at a database level is faster compared to processing the data in Informatica. You cannot remove all the transformations from the mapping. The part of transformation logic that can be pushed at the source or target level is referred to as pushdown optimization.

Consider that you have a mapping with a sequence indicated as follows:

Source - Source Qualifier - Filter - Sorter - Aggregator - Expression - Lookup - Rank - Target.

In Filter transformations, we are filtering the data in a particular location. In Sorter transformations, the data is sorted in a particular department ID. In the Aggregator, we are grouping the data in the department ID. In Expression transformations, the unconnected Lookup transformation is called using the :LKP function, and finally, a Rank is used to get the top-salaried employee into the target.

We can remove the Filter transformation, Sorter transformation, and Aggregator transformation by adding the WHERE clause, the ORDER BY clause, and the GROUP BY clause, respectively, in the SQL override in the Source Qualifier transformation. We cannot remove the Expression transformation, as we cannot write the :LKP function in the SQL override.

So, our mapping becomes simple after using the SQL override, which is indicated as follows:

Source - Source Qualifier - Expression - Lookup - Rank - Target.

Using pushdown optimization will help in saving the processing time by extracting fewer number of records of data from the source and also fewer number of records in the transformations.