Quality Rule Parameters

Template type

Description

Completeness/Uniqueness

Completeness - Null value check/Empty string check

Uniqueness - Field uniqueness check/Field group count check/Field duplicate value count check:

• Field to check: Select the field from the physical table that you want to check.

• Data filtering for the table to check: Disabled by default. If enabled, you can configure filter conditions, such as partition or standard data filtering. These conditions are appended directly to the validation SQL. For tables that require partition filtering, we recommend configuring a partition expression in the scheduling configuration. This sets the partition as the minimum granularity for the quality report. Enter the data filter conditions. For example:

  id = 12 -- For a single table

  T1.id=12 and T2.name = "Zhang San" -- For two tables

Timeliness

• Time function comparison:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Comparison item: A time comparison expression that can include the built-in parameters ${column} and ${bizdate}.

    • Define an expression based on the comparison field. ${column} is a built-in parameter for the comparison item. For example: ${column} or substr(${column}).

    • If you use ${bizdate} for a date function comparison, the system treats it as the business date.

  • Time difference: You can select either Verification field - Comparison field or Comparison field - Verification field. The system uses the integer part of the result without rounding. For example, if the shipping date and order date must be on the same day, you can set Verification field - Comparison field to less than 1 day. As shown in the figure, the settings are: Verification field - Comparison field >= 1 day and Verification field - Comparison field < 1 day.

    

• Single-table field comparison:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Comparison field: Select a field from the physical table to use for comparison.

  • Time Difference: You can select either Verification field - Comparison field or Comparison field - Verification field. The value is truncated to an integer without rounding. For example, if the shipping date and the order date must be on the same day, you can set Verification field - Comparison field to less than 1 day. As configured in the figure, Verification field - Comparison field >= 1 day and Verification field - Comparison field < 1 day.

    

• Two-table time field comparison:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Select comparison table: Select the comparison table and a field within that table to use for comparison.

  • Join expression: Includes the built-in table parameters T1 and T2, where T1 is the table to check and T2 is the comparison table. For example: T1.id = T2.id.

  • Time difference: You can select Verification field - Comparison field or Comparison field - Verification field. The integer part of the result is used, and the value is not rounded. For example, if the shipping date and the order date must be on the same day, you can set Verification field - Comparison field to less than 1 day. Other example conditions include Verification field - Comparison field >= 1 day or Verification field - Comparison field < 1 day.

    

Validity

• Field format check:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Content matching method: Select a method for matching data. Options include Expression, regular expression, string matching (%), starts with, contains, and ends with.

  • Content matching method - Expression: Enter an SQL expression. For example, ${column1}>0. ${column} is a built-in parameter.

  • Content matching method - Regular expression: Enter a regular expression or select a built-in one, such as mobile phone number, landline number, national ID number, email address, and bank card number.

  • Content matching method - String matching (%): A standard LIKE expression that uses % as a wildcard. For example, to match data that starts with 'a', enter a%.

  • Content matching method - starts with: Enter the prefix. A % wildcard is automatically appended. For example, entering a matches values that start with 'a'.

  • Content matching method - contains: Enter the substring. % wildcards are automatically added to the beginning and end. For example, entering a matches values that contain 'a'.

  • Content matching method - ends with: Enter the suffix. A % wildcard is automatically prepended. For example, entering a matches values that end with 'a'.

• Field length check:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Field length: Validates the field length. You can set an enumeration or a range. Example of an enumeration check: To compare against 3, 6, and 9, the generated SQL is x in (3, 6, 9).

• Field value range check:

  • Verification field, Verification table, and Data filtering: For more information, see Completeness/Uniqueness.

  • Value range type: Select a type based on your business requirements.

    • Text: Validates text fields. The field to check must be a text type. You must set the Value range, which supports an enumeration or a range. Example of an enumeration check: To compare against "Zhang San" and "Li Si", the generated SQL is x in ("Zhang San", "Li Si").

    • Number: Validates numeric fields. The field to check must be a numeric type. You must set the Value range, which supports an enumeration or a range. Example of an enumeration check: To compare against 3, 6, and 9, the generated SQL is x in (3, 6, 9).

    • Date: Validates date fields. The field to check must be a date type. You must set the Date format, which supports Year, Year-Month, Year-Month-Day, and Year-Month-Day-Hour-Minute-Second. For example, a year range is 2021~2022, and a year-month range is 2022-01~2022-02.

    • Timestamp: Verifies the validity of a timestamp. You must ensure that the verification field is of the timestamp type.

    • Custom: Allows you to check multiple formats or use functions. The entered content is sent directly to the data source for execution.

      You must set the Value range, which supports an enumeration or a range.

      Example of an enumeration check: To compare against 3, 6, and 9, the generated SQL is x in (3, 6, 9).

      Example of validation for a range setting: The value range is from a minimum value y1 to a maximum value y2. If the field to be compared is x, the final validation SQL is x >= y1 and x <= y2.

      Note

      • For custom field formats, values such as 10 and "10" can behave differently across data sources.

      • Ensure the data source supports the functions that you use.

