The following custom stored proc (c_physical_delete_processing)
works by looking at a field named “dss_deleted_flag” in the  data vault, which equals “N”, and if it can’t find the record in the stage table, it adds the record from the vault table with a dss_deleted_flag = ‘Y’ to the stage table. The stage table would have this field as well, set to default as dss_deleted_flag = ‘N’,

So at the end of the stored proc, you would have the rows which do exist in the source table as “N” and the ones that do not exist as “Y”

 

This works for data vault satellites, and the naming convention we used for data vault stage tables is starting the name with “stg_dv_“,

so a satellite table for example would be s_test_table, and its stage would be stg_sv_s_test_table.

The stored proc would be placed into a a single job in between the stage and satellite tables. E.G the order of job would be

  1. stg_sv_s_test_table
  2. c_physical_delete_processing
  3. s_test_table

 

Code below

 

Share This Via: