Informatica provides a wide array of transformations to manipulate data during the ETL (Extract, Transform, Load) process. These transformations can be broadly categorized in several ways. Here's a breakdown:
Key Categorizations :
- Active vs. Passive Transformations:
- Active Transformations: These can change the number of rows passing through them. They can also change the row type. Examples include Filter, Aggregator, and Joiner transformations.
- Passive Transformations: These do not change the number of rows passing through them. They only modify the data within the rows. Examples include Expression and Lookup (in certain configurations) transformations.
- Connected vs. Unconnected Transformations:
- Connected Transformations: These are part of the data flow in a mapping.
- Unconnected Transformations: These are called from other transformations, such as an Expression transformation.
- Native and Non-native Transformations:
- Native transformations are those that are part of the core informatica software.
- Non-native transformations could be custom transformations, or those that interact with external programs.
Common Transformation Types :
Here are some of the most frequently used transformations:
- Source Qualifier:
- Retrieves data from source databases.
- Filters, sorts, and joins data from relational sources.
- Expression:
- Performs calculations on individual rows.
- Manipulates strings, dates, and numbers.
- Filter:
- Removes rows that do not meet specified criteria.
- Aggregator:
- Performs aggregate calculations (e.g., sum, average, count).
- Groups data based on specified columns.
- Lookup:
- Retrieves data from a lookup table or source.
- Used for data validation and enrichment.
- Joiner:
- Joins data from two or more sources.
- Router:
- Routes rows to different output groups based on specified conditions.
- Sorter:
- Sorts data in ascending or descending order.
- Sequence Generator:
- Generates sequential numbers.
- Update Strategy:
- Specifies how target rows should be updated (e.g., insert, update, delete).
- Normalizer:
- Transforms denormalized data into normalized data.
- Transaction Control:
- Controls transaction boundaries.
This is not an exhaustive list, but it covers the most commonly used transformations in Informatica.
To get the most accurate and up to date information, it is always best to refer to the official Informatica documentation.
