Salesforce MuleSoft Developer: Two Date Formats and 50,000 Rows

Two Date Formats and 50,000 Rows

A batch flow reads a 50,000-record JSON file from SFTP, transforms it with DataWeave, and posts to the Salesforce Bulk API. Source quirks: amount arrives sometimes as a number, sometimes as a string like "1,234.50"; closeDate is ISO-8601 in most records but dd/MM/yyyy in records from one legacy region; notes is "" in about 30% of records and absent in others. The target requires amount as a number, closeDate as yyyy-MM-dd, and notes as null rather than an empty string. The flow currently fails at record 12,000 with a coercion error, and the worker's heap spikes during the run.

1. Write the transformation for `amount` and `closeDate` so a single bad record cannot fail the batch.

Read the worked answer

Use try/orElseTry/orElse chains so each field degrades to null instead of aborting the script.

%dw 2.0
output application/json
---
payload map (rec) -> {
  amount: try(() -> (rec.amount replace "," with "") as Number) orElse null,
  closeDate: try(() -> rec.closeDate as Date {format: "yyyy-MM-dd'T'HH:mm:ss.SSSXXX"})
             orElseTry (rec.closeDate as Date {format: "dd/MM/yyyy"})
             orElse null
}

Notes on the reasoning:

replace then as Number handles both 1234.50 and "1,234.50"; the try absorbs anything else.
Order the date attempts by frequency; the second format only runs when the first throws.
Decide explicitly whether a null closeDate record should be posted or routed to a reject file. Silently nulling a required field is a data-quality bug, not an error-handling strategy - if the target requires it, filter those records out and report them.
For whole-record isolation, wrap the record body in try and emit a reject marker, so one malformed record cannot abort the map.

2. Express the `notes` rule and explain why `default` alone is not enough.

Read the worked answer

default substitutes only when the value is null (absent or explicit null). An empty string is a real value to default, so rec.notes default null leaves "" untouched - the exact bug in this pipeline.

Options that work:

notes: if (isEmpty(rec.notes)) null else rec.notes

or explicitly:

notes: rec.notes match {
  case is String -> if (sizeOf(rec.notes) == 0) null else rec.notes
  else -> null
}

isEmpty returns true for null and for empty strings, arrays, and objects, which matches the requirement here. If a legitimate value could be the string "0" or whitespace-only, define the rule more narrowly (trim first) rather than relying on a truthiness check.

The judgement point: distinguish "missing", "null", and "empty" before writing the expression - in DataWeave they are three different states and only two of them are covered by default.

3. The heap spikes on a 50,000-record file. What DataWeave choices determine whether streaming is preserved?

Read the worked answer

DataWeave streams when the input is streamable (as SFTP/File reads typically are), the output format supports streaming, and the script contains no streaming-incompatible operation.

Operations that force buffering of the whole collection - and therefore the heap spike:

groupBy, orderBy, distinctBy, sizeOf, and anything needing random access or a full pass before emitting.
Building one giant aggregated payload (for example a single array of 50,000 objects) before the HTTP write.

What preserves streaming:

A straightforward map over the array with the reader and writer left as streamed (output application/json).
Emitting as you go, and chunking the Bulk API submission (for example 5,000 records per request) instead of one payload.
Avoiding intermediate variables that hold the full result set.

If you genuinely need a groupBy, do it on a bounded slice, or split the file and process in a foreach/batch job so only one chunk is in memory at a time. Also confirm streaming was not lost for an unrelated reason - logging the whole payload at DEBUG will buffer it and can look exactly like a converter problem.