Skip to main content

Replicate a CDS Entity to Parquet, Deletes Included

This walkthrough replicates a CDS entity from SAP into DuckDB and out to Parquet, keeping the target in step with the source — including rows that were deleted in SAP. By the end you will have:

  • ✅ A first full load of the entity
  • ✅ A repeatable delta step that applies inserts, updates and deletes
  • ✅ A Parquet snapshot you can hand to anything downstream
  • ✅ A recovery path for the night the job dies mid-stream

Why this uses the pipeline engine and not ODP​

ERPL can read CDS entities two ways. ODP is the right tool when the view carries @Analytics.dataExtraction.delta.byElement and you only need inserts and updates.

It cannot tell you about deletes: the annotation points at a change timestamp, and a row that has been deleted has no timestamp left to report. If a deleted sales order must disappear from your warehouse rather than linger forever, you need change capture at the database level — which is what the ABAP Pipeline Engine uses.

Prerequisites​

  • DuckDB and the ERPL extension (INSTALL erpl FROM 'http://get.erpl.io')
  • An SAP system with the pipeline engine available (PRAGMA sap_ape_ping answers)
  • A user holding the ZERPL_APE role — the role is agreed per engagement
  • Free batch work processes: the first delta needs a background job on the SAP side

Step 1: Connect and check​

LOAD erpl;

CREATE SECRET sap (
    TYPE sap_rfc,
    ASHOST 'sap.example.com', SYSNR '00', CLIENT '100',
    USER 'ERPL_APE', PASSWD 'secret', LANG 'EN'
);

-- Does the engine answer, not just the logon?
PRAGMA sap_ape_ping;

-- Does this user have what the extraction needs?
SELECT auth_object, checked_values, verdict, note
FROM sap_ape_check_authorizations('I_SALESORDER')
WHERE verdict <> 'ok';

An empty result from the last query means you are ready. Doing this first turns a failure three hours into a nightly job into a five-second answer.

Step 2: Find the entity and look at it​

SELECT object_name, object_path, released
FROM sap_ape_show(search => 'SALESORDER');

-- A sample, without creating anything server-side
SELECT * FROM sap_ape_preview('I_SALESORDER', max_rows => 20);

-- The shape you will land
SELECT field_name, abap_type, duckdb_type FROM sap_ape_describe('I_SALESORDER');

Step 3: First load​

Use a persistent database. The recovery step below depends on it.

duckdb warehouse.duckdb
-- The first delta call performs the initial load AND registers the subscription.
-- It is slow: SAP generates change-capture triggers in a background job first.
SET erpl_ape_prepare_timeout = 1800;

CREATE TABLE salesorder AS
SELECT * EXCLUDE ("/1DH/OPERATION")
FROM sap_ape_read_delta('I_SALESORDER', 'WAREHOUSE');

Naming the subscriber WAREHOUSE matters: the same name is what resumes on every later run.

Step 4: The nightly delta​

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

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

  -- 'U' is an after-image for both inserts and updates, so replace rather than merge
  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;

-- What happened tonight
SELECT "/1DH/OPERATION" AS op, count(*) FROM chg GROUP BY 1;

Deleting before inserting makes the step idempotent, which Step 6 relies on.

Step 5: Export to Parquet​

COPY salesorder TO 'salesorder.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);

-- Or partition a large entity for downstream engines
COPY salesorder TO 'salesorder/' (FORMAT PARQUET, PARTITION_BY (CreationDate));

Step 6: When the job dies mid-stream​

SAP commits each package as it hands it over and cannot re-send it. If your process dies, those changes are gone from SAP's point of view — the pointer has moved. ERPL keeps a local copy for exactly this, in erpl_ape.delta_spill.

-- Replay what SAP handed over but was never applied.
-- Touches no SAP state, so it works even if SAP is unreachable.
SELECT * FROM sap_ape_read_delta('I_SALESORDER', 'WAREHOUSE', recover => true);

Feed those rows through the same apply step as Step 4. It replays whole packages, so you may see rows you already applied — which is why Step 4 is written to be idempotent. The guarantee is at-least-once.

The spill holds real business data

erpl_ape.delta_spill contains complete, unredacted rows, readable by anyone who can open the database file. Treat warehouse.duckdb accordingly.

Step 7: Clean up when you retire the pipeline​

The subscription is not free: it means database triggers on the source tables for as long as it exists.

SELECT cds_name, subscriber_process, subscription_name, status
FROM sap_ape_show_subscriptions();

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

What's Next?​