A Five-Hour Nightly Load in a Three-Hour Window
A distributor runs Salesforce as its order front end while a legacy ERP remains the system of record for products and pricing. Every night the ERP emits about 850,000 OrderLine changes into a staging table that a middleware tool pushes into Salesforce using REST API calls in chunks of 200 records, with allOrNone set to true per chunk. The nightly window is three hours; runs now take five, and roughly 4% of chunks fail with UNABLE_TO_LOCK_ROW or CPU time-limit errors. Retries create duplicate OrderLines because the upsert key is the Salesforce ID, which is blank on the first load. An after-save trigger on OrderLine synchronously calls an external tax service; when that service is slow, entire chunks roll back. Business now wants price changes reflected within 15 minutes for 40,000 high-priority lines, while the remaining volume can stay nightly.
1. Which Salesforce mechanisms should carry the 850,000 nightly changes and the 40,000 near-real-time lines? Justify the split rather than naming one API.
Read the worked answer
The nightly volume is a bulk, asynchronous, order-tolerant problem, so move it to Bulk API 2.0 ingest jobs with CSV payloads upserted on a unique external ID. Bulk API is designed for this shape: jobs are queued and processed server-side, so the middleware is not holding 4,250 HTTP round trips open against concurrent request limits, and failures are reported row by row in the job results rather than as a whole-chunk exception. Chunk into roughly 100,000-record jobs so a retry is cheap.
The 40,000 near-real-time lines are an event problem. Publish them from the ERP or middleware as Platform Events (or change data capture if the source can emit it) and process with a subscriber that handles retries and uses replay IDs to recover from gaps. Be explicit about the constraint: platform events are retained for a limited window (24 hours, extended for high-volume events), so they are a delta channel, not a backfill mechanism. Anything that must be replayed after that window comes through the bulk path.
So the answer is a hybrid, not a favourite API: bulk for volume and backfill, events for latency-sensitive deltas, one idempotent write path keyed on the external ID so a delta and a bulk record for the same line converge instead of duplicating. The 200-record REST chunks disappear entirely.
2. Design the error handling and idempotency model. Be specific about batch size, transaction boundaries and retry behaviour.
Read the worked answer
Start with the key. Create a unique, external, case-sensitive External ID on OrderLine (ERP order number plus line number) and enforce uniqueness at the database level, not just in middleware. Every write becomes an upsert on that key; replays become updates. This single change removes the duplicate class of failure you are seeing today.
Next, shrink the blast radius. allOrNone=true across 200 records means one lock contention discards 199 good rows. Set allOrNone=false at the record level and rely on Bulk API 2.0's failed-records result, or use smaller transactional units where genuine atomicity is required - for example, an order header and its lines must commit together, but two unrelated orders need not. Aim for one logical order per transaction.
Then build a dead-letter path: write failed rows with their error payload into a staging custom object, assign a retry counter, and reprocess on a schedule with exponential backoff, capping attempts before a human is paged. Keep the successful-record count in a load log so reconciliation with the ERP is mechanical. Reduce lock contention by processing in a consistent order (same key sort), using serial mode where needed, and avoiding concurrent writes to the same parent.
Finally, make the middleware idempotent too: it must be safe to submit the same job twice, and it should query job status rather than blindly resubmitting after a timeout.
3. The tax callout sits in an after-save trigger. Explain the technical problem and the redesign you would propose.
Read the worked answer
The problem is structural, not tuning. Apex cannot make a callout after DML in the same transaction, so a trigger performing the save and then calling out either fails or is forced into awkward workarounds. Even where it is technically permitted, you are holding a database transaction open across a network hop to a third party with unknown latency. Add 200 lines per chunk and you also collide with callout limits (100 per transaction and a cumulative timeout), so a bulk load can never call the tax service one line at a time.
Redesign around the natural boundary. Persist the OrderLine with a status such as Pricing Pending and no tax amount, then do the callout asynchronously: a Queueable or a platform-event-triggered handler that fires one callout per transaction carrying a composite payload of many lines, writes results back, and flips the status. Use Named Credentials for endpoint and credential management, and an HttpCalloutMock in tests.
The uncomfortable part to raise with the business: asynchronous callouts are not transactional. If the service fails after the order is saved, you need a compensating action and a visible state, not a silent retry loop. Decide with the business whether tax is a hard gate before order confirmation (then the order stays in Pending and customers see a delay) or an eventually-consistent adjustment. Cache rates so repeated lookups for the same jurisdiction and date do not hit the service, and consider whether the middleware should orchestrate the tax call entirely, keeping Salesforce out of the synchronous path.