Include CustomSQL into DQ Scorecard

Allow Data Quality scores to be calculated by CustomSQL tests by enabling it in the Config.

By default, Custom SQL tests are not included in the DQ Scorecard & Report as the DQ Scorecard requires that both error row counts and total row counts are included to calculate the Score.

To include Custom SQL score into the calculation of a Data Quality Dimension, you will need enable the Include monitor into Data Quality Scorecard in the monitor configuration.

Once enabled, you will need to provide a valid SQL query which will provide the total_row_count to the calculation of the DQ Scorecard.

How to set up (Example use case)

Scenario: We need to detect rows with invalid event_type values in analytics.events and include the monitor in the Data Quality Scorecard. The custom SQL identifies error rows; a separate total_row_count_query provides the search space so the system can compute DQ Score.

Example:

  • Table: analytics.events

  • Business rule: event_type must be in ('click', 'view', 'purchase'). Rows with other event_type values are considered errors.

  • We want: custom metric = number of rows with invalid event_type; total_row_count = total rows considered in the check (the whole table in this example).

The below is set up in the Config:

Custom SQL test query
SELECT COUNT(*) AS value FROM analytics.events WHERE event_type NOT IN ('click', 'view', 'purchase') AND event_time >= '2025-09-01'::timestamp AND event_time < '2025-09-02'::timestamp';

Then, enable Include monitor into Data Quality Scorecard, and add the query:

Total Row Count Query
Total Row Count query: SELECT COUNT(*) AS value FROM analytics.events WHERE event_time >= '2025-09-01'::timestamp AND event_time < '2025-09-02'::timestamp';

Finally, save the monitor.

Monitor History for each scan

In the above use case, every time the CustomSQL test is ran against the data source, both SQL queries are run to generate the result for each monitor scan. To view the details from each scan:

  1. Go to Config > All monitors. On the desired monitor, click on the ellipsis and select Monitor Info.

  1. If total row count is enabled, then a new column is shown in the History which is row_count.

FAQ

  1. What happens to the DQ Score calculation if Total_Row_Count > Error_Row_Count?

This situation may happen when the total_row_count is no more valid, or there is some logical error in the query. In this case, the specific observation of this DQ Score calculation is rendered invalid and it is omitted in the DQ Report or Dashboard.

Last updated