Set data quality rules-Data Management-阿里云帮助中心

You can use the data quality feature of Data Management (DMS) to define custom quality rules for tables and columns. By integrating these rules with audit tasks in Task Orchestration, you can automate data quality checks to ensure data consistency, uniqueness, and completeness, improving the quality of your data for analysis and development.

Prerequisites

The target instance must be in Security Collaboration mode. For more information, see Control modes.
You must have one of the following permissions:
- You have a system role of Admin, DBA, or Schema Read-Only. For more information, see System roles.
- You are the instance owner or instance DBA of the target instance. For more information, see Edit instance information.
- You have the query, change, or export permission for the table or the database that contains the table. For more information, see View my permissions.

Background information

As the need for data analysis and development grows, ensuring data quality becomes critical. Data often comes from diverse sources, has complex structures, is large in volume, and has interdependencies. Maintaining high data quality is essential to ensure that your data is correct, meets expectations, and produces reliable analysis results. For example, before you start a data analysis task, you should verify that the quality of the source data meets your requirements to ensure the task runs smoothly.

Key concepts

Quality rule: A rule for a table or column that ensures data meets specific expectations. For example, you can define a rule that the number of rows in a table must be a fixed value, or that the maximum value of a column cannot exceed 100.
Audit task: A task that monitors and verifies if data complies with defined quality rules. After you set the quality rules and a scheduling cycle, the system automatically checks the data quality and generates a report.

Procedure

Log in to DMS 5.0.
Go to the table details page. You can use one of the following methods:
- From the SQL Console:
  1. In the top navigation bar, choose SQL Console > SQL Console.
  2. In the Select a database dialog box, search for and select the target database, and then click OK.
  3. In the upper-right corner, click the table list icon .
  4. In the Actions column, choose More > View Table Details to go to the table details page.
- By searching:
  1. Click the Home tab. In the search box in the center of the page, enter the name of the target table.
  2. Select the Table tab, and then click Details in the rightmost column of the target table to go to the table details page.
Click the Data quality tab.

Click Create Rule. In the pane that appears on the right, configure the parameters.

Parameter	Description	Example
Rule Name	The name of the rule.	Maximum column value is 100
Rule Type	The type of the rule. Valid options: Table Rule: Creates a table-level rule. For example, you can check whether the number of rows in a table meets your expectations. Column Rule: Creates a column-level rule. For example, you can check the number of null values in a column.	Column rule
Rule Template	Select a built-in rule template. For more information, see Quality rule templates.	Maximum Value in a Column
Column name	Select the target column for the rule. This parameter is displayed only when Rule Type is set to Column Rule.	`columns_test`
Comparison Method	Select a method to compare the actual value with the Expected Value. Supported methods: Greater, Greater than or equal, Equal, Is less than or equal, Less, and Not equal.	Less than or equal
Expected Value	Enter the expected target value.	100

Click OK.
A quality rule is Enabled by default when created.
Note
You can also perform the following operations on the rule in the Actions column:
- Click Disabled to disable the rule. After a rule is disabled, you cannot use it to create a new audit task.
  
  If the rule is already used in an audit task, the operation fails. You must first delete the audit task and then try again.
- Choose More > Edit to modify the quality rule.
- Choose More > Logs to view the historical versions of the rule.
In the Actions column for the target rule, choose More > New Task Flow.
You are redirected to the Task Orchestration page. There, configure the Audit Task node and publish the task flow.

Note
For more information, see Overview.

After you configure a scheduling cycle and publish the task flow, the system automatically checks the data according to the schedule and the quality rules.

Quality rule templates

Rule Type	Rule Template
Table Rule	Number of Rows in a Table (Fixed Value): Checks whether the current number of rows in the table meets the expected value. For example, you can configure a rule that the number of rows must be less than 100,000.
Column Rule	Maximum Value in a Column: Checks whether the maximum value in a column meets your expectations. For example, you can specify that the maximum value of a primary key column cannot exceed 2,147,483,647 (2³¹ - 1). Minimum Value in a Column: Checks whether the minimum value in a column meets your expectations. For example, you can specify that the minimum value of a field for spare parts cannot be less than 3. Number of Strings That Match an Expression with Wildcards (Fixed Value): Checks whether the number of values that match a specified format in a column meets your expectations. For example, you can specify that the number of values starting with "a" must be less than 10. Number of Strings That Match a Regular Expression (Fixed Value): Checks whether the number of values that match a specified format in a column meets your expectations. For example, you can specify that in a column that stores email addresses, the number of values matching the regular expression must be greater than 100. Number of Null Values in a Column (Fixed Value): Checks whether the number of null values in a column meets your expectations. For example, you can configure the number of null values to be less than or equal to 0. Number of Unique Values in a Column (Fixed Value): Checks whether the number of unique values in a column meets your expectations. For example, you can specify that the number of unique values must be greater than or equal to 100 to ensure that at least 100 different values are present. Number of Duplicate Values in a Column (Fixed Value): Checks whether the number of duplicate values in a column meets your expectations. For example, you can specify that the number of duplicate values must be less than 100 to ensure that the number of occurrences of duplicate values does not exceed 100.