Available Conditions

A condition is the combination of a field, an operator, and a value. Validation Monitors offer a rich library of operators and templated values, to make setting up data quality checks easy. Multiple conditions can be combined using AND and OR logic to make more sophisticated checks.

The following operators and templated values are supported. The metric names for Monitors as Code (MaC) config are listed in the MaC Name column for each condition.

To use templated values, a user must select the is or is not operators. Other operators require a user-defined value.

Operators

ConditionField TypesMaC Name
isnumeric, date, time, string, booleanis
is notnumeric, date, time, string, booleanis (negated: true)
equal tonumeric, date, time, string, booleanequal
not equal tonumeric, date, time, string, booleanequal (negated: true)
greater thannumeric, date, timegreater_than
greater than or equal tonumeric, date, timegreater_than_or_equal
less thannumeric, date, timeless_than
less than or equal tonumeric, date, timeless_than_or_equal
is in setnumeric, stringin_set
is not in setnumeric, stringin_set (negated: true)
containsstringcontains
not containsstringcontains (negated: true)
starts withstringstarts_with
not starts withstringstarts_with (negated: true)
ends withstringends_with
not ends withstringends_with (negated: true)
matches regexstringmatches_regex
not matches regexstringmatches_regex (negated: true)

Templated Values

Users can edit a templated value with SQL if they'd like to tweak it for a specific use case.

Completeness

ConditionField TypesMaC Name
not a number (NaN)numericis_nan
zeronumericis_zero
null stringstringnull_string
containing spacesstringcontaining_spaces
empty stringstringempty_string
nullnumeric, date, time, string, booleannull
all spacesstringall_space

Format

ConditionField TypesMaC Name
emailstringemail
timestamp format ISO-8601Stringtimestamp_iso_8601
timestamp format DD-MM-YYYYstringtimestamp_dd-mm-yyyy
timestamp format DD/MM/YYYYstringtimestamp_dd/mm/yyyy
timestamp format MM-DD-YYYYstringtimestamp_mm-dd-yyyy
timestamp format MM/DD/YYYYstringtimestamp_mm/dd/yyyy
"True", "TRUE", or "true"stringtrue_string
"False", "FALSE", or "false"stringfalse_string
UUIDstringis_uuid
Canada postal codestringca_postal_code
Canada SINstringcan_sin
France postal codestringfr_postal_code
France INSEE codestringfr_insee_code
Germany postal codestringde_postal_code
Germany Tax IDstringde_tax_id
Ireland postal codestringie_postal_code
Ireland PPSNstringie_ppsn
Italy postal codestringit_postal_code
Italy fiscal codestringit_fiscal_code
Netherlands postal codestringnl_postal_code
Netherlands Citizen Service Number (BSN)stringnl_bsn
Poland postal codestringpl_postal_code
Poland National Identification Number (PESEL)stringpl_pesel
Spain postal codestringes_postal_code
Spain National Identity Document Number (DNI)stringes_dni
Switzerland postal codestringch_postal_code
Switzerland Social Security Number (SSN)stringch_oasi
Turkey postal codestringtr_postal_code
Turkey ID numberstringtr_id_no
UK postal codestringuk_postal_code
UK National Insurance Number (NINO)stringuk_nino
US ZIP code (e.g., 94109)stringus_zip_code
US phone numberstringus_phone_number
US state codestringus_state_code
US SSNstringus_ssn

Numeric Range

ConditionField TypesMaC Name
between 0 and 100 (inclusive)numericis_between_0_and_100
between 0 and 1 (inclusive)numericis_between_0_and_1
negativenumericis_negative

Time Range

ConditionField TypesMaC Name
in the futuredate, timein_future
in the pastdate, timein_past
in the past 7 daysdate, timein_past_7_days
in the past 30 daysdate, timein_past_30_days
in the past 365 daysdate, timein_past_365_days
in the past calendar weekdate, timein_past_calendar_week
in the past calendar monthdate, timein_past_calendar_month
yesterdaydate, timeyesterday
todaydate, timetoday
weekdaydate, timeweekday
Sundaydate, timesunday
Mondaydate, timemonday
Tuesdaydate, timetuesday
Wednesdaydate, timewednesday
Thursdaydate, timethursday
Fridaydate, timefriday
Saturdaydate, timesaturday

Defining a Set

The is in set and is not in set operators allow users to define a set using the following methods:

  • From a list: manually enter the values
  • From a field: select a table and field, and the set with be populated with the distinct values in that field. The selected field will be referenced each time the monitor is run, so the set values may change.
  • From a query: write SQL to define the values in the set. The query will run each time the monitor is run, so the set values may change.
Users can define a set from a manually entered list, from a field, or from a query

Users can define a set from a manually entered list, from a field, or from a query