System rule templates list

Knowledge Catalog (formerly Dataplex Universal Catalog) provides the following system rule templates that can be used in any region.

Table shape

Name	Description
Column Count Equality Expectation	Aggregate rule that evaluates whether the number of columns in the table equals a specified value. This rule requires bigquery.tables.get, bigquery.tables.list, and bigquery.datasets.get permissions on the table and dataset being queried.
Column Count Range Expectation	Aggregate rule that evaluates whether the number of columns in the table lies within a specified range. This rule requires bigquery.tables.get, bigquery.tables.list, and bigquery.datasets.get permissions on the table and dataset being queried.
Column Existence Expectation	Aggregate rule that evaluates whether a specific column exists in the table. This rule requires bigquery.tables.get, bigquery.tables.list, and bigquery.datasets.get permissions on the table and dataset being queried.
Freshness Expectation	Aggregate rule that evaluates whether the latest data in a column is newer than a specified interval.
Grouped Freshness Expectation	Aggregate rule that evaluates whether the latest grouped data in a column is newer than a specified interval.
Ordered Schema Match Expectation	Aggregate rule that evaluates whether the ordered columns in the table exactly match an ordered, specified list.
Row Count Equality Expectation	Aggregate rule that evaluates whether the number of rows in the table equals a specified value.
Row Count Range Expectation	Aggregate rule that evaluates whether the number of rows in the table lies within a specified range.
Schema Containment Expectation	Aggregate rule that evaluates whether the set of columns in the table contain all the columns in a specified set. This rule requires bigquery.tables.get, bigquery.tables.list, and bigquery.datasets.get permissions on the table and dataset being queried.
Schema Exclusion Expectation	Aggregate rule that evaluates whether the columns in the table aren't in a specified set. This rule requires bigquery.tables.get, bigquery.tables.list, and bigquery.datasets.get permissions on the table and dataset being queried.
Schema Match Expectation	Aggregate rule that evaluates whether the columns in the table exactly match a specified set. This rule requires bigquery.tables.get, bigquery.tables.list, and bigquery.datasets.get permissions on the table and dataset being queried.
Table Comparison Aggregate Equality Expectation	Aggregate rule that evaluates whether the result of an aggregate expression is equal between the current table and a specified reference table.
Table Comparison Aggregate Range Expectation	Aggregate rule that evaluates whether the result of an aggregate expression on the current table deviates from the reference table by more than a specified tolerance.
Table Comparison Row Count Equality Expectation	Aggregate rule that evaluates whether the number of rows in the current table is equal to the number of rows in a specified reference table, with optional row filtering before counting.
Table Comparison Row Count Factor Equality Expectation	Aggregate rule that evaluates whether the number of rows in the current table is equal to a factor of the number of rows in a specified reference table, with optional row filtering before counting.

Missing values, unique values, and types

Name	Description
Column Type Expectation	Aggregate rule that evaluates whether the column data type matches a specified type. This rule requires bigquery.tables.get, bigquery.tables.list, and bigquery.datasets.get permissions on the table and dataset being queried.
Column Type List Expectation	Aggregate rule that evaluates whether the column data type is in a specified set. This rule requires bigquery.tables.get, bigquery.tables.list, and bigquery.datasets.get permissions on the table and dataset being queried.
Non Null Expectation	Row-level rule that evaluates whether each column value is not null.
Null Expectation	Row-level rule that evaluates whether each column value is null.
String Case Expectation	Row-level rule that evaluates whether each column value string matches a specified casing.
Uniqueness Expectation	Row-level rule that evaluates whether each column value is unique.

Sets and ranges

Name	Description
Decreasing Expectation	Aggregate rule that evaluates whether the values in a column are decreasing. NULL values are ignored. Returns the first row where the expectation isn't met.
Increasing Expectation	Aggregate rule that evaluates whether the values in a column are increasing. NULL values are ignored. Returns the first row where the expectation isn't met.
Range Expectation	Row-level rule that evaluates whether each column value lies within a specified range.
Reference Set Expectation	Row-level rule that evaluates whether each column value is contained by a specified reference set.
Set Exclusion Expectation	Row-level rule that evaluates whether each column value isn't contained by a specified set.
Set Expectation	Row-level rule that evaluates whether each column value is contained by a specified set.

String matching