• Code table reference comparison:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Data source type: Select the data source type.

  • Data source: Select a data source of the corresponding type. The system automatically fills in the DB/Schema of the data source, which you can modify. If the data source cannot connect to the Dataphin client, you must configure it manually.

    Note

    • For performance reasons, cross-data-source comparisons read a maximum of 1,000 code table records. We recommend keeping the code table and the checked table in the same data source.

    • Fields in cross-data-source comparisons are automatically converted to the string type. This may cause exceptions for special field types. We recommend using identical field types in both the code table and the field to check.

  • Code table: Select the target code table.

  • Code table name: Enter a name for the code table. This name serves as a prompt during quality rule configuration.

  • Code table reference field: Select the corresponding reference field in the code table.

    Note

    A code table reference check determines whether a field's value exists in the code table. You must select a field for comparison. For example, you can check whether all user IDs in a user details table exist in the registered users table.

  • Code table filter condition: Set filter conditions for the code table if it is partitioned.

• Data standard code table reference comparison (requires the data standard module):

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Code table: Select a code table in the Published state from the data standard module. To create a code table, see Create and manage standard codes (code tables).

  • Code table reference: The system compares the field value with the reference value selected here. Options include Code value, Code name, and Code English name.

Consistency

• Single-table field value consistency comparison:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Comparison field: Select a field from the physical table to use for comparison.

• Single-table field statistical value consistency comparison:

  • Aggregation method: You can aggregate metrics such as field count, field maximum value, and character duplication rate. For more information, see the aggregation method description for stability.

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Comparison field: Select a field from the physical table to use for comparison.

• Single-field business logic consistency comparison:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Recognition expression: Enter an SQL expression. For example, ${column1}>0, where ${column} is a built-in parameter.

    Note

    • Ensure the executing database supports any functions used in the recognition expression.

    • You can check business logic that involves multiple fields. For example, if Total sales = Unit price * Sales volume, you can use ${total_sales}=${unit_price}*${sales_volume} to check for errors in the total sales amount.

• Two-table field value consistency comparison:

  • Field to check, Select comparison table, Join expression, and Data filtering: See Two-table time field comparison in the timeliness section.

  • Join type: Determines how the total, normal, and abnormal row counts are calculated. You can preview the generated SQL to see the exact logic. Supported join types are left join, right join, inner join, and full join.

    • left join: Calculates metrics based on the table to check (the left table).

    • right join: Calculates metrics based on the comparison table (the right table).

    • inner join: Uses only matched data between the two tables to calculate metrics. This is suitable for cases where you need to check only matched data.

    • full join: Uses all data from both tables to calculate metrics. This is suitable for scenarios that require strong consistency between the two tables.

• Two-table field statistical value consistency comparison:

  • Aggregation method: You can aggregate metrics such as field count, field maximum value, and character duplication rate. For more information, see the aggregation method description for stability.

  • Field to check, Select comparison table, and Data filtering for the table to check: See Two-table time field comparison in the timeliness section.

  • Comparison table data filtering: Enter filter conditions. For example: city='beijing'.

• Cross-source two-table field statistical value consistency comparison:

  • Aggregation method: You can aggregate metrics such as field count, field maximum value, and character duplication rate. For more information, see the aggregation method description for stability.

  • Field to check: Select the field to check in the data table.

  • Data source type: Select the data source type for this table. For information about supported data source types, see Data sources supported by Dataphin.

  • Data source: Select the table's data source. The system automatically fills in the DB/Schema of the data source, which you can modify. If it is an external data source, you must configure it manually.

  • Select comparison table: Select the table and a field within that table to use for comparison.

  • Data filtering for the table to check: Enter filter conditions. For example:

    id = 12 -- For a single table

    T1.id=12 and T2.name = "Zhang San" -- For two tables

  • Comparison table data filtering: Enter filter conditions. For example: city='beijing'.

