> For the complete documentation index, see [llms.txt](https://cleyrop.gitbook.io/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cleyrop.gitbook.io/docs/documentation-fr-en/data-governance/qualite-des-donnees.md).
# Data Quality
Data Quality allows ensuring datasets meet **specific validation criteria**.
It helps detect **anomalies** and correct non-compliance, thus improving the reliability of analyses, dashboards and AI models.
***
## Create a quality rule
{% hint style="warning" %}
* All members of a project can manage the rules of a Dataset created in the project
* Only Platform Managers can manage the quality rules of a Dataset in the Catalog
{% endhint %}
To create a quality rule on a dataset:
1. From the Dataset page, go to the **Quality**
2. Click **Create** tab to open the creation form
3. Fill in the **information** for the rule
* Rule name (required). Unique.
* Rule description (optional)
4. Configure the rule
* **SQL Query** : Enter a SQL query defining the rule. The dataset schema is displayed to make it easier to write the query.
* **Alert level** : Critical**,** Major**,** Minor
5. **Test the query** then Create.\
To validate the creation, the query must be tested to confirm that it works. If the test fails, a specific error message is displayed. The query must respect the rules:
* The query must contain a `SELECT` and end with **;**
* The query must use the Dataset from which the rule was created
* The query can return a boolean, numeric (integer or decimal), text, or date value.
**Example SQL query**
```sql
/* The request will return true only if all values in the column column_name are email addresses */
SELECT COUNT(*) = 0 AS result FROM project.table_name WHERE NOT column_name RLIKE '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$';
```
You can click the :copy: button to copy the name of the Dataset's unique identifier.
### Use a rule template
SQL query templates are available to help create rules. During creation, select a template from the list then click **`Apply template`** to pre-fill the SQL editing area
#### Template list
* The number of rows in the dataset is greater than or equal to
* No duplicate row in the dataset
* The values in the column are all different
* Uniqueness of values across multiple columns
* The values in the column are all between
* The distribution of the values in the column is the same for all values
* The values in the column are all in 'email' format
* The values in the column are exclusively in the list
* The minimum value of the column is greater than
* The maximum value of the column is less than
* The median value of the column is equal to
* The average value of the column is equal to
* The number of non-null values in the column is greater than
* The number of non-null values in the column is greater than (%)
* The number of distinct values in the column is equal to
#### Example of using a template
When the template is chosen and applied, a rule to complete appears:
```sql
/* the query will return true only if values of col_x are not in the list */
SELECT COUNT(*) = 0 AS result FROM {{ table_name }} WHERE {{ col_x }} NOT IN ('{{ val1 }}', '{{ val2 }}')`
```
You need to fill in the fields and remove the braces:
* `{{ col_x }}` : to be replaced by the column name. Example: *textile\_order*
* `{{ table_name }}` : to be replaced by the Dataset's Unique Identifier. Example: *project.table\_name*
* `'{{ val1 }}'` : to be replaced by the desired values. Example: *'Silk'*
### Configure an alert threshold
Depending on the return type of your query, you can define up to 3 alert thresholds with distinct severity levels (Critical, Major, Minor).
After testing your query, the platform automatically detects the return type and displays the available operators. You can also select the type manually.
For each threshold:
1. Choose an **operator** suited to the return type
2. Enter the **comparison value**
3. Associate an **alert level** (Critical, Major or Minor)
4. Combine the conditions with **AND / OR** if needed
{% hint style="info" %}
If no condition is met, no alert is generated.
{% endhint %}
#### Numeric type (integer or decimal)
Available operators: less than, greater than, equal to, less than or equal to, greater than or equal to, not equal to.
**Example** : count rows with negative revenue and trigger an alert according to the number detected:
```sql
SELECT COUNT(*) AS result FROM project.dataset WHERE column < 0;
```
| Condition | Level |
| ---------- | -------- |
| Value > 10 | Critical |
| Value > 0 | Major |
#### Text type
Available operators: equal to, not equal to, contains, is in (list of values), is null, is not null.
**Example** : check that the categories belong to a list of expected values:
```sql
SELECT MAX(category) AS result FROM project.dataset WHERE category NOT IN ('cat1', 'cat2', 'cat3', 'cat4');
```
| Condition | Level |
| ----------------- | -------- |
| Value is not null | Critical |
#### Date type
Available operators: equal to, not equal to, less than, less than or equal to, greater than, greater than or equal to, between, not between, strictly between, not strictly between.
**Example** : check that the data is recent enough:
```sql
SELECT MAX(sale_date) AS result FROM project.dataset;
```
| Condition | Level |
| ----------------------- | -------- |
| Value before 2023-01-01 | Critical |
| Value before 2024-01-01 | Major |
#### Extended Boolean type
You can associate distinct alert levels with the results `true` and `false`.
**Example** : check that there are no duplicates on a sales identifier:
```sql
SELECT COUNT(*) = COUNT(DISTINCT sale_id) AS result FROM project.dataset;
```
| Condition | Level |
| ------------- | -------- |
| Value = false | Critical |
## Monitor quality
Before its first run, the rule has the status `Not run`
**On every successful refresh** of a dataset, quality rules are run automatically.
Rules are not run if the refresh status is Failed or Ignored.
### Detail at the Dataset level
All **rules** and the **alert status** of each, from their latest run, are listed in the Dataset page's Quality tab.
Rules can be filtered by **alert level** by clicking the corresponding box.
By clicking on a rule, you can see the details of the **query** underlying and the associated **alert level** associated.
**Alerts from rule execution**
Execution results are recorded and can be viewed. Each execution can return an alert:
* No alert : no anomaly detected.
* Critical, Major, Minor : an anomaly is detected with an alert level according to the rule's metadata
* Not run **:** the rule has never been run yet
## Edit a quality rule
An existing rule can be modified without having to delete and recreate it.
1. From the Dataset page, tab **Quality**
2. Click the **Edit** icon to the right of the rule to edit
3. Modify the desired items: name, description, SQL query, alert thresholds
4. Test the query then click **Save**
{% hint style="info" %}
Changes take effect at the dataset's next refresh.
{% endhint %}
## Global consolidated view
The **Quality** tab at the global navigation level offers a consolidated, personalized dashboard that brings together all the datasets you have access to, from your projects or the Catalog, as well as those you own. This lets you monitor effectively at a 360° view without multiplying clicks.
By default, you view the data for all the Datasets you can list through your Projects and the catalog.
#### Existing indicators
* Total number of accessible datasets
* % of datasets with configured quality rules
* Total number of quality rules
* % and number of compliant datasets (no alerts at the last calculation)
* % and number of non-compliant datasets (at least one alert detected)
* Distribution of alerts by severity
* Detailed list of datasets with at least one rule defined. You can click on :eye: to see more details about the rules.
* Dataset name
* Project or catalog where the dataset was created if you have access to the project, otherwise the catalog
* Alerts raised
* Date of the last rule calculation
#### Available filters
Enabling filters updates all dashboard data
You can enable a filter to get a more focused view:
* **My subscriptions:** those you are subscribed to
* **My datasets** : those for which you are identified as the owner
* **Projects** : all datasets created or added to the projects you have access to
* **Catalog** : all datasets in the catalog
To return to the global view, click the active filter.
**Advanced filters**
* By alert level
* By classification (bronze / silver / gold)
* By sensitivity (internal / sensitive / restricted)
* By project *available only in the general view and My Datasets*
## Consolidated view - project level
Alerts for the project's Datasets with Data Quality rules are listed in the **Quality** of the **Library**.
Datasets can be filtered by Status or Origin.
## Subscribe to Data Quality alerts
When a quality rule is created, an invitation to subscribe is offered if you are not already subscribed to the relevant dataset.
You can also manage your subscriptions:
1. From the list of Datasets in your project or the Catalog
2. Click :bell: `Subscribe` and choose Quality and/or Refresh
You will then receive an in-app and email notification when an alert is raised or a refresh fails. You can find all your subscriptions in the :gear: `Notification management`.
## Explore the rules
From the Dataset page, Quality tab:
* Click the :info:to display the rule details
## Delete a quality rule
{% hint style="danger" %}
Deletion is permanent.
{% endhint %}
Deleting a quality rule permanently removes a rule from a dataset
1. Go to the Dataset page
2. In the list of quality rules, click the :circle-info: to the right of the rule to delete then the option `Delete` or on the :circle-trash:
3. Click `Confirm` to validate deletion.