Skip to Content

Process incremental records using a high-watermark in Jitterbit Studio

Introduction

A high-watermark is a stored value that marks the most recent point a sync operation has processed. On each run, the operation reads the stored watermark, uses it to filter out already-processed records, and updates the watermark after the batch completes. Only new or modified records are retrieved on subsequent runs.

This guide demonstrates the pattern using Salesforce cases as the source. The watermark is the maximum LastModifiedDate from the previous run. The pattern applies to any source that exposes a reliable modification timestamp and supports filtered queries.

Two cache functions manage the stored value:

  • ReadCache: Retrieves the stored watermark at the start of each run.
  • WriteCache: Updates the watermark after processing is complete.

For an introduction to ReadCache and WriteCache, including cache scope options and rate limits, see Detect and deduplicate records using hash functions.

Design pattern

The watermark read and update steps bracket the main query-and-process logic. The stored date advances after each successful run, so the query window shifts forward automatically.

flowchart LR A["Script
Read watermark
from cache"] --> B["Script
Build filtered
SOQL query"] B --> C["Salesforce
Query activity
or SfLookupAll"] C --> D["Process records
(transformation or
child operations)"] D --> E["Script
Update watermark
in cache"]
Step Purpose
Read watermark Read the stored date; initialize to a default value if nothing is stored.
Build filtered query Embed the watermark in the query filter.
Query source Retrieve records modified at or after the watermark.
Process records Run the transformation or chain of child operations.
Update watermark Set the watermark to the maximum modification date among the retrieved records and write it to cache.

Part 1: Read the watermark

Add a script step as the first step of the operation. The script reads the stored watermark and falls back to a default date on the first run:

// Set the cache key and expiration
cacheKey = $project_name + "_LastModifiedDate";
cacheExpiry = 2592000; // 30 days in seconds

// Read the stored watermark
watermarkDate = ReadCache(cacheKey, cacheExpiry, "project");

// Fall back to the default date if no value is stored
if(length(trim(watermarkDate)) == 0,
    watermarkDate = $default_watermark_date;
);

Cache key: Use a key that is unique to this dataset within the project. Prefixing with the project name or a dataset identifier (for example, $project_name + "_SF_Case_LM") prevents key collisions when multiple watermarks are stored in the same project.

Default date: default_watermark_date is a project variable set in Project variables to a past date far enough back to include all records you want on the first run. Use the ISO 8601 format that Salesforce expects in SOQL: for example, 2000-01-01T00:00:00.000Z.

Expiration: 30 days (2592000 seconds) keeps the watermark available across scheduled runs. Increase this value for operations that run less frequently.

Scope: "project" scope makes the cached value accessible to all operations in the project and persists it between runs. Use "env" if the watermark must be shared across multiple projects in the same environment.

Part 2: Query using the watermark

After the script reads the watermark, query the source using watermarkDate as the filter boundary.

Using SfLookupAll in a script

SfLookupAll returns a two-dimensional array of matching records. Use it when the result set is passed to a subsequent script or transformation for processing:

soql = "SELECT Id, LastModifiedDate FROM Case"
    + " WHERE LastModifiedDate >= " + watermarkDate
    + " AND AccountId != null"
    + " ORDER BY LastModifiedDate ASC";

$caseIds = SfLookupAll("<TAG>endpoint:salesforce/Salesforce</TAG>", soql);

For more on constructing and running SOQL queries, see Query Salesforce records using SOQL.

Using a Salesforce Query activity

If you use a Salesforce Query activity instead of SfLookupAll, pass watermarkDate as a Jitterbit variable and reference it in the activity's condition field. Set the condition operator to greater than or equal to and enter [watermarkDate] as the value.

Use a greater-than-or-equal filter and idempotent processing

A strictly-greater filter (LastModifiedDate > the watermark) can miss records. When several records share the exact boundary timestamp and only some were included in the previous batch, the strict filter excludes the rest on the next run, and they are never processed. Use a greater-than-or-equal filter (>=) so boundary records are re-included. This re-retrieves the records that defined the previous watermark, so the downstream processing must be idempotent (for example, upsert by a unique key, or deduplicate) to avoid creating duplicates. For a deduplication approach, see Detect and deduplicate records using hash functions.

Part 3: Update the watermark

After all records in the batch have been processed, set the watermark to the maximum LastModifiedDate among the records actually retrieved in Part 2, then write it to cache. Add this as the last script step in the operation, or in the final operation of the chain after all child operations complete:

// Derive the new watermark from the records retrieved in Part 2.
recordCount = Length($caseIds);
if(recordCount > 0,
    // Part 2 ordered results by LastModifiedDate ASC, so the last row holds the max.
    newWatermark = $caseIds[recordCount - 1]["LastModifiedDate"];
    WriteCache(cacheKey, newWatermark, cacheExpiry, "project");
);

Derive the watermark from processed records, not the source maximum

Do not set the watermark by re-querying the source for its current maximum (for example, SELECT max(LastModifiedDate) FROM Case). Records can be modified in the source between the Part 2 query and this update step. Those records are not part of the current batch, but a source-maximum query would include their timestamps, advancing the watermark past records that were never retrieved. On the next run, the strict filter skips them and they are missed permanently. Always derive the watermark from the records this run actually retrieved and processed.

If you use the Salesforce Query activity path from Part 2 instead of SfLookupAll, capture the maximum LastModifiedDate as the records are processed (for example, accumulate the maximum in a global variable in the processing transformation), then write that value to cache here.

The if guard prevents WriteCache from overwriting the stored watermark when no records were retrieved this run.

cacheKey and cacheExpiry must match the values used in Part 1. If the update script runs in a different operation than the read script, either assign these values again or store them as project variables so both scripts reference the same key.

Verify the integration

  1. Set default_watermark_date to a date several months in the past. Deploy and run the operation. Add WriteToOperationLog calls to log the value of watermarkDate and the number of records returned. Confirm that watermarkDate equals the default and that the query returned records.

  2. Run the operation a second time without modifying any source records. Because the filter is greater-than-or-equal, the record (or records) whose LastModifiedDate equals the stored watermark are retrieved again. Confirm that only those boundary records are returned (not the full set), which means the watermark was written correctly after the first run, and that idempotent processing does not create duplicate output.

  3. Modify a source record and run the operation again. Confirm that the modified record is returned and processed. Records still at the previous watermark boundary may also be re-retrieved; idempotent processing ensures they produce no duplicates.

  4. If the watermark is not persisting between runs, confirm that:

    • The cache key is identical in both the read and update scripts.
    • Both scripts use the same scope ("project").
    • The value written by WriteCache is in the ISO 8601 format Salesforce expects in SOQL.

  5. If all records are retrieved on every run, the WriteCache call may not be executing. Confirm that the update script runs after all child operations complete. If you use RunOperation to chain operations, place the update script in the parent operation after the RunOperation call returns, not inside a transformation loop.