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