Built-in template validation rules in DataWorks Data Quality - DataWorks

Data Quality provides a variety of built-in monitoring templates. This topic describes the verification logic of Data Quality and the built-in rule templates.

Built-in rule templates

You can use built-in rule templates to quickly create Data Quality rules for a single table or for multiple tables in bulk. For more information, see Configure rules: By table and Configure rules by template.

Template category	Template Name	Description
Table rows	Table rows, static field	The number of rows in the table.
	Table rows greater than 0	Checks whether the number of rows in the table is greater than 0.
	Table rows, 1-day difference	The difference between the number of rows in the table and the number from 1 day ago. Note The baseline is the number of rows in the previous day partition. The system compares it with the number of rows collected on the current day and calculates the difference.
	Table rows, previous cycle difference	The baseline is the number of rows in the partition generated in the previous cycle. The system compares it with the number of rows collected on the current day and calculates the difference.
	Table rows, 1-day, 7-day, 30-day, and 1st-of-the-month volatility	Compares the number of table rows with the samples from 1, 7, and 30 days ago and with the sample collected on the 1st of the current month, and calculates the volatility. The volatility is then compared with the thresholds. An alert is triggered if any comparison fails to meet the rule.
	Table rows, 1-day, 7-day, and 30-day volatility	Compares the number of rows in the table with the numbers collected 1 day, 1 week, and 1 month ago, and calculates the volatility. Note The number of rows in the table is compared with the samples from the previous day, 7 days ago, and 30 days ago to calculate the volatility. The volatility is then compared with the thresholds. An alert is triggered if any volatility exceeds the threshold.
	Table rows, 1-day volatility	The baseline is the sample from the previous day (number of table rows). The system compares it with the number of rows collected on the current day and calculates the volatility. The volatility is then compared with the threshold. An alert is triggered if any comparison fails to meet the rule.
	Table rows, 30-day volatility	The baseline is the sample from 30 days ago (number of table rows). The system compares it with the number of rows collected on the current day and calculates the volatility. The volatility is then compared with the threshold. An alert is triggered if any comparison fails to meet the rule.
	Table rows, 7-day volatility	The baseline is the sample from 7 days ago (number of table rows). The system compares it with the number of rows collected on the current day and calculates the volatility. The volatility is then compared with the threshold. An alert is triggered if any comparison fails to meet the rule.
	Table rows, dynamic threshold	After Intelligent Dynamic Threshold is enabled, you do not need to manually configure volatility thresholds or expected values. The system automatically determines reasonable thresholds based on intelligent algorithms. If a data anomaly is detected, an alert is triggered or the task is blocked immediately.
	Table rows, 30-day average volatility	This template monitors fluctuations in the number of table rows. The baseline is the average number of table rows over the last 30 days, that is, (the sum of the daily row counts over 30 days) / 30.
	Table rows, 7-day average volatility	This template monitors fluctuations in the number of table rows. The baseline is the average number of table rows over the last 7 days, that is, (the sum of the daily row counts over 7 days) / 7.
	Table rows, previous cycle volatility	The baseline is the number of rows in the partition generated in the previous cycle. The system compares it with the number of rows collected on the current day and calculates the volatility.
	Table rows, custom range	Lets you customize the comparison method and the threshold range for the number of table rows.
Condition match rate	Condition match rate, custom range	Lets you customize the comparison method and the threshold range for the match rate of the filter condition.
Table size	Table size, static field.	The storage size of the table, in bytes.
	Table size, difference from 1 day ago (bytes)	The difference between the storage size of the table and the size from 1 day ago, in bytes.
	Table size, previous cycle difference.	The difference from the table size of the previous cycle, in bytes.
	Table size, 1-day volatility	This template monitors fluctuations in the table size. The sample is compared with the sample from the previous day to calculate the volatility. The volatility is then compared with the thresholds. An alert is triggered if any comparison fails to meet the rule. For example, assume that the warning threshold is set to 5% and the error threshold is set to 10%. A warning alert is triggered when the volatility is greater than 5% and less than or equal to 10%. An error alert is triggered when the volatility is greater than 10%.
	Table size, 30-day volatility	This template monitors fluctuations in the table size. The sample is compared with the sample from 30 days ago to calculate the volatility. The volatility is then compared with the thresholds. An alert is triggered if any comparison fails to meet the rule.
	Table size, 7-day volatility	This template monitors fluctuations in the table size. The sample is compared with the sample from 7 days ago to calculate the volatility. The volatility is then compared with the thresholds. An alert is triggered if any comparison fails to meet the rule.
	Table size, dynamic threshold	After Intelligent Dynamic Threshold is enabled, you do not need to manually configure volatility thresholds or expected values. The system automatically determines reasonable thresholds based on intelligent algorithms. If a data anomaly is detected, an alert is triggered or the task is blocked immediately.
Table schema	Table schema change - any change	Checks whether any change occurred in the table schema, including column deletion, column addition, column order change, and column type change. An alert is triggered when any one of these changes is detected. Note For table schema change templates, the baseline table schema is the most recent valid table schema collected by the same rule. During the first check, no historical table schema exists, so the system uses the table schema collected this time as the baseline and the check passes. For information about how to use Spec to configure such rules, see Schema Check rules.
	Table schema change - column deletion	Checks whether any column was deleted, that is, a column that exists in the baseline table schema but does not exist in the table schema collected this time.
	Table schema change - column addition	Checks whether any column was added, that is, a column that exists in the table schema collected this time but does not exist in the baseline table schema.
	Table schema change - column order change	Checks whether the relative order of the columns that exist in both collections changed. Position changes caused by column addition or deletion are not counted.
	Table schema change - column type change	Checks whether the data type of the same column changed.
Null value rows	Null count, static field	Compares the number of null values in the column with a static field. Note Whether a value is null is determined by converting the value to the SQL `IS NULL` expression.
Null value rows	Null record count equals 0	Checks whether the number of null records is 0.
Null value rows / total rows	Null count / total rows, static field	Compares the ratio of the number of null values to the total number of rows with a static field. Note The static field is a decimal.
Format validation	Regular expression validation	Checks whether the column values match the regular expression rule.
	Date format validation	Checks whether the column values match the date format.
	Email format validation	Checks whether the column values match the email format.
	ID card format validation	Checks whether the column values match the ID card format.
	Mobile phone number format validation	Checks whether the column values match the mobile phone number format.
	Currency format validation	Checks whether the column values match the currency format.
	Numeric format validation	Checks whether the column values match the numeric format.
	Telephone number format validation	Checks whether the column values match the telephone number format.
Duplicate value rows	Duplicate count, static field	The total number of rows minus the number of rows after deduplication, that is, the number of duplicate values in the column. The duplicate count is compared with a static field.
Duplicate value rows	Column duplicate count equals 0	Checks whether the number of duplicate values in the column is 0.
Multi-column unique values	Multi-column duplicate count equals 0	Checks whether the number of duplicate values across multiple columns is 0.
Duplicate count / total rows	Duplicate count / total rows, static field	Compares the ratio of the number of duplicate values to the total number of rows with a static field.
Unique value count	Unique value count, static field	Compares the count after deduplication with an expected number, that is, a static field check.
	Unique value count, 1-day, 7-day, and 30-day volatility	Compares the count after deduplication with the samples from 1 day, 1 week, and 1 month ago (the number of unique values in the column), that is, a static field check.
	Unique value count, dynamic threshold	After Intelligent Dynamic Threshold is enabled, you do not need to manually configure volatility thresholds or expected values. The system automatically determines reasonable thresholds based on intelligent algorithms. If a data anomaly is detected, an alert is triggered or the task is blocked immediately.
Unique value count / total rows	Unique value count / total rows, static field	Compares the ratio of the number of unique values to the total number of rows with a static field.
Minimum value	Minimum value, 1-day, 7-day, and 30-day volatility	Retrieves the minimum value of the column and compares it with the samples from 1 day, 7 days, and 1 month ago (the column average) to calculate the volatility. The volatility is then compared with the thresholds. An alert is triggered if any comparison fails to meet the rule.
	Minimum value, dynamic threshold.	After Intelligent Dynamic Threshold is enabled, you do not need to manually configure volatility thresholds or expected values. The system automatically determines reasonable thresholds based on intelligent algorithms. If a data anomaly is detected, an alert is triggered or the task is blocked immediately.
	Minimum value, 1-day volatility	Retrieves the minimum value of the column, compares it with the value from 1 day ago, and then compares the calculated volatility with the threshold.
	Minimum value, previous cycle volatility	Retrieves the minimum value of the column, compares it with the value from the previous cycle, and calculates the volatility. The volatility is then compared with the threshold. An alert is triggered if any comparison fails to meet the rule.
	Column minimum value, custom range	Lets you customize the comparison method and the threshold range for the column minimum value.
