Delta extraction sounds simple: only pull what changed. The interesting part is that last word. Your ERP won’t tell you what changed unless you ask it the right question — and the field you ask about isn’t the one you’d expect.

It’s not the business key. An order number or an employee ID tells you which record you’re looking at, never whether it moved. What you need is a separate field on the same record that the source system stamps on every change — a counter or a pointer. Most often it’s a last-changed timestamp.

This post is about choosing that pointer and wiring it into a schedule. This picks up where the ETL extraction blog leaves off — that one covers models, transform maps, indexing, and coalescing.

 

 

Example Use-case

The examples below use a SAP S/4HANA maintenance order, filtered to planning_plant=1710(data filter), with last_change_date_time as the pointer. This is one example, not the only way to do it. The underlying idea — filter on a pointer field, either with a fixed window or a watermark lookup — is expected to carry over to other ERPs and other pointer field names, since it doesn’t depend on anything SAP-specific. That said, this hasn’t been verified against every source system or every change-tracking mechanism. Some ERPs expose change tracking differently, for example through OData delta tokens, and how that interacts with these two patterns hasn’t been analyzed here. Treat the patterns below as a starting point to test against your own source, not a guarantee that covers every case.

 

Finding the Change Pointer

A last-changed timestamp is the most common pointer, because most ERPs expose one. It’s not the only option. Depending on what the source system gives you, a pointer could also be:

• A modification timestamp under a different name. LastModifiedDateTime, ModifiedOn, updated_at, last_update_date — same idea, different label per system.
• A counter or version number that goes up every time the record changes. This avoids timezone issues, but only works if the field is reliably exposed by the API.
• A separate change log. Some ERPs log changes in their own table instead of stamping the business record. The pointer then lives in that log, not on the object you’re extracting.

Whichever pointer type you have, the two patterns below work on the same principles. Only the field name and comparison operator change.

 

How to build it

Delta logic is set on the Scheduled Extraction record. Open one and you'll see two fields that matter here:  Encoded query  and  Generate encoded query script.

 

• Encoded Query : Static. Whatever you type stays fixed for every run.
• Generated encoded query script : Runs at extraction time. It can read the Encoded query field and add to it.

That second point is the important one. The script doesn't replace the static filter — it builds on top of it. So you can keep your data filter in Encoded query and let the script add the delta clause each run.

This gives you room to combine them in more than one way. The two examples below are the most common, not the only options.

 

Example Patterns

 

Pattern 1 — static encoded query

Encoded Query : 

planning_plant=1710last_change_date_time>=javascript:gs.daysAgoStart( 1 )

"planning_plant=1710" is the data filter — which records you want at all. "last_change_date_time>=javascript:gs.daysAgoStart( 1 )" is the delta logic. The caret () joins them.

Why use it:

• No complex scripting. 
• Easy to explain. “Everything changed in the last day” maps directly to a daily schedule.
• Predictable window. Easy to explain to auditors or support — this extraction always pulls a 24-hour lookback.

Where it falls short:

• The window is fixed to the schedule, not to what actually happened. If a job fails, gets delayed, or the ERP has downtime, you can miss records changed in that gap once the next run’s window moves past them.
• The schedule and the daysAgoStart() value have to stay in sync. Change one without the other and you get gaps or unnecessary re-processing.

Use this as your default when the schedule is stable — daily or hourly, rarely skipped — and a little overlap period is fine. It usually is, as long as your reconciliation key is set up correctly, so overlapping records update in place instead of creating duplicates.

 

Pattern 2 — scripted encoded query 

Encoded Query : 

planning_plant=1710

Scripted Query:

var result = grJob.getValue('encoded_query') ? grJob.getValue('encoded_query') : ''; // look up the latest changed on date in target table var grLastChanged = new GlideRecord(<target_table_name>); grLastChanged.orderByDesc('last_change_date_time'); grLastChanged.setLimit(1); grLastChanged.query(); if (grLastChanged.next() && grLastChanged.last_change_date_time) { if (result != '') { result = result + '' ; // use ^ to separate fields if encoded query is non-empty } //Convert date to ISO if model uses Odata protocol var gdt = grLastChanged.last_change_date_time.getGlideObject(); var epochMs = Number(gdt.getNumericValue()); var latest_extracted_timestamp = new Date(epochMs).toISOString(); result = result + 'last_change_date_time>=' + latest_extracted_timestamp; }

 

Instead of a fixed window, this asks the target table: what’s the newest last_change_date_time I already have? It uses that as the starting point for the next pull. planning_plant=1710 stays static. Only the delta clause is derived.

Why use it:

• Self-healing. If a run fails or gets paused for two days, the next run still starts from the real last point. No gap, no manual backfill.
• Works with any schedule. Hourly, daily, or ad hoc — it doesn’t assume regularity.
• Tracks what you’ve actually extracted, not what changed in a window you picked ahead of time.

Trade-offs:

• More to maintain. It’s a script, not a one-line filter, and target_table_name has to be correct if the job is ever cloned for other use-cases.

One thing to note, not a downside: on the first run there’s no prior record yet, so the lookup returns nothing and the delta clause is skipped. That run becomes a full load automatically. This is expected — it’s not something you need to handle separately.

 

Quick decision guide

 

|

Static + gs.daysAgoStart()

|

Scripted watermark

|
|

Schedule is fixed and rarely skipped

|

Simplest fit

|

Works, but overkill

|
|

Schedule can be paused, delayed, or irregular

|

Risk of gaps

|

Self-correcting

|
|

Data is SLA-sensitive (can’t tolerate missed changes)

|

Not ideal

|

Yes

|
|

Auditability of “what window did we pull”

|

Explicit

|

Implicit — derived each run

|

 

A common middle ground: static filter for low-stakes master data, scripted watermark for transaction data where a missed record actually costs something.

 

One shared risk, either way

Both patterns need the right reconciliation key on the extraction table — the field used to match an incoming record to an existing one(e.g. Maintenance Order ID in the discussed example). Get it wrong, and delta load creates duplicates instead of updates. Check this first, before choosing either pattern above.

 

Summary

• Delta extraction runs on a pointer field. Usually a last-changed timestamp, sometimes a counter or change log. It’s separate from the business key, and separate from any one ERP vendor.
• The SAP example above is just that — an example. The underlying pattern is expected to carry over to other source systems and other pointer fields, but that hasn’t been verified case by case, especially for systems with their own change-tracking mechanisms.
• A static filter with gs.daysAgoStart() is the simplest option. Good for stable, forgiving schedules.
• A scripted watermark is more resilient to failed or delayed runs. Better for SLA-sensitive data. Costs a small script to maintain.
• Either way, delta load only works if the reconciliation key is right.