DataWorks:Configure a monitoring rule for a single table

You need to perform the following steps to configure monitoring rules for a table:

1. Select the table whose data quality you want to check based on monitoring rules.

2. Create a quality monitor for the table.

   When you create and configure a monitor for the table, you can specify the range of table data whose quality you want to monitor, such as a partition, and select the monitoring rules that you want to associate with the monitor. This way, the monitor can be used to monitor the quality of the specified table data based on the rules.

   • You can create different monitors for different partitions of the same table and associate different monitoring rules with the monitors. This way, the partitions can be monitored based on different data quality check logic.

   • You can specify a trigger method for the monitor. The monitor can be triggered when the scheduling node that generates the table is run or when you manually run the monitor.

   • You can specify the conditions under which alerts are sent. You can determine whether to send alerts based on the severity of data quality check results. You can determine whether to block the task that generates the monitored table based on the severity of data quality issues when data changes in the table are automatically checked.

3. Configure data quality rules to add to quality monitoring.

   You can select the monitoring rules based on which you want to monitor the data quality of the table. The specific check methods that are defined in the rules are implemented to determine whether the table data meets your business requirements.

   Data Quality allows you to create monitoring rules based on built-in rule templates or create custom template rules based on custom SQL statement logic.

   • Built-in template rules: You can create monitoring rules based on built-in rule templates provided by DataWorks.

   • Custom template rules: You can create custom template rules if the built-in rule templates cannot meet your requirements for monitoring the quality of data specified by partition filter expressions. You can save frequently used custom rules as rule templates for future use.

4. Test and subscribe to the monitor.

Step 1: Go to the Table Quality Details page

1. Go to the Data Quality page.

   Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Governance > Data Quality. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Quality.

2. Go to the Configure by Table page.

   In the left-side navigation pane, choose Configure Rules > Configure by Table.

   1. In the Connection section, select the database where the table for which you want to configure rules resides.

   2. Search for the desired table based on conditions, such as the database type, database, and table name. Then, click the name of the table or click Rule Management in the Actions column of the table to go to the Table Quality Details page.

      This page displays all the monitors and rules configured for the table. You can quickly filter rules based on whether the rules are associated with a monitor. For rules that are not associated with a monitor, you can specify the running mode for the rules.

      

Step 2: Create a monitor

1. Create a monitor.

   You can use one of the following methods to create a monitor:

   Rule Management tab

   On the Table Quality Details page, click the Rule Management tab. Click the icon next to Monitor Perspective .

   

   Monitor tab

   On the Table Quality Details page, click the Monitor tab. Click Create Monitor.

   

