Missing value rules

Use missing value rules to check whether a data field contains NULL values or other values you define as missing.

Rule syntax

A missing value rule is composed of the following parts:

Part	Description	Example
Metric	The measurement function	`missing_count` or `missing_percent`
Argument	The field to check	`birthday`
Comparison	The operator	`=`, `<`, `>`
Threshold	The acceptable limit	`0`, `5%`
Config key	(Optional) Custom missing value definition	`missing`
Config value	(Optional) Regex or value list	`regex: "(?:N/A)"`

Metrics

Metric	Description
`missing_count(<field>)`	Counts the number of rows where the field is NULL.
`missing_percent(<field>)`	Calculates the percentage of rows with missing values.

Check for NULL values

By default, the system treats NULL as the only missing value. Use missing_count to assert that no rows are NULL, or missing_percent to allow a limited proportion.

Example 1: The birthday field must have no NULL rows.

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: dt=$[yyyymmdd]/hh=$[hh24-1/24]
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - missing_count(birthday) = 0
computeResource:
  id: 2001

Example 2: No more than 5% of number_employees rows may be NULL.

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: dt=$[yyyymmdd]/hh=$[hh24-1/24]
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - missing_percent(number_employees) < 5%
computeResource:
  id: 2001

Define custom missing values

To treat non-NULL values as missing, add a missing block under the rule. Use regex for pattern-based definitions or values for an explicit list.

Using regex: The following rule treats any value matching N/A as missing.

rules:
  - missing_count(first_name) = 0
    missing:
      regex: "(?:N/A)"

Using a value list: The following rule treats n/a, NA, and none as missing.

rules:
  - missing_count(first_name) = 0
    missing:
      values:
        - n/a
        - NA
        - none

Retain rows with missing values

Set collectFailedRows: true on a rule to save any rows that triggered a warn or fail result to a problem data table for troubleshooting.

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: dt=$[yyyymmdd]/hh=$[hh24-1/24]
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - missing_percent(number_employees) < 5%
    collectFailedRows: true
computeResource:
  id: 2001

Configuration example

The following example combines multiple missing value rules in a single dataset configuration.

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: dt=$[yyyymmdd]/hh=$[hh24-1/24]
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - missing_count(birthday) = 0
  - missing_percent(gender) < 5%
  - missing_count(first_name) = 0
    missing:
      regex: "(?:N/A)"
  - missing_count(first_name) = 0
    missing:
      values:
        - n/a
        - NA
        - none
  - missing_percent(email_address) = 0%
computeResource:
  id: 2001