bulk-api-2-patterns

# Bulk API 2.0 Patterns (Integration)

This skill activates when an integration engineer, ETL author, or middleware developer must make a **long-running Bulk API 2.0 pipeline reliable**: not merely issue REST calls, but coordinate job creation, data upload, the mandatory close signal, asynchronous processing, result retrieval, and safe retries. Bulk API 2.0 is asynchronous; successful internal batches are not rolled back when other batches fail, so integration design must treat outcomes as **per-record and per-batch**, not as a single atomic transaction. That asymmetry drives most production incidents when teams assume “the job failed so nothing landed,” or when they omit the step that transitions a job from **Open** to processing.

The platform exposes ingest jobs under `.../jobs/ingest/` and query jobs under `.../jobs/query/`. For ingest, after CSV data is uploaded with `PUT` against the job’s data URL, you **must** send `PATCH` on the job resource with `{"state":"UploadComplete"}` so Salesforce can start processing. Skipping that request leaves the job waiting indefinitely—there is no background timeout that substitutes for an explicit client signal. Multipart job creation is an exception: when job metadata and CSV are posted together as `multipart/form-data`, Salesforce completes the upload phase for you and you do not manually set `UploadComplete`. Choose multipart only when payload size fits the documented multipart limits; larger loads should use the standard create → upload → `UploadComplete` sequence.

Operational limits matter at integration design time. Salesforce creates internal batches of up to 10,000 records and caps total processed volume per org per rolling window (see *Bulk API 2.0 Limits* in the official guide). Each upload `PUT` must keep payload under the per-request size cap described in the guide (including base64-related guidance where applicable). Exceeding limits produces failed batches or throttling; the connector should surface `numberRecordsFailed`, `numberRecordsProcessed`, and error result files rather than silently restarting whole files.

For **query** jobs, results arrive as paginated CSV. The only supported way to walk pages is to read the `Sforce-Locator` response header from each `GET .../results` call, pass that opaque token as the `locator` query parameter on the next call, and stop when the header’s value is the literal string `null`. Guessing locator values or omitting pagination strands part of the extract off-platform.

Use **`data/bulk-api-patterns`** when the primary need is call-level request/response examples, CSV grammar, or v1 vs v2 comparisons. Use **`integration/real-time-vs-batch-integration`** when the open question is whether bulk is appropriate versus events or synchronous callouts. This integration skill focuses on **connector behavior**: idempotency keys (typically upsert external IDs), sequencing multi-job loads, backoff on `InProgress`, and parsing `successfulResults`, `failedResults`, and `unprocessedRecords` to build the next upload file.

---

## Before Starting

Gather this context before working on anything in this domain:

| Context | What to gather |
|---|---|
| Auth and instance URL | OAuth access token, API version segment (e.g. `v66.0`), and the correct My Domain base URL for all subsequent job URLs returned by `contentUrl`. |
| Upload path | Single-part CSV `PUT` sequence vs. multipart `POST` create — multipart skips manual `UploadComplete` by design. |
| Failure semantics | Whether downstream systems can tolerate partial success; if not, compensating transactions must live **outside** Salesforce because bulk commits are not all-or-nothing. |
| Most common wrong assumption | That creating a job and streaming CSV is enough for processing to start. Without `UploadComplete` (non-multipart), nothing enters `InProgress`. |
| Limits in play | Daily processed-record ceiling, per-upload payload size, and query page sizing via `maxRecords` where supported — cross-check the current *Limits and Allocations* topic for the API version in use. |

---

## Core Concepts

### Ingest job state machine (integration view)

From an integrator’s perspective, ingest jobs move through **Open → UploadComplete → InProgress → JobComplete | Failed | Aborted**. **Open** means additional `PUT` uploads may still be sent to `contentUrl`. **UploadComplete** means the client has declared that uploads are finished; Salesforce will not accept more data for that job. **InProgress** covers automatic batching and record operation execution. **JobComplete** means processing finished, not that every row succeeded—inspect counts and download result CSVs. **Failed** indicates the job could not be completed after repeated internal attempts (distinct from per-row validation errors surfaced while the job still completes). **Aborted** is operator-driven cancellation when permitted.

### Partial success and retry scope

When some rows fail validation or hit row-level errors, Salesforce can still reach **JobComplete** with a non-zero `numberRecordsFailed`. Successful rows remain committed. Integration-layer retries must therefore **rebuild a new job** (or a new CSV) containing only unresolved rows plus any net-new inserts, typically guided by `failedResults` and `unprocessedRecords`. Re-sending an entire million-row file after ten failures is wasteful and risks duplicate operations unless the operation is upsert keyed by a natural or external identifier.

### Query extracts and locators

Large SOQL extracts must be consumed through repeated `GET .../jobs/query/{id}/results` calls. Each response includes the next locator in `Sforce-Locator`. Continue until that header equals `null` (the string shown in official examples). Never synthesize locator tokens; only reuse the value returned by Salesforce.

---

## Common Patterns

### Pattern 1: Hardened single-job ingest (create → upload → UploadComplete → poll → results)

**When to use:** Middleware owns a CSV file larger than multipart convenience thresholds or generated in stages.