Maximum value	Maximum value, 1-day, 7-day, and 30-day volatility	Retrieves the maximum value of the column and compares it with the samples from 1 day, 7 days, and 1 month ago (the column average) to calculate the volatility. The volatility is then compared with the thresholds. An alert is triggered if any comparison fails to meet the rule.
	Maximum value, dynamic threshold	After Intelligent Dynamic Threshold is enabled, you do not need to manually configure volatility thresholds or expected values. The system automatically determines reasonable thresholds based on intelligent algorithms. If a data anomaly is detected, an alert is triggered or the task is blocked immediately.
	Maximum value, 1-day volatility	Retrieves the maximum value of the column, compares it with the value from 1 day ago, and then compares the calculated volatility with the threshold.
	Maximum value, previous cycle volatility	Retrieves the maximum value of the column, compares it with the value from the previous cycle, and calculates the volatility. The volatility is then compared with the threshold. An alert is triggered if any comparison fails to meet the rule.
	Column maximum value, custom range	Lets you customize the comparison method and the threshold range for the column maximum value.
Average value	Average value, 1-day, 7-day, and 30-day volatility	Retrieves the average value of the column and compares it with the samples from 1 day, 7 days, and 1 month ago (the column average) to calculate the volatility. The volatility is then compared with the thresholds. An alert is triggered if any comparison fails to meet the rule. Note The average value of the column is compared with the average value of the column from the previous day, from 7 days ago, and from 30 days ago, respectively.
	Average value, dynamic threshold	After Intelligent Dynamic Threshold is enabled, you do not need to manually configure volatility thresholds or expected values. The system automatically determines reasonable thresholds based on intelligent algorithms. If a data anomaly is detected, an alert is triggered or the task is blocked immediately.
	Average value, 1-day volatility	Retrieves the average value of the column, compares it with the value from 1 day ago, and then compares the calculated volatility with the threshold.
	Column average value, custom range	Lets you customize the comparison method and the threshold range for the column average value.
Sum value	Sum value, 1-day, 7-day, and 30-day volatility	Retrieves the sum of the column and compares it with the samples from 1 day, 7 days, and 1 month ago (the column average) to calculate the volatility. The volatility is then compared with the thresholds. An alert is triggered if any comparison fails to meet the rule.
	Sum value, dynamic threshold	After Intelligent Dynamic Threshold is enabled, you do not need to manually configure volatility thresholds or expected values. The system automatically determines reasonable thresholds based on intelligent algorithms. If a data anomaly is detected, an alert is triggered or the task is blocked immediately.
	Sum value, 1-day volatility	Retrieves the sum of the column, compares it with the value from 1 day ago, and then compares the calculated volatility with the threshold.
	Sum value, previous cycle volatility	Retrieves the sum of the column, compares it with the value from the previous cycle, and calculates the volatility. The volatility is then compared with the threshold. An alert is triggered if any comparison fails to meet the rule.
	Column sum value, custom range	Lets you customize the comparison method and the threshold range for the column sum value.
Enum values	Unmatched enum unique value count, static field	Compares the number of unique values of the unmatched enum items.
	Unmatched enum row count, static field	The number of rows in the column where the enum items do not match, which can be compared with a specified static field.
	Unmatched enum row count equals 0	The expected normal state: the static field and the comparison method are set to =0. An alert is triggered as long as the number of rows with unmatched enum items is > 0.
	Enum values, custom, static field	Lets you customize the statistical metrics related to enum items, such as the count, the number of rows, the comparison method, and the threshold.
Discrete values	Discrete value (status value), static field	After GROUP BY, the count of each group is compared with a static field.
	Discrete value (number of groups), static field	After GROUP BY, the number of groups is compared with a static field.
	Discrete value (number of groups), dynamic threshold	After Intelligent Dynamic Threshold is enabled, you do not need to manually configure volatility thresholds or expected values. The system automatically determines reasonable thresholds based on intelligent algorithms. If a data anomaly is detected, an alert is triggered or the task is blocked immediately.
	Discrete value (status value), dynamic threshold	After Intelligent Dynamic Threshold is enabled, you do not need to manually configure volatility thresholds or expected values. The system automatically determines reasonable thresholds based on intelligent algorithms. If a data anomaly is detected, an alert is triggered or the task is blocked immediately.
	Discrete value (number of groups), 1-day volatility	After GROUP BY, the number of groups is compared with the sample from 1 day ago to calculate the volatility.
	Discrete value (number of groups and status value), 1-day, 7-day, and 30-day volatility	After GROUP BY, the number of groups and the count of each group are compared with the samples from 1 day ago, 7 days ago, and 30 days ago (the discrete values) to calculate the volatility.

Note

Table size validation rules cannot be configured for EMR tables.

How volatility is calculated

Volatility calculation: The volatility formula is (Current value - Baseline value) / Baseline value. Here, Current value is the actual metric value of the current check, and Baseline value is dynamically determined by the template you select. For example:

