Efficient Delta Creation in the Datasphere: A Practical Guide

[Hagen]

1                Introduction: The Importance of Delta Processing in Modern Data Architectures
Nowadays, large amounts of data are transported from A to B. Therefore, it is becoming increasingly important to either consume or analyze data virtually, or to consume a delta. Since this is not always possible, this article will show how I have solved this problem within the Datasphere.

Step 1: Extracting Data into a Staging Table
The first step is straightforward: extract the data from the source system into a staging table.

At this stage, the goal is not to transform the data but simply to create a 1:1 copy of the source dataset via Replication Flow. This staging layer represents the most recent snapshdot of the source system. 

The staging table acts as the input layer for delta processing. Each load refreshes this table with the latest data extracted from the source.

Step 2: Creating a Hash for Change Detection
To detect changes efficiently, we generate a hash value for each record based on the relevant business attributes.

The idea behind this is simple: If any field changes, the hash value changes as well.

This avoids expensive field-by-field comparisons and keeps the comparison logic compact.

Example for SQL-View for new data:

SELECT
    MATERIAL,
    MATERIAL_DESC,
    QUANTITY,
    HASH_SHA256(
        TO_BINARY(
            COALESCE("Material_Desc",'')                || '|~|' ||
            COALESCE("Article_Class",'')                || '|~|' ||
            COALESCE("BASE_UOM",'')
        )
    ) AS HASH_VALUE,
FROM IL_LT_MATERIAL

This hash essentially acts as a fingerprint of the record. When we compare two datasets later, we only need to compare the hash values instead of every column.

The ~ is for that case, that we don't overlapp content from two or more fields.

For large datasets, this approach can significantly simplify the change detection process.

Beside the new replicated Data we have the "active" Data in another table, which are loaded before. The hash-value is created as well, but over an Transformation Flow. After that we can compare each line over the key (Material).

Step 3: Calculating the Delta
Once the staging data contains the hash values, it can be compared to the current active dataset.

This comparison identifies three possible scenarios:

• INSERT – the record exists in staging but not in the active dataset
• UPDATE – the record exists in both datasets but the hash value changed
• DELETE – the record exists in the active dataset but not in the new staging data

The first time the pipeline runs (initial load), there is no previous dataset to compare against. In that case, the active dataset is simply initialized.

From the second run onward, the system starts identifying real deltas.

The harmonized Table (comparison of new and active data) should be enabled with delta capture.  After enabling you get two new fields: Change_Type and Change_Date. Map your calculated Change_Type  field to the new Change_type from your delta table. The Change_Date ist filled automatically with the actual loading timestamp.

Example logic for SQL-View:

SELECT
N.MATERIAL,
N.MATERIAL_DESC,
N.QUANTITY,
CASE
WHEN A.MATERIAL IS NULL THEN 'I'
WHEN N.HASH_VALUE <> A.HASH_VALUE THEN 'U'
WHEN N.MATERIAL IS NULL THEN 'D'
END AS DELTA_TYPE
FROM IL_SV_MATERIAL_HASH N
FULL OUTER JOIN IT_LT_MATERIAL_ACTIVE A
ON S.MATERIAL = A.MATERIAL
WHERE  A."KEY_HASH" IS NULL                    -- Insert
    OR N."KEY_HASH" IS NULL                    -- Delete
    OR N."VALUE_HASH" <> A."VALUE_HASH"        -- Update;

Each detected change is stored together with a timestamp, which allows us to track when the change was detected.

This timestamp becomes particularly useful for auditing, monitoring, and time-based queries.

Step 4: Maintaining the Delta Table
The detected changes are written into a delta table. Instead of storing the full dataset again, this table only contains the records that changed.

Typical columns include:

• Business key (e.g. MATERIAL, PLANT)
• Updated values
• Change type (INSERT / UPDATE / DELETE)
• Change timestamp

With each new load, additional delta records are appended / updated.

Step 5: Providing the Delta to Data Consumers
Once the delta table is maintained, it can be exposed to downstream consumers.

Via Replication Flow the Data can be provided to the target, for e.g. ADLS (Premium Outbound neccessary)

Applications, dashboards, or integration services can query the delta table and filter by change type.

For example:

• retrieve only new records
• process updates
• synchronize deletions

Because only the changed data is provided, consumers do not need to process the entire dataset each time.

This keeps integrations lightweight and improves overall system performance.

Process Overview
The complete workflow can be summarized in five steps:

1. Extract the latest data into a staging table
2. Generate a hash value for each record
3. Compare the staging dataset with the active dataset
4. Identify inserts, updates, and deletes
5. Store and expose the delta for downstream consumption