Skip to main content

Simple data validations

Nullity and Uniqueness

Column must not have null values

The indicated column must not have null values in any row.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.

Examples

Consider the next table:

IDANIMALAGE
1DOG3
2CATNULL
3COW4
  • Column must not have null values on the ANIMAL column will return Success ✅
  • Column must not have null values on the AGE column will return Error ❌

Column values must be null

Column values must be null.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.

Examples

Consider the next table:

IDANIMALAGE
1DOGNULL
2CATNULL
3COWNULL
  • Column values must be null on the AGE column will return Success ✅
  • Column values must be null on the ANIMAL column will return Error ❌

Column values must be unique

Column values must be unique across all rows.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.

Examples

Consider the next table:

IDANIMALAGE
1DOG2
2CAT3
3COW3
  • Column values must be unique on the ID column will return Success ✅
  • Column values must be unique on the AGE column will return Error ❌

Set of column values must be unique

Given two or more columns the set of values must be unique across all rows.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
Column listSetSet of comma separated values. Strings should be surrounded by quotes. For example: "column_a","column_b"

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG2Joe
2DOG2Mary
3COW3Alex
  • Set of column values must be unique on the set "ID","ANIMAL","AGE" column will return Success ✅
  • Set of column values must be unique on the set "ANIMAL","AGE" column will return Error ❌

Column values must be unique in the same row

Values for the given columns must be unique for each row.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
Column listSetSet of comma separated values. Strings should be surrounded by quotes. For example: "column_a","column_b"

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG2Joe
2DOG2Mary
3COW3Alex
  • Column values must be unique in the same row on the set "ID","ANIMAL" column will return Success ✅
  • Column values must be unique in the same row on the set "ID","AGE" column will return Error ❌

Set of values

Column values must be in set

Every distinct column value must be contained in the given set.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Value setArraySet of comma separated values. Strings should be surrounded by quotes. For example: "large","small","medium"

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG2Joe
2CAT2Mary
3COW3Alex
  • Column values must be in set with the set "DOG","CAT","COW" on the ANIMAL column will return Success ✅
  • Column values must be in set with the set "JOE","MARY" on the column OWNER will return Error ❌

Column distinct values must contain set

The given set must be contained in the set of distinct column values.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Value setSetSet of comma separated values. Strings should be surrounded by quotes. For example: "large","small","medium"

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG2Joe
2CAT2Mary
3COW3Alex
  • Column distinct values must contain set with the set "DOG","CAT" on the ANIMAL column will return Success ✅
  • Column distinct values must contain set with the set "JOE","PAUL" on the column OWNER will return Error ❌

Column distinct values must be equal to set

Set of distinct column values must be equal to the given set.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Value setSetSet of comma separated values. Strings should be surrounded by quotes. For example: "large","small","medium"

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG2Joe
2CAT2Mary
3COW3Alex
  • Column distinct values must be equal to set with the set "DOG","CAT","COW" on the ANIMAL column will return Success ✅
  • Column distinct values must be equal to set with the set "JOE","MARY" on the column OWNER will return Error ❌

Column most common value must be in set

The most common value in the column must be in the given set.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Value setSetSet of comma separated values. Strings should be surrounded by quotes. For example: "large","small","medium"

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG2Mary
2DOG2Mary
3COW3Alex
  • Column most common value must be in set with the set "DOG","CAT" on the ANIMAL column will return Success ✅
  • Column most common value must be in set with the set "JOE","PAUL" on the column OWNER will return Error ❌

Values in range

Column maximum value must be in range

The column maximum value must be between a minimum value and a maximum value. This validation is by default inclusive.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Minimum valueNumberMinimum allowed value.
Exclude minimum valueBooleanIf you check this option the minimum value must be strictly larger than the specified minimum value.
Maximum valueNumberMaximum allowed value.
Exclude maximum valueBooleanIf you check this option the maximum value must be strictly smaller than the specified maximum value.

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG1Mary
2CAT2Joe
3COW3Alex
  • Column maximum value must be in range with a minimum value of 0 and a maximum value of 20 on the AGE column will return Success ✅
  • Column maximum value must be in range with a minimum value of 3 (excluding the minimum value) and a maximum value of 20 on the AGE column will return Error ❌

Column mean value must be in range

