Compute node
What it does
The Compute node works in one of three modes:
- Derived Columns + Sort: adds columns calculated from each row, and sorts the rows.
- Group By + Aggregates: turns rows into one row per group, with totals, counts, averages, minimums or maximums.
- Window Functions: numbers or ranks rows within each partition, keeping every row.
When to use
- A column is calculated from others:
full_name = CONCAT(first, ' ', last),is_active = EQ(status, 'active'). - You need totals per group, such as revenue per customer or orders per day.
- You need the first, top or ranked row per group, such as the latest order per customer.
To rename and choose columns for a target, use a Transform. Both feed a Load through a Transform.
Configure
Select the node and press Configure to open Configure Compute. Choose the Compute Mode first; each mode has its own settings.
Save and Done stay unavailable while a required setting is missing. Cancel discards the changes made since the dialog opened or was last saved. A message confirms each save.
Derived Columns + Sort
- Derived columns: each has a Name and an Expression, such as
NOW()orROUND(MUL(price, qty), 2). Press fx to open the expression editor with the function library, the upstream fields and validation. - Sort keys: each has a Field and a Direction,
ASCorDESC. Rows are sorted by the first key, then the next.
Group By + Aggregates
- Group Keys: the upstream columns that define a group.
- Aggregates: each has an operation (
count,sum,avg,minormax), a Field (optional forcount) and an output name, such astotal_amount.
Groups are built from every row that reaches the node, so the output arrives after the last input row.
Window Functions
- Partition By: the columns that define each partition.
- Order By: the order within each partition.
- Window Functions: tick
row_number,rankordense_rank, and name the output column for each.
Ports
| Port | Direction | Kind | Notes |
|---|---|---|---|
in | in | data | Rows. |
out | out | data | Rows with derived columns, one row per group, or rows with their rank. |
error | out | control | Receives the input rows when the node fails. |
Examples
Line total. Derived Columns + Sort with a column line_total = ROUND(MUL(price, qty), 2), sorted by line_total DESC.
Revenue per customer. Group By + Aggregates with Group Keys customer_id, aggregates sum of amount as revenue and count as orders. A thousand orders across forty customers give forty rows.
Latest order per customer. Window Functions with Partition By customer_id, Order By order_date descending, row_number as rn. A Filter downstream keeps rows where rn is 1.
Troubleshooting
| Symptom | Cause | What to do |
|---|---|---|
| A derived column is empty | The expression names a field the row does not have. | Check field names in the fx editor. |
| More groups than expected | A group key has variations in case or spaces. | Normalise the key with a derived column first, then group on it. |
| Group output arrives at the end of the run | Groups need every row before they are complete. | No action needed. |
| Rank numbering restarts unexpectedly | The partition columns split the rows more finely than intended. | Remove a column from Partition By. |