Schema Check rules - DataWorks - Alibaba Cloud Documentation Center

You can use Schema Check rules to detect changes to a table schema, including column deletions, column additions, column order changes, and column type changes, and to trigger an alert when a change is detected.

Configuration example

The following example triggers warn (an orange alert) when the table schema changes in any way, and fail (a red alert) when a column is deleted.

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - assertion: change for schema
    warn:
      - when any changes
    fail:
      - when column delete

Define a Schema Check rule

When a Schema Check rule runs, the system collects the table schema in real time, compares it with the baseline table schema to identify what has changed, and then matches the changes against the warn and fail conditions defined in the rule to produce a check result. A rule has the following properties:

assertion: fixed to change for schema, which indicates a Schema Check rule.
warn: the list of conditions for an orange alert. An orange alert is triggered when any one of these conditions is met.
fail: the list of conditions for a red alert. A red alert is triggered when any one of these conditions is met.

A condition is in the format when <change type>. The supported conditions are as follows.

Condition	Description
`when any changes`	The table schema changes in any way, including column deletion, column addition, column order change, and column type change.
`when column delete`	A column is deleted.
`when column add`	A column is added.
`when column index change`	The relative order of the columns that exist in both collections changes.
`when column type change`	The data type of a column changes.

The check result is determined by the following priority:

If any fail condition is matched: the check result is fail, and a red alert is triggered.
If no fail condition is matched but any warn condition is matched: the check result is warn, and an orange alert is triggered.
If no condition is matched: the check passes.

Note

A Schema Check rule determines its result from the warn and fail conditions. You do not need to configure a numeric threshold or specify the columns to monitor.

Use a system rule template

Data Quality provides five system rule templates for schema change detection, one for each of the five change scenarios. Rules created from a template all use assertion: change for schema under the hood. The equivalent syntax of each template is as follows. For more system rule templates, see Rule templates.

Template ID	Template name	Equivalent syntax
`SYSTEM:table:schema:any_changes`	Schema change - Any change	`assertion: change for schema fail: - when any changes`
`SYSTEM:table:schema:column_delete`	Schema change - Column deletion	`assertion: change for schema fail: - when column delete`
`SYSTEM:table:schema:column_add`	Schema change - Column addition	`assertion: change for schema fail: - when column add`
`SYSTEM:table:schema:column_index_change`	Schema change - Column order change	`assertion: change for schema fail: - when column index change`
`SYSTEM:table:schema:column_type_change`	Schema change - Column type change	`assertion: change for schema fail: - when column type change`

For example, if you use the SYSTEM:table:schema:column_delete template, a red alert is triggered when a column is deleted. The template has the corresponding fail condition built in, so you only need to specify templateId:

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - templateId: "SYSTEM:table:schema:column_delete"

Templates are shortcuts for preset scenarios. If you need to combine multiple change types, or set both warn and fail conditions, use the assertion: change for schema syntax and define the conditions yourself.

Combined condition examples

The following examples show only the rules section.

Example 1: An orange alert is triggered when the table schema changes in any way, and a red alert is triggered when a column is deleted.

rules:
  - assertion: change for schema
    warn:
      - when any changes
    fail:
      - when column delete

Table schema change	Check result
No change	Pass
Column added	Orange alert (matches the `warn` condition)
Column deleted	Red alert (matches the `fail` condition)
Columns deleted and added at the same time	Red alert (`fail` conditions take priority)

Example 2: Only column deletion and column type change are monitored.

rules:
  - assertion: change for schema
    fail:
      - when column delete
      - when column type change

Table schema change	Check result
Column added	Pass (no condition matched)
Column deleted	Red alert
Column type change	Red alert
Column order change	Pass (no condition matched)

Example 3: Only the warn condition is set; the fail condition is not set. A column type change or a column order change triggers only an orange alert, not a red alert.

rules:
  - assertion: change for schema
    warn:
      - when column type change
      - when column index change

How detection works

For each check, the system compares the table schema collected in real time (the sample value) with the baseline table schema (the baseline value):

Baseline value: the most recent valid table schema collected by the same rule.
Sample value: the table schema collected in real time during this check.
First check: when no historical sample value exists, the system uses the table schema collected this time as the baseline value, and the check passes.

Each type of change is determined as follows.

Change type	How it is determined
Column deletion	The column exists in the baseline value but not in the sample value.
Column addition	The column exists in the sample value but not in the baseline value.
Column order change	Only the columns that exist in both the baseline value and the sample value are compared. A column order change is identified when the relative order of these columns changes. Position shifts of other columns caused by a column being added or deleted are not counted as a column order change.
Column type change	The same column has different data types in the baseline value and the sample value.

Note

Regardless of the conditions configured in the rule, the system records all changes detected in this check. Change types that are not specified in the conditions do not affect the check result.

View check results

When you view the check details of a rule, a Schema Check rule displays the following in the Quality collection and comparison status results section:

Check item: displayed as Table Schema.
Actual result: the number of columns in the sample value, plus the counts of deleted columns, added columns, type changes, and order changes.
Reference: the number of columns in the baseline value, labeled as Schema Snapshot.
Difference: the total number of changes. On the first check, this is displayed as First Check.

Click View Full Table Schema Comparison to see the column lists of the baseline value and the sample value side by side. Changed columns are marked in different colors. On the first check, there is no baseline value, and only the current table schema is displayed.

Supported data sources

Schema Check rules support the following data sources: MaxCompute, Hologres, EMR Hive, CDH Hive, Lindorm, MySQL, StarRocks, SQL Server, and SelectDB.