The nightly price import that melts the container

Why it is slow

• 90,000 individual Commit activities mean 90,000 validation runs and 90,000 round trips to the database.
• Committing with events enabled fires every event handler, after-commit microflow and notification for each row; on a bulk import these are pure overhead and can themselves create objects (which also explains part of the heap growth).
• No batching: the transaction and the change list grow monotonically for the whole run.

Why it runs out of memory

• Every PriceRow stays referenced by the microflow's variable list for the duration of the run, so all 90,000 objects plus their change state live on the heap at once. Commit does not release the object; scope does.

Restructure

1. Read and parse in chunks (for example 1,000 lines), and call a sub-microflow per chunk that creates and commits its rows with events disabled, then returns only a summary. Objects of the previous chunk then fall out of scope.
2. Use Commit without events for imported rows, and only enable validation where it matters; if you need validation, run it explicitly in the chunk before committing.
3. Do the parsing in a Java action or a streamed approach if the file itself is large, instead of holding the whole document content as a string.
4. Verify with a production-sized file, not a 500-line sample, and watch heap and duration in the cloud metrics.

Error isolation

• Validate before committing: parse each field into a target type, catch the failure in the modeler (or in the parsing Java action) and record the line in an ImportError entity with line number and reason.
• Handle errors per chunk rather than per whole run. In the error handling of the activity that can fail, 'Continue' lets the run proceed but leaves the failure silently swallowed; 'Custom with rollback' rolls back the current transaction, which can also discard rows you wanted to keep. Decide deliberately which one matches your requirement, and keep failing work in a small sub-microflow so the blast radius is a chunk, not the night.
• Never let a malformed number reach an unhandled error at the top of the scheduled event; if it does, the transaction is rolled back and the run reports nothing useful.

Idempotency

• Give each row a natural key: supplier + file date + product code. Before creating, retrieve by that key; skip or update instead of insert.
• Alternatively write the parsed rows into a staging entity with a unique import batch, then merge into the live entity, so a failed run can be restarted after deleting the batch.
• Log a run record (batch id, rows read, rows created, rows rejected) so the next morning's question 'did it complete?' has a data answer.

Changes

• Chunk size becomes tunable; keep it small enough that one chunk fits comfortably in heap, and avoid re-reading the file per chunk.
• Consider moving the bulk insert into a Java action or a staged bulk-copy if the platform-level commit is still the bottleneck; measure before adding that complexity.
• Scheduled events run on a single node in a multi-node environment, so long runs can overlap with the next schedule; add a guard that refuses to start if a run is still marked 'in progress', and consider moving the trigger to a queue-style design.

Monitoring

• Application log nodes for the import (log a start/end line with counts at Info level) and a dedicated error node so failures are easy to filter.
• JVM heap and CPU graphs in the cloud portal during the scheduled window; a sawtooth that never returns to baseline means objects are still being retained.
• Database growth and the duration trend per run, so a slow regression from 4 to 25 minutes is visible before it becomes an outage.