System rule templates list

Knowledge Catalog (formerly Dataplex Universal Catalog) supports a range of rule templates that you can use to define and automate data quality and data validation processes. Implementing these templates is a key part of data governance, ensuring that datasets remain accurate and consistent.

To identify which of these templates to use, you can perform data profiling on your resources to identify potential quality issues. After identifying the required quality checks, you can select the appropriate templates from this page to define rules for your resources.

This page lists the available system rule templates that you can use 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.

What's next