Name	Description
Pattern Exclusion Expectation	Row-level rule that evaluates whether each column value doesn't match a specified SQL LIKE pattern.
Pattern Exclusion Expectation List	Row-level rule that evaluates whether each column value doesn't match any of the specified SQL LIKE patterns.
Pattern Expectation	Row-level rule that evaluates whether each column value matches a specified SQL LIKE pattern.
Pattern Expectation List	Row-level rule that evaluates whether each column value matches at least one of the specified SQL LIKE patterns.
Regex Exclusion Expectation	Row-level rule that evaluates whether each column value doesn't match a specified regular expression.
Regex Exclusion Expectation List	Row-level rule that evaluates whether each column value doesn't match any of the specified regular expressions.
Regex Expectation	Row-level rule that evaluates whether each column value matches a specified regular expression.
Regex Expectation List	Row-level rule that evaluates whether each column value matches at least one of the specified regular expressions.
String Length Equality Expectation	Row-level rule that evaluates whether each column value string length equals a specified value.
String Length Range Expectation	Row-level rule that evaluates whether each column value string length lies within a specified range.

Aggregate functions

Name	Description
Distinct Values Containment Expectation	Aggregate rule that evaluates whether the set of distinct column values contains all the values in a specified set.
Distinct Values Count Equality Expectation	Aggregate rule that evaluates whether the number of distinct values in the column equals a specified value.
Distinct Values Count Range Expectation	Aggregate rule that evaluates whether the number of distinct values in the column lies within a specified range.
Distinct Values Match Expectation	Aggregate rule that evaluates whether the set of distinct column values exactly matches a specified set.
Distinct Values Proportion Expectation	Aggregate rule that evaluates whether the proportion of distinct values in the column (that is, distinct values count / total rows count) is within a specified range.
Distinct Values Set Expectation	Aggregate rule that evaluates whether each distinct column value is contained by a specified set.
Max Range Expectation	Aggregate rule that evaluates whether the column maximum lies within a specified range.
Mean Range Expectation	Aggregate rule that evaluates whether the column average lies within a specified range. If a floating-point type is returned, the AVG() function's result is non-deterministic.
Median Range Expectation	Aggregate rule that evaluates whether the column median lies within a specified range.
Min Range Expectation	Aggregate rule that evaluates whether the column minimum lies within a specified range.
Mode Range Expectation	Aggregate rule that evaluates whether all column modes (most frequent values) lie within a specified range. If there are multiple modes, each will be checked against the range and any failing modes will be returned.
Mode Set Expectation	Aggregate rule that evaluates whether all column modes (most frequent values) are within the specified set. NULL values are ignored when calculating modes. If there are multiple modes with the same highest frequency, each will be checked against the set and any failing modes will be returned in an array.
Quantile Range Expectation	Aggregate rule that evaluates whether the value at the specified quantile for the column lies within a specified range.
Standard Deviation Range Expectation	Aggregate rule that evaluates whether the column standard deviation lies within a specified range.
Sum Range Expectation	Aggregate rule that evaluates whether the column sum lies within a specified range. If a floating-point type is returned, the SUM() function's result is non-deterministic.
Table Comparison Unique Values Count Equality Expectation	Aggregate rule that evaluates whether the number of unique values in a specified column is equal between the current table and a reference table.

Multi-column

Name	Description
Column Comparison Equality Expectation	Row-level rule that evaluates whether the value in column A is equal to the value in column B for each row.
Column Comparison Greater Expectation	Row-level rule that evaluates whether the value in column A is greater than the value in column B for each row.
Column Pairs Set Expectation	Aggregate rule that evaluates whether the paired values from column A and column B are contained by a specified set.
Multi Column Sum Equality Expectation	Aggregate rule that evaluates whether the sum of all rows across a specified list of columns equals a specified value. If a floating-point type is returned, the SUM() function's result is non-deterministic.
Multi Column Uniqueness Expectation	Row-level rule that evaluates whether the combination of values across a specified list of columns is unique for each row (for example, a multi-column primary key).
Record Column Uniqueness Expectation	Row-level rule that evaluates whether the values across a specified list of columns are unique within each record (row). Note that records can be duplicated.

Distributional functions

Name	Description
Date Part Completeness Expectation	Aggregate rule that evaluates whether a table has data for every interval (for example, every day) within a date range.
Standard Deviation Values Expectation	Row-level rule that evaluates whether each column value lies within a specified number of standard deviations from the column average.

Generic conditions

Name	Description
Row Condition Expectation	Row-level rule that evaluates whether each row in a table passes the specified condition.
SQL Assertion	Aggregate rule that evaluates the number of rows returned for the provided statement. If any rows are returned, this rule fails.
Table Condition Expectation	Aggregate rule that evaluates whether the provided expression is true for a table.