(Glue-2111) Delta Mechanisms

Full extraction

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

Timestamp

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 record with an 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 the 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 then contains 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.

Additionally, you can set Timestamp offset (in seconds) - value of time shift for the next delta load in seconds. This will help to lower the risk that the new records coming to the source table during extraction will not be captured by delta or delta init loads.

Example:

During the delta or delta init load, the source table was updated with a new record. This record is not part of the extracted data, as it was saved after the data was selected from the source, but at the same time, it will not be transferred with the next delta load due to its timestamp value. For such cases, it's useful to have a safety internal to ensure that all records are being captured by timestamp delta.

Date

Select Date field attribute, based on which the data is extracted, same as by the Timestamp delta type. Similar to the Timestamp delta, the highest value of the selected attribute is stored after each extraction. However, with the difference that records with the values of the current date and higher are ignored. This ensures that data is extracted from a complete day and not only from its part. This behavior is also applied to the first, full extraction.

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 the 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, which is the 14.04.2018, 14 records with the values 10.03.2018, 29.03.2018 and 20.04.2018 are inserted into the source table. The source table then contains the records with the values 10.03.2018, 11.03.2018, 29.03.2018, 13.04.2018, 14.04.2018, and 20.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 the value 14.04.2018 aren't transferred, because the current day has the same value (14.04.2018 = 14.04.2018). Records with the value 20.04.2018 aren't transferred because the value is higher than the current day (20.04.2018 > 14.04.2018).

Value delta

Select a Value field attribute that contains numeric values. Based on this Value field data is extracted, similar to the Date and Timestamp delta. After each extraction, the highest value of the selected field is stored. In the following extractions only records with a value higher than the stored value are transferred. The first load is always a Full extraction.

Distinct value delta

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

Delta process for Distinct value delta

1. Value delta extraction is based on a delta field that you choose. After you select 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 blacklist.

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 afterward, it stores these values in the blacklist.

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

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

Choose this option, if you don't want to transport any historical data but only actual data, which will be generated after the initial delta run.

Trigger delta 

With trigger delta, a special database table keeps a 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.

More information on how to set the delta types in Glue can be found in the chapter (Glue-2111) (Deprecated) How To Set Delta Types.