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
| Condition | Field Types | MaC Name |
|---|---|---|
| is | numeric, date, time, string, boolean | is |
| is not | numeric, date, time, string, boolean | is (negated: true) |
| equal to | numeric, date, time, string, boolean | equal |
| not equal to | numeric, date, time, string, boolean | equal (negated: true) |
| greater than | numeric, date, time | greater_than |
| greater than or equal to | numeric, date, time | greater_than_or_equal |
| less than | numeric, date, time | less_than |
| less than or equal to | numeric, date, time | less_than_or_equal |
| is in set | numeric, string | in_set |
| is not in set | numeric, string | in_set (negated: true) |
| contains | string | contains |
| not contains | string | contains (negated: true) |
| starts with | string | starts_with |
| not starts with | string | starts_with (negated: true) |
| ends with | string | ends_with |
| not ends with | string | ends_with (negated: true) |
| matches regex | string | matches_regex |
| not matches regex | string | matches_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
| Condition | Field Types | MaC Name |
|---|---|---|
| not a number (NaN) | numeric | is_nan |
| zero | numeric | is_zero |
| null string | string | null_string |
| containing spaces | string | containing_spaces |
| empty string | string | empty_string |
| null | numeric, date, time, string, boolean | null |
| all spaces | string | all_space |
Format
| Condition | Field Types | MaC Name |
|---|---|---|
| string | ||
| timestamp format ISO-8601 | String | timestamp_iso_8601 |
| timestamp format DD-MM-YYYY | string | timestamp_dd-mm-yyyy |
| timestamp format DD/MM/YYYY | string | timestamp_dd/mm/yyyy |
| timestamp format MM-DD-YYYY | string | timestamp_mm-dd-yyyy |
| timestamp format MM/DD/YYYY | string | timestamp_mm/dd/yyyy |
| "True", "TRUE", or "true" | string | true_string |
| "False", "FALSE", or "false" | string | false_string |
| UUID | string | is_uuid |
| Canada postal code | string | ca_postal_code |
| Canada SIN | string | can_sin |
| Australia postal code | string | aus_postal_code |
| Australia state code | string | aus_state_code |
| France postal code | string | fr_postal_code |
| France INSEE code | string | fr_insee_code |
| Germany postal code | string | de_postal_code |
| Germany Tax ID | string | de_tax_id |
| Ireland postal code | string | ie_postal_code |
| Ireland PPSN | string | ie_ppsn |
| Italy postal code | string | it_postal_code |
| Italy fiscal code | string | it_fiscal_code |
| Netherlands postal code | string | nl_postal_code |
| Netherlands Citizen Service Number (BSN) | string | nl_bsn |
| Poland postal code | string | pl_postal_code |
| Poland National Identification Number (PESEL) | string | pl_pesel |
| Spain postal code | string | es_postal_code |
| Spain National Identity Document Number (DNI) | string | es_dni |
| Switzerland postal code | string | ch_postal_code |
| Switzerland Social Security Number (SSN) | string | ch_oasi |
| Turkey postal code | string | tr_postal_code |
| Turkey ID number | string | tr_id_no |
| UK postal code | string | uk_postal_code |
| UK National Insurance Number (NINO) | string | uk_nino |
| US ZIP code (e.g., 94109) | string | us_zip_code |
| US phone number | string | us_phone_number |
| US state code | string | us_state_code |
| US SSN | string | us_ssn |
Numeric Range
| Condition | Field Types | MaC Name |
|---|---|---|
| between 0 and 100 (inclusive) | numeric | is_between_0_and_100 |
| between 0 and 1 (inclusive) | numeric | is_between_0_and_1 |
| negative | numeric | is_negative |
Time Range
| Condition | Field Types | MaC Name |
|---|---|---|
| in the future | date, time | in_future |
| in the past | date, time | in_past |
| in the past 60 minutes | time | in_past_60_minutes |
| in the past 24 hours | time | in_past_24_hours |
| in the past 7 days | date, time | in_past_7_days |
| in the past 30 days | date, time | in_past_30_days |
| in the past 365 days | date, time | in_past_365_days |
| in the past calendar week | date, time | in_past_calendar_week |
| in the past calendar month | date, time | in_past_calendar_month |
| yesterday | date, time | yesterday |
| today | date, time | today |
| weekday | date, time | weekday |
| Sunday | date, time | sunday |
| Monday | date, time | monday |
| Tuesday | date, time | tuesday |
| Wednesday | date, time | wednesday |
| Thursday | date, time | thursday |
| Friday | date, time | friday |
| Saturday | date, time | saturday |
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
Updated about 1 month ago