Informatica Transformations

Informatica Transformations in PowerCenter

Informatica Transformations are PowerCenter repository objects used inside mappings and mapplets to read, modify, route, validate, aggregate, and pass data from source definitions to target definitions. A transformation is where most ETL business rules are implemented: filtering invalid rows, calculating new columns, joining sources, looking up reference values, generating keys, sorting records, and deciding whether a row should be inserted, updated, deleted, or rejected.

In Informatica PowerCenter Designer, transformations are commonly classified by how they behave in the data pipeline. The most important classifications are active transformations and passive transformations. You will also hear about connected transformations, unconnected transformations, reusable transformations, and non-reusable transformations.

Where Informatica Transformations Fit in an ETL Mapping

In a typical Informatica mapping, data starts from a source definition, passes through one or more transformations, and finally loads into a target definition. Each transformation performs a specific task in that flow. For example, a Source Qualifier reads rows, a Filter removes unwanted rows, an Expression derives new column values, a Lookup adds reference data, and an Update Strategy marks rows for insert or update.

Data cleansing: remove invalid rows, trim spaces, handle null values, and standardize formats.
Data conversion: change data types, formats, dates, or string values before loading.
Data enrichment: add lookup values such as customer segment, region name, or product category.
Data integration: join, union, normalize, or aggregate data from multiple sources.
Load control: decide insert, update, delete, reject, commit, and rollback behavior.

Active and Passive Transformations in Informatica

The active/passive classification is based on whether a transformation can change the number of rows that pass through it, change transaction boundaries, or change the row type. This classification is important because it affects mapping design, row flow, session behavior, and performance tuning.

Comparison point	Active transformation in Informatica	Passive transformation in Informatica
Row count	May increase, decrease, or reorder rows.	Returns the same number of rows that it receives.
Transaction boundary	May affect transaction control or commit/rollback behavior.	Does not change the transaction boundary.
Row type	May change how the row is treated, such as insert, update, delete, or reject.	Does not change the row type.
Common examples	Aggregator, Filter, Joiner, Router, Rank, Sorter, Update Strategy.	Expression, Lookup, Sequence Generator, Stored Procedure, HTTP.
Typical use	Used when rows must be filtered, grouped, routed, joined, sorted, or controlled.	Used when values must be calculated, looked up, generated, or passed through without changing row count.

What is Active Transformation in Informatica?

An active transformation in Informatica can affect the number of rows that pass through the transformation, change transaction boundaries, or change the row type. If a transformation receives n input rows, it may return fewer rows, the same number of rows, or more rows depending on its purpose and configuration.

For example, a Filter transformation may remove rows that do not match a condition, an Aggregator transformation may combine many detail rows into one summary row, and a Router transformation may send a row to one or more output groups.

Transformation	Type	Description	Example use
Aggregator	Active / Connected	Performs aggregate calculations such as SUM, AVG, MIN, MAX, and COUNT.	Calculate total sales by customer or month.
Application Source Qualifier	Active / Connected	Represents the rows that the Integration Service reads from an application, such as an ERP source, when it runs a session.	Read rows from an application source before applying mapping logic.
Custom	Active or Passive / Connected	Calls a procedure in a shared library or DLL.	Apply custom processing that is not available through built-in transformations.
Filter	Active / Connected	Filters data based on a condition.	Load only active customers or valid transaction rows.
Java	Active or Passive / Connected	Executes user logic coded in Java. The byte code for the user logic is stored in the repository.	Apply Java-based validation or conversion logic.
Joiner	Active / Connected	Joins data from different databases or flat file systems.	Join customer records from a database with sales data from a flat file.
Normalizer	Active / Connected	Acts as a source qualifier for COBOL sources. It can also normalize data from relational or flat file sources.	Convert repeating columns into multiple rows.
Rank	Active / Connected	Limits records to a top or bottom range.	Find the top 10 products by revenue.
Router	Active / Connected	Routes data into multiple output groups based on group conditions.	Send domestic and international orders to different pipelines.
Sorter	Active / Connected	Sorts data based on one or more sort keys.	Sort records before aggregation or comparison.
Source Qualifier	Active / Connected	Represents the rows that the Integration Service reads from a relational or flat file source when it runs a session.	Select, join, or filter source rows before downstream transformations.
Transaction Control	Active / Connected	Defines commit and rollback transactions.	Commit rows after a logical business transaction is complete.
Union	Active / Connected	Merges data from different databases or flat file systems.	Combine sales rows from multiple regional files into one pipeline.
Unstructured Data	Active or Passive / Connected	Transforms data in unstructured and semi-structured formats.	Extract usable fields from documents or semi-structured inputs.
Update Strategy	Active / Connected	Determines whether rows are inserted, deleted, updated, or rejected.	Mark changed customer rows for update and new customer rows for insert.
XML Generator	Active / Connected	Reads data from one or more input ports and outputs XML through a single output port.	Create XML output from relational input rows.
XML Parser	Active / Connected	Reads XML from one or more input ports and outputs data to one or more output ports.	Parse XML order data into structured rows.
XML Source Qualifier	Active / Connected	Represents the rows that the Integration Service reads from an XML source when it runs a session.	Read XML source rows for further transformation.

