(Glue-2111) Capturing of deleted entries

Owned by SNP
Nov 22, 2021
3 min read

In addition to the standard SAP table data replication, the SAP Table Fetcher with trigger delta can be set up to transfer also deleted records. These records will have only key field values filled in the target Glue Table. If you want to distinguish them further, a deletion flag field can be added to the Glue table, which will have a value ‘D' filled for deleted records using the rule ‘Delete flag’ in the Extraction Process transformation mapping. Please note, that deleted records are transferred after standard inserted/updated records.

For some storages, there is an option to persistently remove the records with the deletion flag 'D'. For more information please refer to section Merge using captured deleted entries.

Additional steps to standard SAP table to Storage extraction

  1. To add the deletion flag field, go to Glue Table and add the GLDELFLAG field using the button Add deletion flag. You can also add your own field, but in that case, the rule in the Extraction process won’t be assigned automatically.

     

  2. In the SAP Table Fetcher, check the Read deleted entries checkbox.

     

     

  3. In the Extraction Process, the rule Delete flag should be assigned automatically using the Suggest transformation mapping button, if the default deletion flag field was used in step 1. Otherwise, select the rule manually.

Merge using captured deleted entries

When using the deletion flag to capture entries that have been removed, some storages can utilize the merge functionality. Currently supported storages:

  • MS SQL

  • Snowflake

  • Google BigQuery

The merge functionality fetches the data and decides if the entries should be deleted or inserted/updated based on the deletion flag of the row.

The content of the shadow table used for the trigger load with the merge operation

 

The merge logic removes entries from the storage marked as “DELETE” and inserts/updates entries marked as “UPDATE” or “INSERT”. Based on values in the “TYPE” column the target deletion flag field contains the value “D” or ““ (space) during the load. If the source table is DSO or some structure, where this delta logic is already handled, the user doesn’t have to set up the “Capturing of deleted entries”.

1. Capture deleted entries

The merge functionality uses the deletion flag field name to decide which entries should be deleted or inserted/updated. The setup is described in the chapter above Capturing of deleted entries.

This step is required only if there is no deletion field in the source structure. For example, DSOs have RECORDMODE in their structure by default. In this case, there is not required to create an additional field, only set it as “Delete field” in the extraction process and in the consumer settings.

2. Consumer setup

When doing the merge setup, the GLUE table should already contain a deletion field (using trigger delta or default SAP field).

Extraction Process - GLDELFLAG marked as “Delete flag”

 

The consumer needs to have information about the deletion field. This setting can be applied while editing the consumer in the “/DVD/GL80“ transaction. Additional information is available on the Glue Table Consumer page.