2. Configure the parameters for quality monitoring.

   Configuration item	Parameter	Description
   Basic Configurations	Monitor Name	The name of the monitor.
   	Quality Monitoring Owner	You can specify the owner of the monitor as needed. When you configure alert subscriptions, you can specify the monitor owner as the alert recipient by using Email, Email and SMS, or Telephone.
   	Monitored Object	The object for which you want to check the data quality. The default value is the current table.
   	Data Range	The range of table data whose quality you want to monitor. You can use a partition filter expression to define the partition that needs to be checked. For a non-partitioned table, you do not need to configure this parameter. All data in the table is checked by default. For a partitioned table, you must set this parameter to a value in the Partition key=Partition value format. The partition value can be a constant or a built-in partition filter expression. Note If you configure a monitoring rule based on a custom template or a custom SQL statement, this parameter does not take effect. Instead, the partition checked by the rule is determined by the custom SQL statement that is specified in the rule.
   Monitoring Rule	Monitoring Rule	The monitoring rules that you want to associate with the monitor. The quality of data in the specified range is monitored based on the rules. Note You can create different monitors for different partitions of the same table and associate different monitoring rules with the monitors. This way, the partitions can be monitored based on different data quality check logic. If you have not created monitoring rules, you can skip the configuration of this parameter and complete the creation of the monitor first. When you create and configure a monitoring rule, you can add the monitoring rule to a monitor. For information about how to create and configure a monitoring rule, see Step 3: Configure a monitoring rule.
   Running Settings	Trigger Method	The trigger method for the monitor. Triggered by Node Scheduling in Production Environment: After the scheduling node that you associate with the monitor finishes running in Operation Center, the rules that are associated with the monitor are automatically triggered. Note that dry-run nodes do not trigger monitoring rules to run. Triggered Manually: The monitoring rules that are associated with the monitor are manually triggered. Important If the table whose data quality you want to check is a non-MaxCompute table and Triggered By Node Scheduling In Production Environment is selected for Trigger Method, you cannot associate scheduling nodes that are run on the shared resource group for scheduling with the monitor. Otherwise, an error may be reported when the monitor is run.
   	Associated Scheduling Node	If you set the Trigger Method parameter to Triggered By Node Scheduling In Production Environment, you can configure this parameter to select the scheduling nodes that you want to associate with the monitor. After the scheduling nodes finish running, the rules that are associated with the monitor are automatically triggered.
   	Running Resources	The resources that are required to run the rules. By default, the data source to which the monitored table in the current workspace belongs is selected. If you select another data source, make sure that the related resources can access the monitored table.
   Handling Policies	Quality Issue Handling Policies	The blocking or alerting policy that is used to process detected data quality issues. Blocks: If a data quality issue is detected in the table, the scheduling node in the production environment that generates the table is identified, and the system sets the running status of the node to Failed. In this case, the descendant nodes of the node cannot be run, which blocks the production link to prevent the spread of dirty data. Default value: Strong rules · Red anomaly. Alert: If a data quality issue is detected in the table, the system sends alert notifications to the alert recipient by using the configured notification method. Default values: Strong rules · Red anomaly, Strong rules · Orange exception, Strong rules · Check Failed, Weak rules · Red anomaly, Weak rules · Orange exception, and Weak rules · Check Failed.
   	Alert Method Configuration	You can send alert notifications by using Email, Email and SMS, DingTalk Chatbot, DingTalk Chatbot @ALL, Lark Group Chatbot, Enterprise Wecha Robot, Custom WebHook, or Telephone. Note You can add a DingTalk chatbot, Lark chatbot, or WeChat chatbot and obtain a webhook URL. Then, copy the webhook URL to the Recipient field in the Manage Subscriptions dialog box. The Custom Webhook notification method is supported only in DataWorks Enterprise Edition. For information about the message format of an alert notification sent by using a Custom Webhook, see Appendix: Message format of alert notifications sent by using a custom webhook URL. When you select Email, Email and SMS, or Telephone as the notification method, you can specify Recipient as Monitor Owner, Shift Schedule, or Node Owner. Data Quality Monitoring Owner: Alert information will be sent to the Quality Monitoring Owner set in the Basic Configurations section of the current quality monitor. Shift Schedule: When the monitoring rule associated with the monitor is triggered and an alert is generated, the system sends alert notifications to the person on duty for the current day in the shift schedule. Scheduling Task Owner: Alert notifications are sent to the owner of the scheduling node associated with the monitor.

3. Click Save to complete the creation of the monitor.

Step 3: Configure a monitoring rule

1. On the Table Quality Details page, click the Rule Management tab, select the created monitor, and then click Create Rule. The Create Rule panel appears.