• Two-table field business logic consistency comparison:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Business logic: Enter an SQL expression. For example, ${column1}>0, where ${column} is a built-in parameter.

    Note

    You can check business logic that involves multiple fields. For example, if Total sales = Unit price * Sales volume, you can use ${T1.total_sales}=${T2.unit_price}*${T2.sales_volume} to check for errors in the total sales amount.

Stability

• Table stability check/Table volatility check:

  • Aggregation method: You can aggregate metrics such as field count, field maximum value, and character duplication rate. For more information, see the aggregation method description for stability.

  • Data filtering for the table to check: See the description for completeness/uniqueness.

• Field stability check/Field volatility check:

  • Field to check and Data filtering for the table to check: See the description for completeness/uniqueness.

  • Aggregation method: You can aggregate metrics such as field count, field maximum value, and character duplication rate. For more information, see the aggregation method description for stability.

Custom SQL

• Custom statistical metric check:

  Custom SQL: Supports SELECT statements. The query must include the main table. For example:

  select sum(sale) from tableA where ds=${bizdate};

• Custom data detail check:

  • Total row count SQL: Enter the SQL to calculate the total number of rows. For example: select count(*) from tableA where ds=${bizdate};

  • Abnormal row count SQL: Enter the SQL to calculate the number of abnormal rows. For example: select count(*) from tableA where ds=${bizdate} and age<0;

  • Abnormal data SQL: Enter the SQL to query abnormal data. For example: select ${t1.c1} as dataphin_quality_error_data from ${t1}  where ds=${bizdate} and ${t1.c1}<0;

Template type

Parameter

Description

Completeness

Abnormal Row Count/Normal Row Count/Abnormal Rate/Normal Rate

• Normal row count: The number of unique rows.

• Normal rate: Normal row count / Total row count.

• Abnormal row count: Total row count - Normal row count, that is, the sum where the count is greater than 2.

• Abnormal rate: 1 - Normal rate, or Abnormal row count / Total row count.

  Note

  Unique value: A record that appears only once. This refers to a group with a count of 1 after grouping.

Uniqueness

Abnormal Row Count/Normal Row Count/Abnormal Rate/Normal Rate

• Normal row count: The number of unique rows.

• Normal rate: Normal row count / Total row count.

• Abnormal row count: The total row count minus the normal row count, which is the sum where the count is greater than 2.

• Abnormal rate: 1 - Normal rate, or Abnormal row count / Total row count.

  Note

  Unique value: A record that appears only once. This refers to a group with a count of 1 after grouping.

Statistical value

The data returned by the count distinct function.

Statistical value (number of duplicate rows/duplication rate)

• The count of extra duplicate rows, calculated as: Total row count - Distinct row count.

• Duplication rate = Number of duplicate rows / Total row count.

• To calculate the total count or rate of all rows that have duplicates, use the Abnormal Row Count or Abnormal Rate metrics from the uniqueness check.

Timeliness, validity

Abnormal Row Count/Normal Row Count/Abnormal Rate/Normal Rate

• Normal row count: The number of unique rows.

• Normal rate: Normal row count / Total row count.

• Abnormal row count: Total row count - Normal row count, which is the sum where count is greater than 2.

• Abnormal rate: 1 - Normal rate, or Abnormal row count / Total row count.

  Note

  Unique value: A record that appears only once. This refers to a group with a count of 1 after grouping.

Consistency

Statistical difference, Statistical difference rate (%)

Statistical difference: Field to check - Comparison field.

Statistical difference rate: Field to check / Comparison field.

Stability

Statistical value (1-day volatility rate, 7-day volatility rate, 30-day volatility rate)

The system calculates volatility rates by comparing the current row count with historical data from 1, 7, and 30 days prior. These rates are then compared against a set threshold, and an alarm is triggered if any rule is not met.

Custom SQL

Abnormal Row Count/Normal Row Count/Abnormal Rate/Normal Rate

• Normal row count: The number of unique rows.

• Normal rate: Normal row count / Total row count.

• Abnormal row count: total row count - normal row count, that is, the sum of count values that are greater than 2.

