SAP Delta Loads Into Microsoft Fabric: Handling Deletes, Reversals and Reprocessed Documents

The analytics platform is live, the SAP data is in OneLake, and for the first few weeks the numbers match. Then, slowly, they do not. The Fabric revenue figure is a little higher than SAP’s. A cancelled order still shows as open. A document that was reprocessed appears twice. Nobody changed the pipeline, and yet the copy has drifted.

The cause is almost always the delta load. A pipeline built to pick up "everything modified since the last run" captures inserts and updates cleanly — and misses the three things SAP does that are not inserts or updates: it hard-deletes records, it reverses postings with offsetting documents, and it reprocesses or re-exports documents that the pipeline then loads again.

None of these throws an error. The load succeeds every night. The gap just widens, because a change-capture method that only understands "new or changed rows" cannot see a row that was removed, and cannot tell a reprocessed document from a genuinely new one.

The first fix is to stop relying on a naïve "last-modified timestamp" column and capture change the way SAP actually expresses it. For transactional tables, that means log-based change data capture, SAP’s change pointers, or the delta mechanisms exposed through SAP’s own extraction interfaces — each of which reports deletes and changes as events rather than leaving you to infer them from a timestamp that deleted rows no longer have.

On a Microsoft Fabric estate this typically lands through Mirroring or Azure Data Factory into a Bronze layer in OneLake, preserving the change type — insert, update, delete — on every row. The point is that the change feed carries the operation, so downstream logic can act on a delete as a delete instead of never seeing it.

Where real-time change capture is not available for a given source, the honest fallback is a periodic full reconciliation of key tables rather than pretending the timestamp delta is complete. A nightly delta plus a weekly full compare is a legitimate pattern; a nightly delta alone, trusted blindly, is how drift starts.

In SAP, you rarely delete a posted financial document — you reverse it. The original stays; a new, offsetting document cancels its effect. If your pipeline treats the reversal as just another insert, both the original and the reversal land, and your analytics correctly nets them to zero. If instead someone "cleans up" by deleting the original to avoid the clutter, the reversal now over-corrects and the numbers go wrong.

The correct model is to keep both documents and let the measures net them, exactly as SAP does. A reversal is a first-class transaction with its own document number and a reference to what it reverses; modelled that way, the audit trail is intact and the net figure is right without special-casing.

This matters most for revenue, cost and inventory movements, where reversals are routine month-end activity. A platform that silently loses either side of a reversal pair will disagree with SAP precisely during the close, which is the worst possible time to discover it.

When SAP does genuinely delete a record — a line removed before posting, a master-data record purged — the lakehouse has to act on it, and the right action is almost never a silent hard delete in Bronze. The common pattern is a soft delete: mark the row with a deleted flag and a timestamp in the Silver layer, so history and lineage are preserved while the Gold model excludes it from current figures.

Soft deletes keep the audit trail and let you answer "when did this disappear, and what did it affect?" — a question a hard delete makes impossible. They also make the pipeline reversible: a delete captured in error can be undone by clearing the flag rather than re-extracting from SAP.

The one thing not to do is ignore deletes because the happy-path load works without them. A cancelled order that never gets flagged as cancelled is the open order that inflates your backlog for a quarter. Deletes are the least visible change and the one most worth handling deliberately.

SAP documents get reprocessed and re-exported more than people expect — a failed IDoc reprocessed, a batch re-run, a document re-extracted after a correction. If the load appends blindly, that document now exists twice, and the duplicate quietly inflates every total built on it.

The fix is an idempotent load: a MERGE keyed on the document’s natural key so that loading the same document twice updates the existing row rather than inserting a second one. Running the pipeline twice on the same data should leave the lakehouse in the same state as running it once — that is the test of idempotency, and it is worth actually running.

Delta Lake, under OneLake, makes this straightforward: MERGE on the business key, apply the change type, and the reprocessed document reconciles instead of duplicating. Without it, a single reprocessing incident leaves a double-count that nobody traces back to its cause for weeks.

The only way to know the Fabric copy still matches SAP is to check it, on a schedule, automatically. Reconcile row counts and control totals — document counts, posted amounts, inventory quantities — between SAP and the Gold layer for key tables, and alert when they diverge beyond a tolerance. This turns drift from something discovered at month-end into something flagged the morning it starts.

Reconciliation is also what makes the platform defensible to finance. "The Fabric revenue total matches the SAP control total to the cent as of this morning" is a statement a CFO can rely on; "it should match" is not. The reconciliation job is cheap to build and is the single highest-trust feature on an ERP-fed analytics platform.

So the full pattern is: capture change as events including deletes; model reversals as offsetting documents; soft-delete rather than silently drop; MERGE for idempotency; and reconcile control totals against SAP on a schedule. Each one closes a specific way the copy drifts — and together they are the difference between an analytics platform finance trusts and one it quietly works around.