2. Create a monitoring rule.

   You can use one of the following methods provided by Data Quality to configure monitoring rules based on your business requirements.

   Method 1: Configure a rule based on a built-in rule template

   Data Quality provides dozens of built-in rule templates. You can quickly create a monitoring rule based on a built-in rule template.

   Note

   • In the upper part of the Create Rule panel, you can click + System Template Rules. Then, you can configure the Template parameter to select the desired rule template.

   • Alternatively, you can find the desired built-in rule template in the left-side navigation pane of the Create Rule panel and click + Use.

   • You can add multiple rules at the same time.

   

   Parameters for configuring a rule based on a built-in rule template

   Parameter	Description
   Rule Name	The name of the monitoring rule.
   Template	Define the type of rule validation that needs to be performed on the table. Data Quality provides many built-in table-level and field-level rule templates that are ready for use. For more information, see View built-in rule templates. Note You can configure field-level monitoring rules of the following types only for numeric fields: average value, sum of values, minimum value, and maximum value.
   Rule Scope	The application scope of the rule. For a table-level monitoring rule, the application scope is the current table by default. For a field-level monitoring rule, the application scope is a specific field.
   Comparison Method	The comparison method that is used by the rule to check whether the table data is as expected. Manual Settings: You can configure the comparison method to compare the data output result with the expected result based on your business requirements. You can select different comparison methods for different rule templates. You can view the comparison methods that are supported by a rule template in the DataWorks console. For numeric results, you can compare a numeric result with a fixed value, which is the expected value. The following comparison methods are supported: Greater Than, Greater Than Or Equal To, Equal To, Not Equal To, Less Than, and Less Than Or Equal To. You can configure the normal data range (normal threshold) and abnormal data range (red threshold) based on your business requirements. For fluctuation results, you can compare a fluctuation result with a fluctuation range. The following comparison methods are supported: Absolute Value, Raise, and Drop. You can configure the normal data range (normal threshold) based on your business requirements. You can also define data output exceptions (orange threshold) and unexpected data outputs (red threshold) based on the degree of abnormal deviation. Intelligent Dynamic Threshold: If you select this option, you do not need to manually configure the fluctuation threshold or expected value. The system automatically determines the reasonable threshold based on intelligent algorithms. If abnormal data is detected, an alert is immediately triggered or the related task is immediately blocked. When the Comparison Method parameter is set to Intelligent Dynamic Threshold, you can configure the Degree of importance parameter. Note Only monitoring rules that you configure based on a custom SQL statement, a custom range, or a dynamic threshold support the intelligent dynamic threshold comparison method.
   Monitoring Threshold	If you set the Comparison Method parameter to Manual Settings, you can configure the Normal Threshold and Red Threshold parameters. Normal Threshold: If the data quality check result meets the specified condition, the data output is as expected. Red Threshold: If the data quality check result meets the specified condition, the data output is not as expected. If the rule that you configure is a rule of the Intelligent Dynamic Threshold, you must configure the Orange Threshold. Orange Threshold: If the data quality check result meets the specified condition, the data is abnormal but your business is not affected.
   Retain problem data	If the monitoring rule is enabled and a data quality check based on the rule fails, the system automatically creates a table to store the problematic data that is identified during the data quality check. Important The Retain problem data parameter is available only for MaxCompute tables. The Retain problem data parameter is available only for specific monitoring rules in Data Quality. If you Disable the monitoring rule, problematic data is not stored.
   Status	Specifies whether to Enable or Disable the rule in the production environment. Important If you Disable the rule, the rule cannot be triggered to perform a test run or triggered by the associated scheduling nodes.
   Degree of importance	The strength of the rule in your business. Strong rules are important rules. If you set the parameter to Strong rules and the critical threshold is exceeded, the scheduling node that you associate with the monitor is blocked by default. Weak rules are regular rules. If you set the parameter to Weak rules and the critical threshold is exceeded, the scheduling node that you associate with the monitor is not blocked by default.
   Configuration Source	The source of the rule configuration. The default value is Data Quality.
   Description	You can add additional descriptions to the rule.

   Method 2: Configure a rule based on a custom rule template

   Note

   Before you use this method to create a rule, you must go to Quality Assets > Rule Template Library to create a custom rule template. For more information, see Create and manage custom rule templates.

   When you reference a custom rule template, the basic configurations of the template, such as FLAG parameter and SQL, are automatically displayed. You can configure the Rule Name parameter based on your business requirements, and the Monitoring Threshold parameter based on the rule type. For example, you must define a normal threshold and a critical threshold for a numeric rule, and you must define a warning threshold in addition to a normal threshold and a critical threshold for a fluctuation-type rule.

   

   Parameters for configuring a rule based on a custom rule template

   Only the parameters that are unique to rules based on custom rule templates are described in the following table. For information about other parameters, see the parameters for configuring a rule based on a built-in rule template.

   Parameter	Description
   FLAG parameter	The SET statement that you want to execute before the SQL statement in the rule is executed.
   SQL	The SQL statement that determines the complete check logic. The returned results must be numeric and consist of one row and one column. In the custom SQL statement, enclose the partition filter expression in brackets []. Example: SELECT count(*) FROM ${tableName} WHERE ds=$[yyyymmdd]; Note In this statement, the value of the ${tableName} variable is dynamically replaced with the name of the table for which you are configuring monitoring rules. For information about how to configure a partition filter expression, see the Appendix 2: Built-in partition filter expressions section in this topic. If you have created a monitor for the table, the setting of the table partition that you specify in the Data Range parameter during the monitor configuration no longer takes effect for the table after you configure this parameter. The rule determines the table partition to be checked based on the setting of WHERE in the SQL statement.

   Method 3: Configure a rule based on a custom SQL statement

   This method allows you to configure the data quality check logic for the table based on your business requirements.

   

   Parameters for configuring a rule based on a custom SQL statement

   Only the parameters that are unique to rules based on a custom SQL statement are described in the following table. For information about other parameters, see the parameters for configuring a rule based on a built-in rule template.

   Parameter	Description
   FLAG parameter	The SET statement that you want to execute before the SQL statement in the rule is executed.
   SQL	The SQL statement that determines the complete check logic. The returned results must be numeric and consist of one row and one column. In the custom SQL statement, enclose the partition filter expression in brackets []. Example: SELECT count(*) FROM <table_name> WHERE ds=$[yyyymmdd]; Note You must replace <table_name> with the name of the table for which you are configuring monitoring rules. The SQL statement determines the table that needs to be monitored. For information about how to configure a partition filter expression, see the Appendix 2: Built-in partition filter expressions section in this topic. If you have created a monitor for the table, the setting of the table partition that you specify in the Data Range parameter during the monitor configuration no longer takes effect for the table after you configure this parameter. The rule determines the table partition to be checked based on the setting of WHERE in the SQL statement.

