# Include CustomSQL into DQ Scorecard

{% hint style="info" %}
By default, Custom SQL tests are not included in the [DQ Scorecard](https://docs.decube.io/dashboard/health-score) & [Report](https://docs.decube.io/reports/asset-report-data-quality-scorecard) as the DQ Scorecard requires that **both error row counts and total row counts** are included to calculate the Score.
{% endhint %}

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.

{% embed url="<https://www.loom.com/share/fd585bcd7602408c965a2a6a4cbf8a2a?sid=4d031395-953c-4caf-8c46-1f4e41bed154>" %}

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:

{% code title="Custom SQL test query" overflow="wrap" %}

```sql
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';
```

{% endcode %}

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

{% code title="Total Row Count Query" overflow="wrap" %}

```sql
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';
```

{% endcode %}

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.

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-4120d0baa6ff550f835a08de743f1c43657d4064%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://1779874722-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTw0qpCVzfrIXqS4FEg4T%2Fuploads%2Fgit-blob-b30dbaa938c606025df9e205834286f276be5f71%2Fimage.png?alt=media" alt=""><figcaption></figcaption></figure>

## 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.

{% hint style="warning" %}
It is highly recommended that you take great care in setting up the Total Row Count query to ensure that the DQ scores are accurately captured.
{% endhint %}