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. |