Column mean value must be between a minimum value and a maximum value. This validation is by default inclusive.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Minimum valueNumberThe minimum value for the column mean.
Exclude minimum valueBooleanIf you check this option the mean value must be strictly larger than the specified minimum value.
Maximum valueNumberThe maximum value for the column mean.
Exclude maximum valueBooleanIf you check this option the mean value must be strictly smaller than the specified maximum value.

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG1Mary
2CAT2Joe
3COW3Alex
  • Column mean value must be in range with a minimum value of 2 and a maximum value of 10 on the AGE column will return Success ✅
  • Column mean value must be in range with a minimum value of 1 and a maximum value of 3 (excluding the maximum value) on the AGE column will return Error ❌

Column minimum value must be in range

Column minimum value must be between a minimum value and a maximum value. This validation is by default inclusive.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Minimum valueNumberMinimum allowed value.
Exclude minimum valueBooleanIf you check this option the min value must be strictly larger than the specified minimum value.
Maximum valueNumberMaximum allowed value.
Exclude maximum valueBooleanIf you check this option the min value must be strictly smaller than the specified maximum value.

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG1Mary
2CAT2Joe
3COW3Alex
  • Column minimum value must be in range with a minimum value of 0 and a maximum value of 20 on the AGE column will return Success ✅
  • Column minimum value must be in range with a minimum value of 1 (excluding the minimum value) and a maximum value of 20 on the AGE column will return Error ❌

Column proportion of unique values must be in range

Proportion of unique values must be between a minimum value and a maximum value.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Minimum valueNumberThe minimum proportion of unique values. (Proportions are on the range 0 to 1)
Exclude minimum valueBooleanIf you check this option the proportion of unique values must be strictly greater than the specified minimum value.
Maximum valueNumberThe maximum proportion of unique values. (Proportions are on the range 0 to 1)
Exclude maximum valueBooleanIf you check this option the proportion of unique values must be strictly smaller than the specified maximum value

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG1Mary
2CAT2Joe
3CAT3Alex
  • Column proportion of unique values must be in range with a minimum value of 0.5 and a maximum value of 0.8 on the ANIMAL column will return Success ✅.
  • Column proportion of unique values must be in range with a minimum value of 0.8 and a maximum value of 1 on the ANIMAL column will return Error ❌

In this case the ANIMAL column has 3 total values and 2 unique values so the proportion of unique values will be 0.66.

Column total unique values must be in range

The total number of unique values must be between a minimum value and a maximum value. This validation is by default inclusive.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Minimum valueNumberThe minimum number of unique values allowed.
Maximum valueNumberThe maximum number of unique values allowed.

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG1Mary
2CAT2Joe
3COW3Alex
  • Column total unique values must be in range with a minimum value of 3 and a maximum value of 5 on the ANIMAL column will return Success ✅.
  • Column total unique values must be in range with a minimum value of 10 and a maximum value of 20 on the ANIMAL column will return Error ❌

Multiple columns

Column A must be greater than column B

Every value in column A must be greater than the value in column B for the same row.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
Column AColumnThe column A name.
Column BColumnThe column B name.
Values can be equalBooleanIf you check this option, the values in column A can be equal to column B and not strictly greater.

Examples

Consider the next table:

IDANIMALANIMAL_AGEOWNEROWNER_AGE
1DOG1Mary23
2CAT2Joe30
3COW3Alex42
  • Column A must be greater than column B with column A OWNER_AGE and column B ANIMAL_AGE column will return Success ✅.
  • Column A must be greater than column B with column A ANIMAL_AGE and column B OWNER_AGE column will return Error ❌.

String values

Strings length must be in range

Column values must be strings with length between a minimum value and a maximum value. This validation is by default inclusive.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
Minimum valueNumberThe minimum value for the string length.
Maximum valueNumberThe maximum value for the string length.

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG1Mary
2CAT2Joe
3COW3Alex
  • Strings length must be in range with a minimum value of 3 and a maximum value of 15 on the ANIMAL column will return Success ✅.
  • Strings length must be in range with a minimum value of 5 and a maximum value of 20 on the OWNER column will return Error ❌

Strings length must be equal to

Column values must be strings with length equal to the given value.

Available for

ConnectorAvailable
BigQuery
MySQL
PostgreSQL
Snowflake

Parameters

NameTypeDescription
ColumnColumnThe column name to validate.
ValueNumberString length value.

Examples

Consider the next table:

IDANIMALAGEOWNER
1DOG1Mary
2CAT2Joe
3COW3Alex
  • Strings length must be in range with a value of 3 on the ANIMAL column will return Success ✅.
  • Strings length must be in range with a value of 4 on the OWNER column will return Error ❌