Description:

The Order Columns Transform step rearranges the order of columns in an ETL dataset without modifying the underlying data values. This transformation enables ETL designers to define the exact sequence of columns in the output schema, ensuring compatibility with destination databases, file formats, reporting tools, and external applications.

Column ordering is configured via the Configure Columns List property, which assigns a position to each selected column in the output dataset. During execution, the transformation reorganizes the schema according to the configured positions while preserving all record data.

The Order Columns Transform step is commonly used near the end of an ETL pipeline after data cleansing, validation, enrichment, and schema modifications have been completed, but before exporting or loading the final dataset.

The step supports:

• Custom column ordering
• Position-based schema configuration
• Output schema standardization
• Export preparation
• Destination-specific layouts
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Preparing CSV exports
• Creating fixed-column file layouts
• Database import preparation
• Standardizing reporting schemas
• API payload preparation
• Data migration projects
• Enterprise system integration

Usage:

The Order Columns Transform step is typically placed near the end of an ETL pipeline after all required transformations have completed and immediately before exporting or loading the dataset.

During execution, the transformation rearranges the column sequence according to the configured positions. The resulting dataset contains the same records and values as the input dataset, but with the columns presented in the specified order.

A typical ETL pipeline might look like this:

Typical ordering scenarios include:

• Preparing files for third-party systems
• Matching database import templates
• Producing standardized report layouts
• Creating fixed-format CSV exports
• Aligning schemas across multiple applications
• Reordering columns for business users
• Preparing datasets for regulatory reporting

After the transformation completes, downstream components receive the dataset using the configured column order.

Prerequisite:

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

Example:

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

• Create a new ETL definition named “clsOrderColumnsTransformDef” and open it in designer mode. 
• Drag “clsInputCSV, clsOrderColumnsTransform, 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 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 follows. This function helps to understand the schema at a glance.  

• Click the "clsOrderColumnsTransform" 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 "clsOrderColumnsTransform" 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. Set the order position starting from 1. Click the Save button. You may add multiple columns for transformation 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, create a new ETL instance, and execute. Render the ETL instance. Click the ETL step to view its properties. The “clsOrderColumnsTransform” step should order columns according to the output file’s configuration. The reordered dataset is then passed to downstream ETL components or exported in the required column sequence. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Perform column ordering after all schema modifications such as renaming, removing, or adding columns.
• Match the required column sequence expected by destination systems to avoid import failures.
• Use standardized column ordering across ETL definitions for consistency.
• Verify exported files after changing column positions.
• Keep frequently referenced business identifiers near the beginning of the dataset where appropriate.
• Review downstream integrations whenever column ordering changes.
• Combine this transform with Rename Column, Output Column Transform, Set Field Value, Validate Data, Sorting Transform, Pivot Transform, CSV Output File, JSON Output File, or SQL Output components to build complete ETL workflows.

Notes:

• This transformation changes only the order of columns and does not modify any data values.
• Multiple columns can be reordered within a single transformation.
• Every configured column must exist within the ETL schema.
• Columns not explicitly assigned a position should be reviewed to ensure the desired output layout is achieved.
• Invalid column references or duplicate position assignments may result in ETL execution errors.
• Column ordering affects the presentation of the output dataset but does not change record content.
• This transform is commonly used for reporting, file exports, system integration, regulatory submissions, data migration, and enterprise data exchange.
• Validate the resulting schema before deploying ETL definitions to production environments.

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:

• Column position assignments
• ETL schema validation
• Output schema 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.