• Abnormal rate: 1 - Normal rate, or Abnormal row count / Total row count.

  Note

  Unique value: A record that appears only once. This refers to a group with a count of 1 after grouping.

Statistical value (1-day volatility rate, 7-day volatility rate, 30-day volatility rate)

The system calculates volatility rates by comparing the current row count with historical data from 1, 7, and 30 days prior. These rates are then compared against a set threshold, and an alarm is triggered if any rule is not met.

Template type

Description

Uniqueness

Field Group Count Check / Field Duplicate Value Count Check: You must configure data filtering.

Data filtering: Disabled by default. After you enable it, you can configure filtering conditions for the validation table, partition filtering, or general data filtering. These conditions are directly appended to the validation SQL. If the validation table requires partition filtering, we recommend that you configure a partition expression in the scheduling configuration. After you configure the expression, the quality report uses the validation partition as the finest viewing granularity. Enter the data filtering content, for example:

id = 12 -- For a single table

T1.id=12 and T2.name = "Zhang San" -- For two tables

Stability

Field Stability Check/Field Fluctuation Check:

• Data filtering: Same as the description under Uniqueness.

• Statistical method: Select the statistical method for the metric.

  • Field count: Compares the count of values in the field with a specified fixed value.

  • Field unique value count: Compares the count of unique values in the field with a specified fixed value.

  • Field sum: Compares the field's sum (SUM) with a fixed value.

  • Field average: Compares the average of the field's values with a specified fixed value.

  • Field maximum: Compares the maximum value of the field with a threshold.

  • Field minimum: Compares the minimum value of the field with a threshold.

  • Field duplicate row count: Compares the number of duplicate values with a fixed value. The number of duplicate values is Total row count - Distinct row count.

  • Field duplication rate (%): Compares the ratio of duplicate values to the total row count (Duplicate count / Total row count) with a fixed value.

  • Field group count: Compares the number of groups for the field with a specified fixed value.

  • Field null count: Compares the count of null values in the field with a specified fixed value.

  • Field null rate: Compares the ratio of null values to the total row count (Null count / Total row count) with a fixed value.

Template type

Parameter

Description

Uniqueness

Field Group Count

Compares the number of groups for the field with a specified fixed value.

Statistic (Duplicate Row Count/Duplication Rate)

• Duplicate row count represents the number of extra duplicated rows and is calculated as: Total row count - Distinct row count.

• Duplication rate = Duplicate row count / Total row count.

• To calculate the total number of all duplicate rows (not just the extra ones), use the "abnormal row count" and anomaly rate metrics from the uniqueness validation template.

Stability

Statistic

The count of distinct values, which is the result of a COUNT(DISTINCT) operation.

Statistic (1-day, 7-day, 30-day fluctuation rate)

This check compares the current row count with counts from 1, 7, and 30 days ago to determine the fluctuation rate. An alert is triggered if this rate violates the configured threshold.

Average value fluctuation detection (7-day, 30-day)

The baseline value is the average row count over the last 7 or 30 days. This check compares the fluctuation of the current value against this baseline average.

Statistic (fluctuation rate vs. 1st of month/last month/last year)

Compares the current row count with counts from the first day of the current month, the previous month, and the previous year to determine the fluctuation rate. An alert is triggered if this rate violates the configured threshold.

Parameter

Description

Verification field

Select the field to verify.

Metric operator

Select the algorithm for the data.

Object form

Select single-value data or multi-value data.

Time condition

Select the field for the time condition.

Enable conditional limit

Select Enable or Disable to toggle the conditional limit.

Offline data

Select an offline data table from the drop-down list.

Offline data retrieval

Disabled by default. When enabled, use SQL to specify how data is retrieved from the offline data table.

Time zone settings

Select a time zone from the drop-down list.

Parameter

Description

Verification field

Select the field to verify.

Metric operator

Select the algorithm for the data.

Object form

Select single-value data or multi-value data.

Time condition

Select the field for the time condition.

Enable conditional limit

Select Enable or Disable to toggle the conditional limit.

Number of comparison links

Select the comparison type: real-time triple-link comparison or real-time dual-link comparison.

Comparison link 1/Comparison link 2

Select the real-time meta table(s) for the comparison links:

• For the real-time dual-link comparison template, select one real-time meta table.

• For the real-time triple-link comparison template, select two real-time meta tables.

Time zone settings

Select a time zone from the drop-down list.