Validity metrics

Use a validity metric in a check to surface invalid or unexpected values in your dataset.
Read more about SodaCL metrics and checks in general.

checks for dim_customer:
  - invalid_count(email_address) = 0:
      valid format: email
  - invalid_percent(english_education) = 0:
      valid length: 100
  - invalid_percent(total_children) <= 2:
      valid max: 6
  - invalid_percent(marital_status) = 0:
      valid max length: 10
  - invalid_count(number_cars_owned) = 0:
      valid min: 1
  - invalid_percent(marital_status) = 0:
      valid min length: 1
  - invalid_percent(birthday) < 5%:
      valid regex: (0?[0-9]|1[012])[/](0?[0-9]|[12][0-9]|3[01])[/](0000|(19|20)?\d\d)
  - invalid_count(house_owner_flag) = 0:
      valid values: ['0', '1']

Define checks with validity metrics
Optional check configurations
List of validity metrics
List of configuration keys
List of valid formats
List of comparison symbols and phrases
Go further

Define checks with validity metrics

In the context of SodaCL check types, you use validity metrics in standard checks. Refer to Standard check types for exhaustive configuration details.

You can use all validity metrics in checks that apply to individual columns in a dataset; you cannot use validity metrics in checks that apply to entire datasets. Identify the column(s) by adding one or more values in the argument between brackets in the check.

• You must use a configuration key:value pair to define what qualifies as an valid value.
• If you wish, you can add a % character to the threshold for a invalid_percent metric for improved readability.

checks for dim_customer
  - invalid_count(number_cars_owned) = 0:
      valid min: 1

You can use validity metrics in checks with fixed thresholds, or relative thresholds, but not dynamic thresholds. See Checks with fixed thresholds for more detail.

checks for dim_reseller:
# a check with a fixed threshold
  - invalid_count(email_address) = 0:
      valid format: email
# a check with a relative threshold
  - invalid_percent(english_education) < 3%:
      valid max length: 100

Specify valid values

Use a nested configuration key:value pair to provide your own definition of a valid value. There are several configuration keys that you can use to define what qualifies as valid; the examples below illustrate the use of just a few config keys. See a complete List of configuration keys below.

A check that uses a validity metric has six mutable parts:

The example below defines two checks. The first check applies to the column house_owner_flag. The valid values configuration key specifies that if a row in that column contains anything other than the two valid values in the list, Soda registers them as invalid. The check fails if Soda discovers more than five values that are not 0 or 1.

• Values in a list must be enclosed in square brackets.
• Numeric characters in a valid values list must be enclosed in single quotes.

The second check uses a regular expression to define what qualifies as a valid value in the birthday column so that any values that do not match the pattern defined by the regex qualify as invalid.

checks for dim_customer:
  - invalid_count(house_owner_flag) = 0:
      valid values: ['0', '1']
  - invalid_count(birthday) = 0:
      valid regex: ^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$

First check:

Second check:

Specify valid format

If the data type of the column you are checking is TEXT (such as character, character varying, or string) then you can use the valid format configuration key. This config key uses built-in values that test the data in the column for specific formats, such as email address format, date format, or uuid format. See List of valid formats below.

The check below validates that all values in the email_address column conform to an email address format.

checks for dim_customer:
  - invalid_percent(email_address) = 0:
      valid format: email

Troubleshoot valid format

Problem: You are using a valid format to test the format of values in a column and the CLI returns the following error message when you run a scan.

  | HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Error occurred while executing scan.
  | unsupported operand type(s) for *: 'Undefined' and 'int'

Solution: The error indicates that the data type of the column is not TEXT. Adjust your check to use a different configuration key, instead.

Send failed rows to Soda Cloud

If you have connected Soda Core to a Soda Cloud account, checks with validity metrics automatically send samples of any failed rows to Soda Cloud.

1. To review the failed rows in Soda Cloud, navigate to the Monitors dashboard.
2. Click the row for a check for invalid values, then go to the Failed rows tab.

Optional check configurations

Example with check name

checks for dim_customer:
  - invalid_count(first_name) = 0 :
      valid min length: 2
      name: First name has 2 or more characters

Example with alert configuration

  - invalid_count(house_owner_flag):
      valid values: ['0', '1']
      warn: when between 1 and 5
      fail: when > 6  

Example with filter

checks for dim_customer:
  - invalid_percent(marital_status) = 0:
      valid max length: 1
      filter: total_children = 0

Example with quotes

checks for dim_customer:
  - invalid_count("number_cars_owned") = 0:
      valid min: 1

Example with for each

for each dataset T:
  datasets:
    - dim_customer
    - dim_customer_%
  checks:
    - invalid_count(email_address) = 0:
        valid format: email

Example with dataset filter

coming soon

List of validity metrics

List of configuration keys

The column configuration key:value pair defines what SodaCL ought to consider as valid values.

List of valid formats

Valid formats apply only to columns using data type TEXT.

List of comparison symbols and phrases

 = 
 < 
 >
 <=
 >=
 !=
 <> 
 between 
 not between 

Go further

• Use validity metrics in checks with alert configurations to establish warn and fail zones
• Use validity metrics in checks to define ranges of acceptable thresholds using boundary thresholds.
• Need help? Join the Soda community on Slack.
  

Last modified on 01-Jul-22

Was this documentation helpful?
Share feedback in the Soda community on Slack.

Help improve our docs!

• Request a docs change.
• Edit this page in our GitHub repo.