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.