SfSkills Domains Docs GitHub

Home / data / data-loader-batch-window-sizing

data-loader-batch-window-sizing

Choose the right batch size, parallel/serial mode, and load window for Data Loader, Bulk API V1/V2, and custom Database.executeBatch jobs against a given object volume and complexity profile. Covers tradeoffs between batch size, trigger CPU cost, sharing recalculation cost, and row-skew lock contention. NOT for CSV column mapping (use data/data-loader-csv-column-mapping). NOT for picklist validation pre-load (use data/data-loader-picklist-validation-pre-load). NOT for sharing-recalc tuning after the load lands (use data/sharing-recalculation-performance).

Open the skill folder on GitHub

Data Loader Batch Window Sizing

Activate this skill when planning a high-volume insert, update, or upsert through Data Loader, Bulk API V1, Bulk API V2, REST Composite, or a custom Database.executeBatch job — and someone needs to pick a defensible batch size and mode before kicking off the load. The wrong batch size on a complex object turns a 30-minute job into a 14-hour job, fills the org's API call budget, and produces a multi-hour sharing-rule recalculation that nobody scheduled.

This skill is NOT about CSV column mapping (see data/data-loader-csv-column-mapping), NOT about pre-load picklist validation (see data/data-loader-picklist-validation-pre-load), and NOT about tuning sharing recalculation after the load already landed (see data/sharing-recalculation-performance). It is the upstream sizing decision: how big each batch should be, whether to run parallel or serial, and how to split the work into windows.

Before Starting

Gather this context before recommending a batch size:

ContextWhat to confirm
Object profileTriggers, process builder/flow record-triggered automation, validation rules, duplicate rules, sharing rules, role hierarchy, criteria-based sharing, territory management, field history tracking — each changes per-record cost.
Volume tierUnder 10K, 10K–1M, 1M–10M, or 10M+? The right answer flips between tiers.
OWD settingPublic R/W skips implicit-share recalc; Private + role hierarchy does not. Loading 100K rows of one is a different physics problem from the other.
Parent–child shapeAre parents already present or part of the same load? If part of the load, External Id deferred linkage is usually safer than strict parent-first ordering.
API call budgetEnterprise base ~100K calls/day; Bulk V1 consumes per batch, Bulk V2 per job state poll. Confirm headroom before sizing.
Time budgetMaintenance window vs business hours. Business hours forces smaller batches and serial mode for sensitive objects.

The single most common wrong assumption: "200 is the safe default for everything." Data Loader's UI default of 200 is not a recommendation — it is a starting point. For complex Account loads it is often too high; for simple Lead inserts it is wastefully low.

Core Concepts

1. Batch size is a tradeoff between API calls and per-batch CPU

Smaller batches mean more API calls (each batch consumes 1 from the daily budget) but lower per-batch CPU consumption. Larger batches reduce API calls but multiply trigger work — 200 records × 10 record-triggered automations × per-record CPU can hit the 60s synchronous CPU limit (or 10-minute batch CPU limit for Database.Batchable) on a single batch.

Concrete numbers to memorise:

2. Parallel mode amplifies row-lock contention

Bulk API V2 runs in parallel mode by default. When two parallel batches try to update the same parent record (e.g., two child Contacts whose parent Account is the same), the second batch fails with UNABLE_TO_LOCK_ROW. The classic shape is a row-skewed parent — one Account with 10,000 Contacts, one Owner with 10,000 Accounts, or one Lookup target with disproportionate children.

Switch to serial mode for: Account, Opportunity (especially with territories), any object with a row-skewed parent, any update path that triggers parent rollup recalculation. Serial mode trades wall-clock speed for predictability — it is the right call any time predictability matters more than minimum runtime.

3. Sharing recalculation is the hidden cost on Private OWD objects

Inserting 100K Accounts into a Private OWD org with a role hierarchy triggers implicit-share row creation for every level of the hierarchy that has access. The visible insert finishes quickly; the sharing recalculation runs asynchronously for hours afterwards and can lock out reports, list views, and other automation while it runs. This is the #1 reason "the load finished but the org is unusable for the rest of the day."

Mitigations: load with the OWD owner unchanged (avoid post-insert ownership re-assignment), use Defer Sharing Calculations (Setup → Sharing Settings → Defer Sharing Calculations) for the load window if available, schedule the load before a low-traffic window, and never re-parent ownership immediately after the load.

