The nightly job reports success, yet the data warehouse is missing a few hundred accounts, and a handful of contacts failed with UNABLE_TO_LOCK_ROW. Salesforce incremental data sync looks simple on paper: pull what changed since the last run and push it across. Whether it stays trustworthy depends on three quieter decisions: which timestamp marks a change, what order records load in, and when the job runs. The practices below come from the official Salesforce Integration Patterns guide, with the reasoning behind each.
Data model choices for very large objects are covered in large data volume architecture patterns, and keeping extract queries selective is its own topic in SOQL best practices for large data volumes. A one-time migration has different priorities again; the data migration strategy handles that case.
The sync loop
A dependable incremental sync follows the same cycle every run:
- Read the watermark: the timestamp of the last successful run.
- Extract only records changed since then.
- Match records using primary keys from both systems (a Salesforce ID and an External ID).
- Load the changes.
- Write the new watermark only after the load finishes successfully.
Keep the watermark in a small tracking table inside the ETL tool. If Salesforce is down or a job fails partway through, the next run still knows where to start.
SystemModstamp vs LastModifiedDate
Both fields look like “last changed” timestamps, but they don’t change for the same reasons.
| LastModifiedDate | SystemModstamp | |
|---|---|---|
| Changes when a user or API call edits a field | Yes | Yes |
| Changes on some system-driven updates | No | Yes |
| Writable | Yes, on insert when Set Audit Fields upon Record Creation is enabled | No, strictly read-only |
Used by getUpdated() replication | Only as a fallback | Yes, as the primary field |
| Standard index | No | Yes |
System-driven updates that can move SystemModstamp without touching LastModifiedDate include:
- Some workflow and flow field updates
- Formula fields recalculated because a referenced field on a related record changed
- Roll-up summary recalculation
- Sharing recalculation
- Certain background platform processes
Why the writable field is a trap
When “Set Audit Fields upon Record Creation” is enabled, a migration or data load can insert records with a backdated LastModifiedDate. If your sync filters on LastModifiedDate, those records fall before the watermark and never get extracted. SystemModstamp can’t be backdated, so it doesn’t have this gap.
The counter-argument
On very large objects that use skinny tables, LastModifiedDate is included by default, so some teams argue it is the faster filter there. That is a performance argument only. SystemModstamp is already one of the standard indexed fields, and for correctness it remains the safer default.
SELECT Id, Name, External_Id__c, SystemModstamp
FROM Account
WHERE SystemModstamp > 2026-09-14T02:00:00Z
ORDER BY SystemModstampRecord locking during loads
Saving a child record briefly locks its parent. That matters as soon as a load runs in parallel.
The failure. Contacts for the same Account are scattered across several batches. Two batches running at the same time both try to lock that Account. One gets the lock, and saves in the other batch fail with UNABLE_TO_LOCK_ROW.
The fix. Sort the file by parent ID before loading, so all children of one parent sit in the same batch. The batches still run in parallel, but they stop competing for the same parent rows.
Bulk API 2.0 always runs in parallel
| Bulk API 2.0 | Bulk API 1.0 | |
|---|---|---|
| Batch processing | Always parallel | Parallel or serial |
| Chunking | Automatic | You size the batches |
| Failure scope | Each batch succeeds or fails independently | Each batch succeeds or fails independently |
Because Bulk API 2.0 has no serial option, sorting by parent is the main defence against lock contention. Bulk API 1.0 in serial mode is sometimes used as a fallback when sorting isn’t enough, at the cost of a much slower load.
ETL practices from the official guide
- Extract only what changed. Full-table extracts on every run add load and hide real changes in noise.
- Match on keys from both systems. Store the other system’s primary key in an External ID field and upsert on it.
- Keep post-load automation selective. Triggers and flows that fire on every loaded record multiply the cost of each batch. Add bypass logic or entry criteria for integration users where it’s safe.
- Load outside business hours. A daytime load competes with users for the same records, which brings back the locking problems described above.
- Keep the watermark outside Salesforce so a failed job can restart cleanly.
Quick reference
| Problem | Fix |
|---|---|
| Records missing from the incremental extract | Filter on SystemModstamp; check for backdated audit fields |
UNABLE_TO_LOCK_ROW on child loads | Sort by parent ID before loading |
| Load slows down or fails during the day | Move the job to off-peak hours |
| Job fails halfway and restarts from scratch | Store the watermark in the ETL tool; write it only after success |
| Duplicates after a rerun | Upsert on an External ID |
If this comes up in an interview
If asked why a nightly load fails on some child records but not others, describe parent locking across parallel batches and the sort-by-parent fix. If asked which timestamp to sync on, choose SystemModstamp and give both reasons: it captures system-driven changes, and it can’t be backdated.