For N-day volatility templates, the “Baseline value” is the metric value of the same rule from N days ago.
For N-day average volatility templates, the “Baseline value” is the arithmetic average of the metric values over the past N days.
For previous cycle volatility templates, the “Baseline value” is the metric value from the previous scheduling cycle.

Verification logic

Data Quality supports three verification methods: Compare with static field, Thresholds, and Auto-Generated Threshold.

Verification method	Verification logic
Compare with static field	The system evaluates the check expression and returns a Boolean value. The following comparison operators are supported: `>`, `<`, `>=`, `<=`, and `!=`. If the result is `true`, the check passes. Otherwise, an error alert is returned.
Thresholds	This method monitors the magnitude of metric changes and supports three comparison types: Absolute Value: focuses on the magnitude of the change regardless of increase or decrease. For example, an alert is triggered as long as the row count volatility exceeds 10%. Raise: focuses only on abnormal increases of the metric. For example, when you monitor costs, you care only about whether the cost rises significantly. Drop: focuses only on abnormal decreases of the metric. For example, when you monitor the order volume, you mainly care about whether the order volume drops suddenly. The system determines the alert level based on the “warning threshold” and “error threshold” that you set.
Auto-Generated Threshold	You do not need to manually set thresholds. The system automatically detects the correctness of the metric in real time based on algorithm models. An alert is triggered if the metric exceeds a reasonable fluctuation range.

Appendix 1: Definition of the previous cycle

The previous cycle task is determined by excluding checks with the same data timestamp, sorting the remaining checks by data timestamp in descending order. If the data timestamps are the same, the checks are sorted by run time in descending order. The first executed task in this order is used as the baseline. For more information, see the following table:

Recurrence

Data timestamp

Baseline comparison method

FAQ

Normal daily scheduling scenario

Historical data timestamps:

2024-06-01
2024-06-02
2024-06-03
2024-06-04
2024-06-05

When the scheduled task with the data timestamp 2024-06-06 starts the check, the check of 2024-06-05 is used as the baseline for comparison.

Historical data backfill scenario:

Background:

The tasks from 2024-06-01 to 2024-06-05 ran as expected. After the scheduled instance on 2024-06-05 ran, data was backfilled for the instance with the data timestamp 2024-07-01. When the scheduled task with the data timestamp 2024-06-06 starts the check, which date is used as the baseline for comparison?

Conclusion:

The scheduled task with the data timestamp 2024-06-06 uses the check of the data timestamp 2024-07-01 as the baseline for comparison. Because the data timestamp 2024-07-01 is later, the normal daily scheduled tasks use 2024-07-01 as the baseline until the scheduled instance of 2024-07-02 finishes execution.

Normal hourly scheduling scenario

Historical data timestamps:

2024-06-01
2024-06-02
2024-06-03

The tasks are scheduled by hour and run 3 times a day.

When the hourly scheduled task with the data timestamp 2024-06-04 starts the check, the last check of the data timestamp 2024-06-03 is used as the baseline for comparison.

Hourly scheduling scenario:

Background:

The three hourly scheduled instances of each day from 2024-06-01 to 2024-06-03 and the 1st scheduled instance of the data timestamp 2024-06-04 ran as expected. When the 2nd scheduled instance of the data timestamp 2024-06-04 starts the check, which date is used as the baseline for comparison?

Conclusion:

When the 2nd scheduled task instance of the data timestamp 2024-06-04 starts the check, it excludes the 1st check of the same day (2024-06-04) and uses the last check of the data timestamp 2024-06-03 as the baseline for comparison.

Appendix 2: Definition of the N-day-ago sample value for hourly tasks

When an hourly task retrieves the sample from N days ago, the system first sorts the instances of the Nth day by run time (not the scheduled time) in descending order. By default, the result data of the first instance in this order is used as the sample value to compare with the current result. For more information, see the following table:

Recurrence

Data timestamp

Volatility comparison method

FAQ

Normal hourly scheduling scenario

Historical data timestamps:

2024-06-01
2024-06-02
…
2024-06-08

The tasks are scheduled by hour and run 3 times a day.

If the comparison is the 7-day volatility, when the hourly scheduled task of the run date 2024-06-08 starts the check, the last sample value of the run date 2024-06-01 is used.

Hourly scheduling scenario:

Background:

Three hourly scheduled instances ran each day from 2024-06-01 to 2024-06-08. When the 2nd scheduled instance of the run date 2024-06-08 starts the check, which scheduled result of which day is used as the sample value for the 7-day volatility comparison?

Conclusion:

When the 2nd scheduled task instance of the run date 2024-06-08 starts the check, the last check of the task date 2024-06-01 is used as the sample value for the 7-day volatility comparison.