ERP Deletes & Change Tracking

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. Some sources deliver deletes in the ordinary batch, some in a separate ledger, some only through a change-feed path you have to stand up deliberately, and some not at all. Know which one you’re on before you design the load.

Shorthand on this page: ODP is Operational Data Provisioning, SAP's extraction framework; CDC is change data capture; BICC is Business Intelligence Cloud Connector, Oracle Fusion's extract framework.

The vendors have no shared vocabulary for this, so here is the one this page uses.

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 CDC reads 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 in this library without exception.

The source history guide sorts the references by what survives the trip rather than by the signal they emit — see 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 up front: 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.

Four patterns cover every case. 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 normalized delete property, 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 DELETE arm 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 deleted-record ledger is the best-known tombstone surface in this library and it publishes no internal id, so there it degrades into a staleness detector feeding the scheduled full snapshot in (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’sBICC 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 the scheduled full snapshot in (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.