> 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/v4.7-en/data-governance/qualite-des-donnees.md).
# Data quality
Data Quality brings together all the rules and processes that ensure data compliance and reliability.
Data Quality (data quality) ensures that datasets comply with **specific validation criteria**.
It helps detect **anomalies** and correct non-compliances, thereby improving the reliability of analyses and AI models.
***
## Create a quality rule
{% hint style="warning" %}
* All project members can manage the rules of a Dataset created in the project
* Only Platform Managers can manage the quality rules of a Catalog Dataset
{% endhint %}
To create a quality rule on a dataset:
1. From the Dataset page, go to the **Quality**
2. Click on **Create** 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 an SQL query defining the rule. The dataset schema is displayed to make writing the query easier.
* **Alert level** : Critical**,** Major**,** Minor
5. **Test the query** then Create.\
To validate creation, the query must be tested to confirm it works. If the test fails, a specific error message is displayed. The query must meet the following rules:
* The query must contain a `SELECT` and end with **;**
* The query must use the Dataset from which the rule was created
* The query may return a boolean, numeric (integer or decimal), text, or date value.
**SQL query example**
```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 button :copy: to copy the name of the Dataset's unique identifier.
### Use a rule template
SQL query templates are available to make rule creation easier. When creating, select a template from the list then click on **`Apply template`** to pre-fill the SQL editor area
#### Template list
* The number of rows in the dataset is greater than or equal to
* No duplicate rows 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 among the column values 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
* 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 request will return true only if value of col_x are not in list */
SELECT COUNT(*) = 0 AS result FROM {{ table_name }} WHERE {{ col_x }} NOT IN ('{{ val1 }}', '{{ val2 }}')`
```
You must fill in the fields and remove the braces :
* `{{ col_x }}` : replace with the column name. Example : *textile\_order*
* `{{ table_name }}` : replace with the Dataset's Unique Identifier. Example : *project.table\_name*
* `'{{ val1 }}'` : replace with 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 conditions with **AND / OR** if necessary
{% 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, different from.
**Example** : count rows with negative revenue and trigger an alert based on 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, different from, 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, different from, 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 sale ID:
```sql
SELECT COUNT(*) = COUNT(DISTINCT sale_id) AS result FROM project.dataset;
```
| Condition | Level |
| ------------- | -------- |
| Value = false | Critical |
## Monitor quality
Before its first execution, the rule has status `Not executed`
**On each successful refresh** of a dataset, the quality rules are executed automatically.
The rules are not executed if the refresh has Failed or Ignored status.
### Details at the Dataset level
All **rules** and the **alert status** for each one, from their latest execution, are listed in the Quality tab of the Dataset page.
It is possible to filter the rules by **alert level** by clicking the corresponding panel.
By clicking on a rule, you can see the details of the **query** underlying and the **alert** associated.
**Alerts resulting from the rule execution**
Execution results are saved 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 executed **:** the rule has never been executed yet
## Edit a quality rule
It is possible to modify an existing rule without deleting and recreating it.
1. From the Dataset page, tab **Quality**
2. Click the icon **Edit** to the right of the rule to edit
3. Modify the desired elements: name, description, SQL query, alert thresholds
4. Test the query then click on **Save**
{% hint style="info" %}
The changes take effect at the next dataset refresh.
{% endhint %}
## Global consolidated view
The **Quality** tab in the global navigation provides a consolidated, personalized dashboard bringing together all the datasets you have access to, from your projects or the Catalog, as well as those you own. This lets you monitor everything effectively 360° without extra clicks.
By default, you view the data for all Datasets you can list through your Projects and the catalog.
#### Available indicators
* Total number of accessible datasets
* % of datasets with configured quality rules
* Total number of quality rules
* % and number of compliant datasets (no alert in the latest calculation)
* % and number of non-compliant datasets (at least one alert detected)
* Distribution of alerts by severity
* Detailed list of datasets having 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 catalog
* Reported alerts
* Date of the last rule calculation
#### Available filters
Activating filters updates all dashboard data
You can activate a filter for a more targeted view:
* **My subscriptions:** those you are subscribed to
* **My datasets** : those for which you are identified as responsible
* **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**.
It is possible to filter Datasets by Status or Origin.
## Subscribe to Data Quality alerts
When creating a quality rule, a subscription prompt is offered if you are not already subscribed to the dataset concerned.
You can also manage your subscriptions:
1. From the list of Datasets in your project or in the Catalog
2. Click on :bell: `Subscribe` and choose Quality and/or Refresh
You will then receive an in-app and email notification when an alert is raised or when a refresh fails. You can find all your subscriptions in the :gear: `Notification management`.
## Explore rules
From the Dataset page, Quality tab:
* Click on 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 on the :circle-info: to the right of the rule to delete then option `Delete` or on the :circle-trash:
3. Click on `Confirm` to confirm the deletion.
