Creating Expression Transformation in Informatica

Expression Transformation in Informatica is a passive, connected transformation used to calculate values for each incoming row. It performs row-level, non-aggregate operations such as concatenating names, changing text case, calculating numeric values, converting data types and applying date logic.

An Expression Transformation does not change the number of rows passing through the mapping. Instead, it evaluates an expression separately for every row. Common Informatica functions used in this transformation include CONCAT, SUBSTR, LENGTH, ABS, ROUND, TO_CHAR and TO_DATE.

In this Informatica tutorial, we create an Expression Transformation that loads only customers from the city of WASHINGTON and derives a full name from the FIRST_NAME and LAST_NAME columns before loading the records into a staging table.

Expression Transformation Scenario and Mapping Objects

The source table in this example is CUSTOMER, and the target table is Stg_exp_customer. A Filter Transformation selects only rows where the city is WASHINGTON, while the Expression Transformation creates the derived Full_Name value.

• Mapping name: m_<Target Table Name>
• Session name: s_<Mapping Name>
• Workflow name: wf_<Mapping Name>

For this tutorial, the mapping name is m_Stg_exp_Customer.

Create the Source and Target Definitions for the Expression Mapping

• Import the CUSTOMER source definition into Source Analyzer. If it already exists in the repository folder, reuse the existing definition.
• Import the Stg_exp_customer target definition into Target Designer using the configured database or ODBC connection.
• Confirm that the target contains a column for the derived full name and the required source columns.

Create the m_Stg_exp_Customer Mapping

Open Mapping Designer and create a mapping named m_Stg_exp_Customer.

Drag the CUSTOMER source and Stg_exp_customer target definitions into the Mapping Designer workspace. Informatica creates the Source Qualifier for the source definition.

Filter WASHINGTON Customer Rows Before the Expression Transformation

Create a Filter Transformation, and connect the required columns from SQ_CUSTOMER to the filter.

Open the Filter Transformation properties and define a condition that passes only records whose CITY value is WASHINGTON. Check the source data for leading spaces, trailing spaces or inconsistent letter case if the expected rows are not returned.

Add an Expression Transformation to Derive Full_Name

Create a new Expression Transformation and connect the required output ports from the Filter Transformation to it.

Double-click the Expression Transformation, open the Ports tab and use the new-port button on the right side to add a derived output port.

Name the new port Full_Name. Choose an appropriate string datatype and set the precision to a value large enough to hold the combined first and last names. The example uses a precision of 40.

Clear the I checkbox for the Full_Name port so that it is configured as an output-only port. Input ports receive values from an upstream transformation, while output ports return calculated values to downstream objects.

Click the expression editor button for the Full_Name port.

Enter the expression CONCAT(FIRST_NAME,LAST_NAME) and validate it in the editor.

The Expression Editor provides access to three useful groups:

• Functions: Lists built-in Informatica functions for strings, numbers, dates, conversions and conditional logic.
• Ports: Lists ports available in the current transformation so they can be referenced in expressions.
• Variables: Lists variable ports that can store intermediate row-level calculations.

The expression shown in the screenshot joins the two values directly. If the target should contain a space between the first and last name, use a string expression that includes a space and handles null values according to the project requirements.

Connect the Expression Output Ports to Stg_exp_customer

Close the Expression Editor and connect the required output ports from the Expression Transformation to the corresponding target columns. Connect Full_Name to the target full-name column.

Save the mapping and run mapping validation. The completed data flow should contain the source, Source Qualifier, Filter Transformation, Expression Transformation and target in the correct sequence.

Create the Session for the Expression Transformation Mapping

Open Workflow Manager, go to Task Developer and create a session task for the mapping.

Configure the source and target database connections in the session properties. Also review target load options, commit settings, error handling and any required pre-SQL or post-SQL configuration.

Create and Run the Informatica Workflow

Open Workflow Designer, create a workflow and add the session task. Connect the Start task to the session, save the workflow and run it.

Use Workflow Monitor to verify that the session completed successfully. Review the session log for source rows read, rows passed by the filter, target rows loaded, rejected rows and expression-related errors.

How to Test the Expression Transformation Mapping

Code checking:-

Before testing or executing any mapping, we first need to check that weather we have given right links or not. And also need to check properties of transformation weather required properties defined or not.

Validate the mapping before execution and confirm that every required port is connected. Check the Filter Transformation condition, the Full_Name expression, output-port settings, target column mapping and source and target session connections.

