Capturing Delta Changes in Your Local Table

Enable Delta Capture in your local table to automatically track all inserts, updates and deletions of its records via Change Date and Change Type columns. You can use replication flows, transformation flows, and transformation flows on file to write to these tables and to read from them, extracting only delta changes for each run.

Prerequisites

To enable Delta Capture:
  • You table must have a status Not Deployed.

  • You must define one or more key columns.

Switching Delta Capture On

Local tables can be used as source data or target data by SAP Datasphere apps. For some business scenarios, you might need to keep an eye on changes that will be made after you have deployed your local table. For example, when you import a new csv file, or while running a replication flow, you might want to know which data is updated or deleted. When creating a local table, you can switch on a toggle that will capture future updates made in your table.

You enable Delta Capture while creating a local table. See Creating a Local Table.

When Delta Capture is switched on:
  • Delta Capture Table field is added and a default name for the delta capture table is defined (Technical name + Delta).

  • 2 additional columns are automatically created in my table:

    Column Description Possible Values

    Change Date

    The column will track the last date and time of the last change to an individual record.

    Current UTC timestamp.

    Change Type

    This column will track the type of last change made to a record.

    This column will track the type of last change made to a record.When a record is inserted or updated corresponding change types are used: "I" for insert (a new record is added), "U" for update (an existing record gets an updated value), or "A" for upsert (insert records if they do not exist yet in the target table, or update them by primary key if they do exist). When an existing record is deleted other specific change types are used (for example "D"). Note that deleting a record will not physically delete it, so that the changes can be propagated to the different objects that consume it in delta mode. It is however filtered out when accessing the Local Table (using the Active Records Table). Also, note that the change types provided by the different SAP Datasphere apps vary and may depend on the actual source that is connected. The handling of the different change types is implemented internally by SAP Datasphere apps that consume the Delta Capture Table with no need for consideration in modeling. For more information on records deletion, see Load or Delete Local Table Data

You can change both business name and technical name but you can't change the data type (read-only).

Importing a File to Your Local Table With Delta Capture Enabled

You can import another csv file containing data in your table. In this case, the newly imported data will get change type as "I".

For example, if I import a csv file with new data, the change type is set to "I".

For more information, see Load or Delete Local Table Data

Deployment and Consumption of Local Table With Delta Capture

When a local table with delta capture is deployed, the following objects are created and stored in the repository:
  • The table that contains the delta capture columns. The technical name ends with "Delta".

  • The table that contains only the active records. It excludes both the delta capture columns and deleted records ("M" and "D"), and keeps only the active records.

The 2 objects are consumed differently by SAP Datasphere apps:
  • Most SAP Datasphere apps consume a local table with delta capture through the Active Records table only. In these cases, local tables behave the same way independent of whether Delta Capture is set to "On" or "Off". For examples in Graphical Views, SQL Views, E/R Modeler or Business Builder.

  • The following SAP Datasphere apps also interact with the Delta Capture Table that contains the delta columns:

Displaying Records Archived in Your ABAP Source System in Views

If you archive records in your ABAP source system, these records are by default replicated as deletion (change type 'D') and marked as deleted in a table with delta capture enabled. They will therefore not be available in SAP Datasphere views for consumption, as views will read from the active records table, which excludes both deleted (change type 'D') and archived records (change type 'M').

So that the archived records from your ABAP source system do not appear in your tables in SAP Datasphere as deleted records, please follow the guidance from SAP Note 3290438Information published on SAP site that suggests two configuration options using the table DHCDC_TRIGGERSTG in the ABAP source system (value 'A' or value 'D').

When using the default option (value 'A') in the table DHCDC_TRIGGERSTG, archived records are replicated to SAP Datasphere with change type 'M', so that you can distinguish in a local table with delta capture enabled between archived records (change type 'M') and deleted records (change type 'D'). To consider archived records (change type 'M') as active records for a specific table (so that a view will display them), we recommend using a transformation flow that maps the records with change type 'M' to change type 'U' in the target table. This transformation flow can either use a graphical view transform with calculated columns or an SQL view transform. In that transform, all records with change type 'M' are mapped to 'U' (see SQL example here below) with all other source columns mapped unchanged to target columns.

The target table for this transformation flow can either be a copy of the source table or the source table itself (in that case please introduce an additional filter to only process records with change type 'M').