What is the Hard Delete Detection?
Hard Delete detection is abbreviated as HDD. It is a process to determine the records removed or physically deleted from the source system. Informatica MDM determines the records which are removed from the source system and soft delete it in the associated MDM base object tables.
What are the soft delete and the hard delete?
Soft delete and hard delete are not Informatica MDM concepts. These are well known concepts for any database management system. Soft deletion is achieved by using a column such as 'STATUS' or 'ACTIVE_INACTIVE' or any other column which tells the record is deleted or active for business. So soft deleted records are physically maintained in the database but are not active for business purposes. The soft deleted records can be recovered by making them active and making them available them for business.
On the other hand the hard deleted records are physically removed from the database and those are not available to business once those are hard deleted.
Do all types of databases support HDD in Informatica?
No. Only Oracle and Microsoft SQL Server environments can detect records that are removed from the source systems. The DB2 database environment cannot detect records which are removed from the source systems.
How HDD works in the Informatica MDM?
- The stage job in the MDM Hub compares all the records in the landing with the records in the previous landing table (aka PRL table) associated with each landing table.
- After determining the missing records in the landing table those are flagged as hard deletes for a full load.
- The hard delete flagged records are reinserted back into the landing table along with a delete flag value.
- For flagging records for hard deletes in the source, either we can use HUB_STATE_IND column or any other custom column.
- After running the stage and the load job in the MDM Hub, records are updated in the associated base object table.
What are the requirements for HDD implemenation?
Below are major requirements for implementing HDD :
- In order for HDD to work, we need to have a full load every time. It does not work with incremental or transitional loads.
- We need to create a hard delete detection table in the repository table to configure hard deletes.
- In order to make entry into the job metric table, we need to maintain an additional configuration.
- HDD requires user exits to be written in Java.
What are the User Exits required for HDD implementation?
Below are the user exits required to be implemented for HDD:
1. Post Landing User Exits
2. Post Stage User Exits
Sample code for User Exits:
Here is a sample code for Post Landing User Exits:
public class PostLandingUE implements PostLandingUserExit {
public void processUserExit(UserExitContext oUEContext, String stagingTableName, String landingTableName,
String PRLTableName) throws Exception {
try {
HardDeleteDetection hdd = new HardDeleteDetection(oUEContext.getBatchJobRowid(), stagingTableName);
hdd.startHardDeleteDetection(oUEContext.getDBConnection());
} catch (Exception e) {
e.printStackTrace();
}
}
}
Here is a sample code for Post Stage User Exits:
public class PostStageUE implements PostStageUserExit {
public void processUserExit(UserExitContext userExitContext, String stagingTableName, String landingTableName,
String PRLTableName) throws Exception {
try {
ConsensusFlagUpdate consensusProcess = new ConsensusFlagUpdate(userExitContext.getBatchJobRowid(), stagingTableName);
consensusProcess.startConsensusFlagUpdate(userExitContext.getDBConnection());
} catch (Exception e) {
e.printStackTrace();
}
}
}
The video below provides detailed information about the Hard Delete detection process in the MDM hub.
