Batch Processing for ERP

Batch processing is still the backbone of most ERP data movement — nightly GL syncs, bulk price updates, mass invoice generation, year-end reprocessing — even in a world where real-time integration gets more attention. The failure modes are different from a real-time API integration: instead of one request failing, a batch job can fail halfway through 50,000 records, and the recovery strategy has to account for that from the start, not be bolted on after the first partial-failure incident.

Chunking and checkpointing

Processing a million-row batch as a single transaction is the most common mistake — it holds locks for the duration, makes a mid-run failure roll back everything already done, and gives no visibility into progress. Chunk the batch into fixed-size pages (1,000-10,000 rows depending on record complexity), commit each chunk independently, and persist a checkpoint (the last successfully processed key or offset) so a restart after failure resumes from where it left off instead of reprocessing everything.

CREATE TABLE batch_checkpoint (
    job_name       VARCHAR(100) PRIMARY KEY,
    last_processed_id BIGINT NOT NULL,
    status         VARCHAR(20) NOT NULL, -- running, completed, failed
    updated_at     TIMESTAMP NOT NULL DEFAULT now()
);

-- Resume query for the next chunk
SELECT * FROM staging_orders
WHERE id > (SELECT last_processed_id FROM batch_checkpoint WHERE job_name = 'gl_sync')
ORDER BY id
LIMIT 5000;

Idempotent writes into the ERP

Because a batch step can be retried after a partial failure, every write into the ERP needs to be safe to repeat. The standard pattern is an upsert keyed on a stable business identifier (an external order number, not an auto-increment ID) rather than a blind insert — most ERPs, including Acumatica through its contract-based REST API or OData endpoints, support querying by a key field before deciding whether to create or update. Where the target system genuinely only supports insert, track a processed-records ledger so a retry can skip rows already confirmed written.

Scheduling and avoiding overlap

A batch job that overruns its scheduled window and is still running when the next scheduled run fires is one of the most common causes of ERP data corruption in practice — two instances of the same job processing overlapping data concurrently. Guard against this with a job-level lock (a row in a status table, a distributed lock, or your scheduler's built-in overlap prevention) that refuses to start a new run while a prior one is still marked running, and alert rather than silently skip when that happens.

Error handling: fail-fast vs. skip-and-continue

Whether a bad record should stop the whole batch or get logged and skipped depends on what the batch does. A GL posting batch where one bad journal entry could unbalance the ledger should fail fast on that chunk. A bulk customer-address update where one malformed row shouldn't block 49,999 good ones should skip-and-continue, writing the failed rows to an error table for manual review. Decide this per job type explicitly — a blanket policy in either direction is wrong for some subset of batches.

Observability for long-running jobs

A batch job that silently runs for six hours with no progress signal is undebuggable when it's slow — was it always going to take six hours, or did it stall at record 40,000? Emit a progress metric (rows processed, current checkpoint, estimated completion) on a regular interval, not just a start/end log line, and alert on jobs that exceed their typical duration by a meaningful margin rather than waiting for a hard timeout.

Wrapping up

Batch processing against an ERP is a reliability problem more than a throughput problem — most ERPs can process records fast enough; the hard part is surviving a partial failure without corrupting data or losing progress. Chunk with checkpoints, make every write idempotent, lock against overlapping runs, and decide fail-fast versus skip-and-continue deliberately per job rather than defaulting to one or the other everywhere.