Data Quality Spec configuration guide

YAML example

A simple Data Quality monitoring configuration is as follows:

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - assertion: row_count > 0

Property description

The Spec in the preceding example defines the simplest Data Quality monitoring configuration:

• datasets: the monitored object of Data Quality. This parameter contains three properties.

  • type: the monitored object type. Only one enumerated value is supported: Table.

  • dataSource: the data source to which the monitored object belongs. Set name and envType to identify the data source. You can call ListDataSources to obtain the data source name.

    Note

    Only some data sources are supported. For more information, see Supported data source types.

  • tables: if the monitored object is of the Table type, specify the table names.

    Note

    If the data source is bound at the database level and you need to monitor tables in a non-default schema, configure the table in the schema.table format.

• rules: rules that data is expected to satisfy. A Data Quality monitoring configuration can contain multiple rules.

  When a Data Quality monitoring configuration runs, it scans the data of the monitored object, calculates the metric value specified by each rule in sequence, and compares the value with the expected threshold. The system determines whether a rule passes based on whether the expected threshold is satisfied.

  In most cases, the expectation for data is described by using a metric type (such as row_count), a comparison symbol (such as >), and a threshold (such as 0) in an assertion statement. A check result usually has three states.

  • pass: The check passes. The collected metric value falls within the range defined by the threshold.

  • fail: The check fails. The collected metric value falls outside the range defined by the threshold.

  • error: Another exception occurs during the check, such as a syntax error.

  Note

  • If you explicitly specify a warn-level threshold, the check result can also be warn. For more information, see Add multi-level threshold definitions.

  • You can check the assertion statement for the list of Supported built-in metric types.

  • When you create a Data Quality monitoring configuration, you can also use Custom metric rules.

  In addition, table schema evolution detection rules (assertion: change for schema) are also supported. Instead of calculating a numeric metric, this rule type collects table schema metadata, compares the collected metadata with the baseline table schema, and then matches the change type against the warn and fail conditions defined in the rule to determine the check result. For more information, see Schema Check rules.

Fixed threshold

A fixed threshold applies when you need to compare a metric value directly with a fixed value. A fixed threshold definition consists of the following parts:

• a metric

• an argument (optional)

• a comparison symbol (optional)

• a threshold (optional)

Configuration example

rules:
  # The number of data rows must be greater than 0
  - assertion: row_count > 0
  # size column: the maximum value must be less than or equal to 500
  - assertion: max(size) <= 500

The following table describes the four parts of the fixed-value threshold expression:

For more supported comparison symbols, see Supported comparison symbols (comparison symbol).

Fluctuation threshold

A fluctuation threshold applies when you need to compare the current metric value with historical values of the same metric. For example, the difference between the number of users today and the number of users yesterday must be within 100, or the fluctuation range of today's revenue compared with the average revenue of the last 7 days must be within 10%.

Typically, the metric is preceded by change for to describe a fluctuation threshold. A fluctuation threshold definition consists of the following parts:

• change (keyword)

• an aggregate type (optional)

• a time window

• percent (keyword, optional)

• for (keyword)

• a metric

• an argument (optional)

• a comparison symbol (optional)

• a threshold (optional)

The format of the combined expression is change [aggregate_type] [time_window] [percent] for metric [argument] [comparison_symbol threshold].

Configuration example

rules:
  # The difference between the current number of data rows and the number of data rows at the check 7 days ago must be within 10000 rows
  - assertion: change 7 days ago for row_count < 10000

rules:
  # The difference between the current number of data rows and the average number of data rows over the last 7 days must be within 10000 rows
  - assertion: change average last 7 days for row_count < 10000

rules:
  # The fluctuation percentage of the current number of data rows compared with the number of data rows at the check 7 days ago must be within 50%
  - assertion: change 7 days ago percent for row_count < 50%

Range threshold

You can use between...and... to define a range-type threshold.

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: dt='$[yyyymmdd]' AND hh='$[hh24-1/24]'
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - assertion: change 1 day ago percent for row_count
    warn: 
      - when not between -1% and 1%
    fail: 
      - when not between -5% and 5%

Handling strategy when warn and fail overlap

warn and fail threshold ranges can overlap. If the metric value falls into the overlapping range, the system uses the more severe status, and the result is fail.

Use not between...and... to define the complement of a range

You can negate between...and... by adding not before it to obtain the complement of the range. For example:

rules:
  - assertion: duplicate_count(phone)
    warn: 
      - when not between -1% and 1%
    fail:
      - when not between -5% and 5%

The ranges defined by the preceding code snippet are shown in the following figure:

Set a data filter for a rule

If only a subset of data is required for metric statistics when a rule is checked, for example, only the rows in which the id column is not NULL, you can add a filter setting to the rule. For example:

rules:
  - assertion: row_count > 0
    filter: id IS NOT NULL

Set a data filter for a Data Quality monitoring configuration

In Scan.Dataset, you can add a filter setting. In this case, the filter takes effect during the check of every rule in the Data Quality monitoring configuration.

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: dt='$[yyyymmdd]' AND hh='$[hh24-1/24]'
    dataSource:
      name: odps_first
rules:
  - assertion: row_count > 0

In the preceding code snippet, the dataset contains a filter setting. When each rule is checked, the system first uses the filter to filter the data and then runs the subsequent check.

Supported comparison symbols (comparison symbol)

• >

• >=

• <

• <=

• =

• !=

• between ... and ...

Supported data source types

The following data source types are supported:

• maxcompute

• hologres

• emr

• mysql

• analyticdb_for_mysql

• analyticdb_for_postgresql

• cdh

• starrocks

• lindorm

• sqlserver

• selectdb

Supported built-in metric types

• avg

• row_count

• sum

• min

• max

• distinct_count

• distinct_percent

• table_size

• missing_count

• missing_percent

• duplicate_percent

• duplicate_count

• group_by

• invalid_count

• invalid_distinct_count

• change for schema

  Note

  To detect changes to the table schema, including column deletion, column addition, column order changes, and column type changes, use a table schema evolution detection rule. For more information, see Schema Check rules.

Time window (time_window) definition methods

DataWorks Data Quality supports multiple methods for defining a time window. The basic formats are as follows:

• n time units ago: n (minute[s]|hour[s]|day[s]|week[s]|month[s]) ago, for example, n months ago, n days ago, or n hours ago.

  • One day ago: 1 day ago

  • Seven days ago: 7 days ago

  • One month ago: 1 month ago

  • Eight hours ago: 8 hours ago

  • Fifteen minutes ago: 15 minutes ago

• The last n time units: last n (minute[s]|hour[s]|day[s]|week[s]|month[s]) , for example, last 15 minutes, last 7 days, or last 1 month.

  • The last 15 minutes: last 15 minutes

  • The last 24 hours: last 24 hours

    Note

    The system collects a data point at each one-hour interval, from 24 hours before the current time up to the current time.

  • The last 7 days: last 7 days

  • The last 1 month: last 1 month

• The same time on a specific day of a month or a specific day of a week: 1/2/3/.../-3/-2/-1 of (current|last|n) (months|weeks) (ago)

  • The same time on the first day of the current month: 1 of current month

  • The same time on the last day of the previous month: -1 of last month

  • The same time on Tuesday three weeks ago: 2 of 3 weeks ago