3. Optional. Add the configured rule to a monitor. For more information about a monitor, see Step 2: Create a monitor.

   Note

   The configured monitoring rule can be triggered only if you add the rule to a monitor. To associate a rule with a monitor, you can select an existing monitor here, or select the rule in the Monitoring Rule section when you configure a monitor.

   

4. Click Determine.

Step 4: Test the rule

You can use one of the following methods to test how the rule that you associate with the monitor is triggered:

Step 5: Modify alert subscriptions for the monitor

When you create a monitor in Step 2, you have already configured alert subscriptions. When the rule is triggered, the system sends notifications to the specified alert recipients. If you want to modify alert subscriptions to notify other users, you can use one of the following methods:

Appendix 1: Calculation formulas of the fluctuation rate and variance

• Calculation formula of the fluctuation rate: Fluctuation rate = (Sample value - Baseline value)/Baseline value

  • Sample value: The sample value for the current day. For example, if you want to check the fluctuation in the number of table rows on an SQL task within a day, the sample value is the number of table rows in partitions on that day.

  • Baseline value: The comparison value collected from the previous N days.

  Note

  • If you want to check the fluctuation in the number of table rows on an SQL task within a day, the baseline value is the number of table rows in partitions on the previous day.

  • If you want to check the average fluctuation in the number of table rows on an SQL task within seven days, the baseline value is the average number of table rows in the last seven days.

• Calculation formula of the variance: (Current sample value - Average value of previous N days)/Standard deviation

  Note

  You can calculate the fluctuation variance only for numeric fields such as BIGINT and DOUBLE fields by using the preceding formula.

Appendix 2: Built-in partition filter expressions

• $[yyyymmdd-1]

  The partitions are daily partitions. The partition data matching the data timestamp needs to be checked.

• $[yyyymmdd-1]/hour=$[hh24]

  The partitions are level-2 partitions. All the level-2 partitions on the scheduling day need to be checked.

• $[yyyymmdd]

  The partitions are daily partitions. The partition data on the scheduling day needs to be checked.

• $[yyyymmddhh24miss]

  Partitions contain the 24-hour time format information, such as 20211216174847. The partition data at a specific point in time on the day that is specified by bizdate needs to be checked.

• $[yyyymmddhh24miss-1/24]

  Partitions contain the 24-hour time format information, such as 20211216174847. The partition data at 1 hour earlier than a specific point in time on the day that is specified by bizdate needs to be checked.

• $[yyyymmdd]000000

  The partitions are partitions at 00:00 each day, such as 20211216000000. The partition data at 00:00 on the day that is specified by bizdate needs to be checked.

• $[yyyymmdd-7]

  The partitions are daily partitions, such as 20211216. The partitions that are seven days earlier than the scheduling day need to be checked.

• $[hh24miss-1/24]

  The partitions are hourly partitions, such as 180000. The partitions that are 1 hour earlier than the scheduling time need to be checked.

• $[hh24miss-30/24/60]

  The partitions are hourly partitions, such as 180000. The partitions that are 30 minutes earlier than the scheduling time need to be checked.

• $[add_months(yyyymmdd,-1)]

  The partitions are daily partitions, such as 20211208. The partitions that are one month earlier than the scheduling day need to be checked.