Deletes & Change Tracking Across ERPs

A watermark load is the default first pipeline everywhere, and it works: pull every row whose change stamp moved since the last run, upsert it, move on. What it catches is inserts and updates. A deleted row never appears in a watermark window, because there is nothing left to stamp.

So the deleted rows stay in bronze. Nothing errors. Counts, balances, and open-order totals just run slightly high, and the gap widens quietly for as long as nobody reconciles a number against the source. The discovery event is almost always a period close, when a finance or planning figure is compared against the ERP for the first time in months and doesn't match — at which point the drift is a quarter deep and the fix is a full reload plus a credibility conversation.

The point of this page: which of those failure modes you're exposed to is decided by your ERP, not by your pipeline tooling. Two of the seven references here deliver deletes in the ordinary batch, one delivers them in a separate ledger, three deliver them only through a change-feed path you have to stand up deliberately, and one doesn't deliver them at all. Know which one you're on before you design the load.

The vendors have no shared vocabulary for this, so here is the one this page uses. Every ERP section below names its cell.

The four delete signals. An in-band soft delete is a flag on the row itself, arriving in the ordinary batch — the row comes back marked dead. An out-of-band tombstone ledger is a separate object listing what was deleted; the row is gone from its table, but its death is recorded elsewhere. A CDC delete operation is an operation type on a change record read from the database log — the most complete signal, and the one that costs a licensing conversation. And delete-blind is the fourth cell, which is not a signal at all: the row stops arriving and nothing anywhere says why.

The three change feeds. Log-based CDCreads the database's own transaction log, so it sees every operation including deletes. Vendor export frameworks— ODP, Synapse Link and Fabric Link, the Infor Data Lake, BICC — publish change feeds on the vendor's terms, and whether deletes ride along is a per-framework fact rather than a general one. Audit-column watermarks are plain SQL against a change-date column; they are the universal fallback, they need no permissions anyone has to negotiate, and they are delete-blind in every ERP on this page without exception.

All eight references — EWM gets its own section there — sorted by what survives the trip rather than by the signal they emit are in the source history guide: log CDC and vendor export frameworks both land in its change-feed replication posture, and audit-column watermarks are its watermark pulls.

