Create and manage quality rule templates

Permission description

• Super administrators and quality administrators can create, clone, edit, and delete rule templates.

• All users can access and use rule templates.

• Operation permissions vary for different objects. For more information, see Rule template operation permissions.

Create a rule template

1. On the Dataphin home page, choose Administration > Data Quality from the top menu bar.

2. In the left navigation pane, click Quality Monitoring > Rule Templates. On the Rule Templates page, select the Data Table tab and click Create Rule Template.

3. In the Create Rule Template dialog box, configure the following parameters.

   Parameter	Description
   Basic Information
   Template Name	The template name. Maximum 64 characters.
   Template Type	The template category, used to filter templates during rule configuration. Options: Validity, Consistency, Timeliness, and Sql. Validity includes Standard Reference Table Validation (requires Data Standard module activation), Reference Table Validation, Column Format Validation, Column Value Domain Validation, Column Length Validation. Consistency includes Single Field Business Logic Consistency Comparison, Columns In Two Tables Statistical Consistency Validation, Columns In Two Tables Processing Logic Consistency Validation, Columns Statistical Consistency Validation, Cross-Source Columns Statistical Consistency Validation. Timeliness includes Time Interval Comparison, Time Interval Comparison In Two Tables, Time Comparison With Expression. Sql includes Custom Statistic Validation and Custom Detail Value Validation. For scenarios of different template types, see template type description.
   Data Source Type	The data source type that the template supports. Specifying the type prevents rule failures caused by syntax differences across data sources. Options: General: Validation logic is data source-independent. All data source types use the same validation statement, such as the integrity template. Specific Type: Rule definitions apply only to a selected data source type. Quality rules can reference this template only for monitored objects of the specified type.
   Description	The template description. Maximum 128 characters.
   Template Configuration
   Consistency	When Template Type is Consistency, configure the following parameters based on the selected template. Single Table Field Business Logic Consistency Comparison: Detection Expression: Enter an SQL expression function. Example: ${total_sales}=${unit_price}*${sales_volume}. Note Detection expressions support functions. Ensure the database supports the function at execution time. Validates business logic across multiple fields. For example, to verify that total sales = unit price × sales volume, use ${total_sales}=${unit_price}*${sales_volume}. Variables such as ${total_sales} represent actual table fields and are replaced at execution time. Columns In Two Tables Statistical Consistency Validation/Columns Statistical Consistency Validation: Statistical Method: Count field groups, maximum values, repetition rates, and more. Supported methods are listed in data table parameter configuration. Two Table Field Business Logic Consistency Comparison: Business Logic: Enter an SQL expression function. Example: ${T1.total_sales}=${T2.unit_price}*${T2.sales_volume}. Note Validates business logic across multiple fields. For example, to verify that total sales = unit price × sales volume, use ${T1.total_sales}=${T2.unit_price}*${T2.sales_volume}. Variables such as ${T1.total_sales} represent actual table fields and are replaced at execution time. A maximum of two tables are supported. Cross-Source Columns Statistical Consistency Validation: Statistical Method: Count field groups, maximum values, repetition rates, and more. Supported methods are listed in data table parameter configuration. Data Source Type: Select the data source type of the physical table, including MaxCompute, MySQL, Oracle, Microsoft SQL Server, PostgreSQL, SAP HANA, AnalyticDB for PostgreSQL, ClickHouse, IBM DB2, Hologres, DM (Dameng), StarRocks. Datasource: Select the data source to which the physical table belongs. Select Comparison Table: Select the comparison table in the physical table and the fields to compare in the physical table.
   Validity	When Template Type is Validity, configure the following parameters based on the selected template. Standard Reference Table Validation (requires Data Standard module activation): Reference Tables: Select the published status code table under the data standard. To create a code table, see create and manage standard codes (code tables). Code Table Reference: Compare based on the reference value and field value selected here. Options include Code Value, Code Name, Code English Name. Code Table Reference Comparison: Data Source Type: Select the data source type. Datasource: Select the data source corresponding to the data source type. Note Cross-data source comparisons read up to 1,000 code table records. For best performance, keep the code table and the inspected table in the same data source. Cross-data source comparison fields are automatically converted to strings. Some special field types may have abnormal situations. To avoid conversion issues, use the same field types in both the code table and the validation table. Reference Tables: Select the target code table. Code Table Name: Enter the code table name as a prompt when configuring quality rules. Code Table Reference Field: Select the corresponding reference field in the code table. Note Code Table Reference Table checks whether a field value exists in the code table. Select a comparison field. For example, verify whether all user IDs in the user details table belong to registered users. Code Table Filter Condition: Set the filter condition for the partitioned table to filter data. Field Format Validation: Content Recognition Form: Options include Detection Expression, Regular Expression, String Matching (%), Starts With, Contains, Ends With. Expressions are used to match data that meets the rules. Content Detection Pattern - Detection Expression: Enter an SQL expression function. Example: ${column}>0, where ${column} is a built-in parameter. Content Detection Pattern - Regular Expression: Enter a regular expression in the input box. For example, to match all names that contain "test", define the regular expression as .*test.*. Content Recognition Form-String Matching (%): Standard like expression, using % to represent a wildcard character. For example, to match data starting with "a", enter "a%". Content Recognition Form-Starts With: Enter the string to be matched, and it will automatically append % at the end according to the pattern. For example: "a%". Content Recognition Form-Contains: Enter the string to be matched, and it will automatically append % at the beginning and end according to the pattern. For example: "%a%". Content Recognition Form-Ends With: Enter the string to be matched, and it will automatically append % at the beginning according to the pattern. For example: "%a". Note Detection expressions support functions. Ensure the database supports the function at execution time. Field Value Domain Validation: Value Domain Type: Choose based on actual business needs. Text: Validates the validity of text. Make sure that the field to be validated is of the text type. You must set the Range. You can select Enumeration or Interval. Example of validation using an enumeration: To compare "Zhang San" and "Li Si", the generated validation SQL statement is x in ("Zhang San", "Li Si"). Numeric: Validates the validity of numbers. Make sure that the field to be validated is of a numeric type. You must set the Range. You can select Enumeration or Interval. Example of validation using an enumeration: To compare 3, 6, and 9, the generated validation SQL statement is x in (3, 6, 9). Date: Used to validate the validity of dates. Ensure that the validation field is of date type (date). Timestamp: Used to validate the validity of timestamps. Ensure that the validation field is of timestamp type (timestamp). Custom: Used to validate multiple formats and supports using functions. The content entered will be directly executed on the data source. You need to set the Value Domain Range, supporting Enumeration or Interval Setting. Example of validation using an enumeration: To compare 3, 6, and 9, the generated validation SQL statement is x in (3, 6, 9). Example of validation using an interval: The range is from the minimum value y1 to the maximum value y2. If the field to be compared is x, the final validation SQL statement is x >= y1 and x <= y2. Note Custom field formats, such as 10 and "10", may have different execution results in different data sources. Ensure that the functions used are valid in the data source for quality validation. Column Length Validation: Field Length: Set the length range. Supports Enumeration or Interval Setting. Example of validation using an enumeration: To compare 3, 6, and 9, the generated validation SQL statement is x in (3, 6, 9). Example of validation using an interval: The range is from the minimum value y1 to the maximum value y2. If the field to be compared is x, the final validation SQL statement is x >= y1 and x <= y2.
   Timeliness	When Template Type is Timeliness-Time Interval Comparison, Timeliness-two Table Time Field Comparison, or Timeliness-time Function Comparison, configure the following parameters. Validation Item: Define an expression based on the validation field. The built-in parameter for the validation item is ${column}. Comparison Item: The expression for time comparison. The built-in parameters are ${column} and ${bizdate}. Define an expression based on the comparison field. ${column} is the built-in parameter for the comparison item. Examples: ${column} and substr(${column}). If you select ${bizdate} for the date function comparison, it is recognized as the data timestamp. Time Tolerance: The threshold for the difference between validation and comparison items, truncated (not rounded) to a whole number. For example, to require same-day delivery, set Validation Item - Comparison Item to less than 1 day.
   Custom SQL	When Template Type is Sql, configure the following parameters based on the selected template. Custom Statistic Validation: Define the metric calculation logic. The result must be a number, such as the daily sales amount. Sql: Custom SQL metric statistical logic. Code examples are as follows: Single Table Query select sum(${t1.c1}) from ${t1} where ds=${bizdate}; Subquery select sum(${t1.c1}) from (select ${t1.c1} from ${t1} where ds=${bizdate}) a; Multi-Table Query select sum(${t1.c1}) from ${t1} join ${t2} on $[t1.id]=$[t2.id] where $[t1.ds]=${bizdate}; Custom Detail Value Validation: Define normal and abnormal data through custom SQL. Configure total row count SQL, abnormal row count SQL, and abnormal data SQL. Total Row Count SQL: Required. Define the SQL logic for counting total rows. The result must be a number. This enables metrics such as normal rate and abnormal rate. Example: select count(*) from ${t1} where ds=${bizdate}; Abnormal Row Count SQL: Required. Define the SQL logic for counting abnormal rows. The result must be a number and must include an abnormal data condition. After filling in, you can count the abnormal row count. Combined with the total row count, this enables the abnormal rate metric. Example: select count(*) from ${t1} where ds=${bizdate} and ${t1.c1}<0; Abnormal Data SQL: Optional. Define the SQL logic for identifying abnormal data (single field). This enables the abnormal archiving feature. Example: select ${t1.c1} as dataphin_quality_error_data from ${t1} where ds=${bizdate} and ${t1.c1}<0; Parse Variables: Click Parse Variables to extract variable fields from the custom SQL. You can add a display name and description (up to 128 characters) for each variable. The description appears as a tooltip next to the variable name during rule configuration to help you enter the correct value. The variable `t1` defaults to the current table being validated and is not parsed. Note SQL supports multi-table queries and subqueries. Supports fixed table names and field names. Dataphin tables support referencing physical tables using project names or referencing logical tables using section names. Supports using variables to replace table names or field names. Specify a table: Click Insert Table Name 1 in the editor to quickly enter a table name variable from ${t1} to ${t5}. You can specify up to five data tables. The variables are not case-sensitive. Specify a field of a table: Click Insert Field Name 1 Of Table 1 Only in the editor to quickly enter the field name variable of the table, such as ${t1.c1}. Specify a table and a field: Click Insert Table Name 1 + Field Name 1 in the editor to quickly enter the table name and field name variable, such as $[t1.c1]. Only the above variable formats are supported. Other formats (such as ${t6}, ${table1}, ${a.b}) are parsed as field variables of the validation table. Avoid unsupported variable expressions.
   Parameter Checking	SET parameters for the quality rule. Maximum 1,024 characters. Example: set hive.execution.engine=mr. Note Parameter settings apply only to MaxCompute and Hive data table quality rules. Other data table types ignore this configuration. Applies to all quality rules created from this template. View the final SQL statement in Preview SQL.

4. Click Preview SQL to check the SQL preview of the rule template.

   Note

   Use variable placeholders for any unfilled configuration fields in the template, such as ${table1}, ${column1}.

5. To finalize the creation, click OK.

View rule template list

What to do next

After creating a rule template, configure quality rules as described in create quality rules.