Creating an Incremental Aggregation on a Target Table in a Transformation Flow on File

Incremental aggregation on target tables in transformation flows for efficiently maintaining aggregated results during incremental data loads. Use it to apply aggregation functions (SUM, COUNT, MIN, MAX, AVG, LAST) to numerical columns when delta capture is enabled on the source table.

Add incremental aggregations to the target table. It is useful for handling incremental data loads and maintaining aggregated results efficiently:
  1. Click the target node to display its properties in the side panel. In the Incremental Aggregation section, click Edit Aggregation.

  2. In the Incremental Aggregation dialog are listed all aggregations with numerical data types. You can define the following aggregation types:

    Type Description

    LAST (default)

    Retains the most recent value for the column based on the latest operations applied to the row. When a row is updated, the previous value is replaced with the new value. Columns with non-numerical data types (such as text or dates) can only use the LAST aggregation type.

    SUM

    Calculates the cumulative total of all numerical values in the column. When new data is added incrementally, the new values are added to the existing sum. Useful for tracking totals such as sales amounts or quantities.

    COUNT

    Counts the number of non-null values in the column. Null values are excluded from the count calculations. When new rows are added, the count increments accordingly. Useful for tracking the number of valid records or transactions.

    MIN

    Identifies and retains the minimum (smallest) value in the column across all rows. When used in a transformation flow, an intermediate persistent table file is created to handle these operations within its capacity unit. See Monitoring Capacity Unit Consumption.

    MAX

    Identifies and retains the maximum (largest) value in the column across all rows. When used in a transformation flow, an intermediate persistent table file is created to handle these operations within its capacity unit. See Monitoring Capacity Unit Consumption.

    COUNT*

    Counts all values in the column, including null values. Unlike COUNT, this type provides a total row count. Useful for understanding data volume or detecting missing values when combined with COUNT comparisons.

    AVG

    Calculates the average (mean) of all numerical values in the column. When used in a transformation flow, an intermediate persistent table file is created to handle these operations within its capacity unit. Note that incremental average calculations require careful management to maintain accuracy with delta updates. See Monitoring Capacity Unit Consumption.

  3. Click Save. You can see the updated columns in the Incremental Aggregations section.