What is Passive Transformation in Informatica?

A passive transformation in Informatica does not change the number of rows that pass through it. If it receives n input rows, it returns n output rows. It also maintains the transaction boundary and does not change the row type.

For example, an Expression transformation may calculate a full name from first name and last name, a Sequence Generator may create a surrogate key value, and a Lookup transformation may return reference data for each incoming row.

Transformation	Type	Description	Example use
Expression	Passive / Connected	Calculates values using expressions and functions.	Derive full name, tax amount, margin, or formatted date.
HTTP	Passive / Connected	Connects to an HTTP server to read or update data.	Call an HTTP service during row processing.
Input	Passive / Connected	Defines mapplet input rows and is available in the Mapplet Designer.	Pass rows into a reusable mapplet.
Lookup	Passive / Connected or Unconnected	Looks up values from a relational table, flat file, or other lookup source.	Find customer name, product category, or currency conversion rate.
Output	Passive / Connected	Defines mapplet output rows and is available in the Mapplet Designer.	Pass transformed rows out of a mapplet.
Sequence Generator	Passive / Connected	Generates numeric sequence values, often used as surrogate keys.	Create customer_key or order_key values for a target table.
Stored Procedure	Passive / Connected or Unconnected	Calls a stored procedure.	Run database logic or return a value from a procedure.

Connected, Unconnected, Reusable, and Non-Reusable Transformations

Informatica transformations can also be described by how they are connected and reused in mappings. This classification is separate from active and passive behavior.

Transformation category	Meaning in Informatica	Typical example
Connected transformation	The transformation is connected to the mapping pipeline. It receives data from another object and passes data to another object.	Expression, Filter, Joiner, Aggregator, Router.
Unconnected transformation	The transformation is not directly connected to the pipeline. It is called from another transformation and returns a value.	Unconnected Lookup or Stored Procedure.
Reusable transformation	The transformation is created once and reused in multiple mappings.	A reusable Expression transformation for standard date formatting.
Non-reusable transformation	The transformation is created inside one mapping and is used only in that mapping.	A Filter transformation created for one specific load rule.

Unconnected Transformation in Informatica

An unconnected transformation in Informatica is not linked directly in the mapping pipeline. Instead, it is called from another transformation, usually through an expression, and it returns a value to the calling transformation. The most common example is an unconnected Lookup transformation that returns one lookup value when called.

Use an unconnected transformation when the logic is needed conditionally, when the same lookup must be called from multiple places, or when returning a single value is enough. Use a connected transformation when every row must pass through the transformation as part of the main data flow.

How to Create an Informatica Transformation in PowerCenter Designer

Informatica transformations can be created using Designer tools such as Mapping Designer, Transformation Developer, and Mapplet Designer. After creating a transformation, configure its ports, properties, groups, expressions, conditions, and cache settings as required. Finally, link the transformation to source definitions, other transformations, and target definitions in the mapping or mapplet.

Mapping Designer: Mapping Designer creates transformations inside a mapping that connects source definitions to target definitions. These transformations are usually specific to that mapping.

Transformation Developer: Transformation Developer creates individual reusable transformations. A reusable transformation can be used in more than one mapping, which helps keep common logic consistent.

Mapplet Designer: Mapplet Designer creates and configures a group of transformations called a mapplet. A mapplet can be reused in multiple mappings when the same transformation logic is required repeatedly.

Open the appropriate Designer tool for the mapping, reusable transformation, or mapplet.
Select the transformation type that matches the business rule, such as Expression, Filter, Lookup, Router, or Aggregator.
Add input, output, input/output, variable, or generated ports as required.
Configure transformation-specific properties such as filter condition, lookup condition, group by ports, sort keys, or update strategy expression.
Connect the transformation ports to the previous and next objects in the mapping.
Validate the mapping and review errors related to port data types, unconnected ports, invalid expressions, or unsupported transformation order.

Informatica Transformation Examples by ETL Requirement

The right transformation depends on the ETL rule you need to implement. The following examples show common mapping requirements and the transformations usually used for them.

