Goldengate: How to handle soft deletes

How are you all , it has been a  long while since my last blog so thought of sharing some useful information on goldengate, let us try to implement soft deletes in the goldengate.

Concept: 
      In the source table record got deleted , in the replicated target update the record attribute delete_flag from N to Y, if record got reinserted in the source, in the target modify delete_flag from ‘Y’ to ‘N’. If new record got inserted then delete_flag will be N and if source updates a record in the target also record should get updated but there is no change in the delete_flag value i.e. it will be N only.

Implementation details…..for example let us have source target tables as follow

CREATE TABLE source.SOFT_DETELE_IMPLEMENTATION
(
   ROW_ID      VARCHAR2(15 CHAR) ,
   LOGIN       VARCHAR2(50 CHAR)
);
  
CREATE TABLE target.SOFT_DETELE_IMPLEMENTATION
(
   ROW_ID                 VARCHAR2(15 CHAR) ,
   LOGIN                  VARCHAR2(50 CHAR) ,
   DELETE_FLG             CHAR(1 CHAR)     
);


In extract parameter file add the table which you are interested in
example: edit /app/ggate/dirprm/eextract.prm

EXTRACT EEXTRACT

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID dbuser_name@DB_Instance, PASSWORD password

EXTTRAIL /app/gg/trail/
-- etc

TABLE source.SOFT_DETELE_IMPLEMENTATION, &
      COLS (ROW_ID, LOGIN);


--Modify the Replicat parameter file and add following syntax for corresponding table then perform all necessary task need for extract / replication to work.

edit /app/gg/dirprm/rextract.prm

---------------------------------
-- TARGET Table mapping -- 
---------------------------------
-- following tag will allow multiple maps for single source
ALLOWDUPTARGETMAP
GETINSERTS
GETUPDATES
-- IGNOREDELETES will ignores deleted records
IGNOREDELETES
MAP source.SOFT_DETELE_IMPLEMENTATION,  TARGET target.SOFT_DETELE_IMPLEMENTATION, &
    COLMAP ( ROW_ID = ROW_ID, LOGIN =  LOGIN, DELETE_FLG = "N" ),HANDLECOLLISIONS;
IGNOREINSERTS
IGNOREUPDATES
GETDELETES
-- UPDATEDELETES will convert delete operations into update operations.
UPDATEDELETES
MAP source.SOFT_DETELE_IMPLEMENTATION,  TARGET target.SOFT_DETELE_IMPLEMENTATION, &
    COLMAP ( ROW_ID = ROW_ID, LOGIN =  LOGIN, DELETE_FLG = "Y" ),HANDLECOLLISIONS;

Unit testing:
Inserted a record into source  ( insert into SOFT_DETELE_IMPLEMENTATION values ('400','aaa');)
Target Table Values ---- 400    aaa         N     

Updated a record in the source (update SOFT_DETELE_IMPLEMENTATION set login ='upd'  where row_id ='400';)
Target Table Values ---- 400     upd        N 

deleted a record from source (delete SOFT_DETELE_IMPLEMENTATION where row_id ='400';)
Target Table Values ---- 400     upd        Y   

Inserted same record 2nd time in the source
Target Table Values ----400     aaa         N    

Source record got updated
Target Table Values ----400     upd        N  

Source record got deleted second time

Target Table Values ----400     upd        Y