4. Trigger fire impact compounds linearly with batch size

For an Account with 10 record-triggered automations (triggers + record-triggered Flows), a 200-record batch fires 2,000 automation invocations. Each invocation pays its own SOQL/DML overhead and contributes to the per-transaction governor pool. Doubling the batch to 400 doubles the per-transaction CPU consumption; the chance of hitting the 60-second CPU limit doubles too.

For one-time historical loads, the right move is often a trigger bypass via Custom Metadata (e.g., a TriggerControl__mdt flag the handlers read) plus a separate post-load enrich job that runs the calculations the bypassed triggers would have done. This decouples ingestion from rule evaluation.

5. Parent-child loading order vs External Id deferred linkage

Two valid strategies for parent-child mass loads:

External Id is the right default for any migration from another system that already has stable record identifiers. Strict ordering is for greenfield loads where no External Id exists.

Common Patterns

Pattern A: Public Read/Write simple object, large volume → Bulk V2 parallel, large batch

When to use: Lead inserts, Task/Event mass-create, simple custom-object loads with no record-triggered automation and Public R/W OWD.

How it works: Bulk API V2, parallel mode, batch size 5,000–10,000. The platform's internal 200-row chunking handles trigger evaluation; you save API calls and complete fast.

Why not the alternative: dropping to batch=200 here just multiplies API calls without solving any real problem — there is no sharing recalc, no row-skew, no trigger CPU pressure to manage.

Pattern B: Private OWD object with triggers and role hierarchy → Bulk V2 serial, smaller batch, deferred sharing

When to use: Account loads in a Private R/W org, Opportunity loads with territory management, Contact loads in healthcare/financial-services orgs with strict role hierarchies.

How it works: Bulk API V2, serial mode, batch size 200–500, Defer Sharing Calculations enabled for the duration, load runs in a maintenance window. Triggers stay on (or are bypassed via a CMDT flag with a separate post-load enrich job).

Why not the alternative: parallel mode here produces UNABLE_TO_LOCK_ROW storms; large batches blow the trigger CPU limit; not deferring sharing turns a 1-hour load into a 6-hour sharing-recalc tail.

Pattern C: Parent-child mass migration → External Id upsert, two-pass

When to use: any migration from a legacy CRM where record identity already exists in the source system; any time the load may need to be re-run idempotently.

How it works: add External_Id__c (External Id, Unique, Case-Insensitive) to both parent and child SObjects. Pass 1: upsert parents by External Id. Pass 2: upsert children by External Id with the parent lookup populated by parent External Id (Salesforce resolves the lookup at upsert time). No manual Id mapping required.

Why not the alternative: strict parent-first ordering with Id capture works but breaks on retry — re-running the load creates duplicate parents unless External Id deduplication is bolted on after the fact. Build the right shape from the start.

Pattern D: One-time historical load → trigger bypass + post-load enrich

When to use: a one-time migration of 5+ years of historical Cases, Orders, or Opportunities where the live triggers are designed for incremental day-to-day inserts, not bulk historical replay.

How it works: a TriggerControl__mdt Custom Metadata Type with a Bypass_Object__c flag the trigger handlers read at the top of before insert. Set the flag for the load user, run the load, run a separate post-load enrich Batch Apex job that performs the calculations the bypassed triggers would have done (rollups, sharing-rule sub-cases, validation backfill), then unset the flag.

Why not the alternative: running historical data through real-time triggers is structurally wrong — the triggers were designed for one record at a time arriving from a UI, not 5 years of records arriving in 30 minutes. They will hit governor limits, fire emails to long-departed users, and create duplicate platform-event traffic.

Decision Guidance