**How it works:** `POST /jobs/ingest/` with `object`, `operation`, `contentType: CSV`, and delimiter/line-ending metadata matching the file. Upload bytes with `PUT` to `contentUrl`. Send `PATCH` with `UploadComplete`. Poll `GET` on the job until terminal state. Download `successfulResults`, `failedResults`, and `unprocessedRecords` as separate resources to decide the next action.

**Why not the alternative:** Polling only HTTP `202` from an upload `PUT` is insufficient—upload responses do not substitute for job state polling, and omitting `PATCH` leaves the job permanently **Open**.

### Pattern 2: Ordered parent/child bulk loads

**When to use:** Child rows reference parents that must exist before the child ingest job runs.

**How it works:** Run and **fully complete** the parent ingest job—including result validation—before creating the child job. Encode the dependency in orchestration metadata (workflow engine, queue, or state table) so a retried parent does not launch duplicate children.

**Why not the alternative:** Submitting both jobs concurrently produces intermittent `FIELD_INTEGRITY_EXCEPTION` and foreign-key failures that disappear under light load but fail in production peaks.

### Pattern 3: Query job pagination worker

**When to use:** Downstream warehouse needs the entire query result set without loading everything into one HTTP response.

**How it works:** Create the query job, poll until results are ready, then loop `GET` results passing each returned `Sforce-Locator` until the header reads `null`. Persist the last successful locator with checkpoints to support restart.

**Why not the alternative:** Increasing `maxRecords` does not remove the need for locators on large extracts; skipping pages silently loses data.

---

## Decision Guidance

| Situation | Recommended Approach | Reason |
|---|---|---|
| Small payload, single HTTP round-trip acceptable | Multipart `POST` create with embedded CSV | Platform auto-finishes upload phase; no manual `UploadComplete` |
| Large CSV generated on disk or stream | Standard create + chunked `PUT` + `PATCH UploadComplete` | Meets size limits and explicit close semantics |
| Some rows failed with `JobComplete` | New job with failed-row CSV + upsert external id | Avoids duplicating successes; aligns with non-rollback semantics |
| Extract > one HTTP response | Locator-driven pagination | Only supported navigation through paged CSV results |
| Nightly volume near org daily cap | Spread jobs, monitor `numberRecordsProcessed`, alert before hard stop | Prevents silent truncation when daily allocation is exhausted |

---

## Recommended Workflow

1. Confirm API version, OAuth token lifetime, and object/operation support (including `hardDelete` permission implications) before generating CSV.
2. Choose multipart versus staged `PUT`; if staged, verify declared `lineEnding` and `columnDelimiter` match the physical file.
3. Create the job, upload all parts within per-request size limits, then send `PATCH {"state":"UploadComplete"}` unless multipart already completed the upload phase.
4. Poll job `GET` on a backoff schedule while state is `UploadComplete` or `InProgress`; treat `Failed` and `Aborted` as terminal error paths with logging.
5. On `JobComplete`, download the three result resources, persist raw CSV artifacts for audit, and compute counts versus the source system.
6. For query jobs, walk `Sforce-Locator` until `null`, writing each page to object storage before advancing.
7. Open a follow-up job only for remaining failed/unprocessed rows, preserving idempotency via upsert keys or deterministic deletes.

---

## Review Checklist

- [ ] Non-multipart ingest includes explicit `UploadComplete` after final `PUT`.
- [ ] Polling distinguishes `InProgress`, `JobComplete`, `Failed`, and `Aborted` with separate handling.
- [ ] Partial failures produce a scoped retry file—not a blind full-file replay unless idempotent upsert covers it.
- [ ] Query extracts implement locator pagination without invented tokens.
- [ ] Parent/child loads are sequenced with a hard gate on parent `JobComplete` plus reconciliation.
- [ ] Monitoring includes Salesforce job ID, state transitions, and row counters in external logs.

---

## Salesforce-Specific Gotchas

1. **Silent “never starts” jobs** — Forgetting `UploadComplete` after uploads leaves jobs in **Open** with no processing. Impact: SLAs miss indefinitely until someone aborts the job.
2. **Misread JobComplete** — Operators treat `JobComplete` as “all rows inserted.” Impact: downstream systems advance checkpoints while data is still missing; always reconcile counts.
3. **Locator misuse** — Fabricating or reusing stale locators corrupts extracts. Impact: duplicate or skipped rows in the warehouse; only trust headers from the immediately prior response.

---

## Output Artifacts

| Artifact | Description |
|---|---|
| Job orchestration runbook | Step table listing HTTP methods, states, and polling intervals tied to observability IDs |
| Retry CSV specification | Column list and external-id strategy for the second-pass Bulk job |
| Pagination audit log | Sequence of locators and byte counts per page for query extracts |

---

## Related Skills

- `data/bulk-api-patterns` — Detailed REST examples, CSV rules, multipart structure, and Bulk API v1 comparison tables.
- `integration/real-time-vs-batch-integration` — Architecture-level choice between bulk batch paths and event/callout patterns.
- `data/bulk-api-and-large-data-loads` — Strategic sizing, concurrency discussions, and LDV-oriented load planning.