Keeping a Copy in Sync

Module 8 of 8 in the chapter Moving Data at Scale, chapter 10 of the Integration Architect course. Each module stands on its own. Previous: Choosing the Size of the Truck · Back to the chapter: Moving Data at Scale

The question this module answers: A data warehouse, a reporting database or a partner’s system needs its own copy of Salesforce data, kept current. How does a program do that, and where does the date go?

The Copy That Never Ends

Most of this chapter has been about one trip of the truck: a load, an export, a job you open and close. Keeping a copy in sync is the truck on a schedule, forever. Every night, or every hour, the program asks Salesforce one question: what changed since last time?

The Data Architect course draws the line between the two: a migration has an end, and synchronization doesn’t (Getting Data Out). This module is the program’s side of synchronization: the calls it makes, where the date goes, and the mistakes that leave a copy quietly wrong.

Where the Date Goes

When I’ve looked at replication tools that keep a SQL Server database in step with Salesforce, I remember the last-modified timestamp and some date-driven parameters, maybe even a header. So where does the date actually go? It depends on the door:

The door	Where the date goes	What you get back	How far back
Bulk API 2.0 query (module 5)	In the SOQL: `WHERE SystemModstamp > <the mark>`	The changed rows, at any volume, as CSV	As far as the data goes
Bulk API 2.0 `queryAll`	The same SOQL	Also records “deleted because of a merge or delete,” still in the Recycle Bin	Until the Recycle Bin is emptied
REST sObject Get Updated	URL parameters, `start` and `end`, in UTC	The IDs of records added or changed in the window	30 days
REST sObject Get Deleted	URL parameters, `start` and `end`, in UTC	The IDs deleted in the window, from the delete log	15 days, or less
REST `If-Modified-Since`	A request header	One record (or a describe) only if it changed since then; otherwise `304 Not Modified`	One request at a time

Sources: REST API Developer Guide, API 68.0: sObject Get Updated (opens in a new tab), sObject Get Deleted (opens in a new tab), Conditional Request Headers (opens in a new tab); Bulk API 2.0 and Bulk API Developer Guide, Create a Query Job (opens in a new tab); all checked 2026-11-01.

So the date-driven header I remembered is real: If-Modified-Since. But it’s for one record at a time, or for checking whether an object’s metadata changed. It’s not how a bulk sync works. For a bulk sync, the date goes in the query itself, on SystemModstamp. Get Updated and Get Deleted are the replication calls Salesforce built for exactly this job, and their pages say so: they’re “commonly used in data replication applications.” They return IDs, not rows, so you fetch the rows afterwards, and they stop at 600,000 IDs per call.

SystemModstamp, Not LastModifiedDate

Two fields look like they answer “when did this change?” They don’t answer it the same way. LastModifiedDate is the “date and time when a user last modified this record.” SystemModstamp is the “date and time when a user or automated process (such as a trigger) last modified this record,” and the “trigger” there means Salesforce’s own standard processing, not your Apex (System Fields (opens in a new tab), Object Reference, checked 2026-11-01). A sync wants every change, so it uses SystemModstamp. It’s what Salesforce’s own Get Updated uses, and it’s indexed, so the query stays fast as the table grows.

Salesforce’s page adds a warning worth knowing: SystemModstamp “doesn’t capture every field change.” Its example is one object that takes values from another: when the values change on the other object, its timestamp moves, but not the first one’s. A formula field on a Work Order that shows a value from its Account is the usual case. The Account changes, the formula’s answer changes, and the Work Order’s SystemModstamp doesn’t move. A copy that relies on the timestamp won’t see it. Copy the source fields and work out the formula in the copy, or pull the Accounts too.

One Run, Four Steps

Here’s one run of a sync, as the program does it:

Click to enlarge

One run: read the mark, ask, upsert, catch the deletes. Then again.

1. Read the mark from the copy, not the clock. The obvious design is “pull everything changed since the job last ran.” The safer design is “pull everything changed since the newest record I actually have.” Take the highest SystemModstamp stored in the copy. If last night’s run failed halfway, the clock says you’re up to date; the copy knows you’re not. Salesforce’s polling guidance says the same in its own way: save the window of each successful run, so the program “knows the last time period for which data replication was successfully completed” (Polling for Changes (opens in a new tab), SOAP API Developer Guide, checked 2026-11-01).

2. Ask what changed, starting a little before the mark. A record can be saved in a transaction that started before your mark and finished after your query. Salesforce warns about exactly this. The replication call returns a latestDateCovered, and “for safety, you should use this value for the startDate of your next call to capture the changes that started after this date but did not complete before the endDate,” which matters most when “Salesforce executes a long-running transaction” (GetUpdatedResult (opens in a new tab), SOAP API Developer Guide, checked 2026-11-01). The polling guidance goes further: capture every change “even if it requires processing data redundantly to ensure that there are no gaps.” And it notes the API drops the seconds from the times you give it. So overlap the window on purpose. Choose the door by the zeros: a bulk query for a big night, Get Updated or a REST query for a few records.

