10. Manage Data Quality Checks

A data quality (DQ) check evaluates the business context and completeness of an application’s data per business date, after the data has been loaded and historised. Actions with the trigger types PRELIM_DQ and FINAL_DQ only start if the check is OK.

Make sure that the application exists: Add an Application.

10.1. Create a DQ Check

A DQ check is a view in the schema APB_DQ named RDQ_<APP_NAME>, where <APP_NAME> is the name of a registered application. The view must have exactly three columns and return one row per business date:

BDATE

The business date.

DQ_STATUS

'OK' or 'NOK'.

DQ_REASON

A text that explains the result, e.g. which check failed.

Other views in APB_DQ with different names can be used as helper views.

The following example checks the sales orders of the application CORE_SALES: a business date is OK if rows have been loaded and no amount is negative or NULL.

Example: DQ Check for the Application ‘CORE_SALES’

 1CREATE OR REPLACE VIEW APB_DQ.RDQ_CORE_SALES AS
 2WITH daily_metrics AS (
 3    SELECT
 4        s.RED_BDATE AS BDATE,
 5        COUNT(*) AS TOTAL_ROWS,
 6        COUNT(CASE WHEN s.AMOUNT IS NULL OR s.AMOUNT < 0 THEN 1 END) AS INVALID_AMOUNTS
 7    FROM PSA_SALES.SALES_ORDERS s
 8    GROUP BY s.RED_BDATE
 9)
10SELECT
11    BDATE,
12    CASE WHEN TOTAL_ROWS > 0 AND INVALID_AMOUNTS = 0 THEN 'OK' ELSE 'NOK' END AS DQ_STATUS,
13    CASE
14        WHEN TOTAL_ROWS = 0 THEN 'No records loaded for business date'
15        WHEN INVALID_AMOUNTS > 0 THEN 'Found ' || INVALID_AMOUNTS || ' records with negative or null amount'
16    END AS DQ_REASON
17FROM daily_metrics;

Tip

The check is evaluated once per minute while the result is not OK or the data of the application changes. Keep the view cheap, e.g. aggregate on the business date instead of scanning the complete history.

Then register the new view with API_REDADM.REFRESH; until then it is not evaluated. A view with a wrong column list makes the refresh fail.

Example: Register the DQ Check

1EXECUTE SCRIPT API_REDADM.REFRESH();

10.2. Notify on Failed Checks

A new or changed NOK result is sent by mail (template app_notify_dq_nok) to the recipients of the parameter dq_mail_recipients. Set it with API_REDADM.SET_PARAM:

Example: Set the DQ Mail Recipients

1EXECUTE SCRIPT API_REDADM.SET_PARAM(
2    'dq_mail_recipients'                    -- p_param_name
3   ,'dq-team@example.com'                   -- p_param_value
4);

10.3. Check the DQ Status

The result per application and business date is shown in the column DQ_STATUS of REDADM.STA_APP_STATUS and in REDADM.RED_APP_DQ_STATUS (including the reason). A business date for which the view returns no row has the DQ status NULL, which blocks the DQ-aware actions like NOK.

10.4. Approve a Failed Check

If a NOK result is acceptable, e.g. after a check with the data owner, an Application Manager can approve it for the current data version with API_REDADM.SET_DQ_APPROVAL. A justification is mandatory. The approval is valid until new data arrives for this business date; a NULL status cannot be approved.

Example: Approve the Result of 2024-08-01

1EXECUTE SCRIPT API_REDADM.SET_DQ_APPROVAL(
2    'CORE_SALES'                            -- p_app_name
3   ,'2024-08-01'                            -- p_bdate
4   ,'Negative amounts are cancellations, confirmed by sales controlling'   -- p_comment
5);

The approval (who, when, why) is stored in REDADM.RED_APP_DQ_STATUS. The DQ status of the business date is then reported as OK, and the DQ-aware actions start. In RED Vision the approval is available in the DQ dialog of the application.