Description:

The Aggregate Transform step performs aggregate operations on data flowing through an ETL pipeline.

This transformation allows you to select an input column, apply an aggregate function, and write the resulting value to an output column within the ETL schema. Aggregate calculations are commonly used to summarize large datasets, calculate totals, determine averages, identify minimum or maximum values, and generate statistical information for reporting or analytical workflows.

The aggregate operation is configured using one of the supported Aggregate Operators, enabling the transform to produce summarized information without requiring custom SQL or scripting.

The Aggregate Transform step is typically used after data has been extracted and optionally filtered or sorted, but before loading the transformed data into the destination system.

The step supports:

• Aggregate calculations on selected columns
• Multiple aggregate operators
• Configurable input and output columns
• Integration with ETL schema mappings
• Dataset summarization
• Reporting and analytical workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Data summarization
• Financial calculations
• Inventory reporting
• Sales analysis
• Operational reporting
• Business intelligence preparation
• ETL data transformation

Usage:

The Aggregate Transform step is typically placed after data extraction and optional filtering, but before loading data into the destination.

The transform reads values from the selected source column, applies the configured aggregate operation, and stores the result in the selected output column.

A typical ETL pipeline might look like this:

Typical aggregate operations include:

• Calculating total sales
• Counting records
• Computing average transaction values
• Finding minimum or maximum values
• Summarizing inventory quantities
• Producing analytical datasets

After the aggregation has completed, downstream ETL transformations can continue processing the summarized data before it is written to the destination.

Prerequisite:

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

Example:

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

• Create a new ETL definition named “clsAggregateTransformDef” and open it in designer mode. 
• Drag “clsInputCSV, clsAggregateTransform, 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 "clsAggregateTransform" step to configure its "Required" properties. Provide the step name. Select the input column from the drop-down list. Select the aggregate operator from the drop-down list. Select the output column from the drop-down list. You may also type a new column name that does not exist in the schema and press Enter to add it. Click the button to configure the columns to transform. Click the Save 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 reads the selected source column, performs the configured aggregate operation, and writes the calculated result into the configured output column. Subsequent ETL transforms and destination connectors can then use the aggregated value for reporting, analytics, or additional business processing.

Tips:

• Ensure the selected source column contains data compatible with the chosen aggregate operation.
• Use numeric columns for operations such as Sum and Average.
• Choose descriptive output column names that clearly identify the calculated result.
• Apply filters before aggregation when only a subset of records should be included.
• Combine multiple Aggregate Transform steps to calculate different summary values within the same ETL pipeline.
• Validate the output schema before loading data into the destination.
• Use this transform together with Filter Transform, Sort Transform, Group Transform, Derived Column, or Lookup Transform to build comprehensive ETL workflows.

Notes:

• The source and output columns must exist within the ETL schema.
• The selected aggregate operator must be compatible with the source column's data type.
• Aggregate calculations are performed during ETL execution.
• The calculated result is written to the configured output column.
• Invalid column mappings or incompatible aggregate operations may result in ETL execution errors.
• This transform summarizes data without modifying the original source dataset.
• Proper schema validation should be performed before executing production ETL pipelines.
• Aggregate transformations are commonly used in reporting, data warehousing, business intelligence, financial analysis, and operational dashboards.

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:

• Source schema mappings
• Input column selection
• Aggregate operator selection
• Output column mapping
• Destination schema
• ETL connection settings
• Environment-specific configuration

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

Click here to download the sample file.