One consequence worth stating before the sections: a soft-delete flag inside the ERP's own data model (SAP's LOEKZ) and a soft-delete flag added by the export framework (F&O's IsDelete) look alike in a WHERE clause and mean completely different things. The first says the business marked a document for deletion; the row is still there. The second says the row is gone from the source and the framework is telling you so.

SAP — delete visibility

SAP usually marks before it removes. Purchasing documents carry a deletion indicator — LOEKZ on EKKO and EKPO, and on requisitions in EBAN — and the material master carries a deletion flag at each level: LVORM on MARA (client), MARC (plant), and MARD (storage location). Some of the t-code boilerplate on this site filters LOEKZ <> 'L' for exactly that reason. But the flag is not a delete signal, and it is not the whole story: deliveries are not immutable, and a delivery can be deleted outright before goods issue, leaving no row behind in LIKP (see the deliveries guide). Archiving removes documents beyond the retention horizon as well — the inventory guide states the consequence narrowly: archived material documents amputate the early movement history, so anchor on a physical snapshot you trust and only reconstruct within the retained window.

SAP — change-tracking mechanism

Operational Data Provisioning is the framework a decade of SAP extraction was built on: a subscriber replays inserts, updates, and deletes from the operational delta queue, and the change mode arrives as ODQ_CHANGEMODE. SLT adds an operation flag of its own. The 2026 caveat matters here: SAP Note 3255746 turned ODP-RFC — the transport nearly every third-party connector used — into a technical block for third-party callers on the June 9, 2026 patch day, so which path still carries your deletes is a live question, not settled history. Third-party table and log CDC tools are the alternative; evaluate them on delta capture, delete capture, and cluster-table handling rather than logos.

SAP — landing pattern

Dedup each batch to one row per key on your feed's watermark, then MERGE into bronze translating the feed's delete signal into a physical DELETE — the same shape as pattern (a) below, with SAP's own keyed skeleton published in the extraction guide. Filtering LOEKZ and LVORM is a modeling decision on top of that, not a substitute for it: a flagged row is still present, and a deleted row is not.

SAP EWM is out of scope for this page. Its tables ride the same replication paths as the rest of the SAP estate — see the EWM quirks guide for what is specific to the warehouse layer.

ODP & SAP Note 3255746 →Table-level CDC & extraction tools →Landing in Databricks: raw + MERGE →

JDE — delete visibility

There is generally no soft-delete flag in EnterpriseOne. Rows are removed outright by ordinary business activity, not just by purges. The one to internalize before your first load: sales update (R42800) physically relocates lines from F4211 to F42119, which reads as a delete in one table and an insert in the other. A pipeline watching only one of them silently loses shipped lines.

JDE — change-tracking mechanism

JDE publishes nothing of its own, so the feed is database-level: SQL Server CDC, Oracle GoldenGate or LogMiner-based tooling, or Databricks Lakeflow Connect reading the same back end. The fallback is the audit columns — UPMJ plus a table-dependent time column — and it is delete-blind, period.

JDE — landing pattern

MERGE on the CDC delete operation where you have log-based CDC; periodic key-reconciliation anti-join where you don't; scheduled full reloads for small masters and constants tables. The concrete SQL, keyed on the F4211 order-line key, is in the extraction guide rather than repeated here.

Delete capture →Poor-man's CDC: audit columns →Landing in Databricks: bronze + MERGE →

D365 — delete visibility

F&O is the clean case: the delete arrives in band, on the row, as IsDelete. What makes it a design constraint rather than a freebie is that the signal expires: the sink drops those rows after 28 days (as of August 2026), so a pipeline that falls further behind than that stops seeing deletes at all. Freshness is a correctness requirement here, not a tuning preference — the extraction guide has the exact behavior.

D365 — change-tracking mechanism

Synapse Link for Dataverse or Fabric Link, incremental, watermarked on the sync's own row-version column with a sink timestamp as tiebreak only.

D365 — landing pattern

Dedup on RecId keeping the latest sysrowversion, then MERGE turning IsDelete = true into a physical DELETE — pattern (a). The keyed skeleton against InventTrans is in the extraction guide.

Incremental & soft deletes →Synapse Link for Dataverse →

M3 — delete visibility

Also in band, but carried in the Data Lake's record metadata rather than in a business column — deleteindicator is the one that matters here. The wrinkle is that the same metadata is also how the Data Lake represents change at all: an update appends a variation instead of overwriting, so a raw landed object holds every version of a record and the delete flag has to be read at the right one. The quirks guide lists the full metadata set.

M3 — change-tracking mechanism

The variation metadata, which is authoritative for landed objects. Compass hands you the current, non-deleted and non-archived state unless you explicitly ask for deleted and archived rows — but only for objects in analytical query processing mode; in transactional mode (the off-by-default switch on Compass's per-object Mode Configuration panel) no deduplication is applied and every variation comes back as its own row, so check the mode before trusting the current-state read. The in-table change-date and change-number columns are the fallback for loads that arrive without the metadata, and like every audit-column path they can't signal a delete.

M3 — landing pattern

Dedup each batch to one row per key at the highest variation, then MERGE honoring deleteindicator — pattern (a) again. The keyed skeleton against MITBAL is in the extraction guide; MITLOC follows the same shape at the finer location grain.

Incremental & deletes →Data Lake variations & deletes →

EBS — delete visibility

None in the data. EBS purge programs physically remove rows, and LAST_UPDATE_DATEnever stamps a delete. Nor is the watermark trustworthy in the other direction — batch jobs bulk-stamp rows that didn't really change, and Oracle's own Developer's Guide tells you outright not to qualify rows for processing by the Record History columns (quoted in full in the quirks guide). Treating it as anything more than a fallback is the standard first-pipeline mistake.

EBS — change-tracking mechanism

Log-based CDC — GoldenGate or another log reader — is authoritative: it carries an operation type, real ordering, and real deletes. The WHO-column watermark is fallback-only and delete-blind.

EBS — landing pattern

MERGE on the CDC operation type where CDC exists — the extraction guide's skeleton keys on TRANSACTION_ID against MTL_MATERIAL_TRANSACTIONSand names its metadata columns as illustrative, since every tool spells them differently. Where CDC doesn't exist, a reconciling full refresh is the answer, and small reference and translation tables are better snapshotted outright than watermarked.

Incremental & deletes →WHO columns →

Fusion — delete visibility

Delete-blind by design. A BICC incremental data extract never signals a delete — a purged or hard-deleted row simply stops arriving, and nothing in the batch says so. This is the cell of the taxonomy most teams discover the expensive way, because everything else about the feed works.

Fusion — change-tracking mechanism

BICC incrementals key on per-store incremental key columns against a stored last-extract date, and the prune window re-delivers rows near that boundary on purpose. The delivered answer for deletes is a different job type entirely — the Active Primary Key Extract, which lands the full set of live primary keys as .pecsv files.

Fusion — landing pattern

Two passes, not one: an idempotent upsert MERGE keyed on the primary key (which makes the prune window's deliberate re-delivery harmless), plus a scheduled anti-join against the live-key set — pattern (c). The extraction guide publishes both, keyed against INV_MATERIAL_TXNS; DOO_FULFILL_LINES_ALL needs the same treatment at fulfillment-line grain.

Incremental & deletes →BICC mechanics: offerings, stores, increments →

NetSuite — delete visibility

Out of band, and unusually well delivered. lastmodifieddateis the incremental watermark and it catches inserts and updates only — a deleted row simply stops existing and never appears in a watermark window. A tombstone ledger ships with the product, which is more than most of this table's ERPs manage: deletedrecordrecords the type, the name, the script id, the timestamp and the actor for every record that died, including a flag distinguishing custom records from stock ones. What it does not record is the deleted row's internal id — so it tells you what went away, not which key to delete. Mind the naming when you go looking for it: the documentation calls the surface deletedRecordInConnect, and the analytics table you actually query is deletedrecord.

NetSuite — change-tracking mechanism

A watermark plus a tombstone ledger, both read through the one NetSuite2.com door — and the watermark is rarer than it looks: only seven of the seventy cataloged records carry a modification stamp at all. The gap the ledger leaves is grain: it records deleted records, not deleted lines. Remove one line from a surviving order and nothing is written there at all.

NetSuite — landing pattern

A watermark load where a stamp exists, then deletedrecord windowed and aggregated by type to detect which tables went stale, and a refresh of those — a variant of pattern (b) in which the ledger drives the decision rather than the DELETE, because it exposes no key to delete by. For line-grain tables like transactionline the only safe policy is a periodic full refresh, or a re-pull of every document touched in the window keyed on the header id in transaction — a design-time decision, not something to discover at quarter close. The extraction guide publishes both passes, and names the seven records a watermarked incremental actually works on.

Incremental & deletes →Deletes never reach your watermark →One door out: the NetSuite2.com source →

Nothing new here — every cell restates a section above. Read the second and third columns together: the delete signal tells you what you get, the fallback column tells you what you lose the moment the first column isn't available to you.

Four patterns cover all seven references. Which one you need is decided by the delete signal, not by preference.

(a) MERGE honoring an in-band signal.Where the delete arrives on the row — F&O's IsDelete, M3's deleteindicator, a CDC operation type — dedup the batch to one row per key on the feed's watermark, then MERGE with a WHEN MATCHED AND <flag> THEN DELETEarm ahead of the update arm. This one isn't reprinted here: each reference publishes it keyed on a real table, which is more useful than a generic version. See SAP, JD Edwards, Dynamics 365, Infor M3, and Oracle EBS.

(b) Tombstone diff. Where the deaths are recorded in a separate object, the delete pass is its own job, run after the watermark load on every cycle. Check what the ledger actually keys on before you write it: NetSuite's deletedrecord is the best-known tombstone surface on this page and it publishes no internal id, so there it degrades into a staleness detector feeding (d) rather than the keyed DELETE below.

-- Pattern (b): tombstone diff. For a source that records what died in a
-- separate ledger rather than in the table itself, the incremental load and
-- the delete pass are two different jobs. Land the watermark batch first,
-- then apply every tombstone written since your last run. Placeholders only —
-- the ledger's real column names are in that ERP's own guide.
MERGE INTO <catalog>.<schema>.<target_table> AS tgt
USING (
  SELECT DISTINCT <deleted_key_column> AS deleted_key
  FROM <catalog>.<staging_schema>.<tombstone_table>
  WHERE <record_type_column> = '<record_type>'
    AND <deleted_at_column> >= TIMESTAMP '<watermark>'
) AS src
ON tgt.<key_column> = src.deleted_key
WHEN MATCHED THEN DELETE

(c) Key-reconciliation anti-join.Where nothing signals a delete — Fusion's BICC incrementals, or any audit-column pull — pull the primary keys alone, stage them, and delete whatever bronze holds that the source no longer does. The caveat is grain, and it is easy to miss: a record-level key set reconciles records. A line removed from a surviving document is invisible to it, which is exactly the NetSuite line-grain gap and the reason (d) exists.

-- Pattern (c): key-reconciliation anti-join, for feeds that never signal a
-- delete at all. Extract only the primary-key columns from the source (cheap,
-- even for a large table), stage them, then delete whatever bronze still holds
-- that the source no longer does. Run it on a slower cadence than the
-- incremental load. Caveat: this reconciles the grain of the key set you
-- pulled — a record-grain key set will not find a line deleted from a
-- surviving document.
DELETE FROM <catalog>.<schema>.<target_table> AS tgt
WHERE NOT EXISTS (
  SELECT 1
  FROM <catalog>.<staging_schema>.<live_key_table> AS src
  WHERE src.<key_column> = tgt.<key_column>
)

(d) Scheduled full snapshot.For small masters, constants, and any table whose delete signal doesn't reach the grain you report at, a scheduled full reload is simpler than any of the above and correct by construction. Cheap tables should not get clever pipelines.

The standing rule. Bronze translates delete signals into physical deletes, so bronze reflects the current state of the source and downstream counts stay right. Keeping the deleted rows around “for history” is the mistake that starts the drift. If you want the history, take it from Delta rather than from stale rows: Databricks documents Delta Change Data Feed (opens in new tab) and table history and time travel (opens in new tab) for exactly this, downstream of a bronze layer that tells the truth about what currently exists. Who may read that history is a separate question with its own answer: Unity Catalog governance patterns closes bronze to humans rather than policing a second set of grants over source-shaped tables.

The rubric the per-ERP guides converge on is four questions, in this order.

How big is the table? Masters, constants, and lookup tables are usually small enough that a scheduled full snapshot is both cheaper to operate and immune to every problem on this page. Incremental complexity should be earned by volume, not adopted by default.

How often do rows actually leave? An append-mostly ledger tolerates a delete-blind feed far better than an order table where cancellations are routine business. Estimate the delete rate before you decide how much machinery the deletes deserve.

How fresh does it need to be?Daily batch and a nightly reconciliation pass is a perfectly good answer for most supply chain reporting. Freshness requirements are also what make some feeds non-optional — F&O's 28-day purge window is a freshness constraint imposed on you rather than chosen.

What do your DBA and your license actually allow? Log-based CDC is the complete answer wherever it exists, and it is also the answer most likely to be blocked by something that has nothing to do with engineering — application-specific database licensing, a separately licensed replication product, or log-retention change control. The JDE extraction guide covers that ground in detail; it applies well beyond JDE. See licensing & access caveats. Find out the answer before you design around a path you can't have.