Description:

The Sorting Transform step orders records in an ETL dataset by one or more configured columns.

This transformation enables designers to define one or multiple sorting criteria, allowing records to be arranged in ascending or descending order according to business requirements. Sorting is commonly performed before grouping, aggregating, reporting, merging, removing duplicates, or exporting data to ensure consistent processing and predictable output.

Sorting rules are configured through the Configure Sorting Columns List property, where one or more columns and their corresponding sort directions can be defined.

The Sorting Transform step is typically used after data has been imported and optionally filtered or transformed, but before aggregation, grouping, merging, or loading the data into destination systems.

The step supports:

• Multi-column sorting
• Ascending and descending sort order
• Configurable sorting rules
• Dataset ordering
• Integration with ETL transformations
• Data preparation for reporting
• Enterprise ETL workflows

This step can be used for:

• Preparing reports
• Ordering customer records
• Sorting financial transactions
• Organizing inventory data
• Preparing grouped datasets
• Data migration
• Business intelligence and analytics

Usage:

The Sorting Transform step is typically inserted after importing or filtering data and before grouping, aggregation, reporting, or exporting the processed dataset.

During execution, the step evaluates the configured sorting rules and orders every record in the dataset accordingly. The sorted dataset is then passed to downstream ETL components without modifying the underlying data values.

A typical ETL pipeline might look like this:

Typical sorting scenarios include:

• Sorting customers alphabetically
• Ordering invoices by invoice date
• Sorting transactions by amount
• Ordering products by category and name
• Preparing grouped reports
• Ordering records before duplicate removal
• Preparing datasets for downstream analytics

Once sorting has completed, subsequent ETL components can process the records in the configured order.

Prerequisite:

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

Example:

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

• Create a new ETL definition named “clsSortingTransformDef” and open it in designer mode. 
• Drag “clsInputCSV, clsSortingTransform, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” and other steps, as shown above.
• Define a variable or a global to hold the virtual 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 "clsSortingTransform" 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 "clsSortingTransform" step to configure its "Required" properties. Click the column button to open the transform list configuration window. Click the Add Row (+) button to insert an empty row. Select the column from the drop-down list and the sort order. Click the Save button. You may add multiple columns for transformation by using 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. Execute the ETL process. During execution, the transform evaluates the configured sorting rules and rearranges the dataset accordingly. The sorted records are then made available to downstream ETL components for grouping, aggregation, reporting, export, or additional processing.

Tips:

• Configure the most significant sort column first, followed by secondary sorting columns.
• Use multiple sorting columns to create predictable ordering for complex datasets.
• Sort data before grouping or aggregation when a consistent processing order is required.
• Use descriptive schema names to simplify configuration and maintenance.
• Verify the sorted output using representative sample data before deploying to production.
• Keep sorting rules aligned with business reporting requirements.
• Combine this transform with JSON Input File, Filter Transform, Derived Column, Validate Data, Aggregate Transform, Lookup Transform, or JSON Output File components to build complete ETL workflows.

Notes:

• Sorting rearranges records but does not modify the underlying data values.
• Multiple sorting columns can be configured within a single transformation.
• The configured column names must exist within the ETL schema.
• Very large datasets may require additional processing time depending on the number of records and configured sort columns.
• Invalid schema mappings or improperly configured sorting rules may result in ETL execution errors.
• Sorting is commonly performed before grouping, aggregation, duplicate detection, and report generation.
• This transform is frequently used in reporting, business intelligence, financial processing, inventory management, customer data management, and enterprise integration workflows.
• Validate the sorted output before loading data into production systems.

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:

• Sorting column configuration
• Sort direction for each column
• ETL schema mappings
• Downstream transformation connections
• Destination configuration
• Environment-specific settings

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

Click here to download the sample file.