Skip to main content

APE Delta Extraction and Recovery

This guide covers running CDS delta extraction in production through the ABAP Pipeline Engine — the path that reports deletes. By the end you will be able to:

  • ✅ Run a delta that resumes correctly across calls
  • ✅ Apply inserts, updates and deletes to a target table
  • ✅ Own the subscriptions you create, and drop them cleanly
  • ✅ Recover a read that died mid-stream without losing committed changes
  • ✅ Reason about the delivery guarantee honestly — it is at-least-once, not exactly-once

Prerequisites​

  • LOAD erpl and a sap_rfc secret (RFC guide)
  • A role that passes sap_ape_check_authorizations (the ZERPL_APE role)
  • An entity whose deletes you actually need. If inserts and updates are enough and the view carries @Analytics.dataExtraction.delta.byElement, ODP is the simpler path

Step 1: Understand what the first call does​

The first sap_ape_read_delta on a (entity, subscriber_process) pair does two expensive things that later calls do not:

  1. It registers a subscription server-side.
  2. It makes SAP generate DHCDC logging tables and database triggers on the tables underlying the entity, in a background job.

While that job is pending, the engine returns neither data nor an error — the read waits.

-- First call: the initial load, and the slow one
SET erpl_ape_prepare_timeout = 1800;   -- seconds; default 900
SELECT count(*) FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL');
Batch work processes must be free

If no batch work process is available the preparation job never runs and the read looks like a hang. Check the application log, object DHCDC, subobject ACP_JOB. This is SAP-side; no client setting fixes it.

Step 2: Read the change indicator​

Delta rows carry /1DH/OPERATION:

ValueMeans
UThe row's current state — an insert or an update, both as after-images
DThe row was deleted. Only the key fields are populated

Inserts and updates are not distinguished. The engine sends after-images for both, so there is no information to report — this is engine semantics, not an ERPL simplification. If you need to know which it was, compare against your target table.

SELECT "/1DH/OPERATION" AS op, count(*)
FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL')
GROUP BY 1;

Step 3: Apply the delta​

Deletes first, then upserts, so a row that was deleted and re-created in the same window ends up present rather than absent:

BEGIN;
  CREATE TEMP TABLE chg AS
    SELECT * FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL');

  -- Deletes carry keys only
  DELETE FROM salesorder
   WHERE SalesOrder IN (SELECT SalesOrder FROM chg WHERE "/1DH/OPERATION" = 'D');

  -- After-images: replace whatever is there
  DELETE FROM salesorder
   WHERE SalesOrder IN (SELECT SalesOrder FROM chg WHERE "/1DH/OPERATION" = 'U');
  INSERT INTO salesorder
    SELECT * EXCLUDE ("/1DH/OPERATION") FROM chg WHERE "/1DH/OPERATION" = 'U';
COMMIT;
Your COMMIT is not what advances SAP's pointer

SAP commits each package as it hands it over, during the scan — not when your transaction commits. So a ROLLBACK here discards your rows while SAP considers them delivered. That gap is what Step 5 exists for, and why the spill is written on its own transaction.

Step 4: Own your subscriptions​

A subscription is durable server-side state with an ongoing cost: triggers on production tables.

-- What exists
SELECT cds_name, subscriber_process, subscription_name, transfer_mode, status, created_at
FROM sap_ape_show_subscriptions();

Two name columns, and the difference matters. ERPL registers subscriptions with an ERPL_ prefix so the engine's inventory says which are ours:

  • subscriber_process — the value you passed. This is what round-trips into sap_ape_drop.
  • subscription_name — what SAP stores, and what a Basis administrator sees in DHCDC_MON.
-- Everything on the system, including subscriptions ERPL did not create
SELECT * FROM sap_ape_show_subscriptions(erpl_only => false);

-- Drop with the UNPREFIXED name — the one you read with
PRAGMA sap_ape_drop('I_SALESORDER', 'NIGHTLY_ETL');

