Skip to content

EntityDeltaLoad tableΒΆ

The EntityDeltaLoad table is used for storing information about incremental load of data from the data source and as a way to use the Incremental Load non-Change Tracking enabled by Sidra.

Column Description Format Required
Id Entity delta load identifier int Yes
IdEntity Identifier of the Entity associated to the delta int Yes
IdDeltaAttribute Identifier of the Attribute used for the incremental extraction if present int
DeltaIsDate Flag to determine if the delta Attribute is Date or not bit
LastDeltaValue Latest value of the incremental Attribute that has been extracted nvarchar(50)
IdAuxiliaryDeltaAttribute Potential column that can be used for incremental extractions. Often used when date and time are separated in two columns int
LastAuxiliaryDeltaValue Latest value for the auxiliary delta value nvarchar(50)
CustomExtractDataExpression If the delta Attribute is not present in the Entity, this column contains the custom expression (SQL SELECT) used to query the source table to get the incremental data nvarchar(max)
CustomGetMaxValueExpression If the delta Attribute is not present in the Entity, this column contains the custom expression used to get the previously maximum value for incremental load. The CustomExtractDataExpression will be compared against this value to detect the changes nvarchar(max)
NeedReload If true, this field indicates some data might be loss on incremental load and the table should be reloaded from scratch (full initial load replacing the old data). For more information about this particular column, check this section bit Yes
EnableReload If true, this indicates the reload from scratch (full initial load replacing the old data) can be done in the next load process. For more information about this particular column, check this section bit Yes
BatchSize If the pipeline supports, indicates the batch size that will be used in the incremental load int

This table can be used following the next classification:

  1. Incremental Load with built-in Change Tracking (CT) (only SQL Server and Azure SQL databases):

    • EntityDeltaLoad table is filled automatically if the Intake pipeline detects that Change Tracking is enabled for a table, filling then these columns in EntityDeltaLoad table: IdEntity of the table and LastDeltaValue.

  2. Incremental Load non-Change Tracking (non-CT) enabled by Sidra:

    • It must be configured previously to any load.
    • This incremental load type will use the information of an Attribute to get the incremental load. Usually, that Attribute is a column for the table to load.
    • The columns in EntityDeltaLoad to configure are: IdEntity, IdDeltaAttribute, DeltaIsDate, LastDeltaValue and, optionally , IdAuxiliaryDeltaValue and LastAuxiliaryDeltaValue.
    • IdDeltaAttribute is the Id of the column used to check the incremental, if it is a Date, DeltaIdDate column is set to 1.
    • The "auxiliary" columns are used when you need two columns to know the value for the incremental load, e.g. One column has the date and other column has the time.

  3. Incremental Load with built-in System-period temporal tables:

    • In the case of this incremental load, the load by System-period temporal tables creates a record on the table EntityDeltaLoad with the LastDeltaValue, which is the max date of the last inserted or deleted record, and the IdEntity. In addition, other fields such as IdDeltaAttribute, IdAuxiliaryDeltaValue, and LastAuxiliaryDeltaValue are set as NULL.

If IdDeltaAttribute and IdEntity are populated, then the values CustomGetMaxValueExpression and CustomExtractDataExpression are auto-populated as NULL, as they are not used.

Sidra incorporates, depending on the data source, up to three different modes of incremental data synchronization:

  • Incremental load based on Change Tracking (only for SQL Server and Azure SQL Data Intake Processes): In this case, the native Change Tracking offered by the data sources is used. If the data extraction pipeline detects that Change Tracking is enabled for a table, then automatically the following columns will be filled in EntityDeltaLoad table: IdEntity of the table and LastDeltaValue, with a value of type integer. In this case the value in LastDeltaValue is NOT a date.

  • Incremental based on an Attribute: In this case, you can specify an Attribute to retrieve the incremental from the source system. Usually, this Attribute is a column for the table to load. The following columns need to be manually filled in the EntityDeltaLoad table: IdEntity of the table, IdDeltaAttribute, DeltaIsDate, LastDeltaValue and optionally IdAuxiliaryDeltaValue and LastAuxiliaryDeltaValue. IdDeltaAttribute is in this case the Id of the column to check the incremental. If this value is a Date, then DeltaIsDate column needs to be set to 1. The auxiliary columns are used when you need two columns to know the value for the incremental, for example, when a timestamp is stored in the source table in two different columns e.g One column has a date and another column has the time.