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
- stg_sv_s_test_table
- c_physical_delete_processing
- s_test_table
Code below
CREATE PROCEDURE c_physical_delete_processing @p_sequence integer , @p_job_name varchar(256) , @p_task_name varchar(256) , @p_job_id integer , @p_task_id integer , @p_return_msg varchar(256) OUTPUT , @p_status integer OUTPUT AS SET XACT_ABORT OFF -- Turn off auto abort on errors SET NOCOUNT ON -- Turn off row count messages --=============================================================== -- Control variables used in most programs --=============================================================== DECLARE @v_msgtext varchar(256) -- Text for audit_trail , @v_step integer -- return code , @v_insert_count integer -- no of records inserted , @v_count integer -- General counter , @v_return_status integer -- Query result status , @v_db_code varchar(10) -- Database error code , @v_db_msg varchar(100) -- Database error message DECLARE @v_work_tbl TABLE( stg_tbl_name VARCHAR(64), satellite_tbl_name VARCHAR(64), SQL_str VARCHAR(MAX) ); DECLARE @v_work_tbl2 TABLE( stg_tbl_name VARCHAR(64), satellite_tbl_name VARCHAR(64), SQL_str VARCHAR(MAX) ); DECLARE @v_7_tbl TABLE( stg_tbl_name VARCHAR(64), EDW_tbl_name VARCHAR(64), str_1 VARCHAR(max), str_2 VARCHAR(max), str_3 VARCHAR(max) ); DECLARE @v_stg_tbl_name VARCHAR(64), @v_satellite_tbl_name VARCHAR(64), @v_SQL_str NVARCHAR(MAX); --=============================================================== -- Main --=============================================================== BEGIN TRY SET @v_step = 100; SET @v_insert_count = 0; SET @v_step = 200; WITH v_1 AS ( SELECT DISTINCT wTC_obj_key stg_tbl_obj_key, wTC_name stg_tbl_name, RIGHT(wTC_name, LEN(wTC_name) - LEN('stg_dv_')) EDW_tbl_name FROM WS_wrk_task_ctrl WHERE wTC_job_key = @p_job_id AND wTC_name LIKE 'stg\_dv\_%' ESCAPE '\' AND EXISTS ( SELECT NULL FROM WS_stage_col WHERE sC_obj_key = WS_wrk_task_ctrl.wTC_obj_key AND sC_col_name = 'dss_deleted_flag' ) ), v_2 AS ( SELECT v_1.stg_tbl_name, v_1.EDW_tbl_name, WS_stage_col.sC_col_name column_name, ROW_NUMBER() OVER ( PARTITION BY v_1.stg_tbl_name ORDER BY WS_stage_col.sC_order ) column_ordinal_num FROM v_1 JOIN WS_stage_col ON v_1.stg_tbl_obj_key = WS_stage_col.sC_obj_key AND WS_stage_col.sC_col_name != 'dss_updated_date' ), v_3 AS ( SELECT v_2.stg_tbl_name, v_2.EDW_tbl_name, v_2.column_name, v_2.column_ordinal_num, CASE WS_normal_col.nC_key_type WHEN 'A' THEN 'Y' ELSE 'N' END business_key_flag FROM v_2 LEFT JOIN WS_normal_tab ON v_2.EDW_tbl_name = WS_normal_tab.nT_table_name LEFT JOIN WS_normal_col ON WS_normal_tab.nT_obj_key = WS_normal_col.nC_obj_key AND v_2.column_name = WS_normal_col.nC_col_name AND WS_normal_col.nC_key_type = 'A' ), v_7 AS ( SELECT stg_tbl_name, EDW_tbl_name, (SELECT column_name + ', ' FROM v_3 v_4 WHERE stg_tbl_name = v_3.stg_tbl_name ORDER BY column_ordinal_num FOR XML PATH('') ) str_1, (SELECT CASE column_name WHEN 'cnt' THEN '1/**/ cnt' WHEN 'row_num' THEN '1/**/ row_num' WHEN 'dss_deleted_flag' THEN '''Y'' dss_deleted_flag' ELSE column_name END + ', ' FROM v_3 v_5 WHERE stg_tbl_name = v_3.stg_tbl_name ORDER BY column_ordinal_num FOR XML PATH('') ) str_2, (SELECT column_name + ' = ' + EDW_tbl_name + '.' + column_name + ' AND ' FROM v_3 v_6 WHERE stg_tbl_name = v_3.stg_tbl_name AND business_key_flag = 'Y' ORDER BY column_ordinal_num FOR XML PATH('') ) str_3 FROM v_3 GROUP BY stg_tbl_name, EDW_tbl_name ) ----------------------------------------------------------------- -- add tables to temporary table used in two sets of logic below. INSERT INTO @v_7_tbl SELECT stg_tbl_name, EDW_tbl_name, str_1, str_2, str_3 FROM v_7 WHERE CHARINDEX('ftcacc_transaction',EDW_tbl_name) = 0; ----------------------------------------------------------------- -- Insert rows into stage table for rows no longer in load INSERT INTO @v_work_tbl SELECT stg_tbl_name, EDW_tbl_name, 'INSERT ' + 'INTO '+ stg_tbl_name + ' WITH (TABLOCK) (' + str_1 + 'dss_updated_date' + ') ' + 'SELECT ' + str_2 + 'GETDATE() dss_updated_date ' + 'FROM ' + EDW_tbl_name + ' WHERE dss_current_flag = ''Y'' ' + 'AND dss_deleted_flag = ''N'' ' + 'AND NOT EXISTS (' + 'SELECT NULL ' + 'FROM ' + COALESCE( (SELECT sT_table_name FROM WS_stage_tab WHERE sT_table_name = stg_tbl_name + '_bk_only' ), stg_tbl_name ) + ' WHERE ' + LEFT(str_3, LEN(str_3) - LEN(' AND ')) + ')' SQL_str FROM @v_7_tbl; SET @v_step = 300; WHILE (SELECT COUNT(*) cnt FROM @v_work_tbl ) > 0 BEGIN SELECT TOP 1 @v_stg_tbl_name = stg_tbl_name, @v_satellite_tbl_name = satellite_tbl_name, @v_SQL_str = SQL_str FROM @v_work_tbl ORDER BY 1; SET @v_msgtext = 'Inserting into ' + UPPER(@v_stg_tbl_name) + ' from ' + UPPER(@v_satellite_tbl_name) + '...'; EXEC @v_return_status = WSWrkAudit 'I', @p_job_name, @p_task_name, @p_sequence, @v_msgtext, NULL, NULL, @p_task_id, @p_job_id; EXEC(@v_SQL_str); SET @v_count = @@ROWCOUNT; SET @v_msgtext = '@V_COUNT: ' + CAST(@v_count AS VARCHAR); SET @v_insert_count = @v_insert_count + @v_count; EXEC @v_return_status = WSWrkAudit 'I', @p_job_name, @p_task_name, @p_sequence, @v_msgtext, NULL, NULL, @p_task_id, @p_job_id; DELETE @v_work_tbl WHERE stg_tbl_name = @v_stg_tbl_name; END ----------------------------------------------------------------- -- Update dss_deleted_rows ino stage table where column called 'row' is null INSERT INTO @v_work_tbl2 SELECT stg_tbl_name, EDW_tbl_name, 'update '+ stg_tbl_name + ' set row=1 where row is null AND dss_deleted_flag = ''Y'' ' FROM @v_7_tbl temp JOIN WS_stage_tab on WS_stage_tab.st_table_name= temp.stg_tbl_name JOIN WS_stage_col on sc_obj_key = st_obj_key and sc_col_name = 'row'; SET @v_step = 400; WHILE (SELECT COUNT(*) cnt FROM @v_work_tbl2 ) > 0 BEGIN SELECT TOP 1 @v_stg_tbl_name = stg_tbl_name, @v_satellite_tbl_name = satellite_tbl_name, @v_SQL_str = SQL_str FROM @v_work_tbl2 ORDER BY 1; SET @v_msgtext = 'Updating ' + UPPER(@v_stg_tbl_name) + ' from ' + UPPER(@v_satellite_tbl_name) + '...'; EXEC(@v_SQL_str); SET @v_count = @@ROWCOUNT; SET @v_msgtext = '@V_COUNT: ' + CAST(@v_count AS VARCHAR); SET @v_insert_count = @v_insert_count + @v_count; DELETE @v_work_tbl2 WHERE stg_tbl_name = @v_stg_tbl_name; END ----------------------------------------------------------------- -- Finish processing SET @v_step = 500; SET @p_return_msg = LEFT( 'C_PHYSICAL_DELETE_PROCESSING completed. ' + 'Rows inserted: ' + CAST( @v_insert_count AS VARCHAR ), 256 ); SET @p_status = 1; END TRY BEGIN CATCH SET @v_DB_code = CAST(/**/ERROR_NUMBER() AS VARCHAR); SET @v_DB_msg = /**/ERROR_MESSAGE(); SET @p_status =/**/-3; SET @p_return_msg = LEFT( 'C_PHYSICAL_DELETE_PROCESSING FAILED' + '. Step num.: ' + CAST(@v_step AS VARCHAR) + '. Err. num.: ' + @v_DB_code + '. Err. msg.: ''' + @v_DB_msg + '''', 256 ); EXEC @v_return_status = WSWrkError 'E', @p_job_name, @p_task_name, @p_sequence, @p_return_msg, @v_DB_code, @v_DB_msg, @p_task_ID, @p_job_ID, NULL; END CATCH; RETURN 0