Missing metrics

Last modified on 29-Mar-23

Use a missing metric in a check to surface missing values in the data in your dataset.
Read more about SodaCL metrics and checks in general.

checks for dim_customer
  - missing_count(birthday) = 0
  - missing_percent(gender) < 5%
  - missing_count(birthday) = 0:
      missing regex: (0?[0-9]|1[012])[/](0?[0-9]|[12][0-9]|3[01])[/](0000|(19|20)?\d\d)
  - missing_count(last_name) < 5:
      missing values: [n/a, NA, none]
  - missing_percent(email_address) = 0

Define checks with missing metrics
    Specify missing values or missing regex
    Failed row samples
Optional check configurations
List of missing metrics
List of configuration keys
List of comparison symbols and phrases
Go further

Define checks with missing metrics

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

You can use both missing metrics in checks that apply to individual columns in a dataset; you cannot use missing metrics in checks that apply to entire datasets. Identify the column by adding a value in the argument between brackets in the check.

• SodaCL considers NULL as the default value for “missing”.
• If you wish, you can add a % character to the threshold for a missing_percent metric for improved readability.

checks for dim_customer:
  - missing_count(birthday) = 0

You can use missing metrics in checks with fixed thresholds, or relative thresholds, but not change-over-time thresholds. See Checks with fixed thresholds for more detail.

checks for dim_reseller:
# a check with a fixed threshold
  - missing_percent(phone) = 5%
# a check with a relative threshold
  - missing_percent(number_employees) < 5

Specify missing values or missing regex

SodaCL considers NULL as the default value for “missing”. In the two check examples above, Soda executes the checks to count the number or values which are NULL, or the percent of values which are NULL relative to the total row count of the column.

However, you can use a nested configuration key:value pair to provide your own definition of a missing value. See List of configuration keys below.

A check that uses a missing metric has four or six mutable parts:

The example below defines two checks. The first check applies to the column last_name. The missing values configuration key specifies that any of the three values in the list exist in a row in that column, Soda recognizes those values as missing values. The check fails if Soda discovers more than five values that match NA, n/a, or '0'.

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

The second check uses a regular expression to define what qualifies as a missing value in the birthday column so that any values that are 00/00/0000 qualify as missing. This check passes if Soda discovers no values that match the pattern defined by the regex.

checks for dim_customer:
  - missing_count(last_name) < 5:
      missing values: [NA, n/a, '0']
  - missing_count(birthday) = 0:
      missing regex: (0?[0-9]|1[012])[/](0?[0-9]|[12][0-9]|3[01])[/](0000|(19|20)?\d\d)

First check:

Second check:

Failed row samples

Checks with missing metrics automatically collect samples of any failed rows to display Soda Cloud. The default number of failed row samples that Soda collects and displays is 100.

If you wish to limit or broaden the sample size, you can use the samples limit configuration in a check with a missing metric. You can add this configuration to your checks YAML file for Soda Core, or when writing checks as part of an agreement in Soda Cloud.

checks for dim_customer:
  - missing_percent(email_address) < 50:
      samples limit: 2

For security, you can add a configuration to your data source connection details to prevent Soda from collecting failed rows samples from specific columns that contain sensitive data. Refer to Disable failed rows sampling for specific columns.

Alternatively, you can set the samples limit to 0 to prevent Soda from collecting and sending failed rows samples for an individual check, as in the following example.

checks for dim_customer:
  - missing_percent(email_address) < 50:
      samples limit: 0

To review the failed rows in Soda Cloud, navigate to the Checks dashboard, then click the row for a check for missing values. Examine failed rows in the Failed rows tab; see Examine failed rows for further details.

Optional check configurations

Example with check name

checks for dim_customer:
  - missing_count(birthday) = 0:
      missing regex: (0?[0-9]|1[012])[/](0?[0-9]|[12][0-9]|3[01])[/](0000|(19|20)?\d\d)
      name: Date entered as 00/00/0000

Example with alert configuration

checks for dim_customer:
  - missing_percent(marital_status):
      valid length: 1
      warn: when < 5
      fail: when >= 5  

Example with in-check filter

checks for dim_customer:
  - missing_count(first_name) < 5:
      missing values: [NA, none]
      filter: number_children_at_home > 2

Example with quotes

checks for dim_reseller:
  - missing_percent("phone", "address_line1") = 0

Example with for each

for each dataset T:
  datasets:
    - dim_product
    - dim_product_%
  checks:
    - missing_count(product_line) = 0

Example with dataset filter

filter CUSTOMERS [daily]:
  where: TIMESTAMP '{ts_start}' <= "ts" AND "ts" < TIMESTAMP '${ts_end}'

checks for CUSTOMERS [daily]:
  - missing_count(user_id) = 0

List of missing metrics

List of configuration keys

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

List of comparison symbols and phrases

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

Go further

• Use missing metrics in checks with alert configurations to establish warn and fail zones
• Use missing metrics in checks to define ranges of acceptable thresholds using boundary thresholds.
• Need help? Join the Soda community on Slack.
• Reference tips and best practices for SodaCL.
  

Was this documentation helpful?

What could we do to improve this page?

• Suggest a docs change in GitHub.
• Share feedback in the Soda community on Slack.

Last modified on 29-Mar-23