Performance Pitfalls in DataWeave: The Cost of Dropping Rows

A seemingly simple DataWeave transformation to process transaction data into our general ledger was working fine for our daily runs but taking hours to complete during month-end processing with larger volumes. The issue wasn't where I expected.

The Task

We needed to transform transaction data from our member system by grouping it by account number and name, then summing the debit and credit columns to create consolidated entries for our general ledger.

Original Code:

output application/java
import * from dw::core::Arrays
---
(((drop(payload,1) map (value,i) -> {
    'payer' : value.payer,
    'payee' : value.payee,
    'creditAmt' : if (trim(value.creditAmt) != "") value.creditAmt as Number else 0 as Number,
    'debitAmt' : if (trim(value.debitAmt) != "") value.debitAmt as Number else 0 as Number,
    'dOrC' :  if (trim(value.creditAmt) != "") 'd' else 'c'
})
groupBy ($.payer ++ $.payee ++ $.dOrC)
mapObject (value,key) -> (key): {
    'payer' : value.payer,
    'payee' : value.payee,
    'creditAmt' : sum(value.creditAmt),
    'debitAmt' : sum(value.debitAmt),
})
pluck $

The Problem

My initial assumption was that the grouping and aggregation operations were causing the bottleneck. After testing, I discovered that the culprit was actually how we were removing the header row.

These innocent-looking approaches to removing the first row:

drop(payload,1)

or using array slicing:

payload[1,-1]

It turns out that dropping the first row in DataWeave is surprisingly inefficient, especially when dealing with larger datasets. This operation, which we were using to remove the header row from our CSV input, was causing the entire transformation to grind to a halt.

The Solution

Rather than explicitly removing the header row, I switched to a filtering approach to select only the rows containing actual data. By using a regex pattern to identify rows where either the debit or credit columns contain numeric values, we effectively exclude the header row.

%dw 2.0
output application/java
---
(((payload filter (($.creditAmt matches /^[\d.]+$/) or ($.debitAmt matches /^[\d.]+$/))
map (item) -> {
    payer : item.payer,
    payee : item.payee,
    creditAmt : if (trim(item.creditAmt) != "") item.creditAmt as Number else 0 as Number,
    debitAmt : if (trim(item.debitAmt) != "") item.debitAmt as Number else 0 as Number,
    deborcred :  if (trim(item.creditAmt) != "") 'd' else 'c'
})
groupBy ($.payer ++ $.payee ++ $.deborcred))
mapObject (value,key) -> (key): {
    'payer' : value.payer,
    'payee' : value.payee,
    'creditAmt' : sum(value.creditAmt),
    'debitAmt' : sum(value.debitAmt),
})
pluck $

This approach reduced processing time from hours to under a minute, even with our largest month-end datasets.

Key Takeaways

1. Don't assume you know where performance bottlenecks are. Profile first, optimize second. DataWeave profiling requires manual line-by-line testing with production-sized payloads. Unlike other programming for other stacks, there's no built-in profiler or query plan to help identify bottlenecks.

2. In DataWeave, avoid drop(payload,1) or payload[1,-1] for removing header rows from large datasets. These operations become remarkably inefficient as data volume increases.

3. Instead of removing header rows, use filters to select the data rows you want. While this example uses a regex match for numeric values, you could also filter based on row position, specific field values, or any other criteria that fits your data structure.

4. As with optimizing other systems, when optimizing DataWeave transformations, consider not just what you're trying to accomplish, but alternative approaches to accomplish the same result. Sometimes the most dramatic performance gains come from changing your approach rather than optimizing individual operations.

Hope this helps!

Nick Rubino