3. Upsert into the copy by the Salesforce ID. The overlap means some rows arrive twice. That’s fine if the copy upserts on the record ID: the second one just updates the first. It’s the same idea as module 6, from the other side.

4. Catch the deletes. A normal query never returns a deleted record, so a copy that only asks “what changed?” keeps every deleted record forever, and its reports count them. There are three ways to catch them, each with a catch of its own:

queryAll. A bulk query that includes deleted records, while they’re still in the Recycle Bin. Records leave the Recycle Bin after 15 days at most, sooner if someone empties it, and a hard delete never goes there.
Get Deleted. The IDs from the delete log, for up to 15 days. But read the fine print: when the delete log gets big, a background process “that runs every two hours” trims it, above 5,000 entries for each license in the org. If the entries you need are gone, the call fails with INVALID_REPLICATION_DATE, and Salesforce’s advice is blunt: “do a full pull of the table” (Polling for Changes).
Compare the ID lists. Ask Salesforce for every ID in the object, compare it with every ID in the copy, and remove what Salesforce no longer has. It’s the most work, but it doesn’t depend on a window. At volume, the list of IDs is itself a bulk query.

So decide how often each copy checks for deletes, and make it well inside the window you rely on.

What to Ask of a Replication Tool

Most of the time you won’t write this program yourself. A synchronization tool that keeps a SQL Server database in step with Salesforce is a common choice, and there are tools like it for other databases and warehouses. Whichever one you look at, ask it the questions this module has been answering:

Where does each run start? From the newest record the copy holds, or from when the tool last ran? The first survives a failed night; the second can quietly leave a gap.
Does it overlap, and does it upsert? A little repeat is safe when the copy upserts by the record ID. A gap isn’t.
How does it find deletes? queryAll, Get Deleted, or comparing the lists of IDs, and how often. If it’s Get Deleted, what does it do when the delete log has been trimmed?
Can it check the count? The rows in the copy against the rows in Salesforce: module 6’s check, run by the tool.
Which door does it use for which run? For a few hundred changed rows, an ordinary query is faster. For the first copy or a big night, the dock. A tool that uses the Bulk API for everything, or never, is worth asking about. That’s module 7 again: count the trip.

Should It Be a Copy at All?

Before you build a sync, ask whether the other system needs a copy, and how fresh.

Polling on a schedule is this module. Salesforce’s guidance is to poll “no more frequently than every five minutes”; some programs poll once a day.
Within minutes or seconds, don’t poll. Salesforce’s own data replication page says: “To get real-time notifications of Salesforce record changes, use Change Data Capture instead.” That’s chapter 11, Events.
No copy at all: read the data where it lives, through virtualization. Chapter 19, Data Connectivity and Virtualization (coming soon).
Data 360’s zero copy: share data with a warehouse without moving it. Chapter 20, Data 360 and the Connected Enterprise (coming soon).

Steve Trucking uses two of these, on purpose. The weekly export to Snowflake is a copy kept in step by a bulk query from the mark: about 23,000 deliveries a week today, about 50,600 at year three. The status history the reports need sooner comes through Change Data Capture, the design the Data Architect course drew: one big copy, then a stream of small ones (Getting Data Out).

Test knowledge. Know the replication tools: getUpdated() and getDeleted() (REST sObject Get Updated, 30 days, and Get Deleted, 15 days) return the IDs changed or deleted in a time window, and Salesforce recommends polling no more often than every five minutes; for near real time, use Change Data Capture. Know that SystemModstamp changes when a user or an automated process changes a record, and is what replication uses. Know the ways to catch deletes: queryAll (Recycle Bin, up to 15 days), Get Deleted (the delete log, which can be trimmed sooner), or comparing ID lists; after INVALID_REPLICATION_DATE, do a full pull.

What an Architect Decides Here

Whether it’s a copy at all, and how fresh it must be: a nightly poll, a stream of events, or no copy.
The mark: taken from the copy, stored with each successful run, and how far before it each run starts.
The door for each run, by the zeros: a bulk query for the first copy and big nights, a REST query or Get Updated for small ones.
The deletes: which way, how often, and well inside which window. Hard deletes too.
What the timestamp misses: formulas and values taken from other records, and how the copy gets them.
The full pull: when the program falls back to rebuilding the copy from scratch, and how long that takes at year three.

Explore

Polling for Changes (opens in a new tab) and GetUpdatedResult (opens in a new tab): SOAP API Developer Guide, Salesforce’s replication guidance (checked 2026-11-01)
sObject Get Updated (opens in a new tab) and sObject Get Deleted (opens in a new tab): REST API Developer Guide, API 68.0 (checked 2026-11-01)
System Fields (opens in a new tab): Object Reference, SystemModstamp and LastModifiedDate (checked 2026-11-01)
Getting Data Out: Keeping Another System in Step: the Data Architect course, the watermark and migration against synchronization
Salesforce Developers, REST API Developer Guide, Conditional Request Headers (opens in a new tab), checked 2026-11-07.
Salesforce Developers, Bulk API 2.0 and Bulk API Developer Guide, Create a Query Job (opens in a new tab), checked 2026-11-07.