SituationObject size & complexityBatch sizeModeNotes
Simple custom object, no triggers, Public R/W<10K records2,000–10,000Bulk V2 parallelMaximise throughput; no recalc concerns.
Standard object with triggers and validation rules, Public R/W10K–1M records500–1,000Bulk V2 parallelWatch trigger CPU; drop to 200 if CPU_TIME_LIMIT_EXCEEDED appears.
Private OWD object with sharing rules and role hierarchy>1M records200Bulk V2 serialDefer Sharing Calculations ON; load in maintenance window; expect long async sharing tail.
Parent-child mass load (Account + Contact, Order + OrderItem)any tier500 parent / 500 childBulk V2 serialUse External Id deferred linkage; two-pass upsert.
Object with row-skewed parent (one Account with 10K children)any tier200Bulk V2 serialParallel guarantees UNABLE_TO_LOCK_ROW. Serial is mandatory.
Opportunity with Territory Management 2.0any tier200Bulk V2 serialTerritory assignment runs per-record; serial avoids territory-rule lock contention.
One-time historical load with rich automationany tier500–2,000Bulk V2 parallel with trigger bypassBypass via CMDT, separate post-load enrich job; treat as two phases.
Recurring small daily load<50K records200Data Loader UI default OKStay on Bulk V1 path; not worth optimising.

When in doubt, start at batch=200, serial mode, run a 5,000-record pilot, measure, then scale up. Going small-and-serial first costs minutes; going large-and-parallel first and rolling back costs a maintenance window.

  1. Profile the target SObject — list triggers, record-triggered Flows, validation rules, duplicate rules, sharing rules, role hierarchy, territories, field history tracking, feed tracking. Each one changes the recommendation.
  2. Determine the volume tier — under 10K / 10K–1M / 1M–10M / 10M+. Map to the Decision Guidance table.
  3. Check OWD and parent-child shape — Public R/W vs Private; standalone vs parent-child; row-skewed parents (any single parent with >10K children pointing at it).
  4. Pick batch size and mode from the table, then sanity-check against API call budget (record_count / batch_size = batch_count; each batch is ~1–2 API calls).
  5. Plan the freeze and the enrich — if this is a one-time historical load, design the CMDT trigger-bypass flag and the post-load enrich Batch Apex job before kicking off ingestion.
  6. Run a 5,000-record pilot at the chosen settings; measure runtime, error rate, governor-limit warnings in the debug log. Adjust batch size down if CPU_TIME_LIMIT_EXCEEDED or UNABLE_TO_LOCK_ROW appears.
  7. Run the full load in a maintenance window with Defer Sharing Calculations enabled (Private OWD only); monitor the async sharing recalc tail before declaring done. Run scripts/check_data_loader_batch_window_sizing.py with the object profile to confirm the chosen batch size and mode match the recommendation.

Review Checklist

Before kicking off a high-volume load:

Salesforce-Specific Gotchas

Non-obvious platform behaviors that ruin loads:

  1. Defer Sharing Calculations does NOT defer implicit shares for Account–Contact–Opportunity — only group-membership recalcs and sharing-rule recalcs. Account loads still trigger account-team and account-territory share rows synchronously. See references/gotchas.md.
  2. Bulk API V2 internally chunks to 200 even when you upload 10,000 — the platform inserts in 200-row server transactions for trigger evaluation. Setting batch size to 10,000 does NOT bypass the per-200 trigger CPU envelope; it only reduces the API call count.
  3. Trigger CPU is per server transaction, not per batch upload — chasing CPU errors by raising the batch size does the opposite of what you want. Lowering it or bypassing triggers is the correct lever.
  4. UNABLE_TO_LOCK_ROW under parallel mode is not retryable in place — the failed records must be retried in a separate job (or in serial). Bulk API V2's automatic retry does not solve row-skew row locks.
  5. Field history tracking explodes on bulk update — updating 1M Accounts with 5 history-tracked fields creates 5M AccountHistory rows. Disable history tracking for the load window if the historical state is not being preserved by the load itself.
  6. Loading data as the wrong user creates wrong sharing implications — loading as a System Administrator with Modify All Data populates OwnerId to that admin unless explicitly mapped. The cleanup re-parenting later triggers the entire sharing recalc again.

Output Artifacts

ArtifactDescription
Sizing recommendationBatch size, mode, and load order tailored to the SObject + volume + complexity
Pre-load freeze planWhich triggers/rules/sharing-recalcs to bypass or defer for the load window
Post-load enrich planBatch Apex (or Flow) job that performs the work bypassed triggers would have done
Pilot test plan5,000-record dry run with metrics (runtime, error rate, governor warnings) before full load
Sizing checker runscripts/check_data_loader_batch_window_sizing.py clean exit confirming chosen settings match the table

Package files