--- title: "Extracting millions of records" url: "https://trailassembly.com/query-api-large-extracts/" topic: "Data and Integration" published: "2026-09-21T11:45:53+00:00" updated: "2026-09-23T09:12:24+00:00" --- # Extracting millions of records **Straight answer:** use Bulk API 2.0 query jobs for anything over about a hundred thousand records. It chunks server-side, returns results as CSV you stream, and costs a handful of API requests instead of thousands. REST `queryMore` paging works and will take all day. PK chunking is the fallback when a single query job still cannot complete. ## The three mechanisms, and where each stops | Mechanism | Practical ceiling | API cost | | --- | --- | --- | | REST query + queryMore | Low hundreds of thousands, slowly | One request per page | | Bulk API 2.0 query job | Tens of millions | A few requests total | | Bulk API with PK chunking | Very large objects that otherwise time out | A few per chunk | The API cost column is the one that decides it in practice. Paging 2,000 records at a time through five million rows is 2,500 requests against [your daily allocation](https://trailassembly.com/salesforce-api-limits-monitoring/), and it competes with every other integration you run. ## The Bulk API 2.0 query flow Create the job, poll until it is complete, then fetch results in pages that you stream to disk rather than hold in memory. ```apex POST /services/data/v62.0/jobs/query { "operation": "query", "query": "SELECT Id, Name, AccountId FROM Contact WHERE CreatedDate >= 2024-01-01T00:00:00Z" } ``` Poll the job until state is `JobComplete`, then retrieve: ```apex GET /services/data/v62.0/jobs/query/{jobId}/results?maxRecords=100000 ``` The response carries a `Sforce-Locator` header. Pass it back as the `locator` parameter for the next page, and keep going until the header comes back empty or absent. Two details that catch people: results are CSV rather than JSON, and a locator is not a cursor you can hold for long — process pages as you get them. Use `queryAll` instead of `query` as the operation when you need deleted and archived rows included. ## PK chunking, and when you need it On very large objects, a single query job can time out because the query itself is expensive before any rows are returned. PK chunking splits the work into Id-range chunks that are each cheap to evaluate. ```apex Sforce-Enable-PKChunking: chunkSize=250000 ``` It is a header on the job creation request. Each chunk becomes a separate batch you retrieve independently, so partial progress survives a failure and you can parallelise retrieval. Worth knowing before you reach for it: chunking on a parent object can be requested so that chunks follow the parent’s Ids, which keeps children grouped — useful when you are extracting a parent-child pair together. And chunk size is a tradeoff like any other: larger chunks mean fewer round trips and a longer failure to redo. ## Making the query itself extractable The mechanism does not rescue an unselective query. A Bulk query still goes through [the optimiser](https://trailassembly.com/salesforce-query-optimizer/), and a filter that cannot drive an index will be slow or will fail. - **Filter on indexed fields.** `CreatedDate` and `SystemModstamp` are indexed and are the natural extract boundaries. - **Select only what you need.** Every field is bytes across the wire and time on the server. Extracting 80 fields when the warehouse uses 12 is the most common waste in this whole area. - **No `ORDER BY`.** Sorting a multi-million-row result is expensive and almost never needed for an extract you are loading into something that will sort it anyway. - **Avoid relationship traversal** where you can extract the related object separately and join downstream. One deep query is usually slower than two shallow ones. ## Incremental extraction, which is what you actually want Full extracts are a bad habit. Use `SystemModstamp`, not `LastModifiedDate`, as the watermark, because `SystemModstamp` also moves when the platform updates a record and `LastModifiedDate` does not always. ```apex SELECT Id, Name, SystemModstamp FROM Contact WHERE SystemModstamp > 2026-09-01T00:00:00Z AND SystemModstamp <= 2026-09-02T00:00:00Z ``` Bound both ends. An open-ended upper bound means the window keeps moving while the job runs, and rows modified during extraction may be included or not depending on timing — which produces an extract you cannot reason about. Handle deletes separately with `queryAll` and the `IsDeleted` flag, or with the `getDeleted` call. An incremental extract based on modification date cannot see a row that no longer exists, so a warehouse built only on modstamp windows accumulates records that were deleted in Salesforce years ago. ## Operational points that matter at this size - **Stream to disk.** Do not accumulate the result in memory. This is the most common client-side failure. - **Checkpoint the locator.** If retrieval fails at page 40 of 60, you want to resume rather than restart. - **Watch job concurrency.** There are limits on concurrent bulk jobs. Firing twenty extracts in parallel queues most of them. - **Run big extracts off-peak**, for the same reason you would anywhere else. - **Expect CSV quirks.** Embedded newlines in text fields are legal CSV and break naive line-based parsers. Use a real CSV reader. ## Frequently asked ### Does a Bulk query count against the 50,000-row governor limit? No. That limit applies to SOQL inside an Apex transaction. Bulk API query jobs are an external API path and are not governed by per-transaction Apex limits. ### Can I query all fields without listing them? `FIELDS(ALL)` exists in SOQL but is not a good idea for a large extract. It brings back everything including long text, which is most of your transfer time. List what you need. ### How long do results stay available? Query job results are retained for a limited window after completion, so retrieve promptly rather than creating jobs ahead of a nightly retrieval window. ### Is Bulk API 2.0 always better than 1.0? For most extracts, yes. Server-side chunking and a simpler client. Bulk API 1.0 still has cases where explicit batch control is wanted, including some PK chunking scenarios. ### What about formula fields in an extract? They are computed at query time, so extracting many formula fields over millions of rows is meaningfully slower than extracting stored fields. Where the warehouse can compute the same value, extract the inputs instead. --- *Reference: Bulk API 2.0 Developer Guide*, *SOQL and SOSL Reference* and *API Request Limits and Allocations*, Salesforce. --- Source: https://trailassembly.com/query-api-large-extracts/ Trail Assembly — independent Salesforce write-ups.