sap_ape_drop reports rather than raises — 'DROPPED' or 'NOT_FOUND' — because cleanup in SQL has no try/catch and a pipeline needs to tidy up best-effort.

A client killed mid-scan

If the process dies while its graph is running, the graph stays running server-side and the engine then refuses to erase the subscription it holds. There is nothing to do from the client; the engine's own retention mechanism reclaims it.

Step 5: Recover an interrupted read​

This is the step that distinguishes a toy pipeline from one you can run at 02:00.

The engine commits each package on handover and cannot re-send it. If your process dies mid-scan, SAP's pointer has already moved past the packages it handed you. There is no engine-side recovery to ask for.

So ERPL writes every handed-over package to a local table, erpl_ape.delta_spill, before its rows are consumed — on its own transaction, so a rollback in your transaction cannot discard it.

-- After a crash: replay what SAP handed over but you never consumed
SELECT * FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL', recover => true);

Three properties worth knowing:

  • It touches no SAP state. recover => true reads the spill and nothing else, so it works with SAP unreachable — which is often exactly the situation.
  • It is repeatable. Running it twice returns the same rows; nothing is consumed or advanced.
  • It replays whole packages. You may see rows you already applied. That is the honest shape of the guarantee: at-least-once, not exactly-once. Make your apply step idempotent — the delete-then-insert pattern in Step 3 already is.

An ordinary read after a recover picks up whatever SAP still owes:

SELECT * FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL', recover => true);  -- what was lost
SELECT * FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL');                   -- what is new
The spill holds real business data

erpl_ape.delta_spill contains complete, unredacted rows for as long as the batch is the newest one, and anyone who can open the database file can read it. On a persistent database the last batch lives until the next delta read on that subscriber replaces it. Error messages may also carry a bounded fragment of row data.

erpl_ape_spill_enabled = false opts out — at the cost of making delta extraction at-most-once, meaning an interrupted read loses those changes permanently. That is a real trade-off, not a tuning knob.

Use a persistent database if you want the spill to survive process death, which is the case it exists for. In an in-memory session it still protects against a rolled-back transaction, but not against the process going away.

duckdb etl.duckdb -c "…"     # spill survives a crash
duckdb -c "…"                # spill dies with the process

Step 6: Tune what is worth tuning​

-- Rows per package: smaller = more roundtrips, smaller working set
SELECT count(*) FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL', chunk_size => 10000);

-- Projection is pushed into the reader, so unwanted columns never cross the wire
SELECT * FROM sap_ape_read_delta('I_SALESORDER', 'NIGHTLY_ETL',
    columns => ['SalesOrder', 'SoldToParty', 'TotalNetAmount']);

Two settings shape delta behaviour specifically:

SettingDefaultWhat it does
erpl_ape_prepare_timeout900Seconds to wait for SAP's preparation job on a new subscription. 0 waits indefinitely
erpl_ape_delta_quiet_seconds60Seconds an established subscription waits for its first package before reporting nothing to replicate

The second exists because a replication graph never marks a last batch, so "no changes" and "not yet" look identical on the wire — the only difference is how long you are willing to wait. Too short reports "no changes" when the answer was "not yet".

There is no threads parameter. The engine hands packages over on one session, so unlike ODP there is no parallel fetch to configure.

Troubleshooting​

"already has a subscription … from an earlier build" An older ERPL registered subscriptions without the ERPL_ prefix. Either drain the old one with that older build, or discard it and accept a fresh initial load:

PRAGMA sap_ape_drop('I_SALESORDER', 'NIGHTLY_ETL');

recover => true returns nothing The spill is empty for that subscriber — either spilling was disabled, or the session was in-memory and the process already exited, or the batch was already replaced by a later read. An ordinary read starts a new batch.

recover rejected together with other parameters columns, filters, chunk_size and wireformat cannot be combined with recover. A replay re-emits packages exactly as the engine produced them, so none of them has anything left to influence — accepting them would imply otherwise.

The delta returns the whole entity That is the initial load, which the first call on a fresh subscriber always performs. Subsequent calls return only changes.

What's Next?​