Sample Header Ad - 728x90

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