ETL requirement	Recommended Informatica transformation	Reason
Load only records where status is Active.	Filter	Removes rows that do not satisfy the condition.
Create a new column for total amount.	Expression	Calculates values without changing row count.
Find product category from product code.	Lookup	Fetches reference values from a lookup source.
Generate surrogate key values for a dimension table.	Sequence Generator	Creates sequential numeric values.
Split rows into valid, invalid, and review groups.	Router	Sends rows to different output groups using conditions.
Join customer data from one database with order data from another source.	Joiner	Combines rows from heterogeneous sources.
Calculate monthly sales by region.	Aggregator	Groups detail rows and calculates summary values.
Mark rows for insert or update based on a lookup result.	Update Strategy	Controls the target row operation.
Combine files with the same structure from different regions.	Union	Merges multiple pipelines with matching port structures.

Four Common Data Transformation Types in Informatica Projects

Informatica product documentation classifies transformations by behavior and connectivity, but in project discussions data transformation is also described by the kind of change applied to the data. Four common types are:

Structural transformation: changing the shape of data, such as normalizing repeating columns, joining sources, or splitting rows into groups.
Cleansing transformation: correcting or removing invalid values, trimming spaces, handling nulls, or standardizing formats.
Derivation transformation: creating new values from existing columns, such as full name, tax amount, age, or status flag.
Aggregation transformation: summarizing detail rows into grouped results, such as total sales by region or count of orders by customer.

PowerCenter Transformations and IICS Transformations

Informatica PowerCenter and Informatica Intelligent Cloud Services (IICS) both use transformations to define mapping logic, but the design tools and available transformation options are not always identical. In PowerCenter, transformations are created in client tools such as Designer. In IICS Cloud Data Integration, transformations are configured in cloud mapping designers and mapping tasks.

When moving from PowerCenter to IICS, review each transformation type, expression, lookup rule, parameter, and session behavior instead of assuming a direct one-to-one conversion. Most common ETL ideas remain the same, but configuration screens, supported connectors, and runtime options may differ.

Common Mistakes with Informatica Transformations

Using Filter when Router is needed: A Filter keeps or rejects rows, while a Router can send rows to multiple output groups.
Using Joiner unnecessarily: If both sources are from the same relational database, a Source Qualifier join may be simpler in some designs.
Ignoring sorted input rules: Aggregator, Joiner, and Sorter performance depends heavily on sort order and cache behavior.
Using too many unconnected lookups: Conditional lookup calls can be useful, but repeated lookup calls may affect performance.
Forgetting update strategy handling: Target load behavior must match the session and target properties, not just the Update Strategy expression.
Not validating port data types: Mismatched precision, scale, or date formats can cause session failures or incorrect target values.

Informatica Transformation QA Checklist for Mapping Review

Before validating and running an Informatica mapping, review the transformation design against these points:

Each active transformation is placed intentionally and its effect on row count is understood.
Filter, Router, Joiner, Aggregator, and Update Strategy conditions match the business rules.
Expression ports use correct data types, precision, scale, and date formats.
Lookup conditions, cache settings, and default return values are reviewed.
Sorter, Aggregator, and Joiner settings are checked for performance and sorted input assumptions.
Rejected rows, update rows, delete rows, and transaction control rules are tested with sample data.
Reusable transformations and mapplets are used only when the logic is genuinely shared across mappings.

FAQs on Informatica Transformations

What is a transformation in Informatica?

A transformation in Informatica is a repository object used in a mapping or mapplet to process data. It can read rows, calculate values, filter records, join data, perform lookups, aggregate records, route rows, generate sequence numbers, or control target row operations.

What is the difference between active and passive transformations in Informatica?

An active transformation can change the number of rows, row order, transaction boundary, or row type. A passive transformation returns the same number of rows it receives and does not change the transaction boundary or row type.

How many transformations are there in Informatica?

The exact number depends on the Informatica product, version, license, and installed connectors. In PowerCenter learning materials, the most frequently discussed transformations include Source Qualifier, Expression, Filter, Lookup, Aggregator, Joiner, Router, Rank, Sorter, Sequence Generator, Update Strategy, Union, Normalizer, XML transformations, Stored Procedure, and Java transformations.

What are the four main Informatica PowerCenter client tools?

The four commonly taught PowerCenter client tools are Designer, Workflow Manager, Workflow Monitor, and Repository Manager. Transformations are mainly created and configured in Designer, while workflows and sessions are managed in Workflow Manager and monitored in Workflow Monitor.

What are transformations in IICS?

In IICS, transformations are mapping objects used in Cloud Data Integration to define how data is processed between sources and targets. The concept is similar to PowerCenter transformations, but the available transformations, interface, connectors, and runtime settings may differ.