(GLUE-1806) Delta Mechanisms

Delta mechanisms (deltas) specify which data is extracted. Currently, the delta functionality is supported only for the extraction from a SAP system to a storage.

Deltas serve only as data source definition and do not specify higher purpose processes like replication, offloading, etc.

Full extraction

This type of extraction has no restrictions or further specifications of the source; all data is transferred to a target.

Timestamp

You select the Timestamp field attribute, based on which data is extracted. After each extraction the highest value of the selected timestamp attribute is stored. In the following extractions only records with a attribute value higher than the stored value are transferred. The first load is always a Full extraction.

Example

To simplify, in this example we use only time to express timestamp values (We don't use the complete timestamp with date and time). Therefore, all mentioned times below, have the same day in the complete timestamp format.
Before the first extraction the source table contains values 09:13, 11:10 and 08:15 in the particular column.
By the first extraction all records are transferred and only the value 11:10 is stored as the highest value.
Further records with the values 09:50, 13:10 and 15:05 in the particular column are inserted into the source table. The source table contains then the records with the values 09:13, 11:10, 08:15, 09:50, 13:10 and 15:05.
By the next extraction only the records with the values 13:10 and 15:05 are transferred and the value 15:05 is stored as the highest value.
Records with the value 09:50 aren't transferred, because this value is lower than the stored value 11:10 (09:50 < 11:10).

Date

You select Date field attribute, based on which data is extracted, same as by the Timestamp delta type. Similar as by the Timestamp delta, the highest value of the selected attribute is stored after each extraction. However, with the difference that records with values of the current date and higher are ignored.

Example

In this example the current date is the 13.04.2018.
Before the first extraction the source table contains the values 11.03.2018, 13.04.2018 and 14.04.2018 in the particular column for Date delta.
By the first extraction records with the value 11.03.2018 are transferred and the value 11.03.2018 is stored as the highest value.
Records with the value 13.04.2018 aren't transferred, because the current day has the same value. Records with value 14.04.2018 aren't transferred, because this value is higher than the current day (14.04.2018 > 13.04.2018).
The next day records with the values 10.03.2018, 29.03.2018 and 20.04.2018 are inserted into the source table. The source table contains then the records with the values 10.03.2018, 11.03.2018, 29.03.2018, 13.04.2018, 14.04.2018, and 20.04.2018 and the current date is 14.04.2018.
By the next extraction only records with the values 13.04.2018 and 29.03.2018 are transferred and the value 13.04.2018 is stored as the highest value.
Records with the value 10.03.2018 aren't transferred, because the value is lower than the stored one (10.03.2018 < 11.03.2018). Records with value 14.04.2018 aren't transferred, because the current day has same value (14.04.2018 = 14.04.2018). Records with value 20.04.2018 aren't transferred because the value is higher than the current day (20.04.2018 > 14.04.2018).

Value delta

Selection is based on values of already extracted data in a specified delta field. To configure this mode properly the user must specify this field. Only entries with new distinct values from this field is transferred during the next execution of the extractor. 

Delta process for Value delta

1. Value delta extraction is based on a delta field, which is chosen by the user. After the user selects the delta field, the system checks all the distinct values in the particular delta field and transfers the whole records with these distinct values to the target system. The distinct values of the delta field are then stored in a black list.

As an example the table below contains the fields First name, Last name and Employer ID. The user chooses the 'Employer ID' as the delta field. The system transfers the records (record 1 until 5) with distinctive values (121 until 124) in the field 'Employer ID' to the target system and afterwards stores these values in the black list.

     2. By the next extraction the system checks all the values in the delta field and selects only values which are not in the blacklist. By the delta execution the system transfers only records with these distinct values, which are not included in the black list, to the target system and stores the new distinct values of the delta field in the black-list.

To the above table were added two more records. The system selects in the delta field 'Employer ID' the red marked records with values which are not in the black-list (values 125 and 126) and transfers the whole records (records 6 and 7) with these distinct values to the target system. Afterwards the system stores these new distinct values (125,126) to the black list.
This extraction process is repeated the same way again by the next Value delta extraction.

Trigger delta

With trigger delta, a special database table keeps record of new database records, as they're being added. Every time a DML operation is performed on a given table, a procedure stores information about the newly inserted data to help determine the delta.