How to rollback data mart update if data quality checks are not passed?
-1
votes
1
answer
93
views
Good afternoon!
I am working with Oracle database. I have a PL/SQL procedure which is scheduled with DBMS job to update every night a table
SOME_TABLE
:
CREATE OR REPLACE PROCEDURE MY_PROC
IS
START_DATE DATE;
BEGIN
START_DATE := trunc(add_months(trunc(sysdate), -2), 'MM');
-- delete data for last 3 months and insert updated data for last 3 months
DELETE FROM SOME_TABLE
WHERE 1=1
AND VALUE_DAY >= START_DATE;
INSERT INTO SOME_TABLE
SELECT ...
FROM OTHER_TABLE
...
;
COMMIT;
END;
I want to make some basic data quality checks for this table (number of null values in columns etc). So my question is:
What is the best way to design data quality checks and the procedure MY_PROC
itself to test data quality of the table SOME_TABLE
after update and rollback the update if data quality checks are not passed?
Asked by rsx
(1 rep)
Jun 22, 2023, 08:49 AM
Last activity: Jun 22, 2023, 06:13 PM
Last activity: Jun 22, 2023, 06:13 PM