Check the quality of your tables with data quality rules
Define quality rules on a Lakehouse Manager table, run them with a Data Processing Engine action and follow the quality status of the table
Objective
Data quality rules describe what valid data looks like in a table: a column without nulls, values within a range, a table refreshed recently. This guide explains how to define rules on a table, how they are evaluated, and how to read the results.
The flow has three steps:
- You define rules on a table. A rule belongs to the table, so you can define it before the table is built, and it is kept when the table is rebuilt.
- The Data quality action evaluates the active rules on the table's current snapshot and sends a report to the Lakehouse Manager. For large tables, use Data quality (Spark). You can also send results from your own code with the Python SDK.
- Each report updates the quality status of the table, which rolls up the latest result of every rule.
Requirements
- A Lakehouse Manager table.
- Permissions on the Quality Rule resource to read or manage rules, and on the Quality Report resource to read reports. See Roles and conditions.
Define quality rules
Define rules in the Quality tab of a table: click + Add rule. A table holds up to 100 rules. Some rule types check the values of one or more columns, others check the table's metadata, such as its row count or the time since data was last added.
Every rule needs a critical level, and can also have a warning level (see Thresholds). Its job impact says whether a critical result fails the Data quality job.
Rule types
The pass rate is the share of rows that pass the check.
Parameters by type:
range:min,max, or both. Bounds are numbers for a number column and ISO dates for a date column.min_includedandmax_includedsay whether each bound is accepted (both by default).uniqueness: by default, rows with a null in the checked columns are left out. Setnulls_as_valuetotrueto count null as a value.custom_sql:expression, one boolean expression over the table's columns, in the Trino SQL dialect. Example:amount >= 0 AND status IN ('paid', 'refunded').freshness:unit,minutes,hours(default) ordays. The age is the time since data was last added to the table.volume: no parameter. The bounds are set in the thresholds.
freshness and volume are read from the table's metadata, without scanning the data. The Lakehouse Manager evaluates them itself, each time a report is received and each time the status is read, so they do not need a Data quality run.
Thresholds
The threshold fields depend on the rule type:
The warning level is optional: without it, a result is either OK or Critical.
Job impact
The Job impact of a rule decides what a Critical result does to the Data quality job:
- Report only (default): a Critical result is reported. The DPE job goes on.
- Fail the job: a Critical result fails the DPE job, unless the action sets Critical Results Allowed.
The job impact does not change the result of the rule, only the job. The Data quality action can also count the critical and warning results itself, see When the job fails.
Create and manage rules with the API
Rules can also be managed with the Lakehouse Manager API (see Data Platform APIs) or with the Python SDK:
In the API, the job impact is the severity field: optional for Report only, mandatory for Fail the job. The thresholds are the thresholds object, with a warning and a critical level. For a volume rule each level is a {"min", "max"} range: warning is the expected range and critical the critical limits. For a uniqueness rule the API stores the share of unique rows, not the share of duplicates shown in the interface: 2% of duplicates is 98.
A rule on the amount column, Warning when below 99%, Critical when below 95%, that fails the job:
A rule that checks that data was added in the last day:
When you manage rules:
- A new rule is active. An inactive rule is kept and listed, but it is never evaluated and does not count in the status of the table. Use it to pause a rule without losing its history.
- Every change increments the rule's
version. Send theversionyou read with a change: if someone changed the rule in between, your change is refused withQUALITY_RULE_VERSION_CONFLICTinstead of overwriting theirs. - Check a rule before saving it: the validate route returns the rule as it would be stored, or the reason it would be refused, for example a column that does not exist or thresholds in the wrong order.
Quality reports and status
Rule outcomes
Each evaluation of a rule gives one outcome:
Read a report
A run of the Data quality action sends one report for the table, with one entry per rule. Each entry gives the outcome, the metric, and for pass-rate rules the number of rows evaluated and the number of rows failing the rule.
The report also says:
- which engine evaluated the rules, and which snapshot of the table it read;
- what started the run: a schedule, or a user;
- when the table has grown too large for the action that ran, the package to move to (
suggested_package), for exampledata-quality-pysparkfor the Data quality (Spark) action.
The Lakehouse Manager keeps the last 10 runs of each rule. Each run stores the rule as it was when evaluated, so you can see when a threshold or a parameter changed between two runs.
Table quality status
Each report received, from a Data quality run or from a Custom action, also sets the quality status of the table: one outcome for the whole table, shown with the table in the Lakehouse Manager. It rolls up the latest outcome of each active rule: the worst outcome among ok, warning and critical. Rules without a result do not lower it. When no rule has a result, the status is:
pendingwhen every active rule is waiting for its first run;disabledwhen every rule is inactive;not_evaluatedotherwise, also when the table has no rule.
The job impact of a rule does not enter the status: a Report only rule ending critical makes the table critical, even though it does not fail the Data quality job. The table status says what the data looks like; the job result says whether the run should stop a workflow.
The status is also updated when a rule is created, changed, switched off or deleted, so it always reflects the current rules of the table.
Read it with GET /dwh/v4/logical/objects/{tableId}/quality-status. The answer has one entry per rule, with its outcome, metric and date, and where the result comes from:
live: afreshnessorvolumerule, evaluated at the time of the request;stored: the latest run of the rule;none: the rule has not run yet, or is inactive. An inactive rule is listed with the outcomedisabled.
Go further
- Check data quality after each load
- Check table quality with the Data quality action
- Manage quality rules and send reports with the Python SDK
For training or technical assistance implementing our solutions, contact your sales representative or visit our Professional Services page to request a quote and have your project analysed by our experts.
If you need support with your OVHcloud services, create a request in our Help Centre.
Join our community of users.