HANDLING DELETES DATA IN TARGET TABLE

PURPOSE OF THIS DOCUMENTATION:

This documentation helps us to handle deleted data in target table using MAP OPERATION and TABLE COMPARISION transformations.

• Following are the steps to proceed things in a particular manner.

Below flow is used for Handling delete data in target table.

• Step 1:

Source Data looks like below:

• Step 2:Query Transformation(QRY_IND)

We created the DELETE_IND column by mapping ‘N’ as default which is used to identify deleted data

• Step 3: Table comparison

Table comparison would compare the source and target Table and gives an output containing inserts and updates as Input row type for the MAP_Operation Transformation.

Note: We should enable detect deleted rows option to identify the deleted records in the target table so that we can handle those records in the target table by performing further steps

• Step 4: MAP OPERATION:(INSERT AND UPDATE):

M_I_U map operation operational types should look like below.

• Step 5: MAP OPERATION (DELETE NORMAL) :

M_D_N map operation operational types should look like below where you convert the deletes detected in the Table_Comparission as normals through Map_Operation.

• Step 6: QRY_DEL()

Map ‘Y’ to the DELETE_IND column which would show as an indicator as ‘Y’ for deleted records

• Step 7: MAP OPERATION (NORMAL UPDATE)

• Step 8: We use MERGE TRANSFORMATION so that we can merge the two map operational data
• Step 9: Target table data

I executed the job by deleting 4th & 5th records from a source which would show in the target table with ‘Y’ as delete indicator as below.

Conclusion: we can see deleted records with delete indicator ‘Y’. the records with delete indicator in ‘N’ are exist in target.