Qlik Talend Cloud Data Integration: Handling Delete Operations from SAP HANA in Pipeline tasks

[sureshkumar]

The following issue is observed in a replication from a SAP Hana source to a Snowflake target:

In the source table, all columns are defined as NOT NULL with default values.

However, in the replication project, specifically during Change Data Capture, Null Values are sent to the CT table created as part of Store changes. This is observed when Delete Operations are performed in the source.

In this example, the Register task of the Pipeline Project reads data from the Replication Task Target [The data available in Snowflake storage]. When the Storage task is run, the task fails with NULL result in a non-nullable column.

Resolution

When a DELETE operation is performed in SAP HANA, it removes the entire row from the table and stores only the Primary Key values in the transaction logs.

Operation type = DELETE
Default values are not available and not applied.

As a result, we can only see values for the primary key columns, and the remaining columns contain the null value in the Snowflake Target (__ct table).

To overcome this issue, please try the following workaround:

1. In the Replicate Project, apply a Global Rule Transformation to handle Null Value being populated in Snowflake.
   
   This is done through Add Transformation > Replace Column Value
   

   1. In the Transformation scope step:

      • Source schema: %
      • Source datasets: %
      • Column name is like: %
      • Where data is: UNSPECIFIED
      • Where column key attribute is: Not a key
      • Where column nullability is: Not nullable
        
        
        
        
   2. In the Transformation action step
      
      
      • Replace target value with: $IFNULL(${Q_D_COLUMN_DATA},0)
        
        
        
        

2. Prepare and run the job

3. Go to Snowflake and check the __CT table entry to verify that there are no more null values for non-primary key columns
   
   

4. In the Pipeline Project, use the Register task to load data from the Replication Task

5. Data will now successfully replicate in the storage without NULL values

Environment

• Qlik Talend Cloud Data Integration