Data Checking:-

Generally in ETL process what we do is we just extract data from source with Informatica nothing but a selecting data from source and we do transform data inside ETL then load to target.

We can test this ETL process with a simple formula as below.

Target Data = Source data + Transformation logic inside Informatica

For this mapping, test both the number of loaded rows and the values produced by the Expression Transformation.

1. No of rows from Target is equal to no of rows with source data plus Transformation logic inside Informatica.

For the above mapping, sql’s for this test case are

Select count(*) from  Stg_exp_customer;
Select count(*) from Customer where CITY='WASHINGTON';

Run the source query against the source database and the target query against the target database. The counts should match when the target was empty before execution, no records were rejected and the mapping was run once.

Select count(*) from  Stg_exp_customer = Select count(*) from Customer + where 
CITY='WASHINGTON';

This will not execute in database, just give you to refer your formula.

The second test verifies that the target column values match the source data after applying the filter and Full_Name expression.

2. Data should match in target with source and Transformation logic inside Informatica.

SELECT CUSTOMER.CUSTOMER_ID, fullName, CUSTOMER.ADDRESS1, CUSTOMER.ADDRESS2, CUSTOMER.CITY,
 CUSTOMER.COUNTY, CUSTOMER.POSTAL_CODE, CUSTOMER.COUNTRY_CODE, CUSTOMER.COUNTRY_NAME,
 CUSTOMER.CREATION_DATE,CUSTOMER.CREATED_BY, CUSTOMER.LAST_UPDATE_DATE, 
CUSTOMER.LAST_UPDATED_BY FROM Stg_exp_customer customer where CUSTOMER_ID=100004

Compare the returned target row with the corresponding source row. Confirm that the customer belongs to WASHINGTON and that the target full-name value matches the expression configured in the transformation.

Expression Transformation Ports in Informatica

• Input port: Receives data from an upstream transformation.
• Output port: Returns a calculated or forwarded value to a downstream transformation or target.
• Input/output port: Receives an incoming value and passes it to the next object, with or without use in another expression.
• Variable port: Stores an intermediate calculation that can be reused by later variable or output ports in the same row.

Variable ports are useful when a complex formula would otherwise be repeated. Their order matters because an expression can reference only ports that are evaluated before it.

Common Expression Transformation Errors

• Expression validation fails: Check function syntax, parentheses, port names and datatype compatibility.
• Full_Name is null: Review how the expression handles null values in FIRST_NAME or LAST_NAME.
• Names have no separator: The expression CONCAT(FIRST_NAME,LAST_NAME) does not insert a space automatically.
• Target value is truncated: Increase the precision of the Full_Name output port and the matching target column if necessary.
• Unexpected row count: Recheck the filter condition, previous target data, rejected rows and session log statistics.
• Port cannot be connected: Confirm that the port is enabled as an output port and that its datatype is compatible with the target column.

Expression Transformation Mapping Review Checklist

• Confirm that CUSTOMER and Stg_exp_customer definitions match the required database structures.
• Verify that the Filter Transformation passes only CITY values intended for the WASHINGTON load.
• Confirm that Full_Name is configured as an output port with sufficient precision.
• Validate the CONCAT expression and review its handling of spaces and null values.
• Check every link from Source Qualifier to Filter, Expression Transformation and target.
• Verify source and target connections in the session properties.
• Compare source-filtered and target row counts after the workflow runs.
• Test at least one target record against its source values and expected Full_Name result.

Expression Transformation in Informatica FAQs

Is Expression Transformation active or passive in Informatica?

Expression Transformation is passive because it does not change the number of rows passing through it. It calculates or modifies column values for each incoming row.

Can an Expression Transformation perform aggregate calculations?

It is intended for row-level calculations rather than calculations across groups of rows. Use an Aggregator Transformation when the logic requires functions such as grouped sums, averages or counts.

What is a variable port in an Informatica Expression Transformation?

A variable port stores an intermediate row-level result. Later ports in the same Expression Transformation can reuse that result, which reduces repeated logic and makes complex expressions easier to maintain.

Why does CONCAT not add a space between first and last name?

CONCAT joins the supplied strings exactly as provided. A separator must be included explicitly when the required output is formatted as a first name, a space and a last name.

How do you validate an expression in Informatica PowerCenter?

Open the Expression Editor for the port and use its validation option. Then validate the complete mapping to identify invalid expressions, missing links, incompatible datatypes or required transformation properties.