Description:

The Filter with Expressions step evaluates each record in an ETL dataset against one or more user-defined filter expressions. Records that satisfy the configured expression are retained, while records that do not meet the criteria are excluded from further processing.

Unlike simple column-based filtering, expression filtering allows complex business logic to be defined using multiple fields, comparison operators, logical operators, functions, and calculated conditions. This enables highly flexible filtering based on business rules without requiring custom code.

Filter expressions are configured through the Enter Filter Expressions property. During execution, every incoming record is evaluated independently against the configured expression, and only matching records continue through the ETL pipeline.

The Filter with Expressions step is commonly placed after data extraction and validation, allowing downstream transformations to process only relevant data.

The step supports:

• Expression-based filtering
• Multiple comparison operators
• Complex logical conditions
• Multi-column expressions
• Business rule filtering
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Filtering active customers
• Selecting high-value transactions
• Removing invalid records
• Processing regional data
• Preparing regulatory datasets
• Business intelligence reporting
• Enterprise data integration

Usage:

The Filter with Expressions step is typically placed immediately after importing and validating data so that only qualifying records continue through subsequent transformations.

During execution, each incoming record is evaluated independently. Records matching the configured expression remain in the dataset, while all non-matching records are excluded from further processing.

A typical ETL pipeline might look like this:

Typical filtering scenarios include:

• Selecting customers whose status is Active
• Processing invoices above a specified amount
• Filtering orders for a specific country or region
• Selecting records within a date range
• Removing incomplete or invalid records
• Filtering products based on inventory levels
• Preparing datasets for compliance reporting

By reducing the dataset early in the ETL process, downstream transformations can execute more efficiently while operating only on relevant records.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsFilterTransformExpressionsDef” example.          

• Create a new ETL definition named “clsFilterTransformExpressionsDef” and open it in designer mode. 
• Drag “clsInputCSV, clsFilterTransformExpressions, and clsOutputCSV” steps to the canvas.
• Connect the dots between the “clsInputCSV” step and other steps, as shown above.
• Define a variable or a global to store the file path.  
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsFilterTransformExpressions" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the columns to transform. Then click the Save button. 

• Click the "clsFilterTransformExpressions" step to configure its "Required" properties. Click the button to open the transform list configuration window. Click the Add Row (+) button to insert an empty row. Enter the filter expression in the text box. You may add multiple columns for transformation by clicking the Add Row button. 

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Save the ETL definition, create a new ETL instance, and execute. Render the ETL instance. Click the ETL step to view its properties. The “clsFilterTransformExpressions” step should filter the column values according to the output file configuration. Only records satisfying the configured expression continue to downstream ETL components. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Validate incoming data before applying complex filter expressions.
• Keep expressions as simple and readable as possible to improve maintainability.
• Test expressions using representative datasets before deploying to production.
• Use filtering early in the ETL pipeline to reduce the volume of data processed by downstream transformations.
• Document complex business rules within the ETL definition for future maintenance.
• Review expression logic whenever source schemas or business requirements change.
• Combine this transform with Validate Data, Sorting Transform, Group By Transform, Pivot Transform, Aggregate Transform, Output Column Transform, JSON Output File, or SQL Output components to build comprehensive ETL workflows.

Notes:

• Every incoming record is evaluated independently against the configured expression.
• Records satisfying the expression continue through the ETL pipeline, while non-matching records are excluded.
• Multiple columns may be referenced within a single expression.
• Complex logical conditions can be created using supported comparison and logical operators.
• Invalid expressions or references to non-existent columns may result in ETL execution errors.
• Filtering does not modify the remaining records; it only determines whether they continue in the pipeline.
• This transform is commonly used for business rule enforcement, regulatory reporting, customer segmentation, operational reporting, data cleansing, and enterprise integration.
• Review filter expressions periodically to ensure they continue to reflect current business requirements.

Filter with Conditions vs. Filter with Expressions:

Although both steps filter records from an ETL dataset, they are designed for different configuration styles:

In general:

• Use Filter with Conditions when business rules can be expressed using standard comparison operators configured through the designer.
• Use Filter with Expressions when filtering requires calculated values, nested logical conditions, custom functions, or advanced expression syntax.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

• Filter expression configuration
• ETL schema validation
• Source column verification
• Downstream transformation mappings
• Destination component configuration
• Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.