Tests with context

A set of generic tests that extend common dbt, dbt-utils, and dbt-expectations tests with a context_columns parameter. When a test fails, the failing rows are returned together with the columns you care about — making it much easier to investigate the root cause directly from the test results. If context_columns is omitted, all columns are returned alongside failing rows. A single column can be given as a plain string instead of a list.

No extra packages are required. These tests ship with the Elementary dbt package and do not depend on dbt_utils or dbt_expectations being installed, even where they mirror a test from one of those packages.

If a column listed in context_columns does not exist on the model, a warning is logged and that column is skipped. The test continues and will not error.

To test only some of the rows, use dbt’s where config. Earlier versions of some of these tests took a row_condition argument instead; it was removed in favour of where, which does the same thing and works on every dbt test.

not_null_with_context

elementary.not_null_with_context Validates that there are no null values in a column. Extends dbt’s built-in not_null test.

Parameters

Parameter	Required	Default	Description
`column_name`	Yes	—	The column to test for null values.
`context_columns`	No	`none`	List of additional columns to return with failing rows. Omit to return all columns.

models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - elementary.not_null_with_context:
              context_columns: [customer_id, order_date, amount]

models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - elementary.not_null_with_context

expression_is_true_with_context

elementary.expression_is_true_with_context Validates that a SQL expression holds for every row. Extends dbt_utils.expression_is_true. This is the most flexible test in the set: any condition you can write in SQL, including range checks and comparisons across columns.

This test is table-level, so it has no tested column of its own. List every column you want in the sample under context_columns, including the ones your expression references.

Parameters

Parameter	Required	Default	Description
`expression`	Yes	—	SQL condition that must hold for every row. Rows where it is false are returned.
`column_name`	No	`none`	Apply the expression to a single column, as in `column_name: amount` with `expression: "> 0"`.
`context_columns`	No	`none`	List of additional columns to return with failing rows. Omit to return all columns.

models:
  - name: orders
    data_tests:
      - elementary.expression_is_true_with_context:
          expression: "amount >= 0 and amount <= 10000"
          context_columns: [amount, order_id, customer_id]

models:
  - name: subscriptions
    data_tests:
      - elementary.expression_is_true_with_context:
          expression: "end_date > start_date"
          context_columns: [subscription_id, start_date, end_date]

not_empty_string_with_context

elementary.not_empty_string_with_context Validates that a column contains no empty strings. Extends dbt_utils.not_empty_string.

Parameters

Parameter	Required	Default	Description
`column_name`	Yes	—	The column to test.
`trim_whitespace`	No	`true`	Whether to trim the value before testing, so whitespace-only values also fail.
`context_columns`	No	`none`	List of additional columns to return with failing rows. Omit to return all columns.

models:
  - name: customers
    columns:
      - name: name
        data_tests:
          - elementary.not_empty_string_with_context:
              context_columns: [customer_id, email, created_at]

models:
  - name: customers
    columns:
      - name: notes
        data_tests:
          - elementary.not_empty_string_with_context:
              trim_whitespace: false

expect_column_values_to_not_be_null_with_context

elementary.expect_column_values_to_not_be_null_with_context Expects column values to not be null. Extends dbt_expectations.expect_column_values_to_not_be_null.

Parameters

Parameter	Required	Default	Description
`column_name`	Yes	—	The column to test for null values.
`context_columns`	No	`none`	List of additional columns to return with failing rows. Omit to return all columns.

models:
  - name: subscriptions
    columns:
      - name: end_date
        data_tests:
          - elementary.expect_column_values_to_not_be_null_with_context:
              config:
                where: "status = 'active'"
              context_columns: [subscription_id, customer_id, start_date]

expect_column_values_to_be_unique_with_context

elementary.expect_column_values_to_be_unique_with_context Expects column values to be unique. Returns all duplicate rows (not just a count), so you can see the full context of each duplicate. Extends dbt_expectations.expect_column_values_to_be_unique. Null values are ignored rather than treated as duplicates of one another, matching dbt’s built-in unique test.

Parameters

Parameter	Required	Default	Description
`column_name`	Yes	—	The column to test for uniqueness.
`context_columns`	No	`none`	List of additional columns to return with failing rows. Omit to return all columns.

models:
  - name: customers
    columns:
      - name: email
        data_tests:
          - elementary.expect_column_values_to_be_unique_with_context:
              context_columns: [customer_id, created_at, name]

expect_compound_columns_to_be_unique_with_context

elementary.expect_compound_columns_to_be_unique_with_context Expects a combination of columns to be unique. Returns every duplicate row, so you can see the full context of each collision. Extends dbt_expectations.expect_compound_columns_to_be_unique. Rows where every listed column is null are ignored rather than treated as duplicates of one another. Use this when no single column identifies a row, for example when uniqueness is on a customer and date pair.

Parameters

Parameter	Required	Default	Description
`column_list`	Yes	—	List of columns whose combination must be unique.
`context_columns`	No	`none`	List of additional columns to return with failing rows. Omit to return all columns.

models:
  - name: daily_metrics
    data_tests:
      - elementary.expect_compound_columns_to_be_unique_with_context:
          column_list: [customer_id, metric_date]
          context_columns: [metric_value, loaded_at]

expect_column_pair_values_A_to_be_greater_than_B_with_context

elementary.expect_column_pair_values_A_to_be_greater_than_B_with_context Expects the values of one column to be greater than another. Extends dbt_expectations.expect_column_pair_values_A_to_be_greater_than_B. Both compared columns are always returned with failing rows, before any context_columns.

Parameters

Parameter	Required	Default	Description
`column_A`	Yes	—	The column expected to hold the greater value.
`column_B`	Yes	—	The column compared against.
`or_equal`	No	`false`	Whether equal values pass.
`context_columns`	No	`none`	List of additional columns to return with failing rows. Omit to return all columns.

models:
  - name: orders
    data_tests:
      - elementary.expect_column_pair_values_A_to_be_greater_than_B_with_context:
          column_A: total_amount
          column_B: discount_amount
          or_equal: true
          context_columns: [order_id, customer_id]

expect_column_values_to_match_regex_with_context

elementary.expect_column_values_to_match_regex_with_context Expects column values to match a given regular expression. Extends dbt_expectations.expect_column_values_to_match_regex.

Not supported on SQL Server or Microsoft Fabric. T-SQL has no regular expression functions, so this test raises a clear compilation error on those platforms rather than running.

Parameters

Parameter	Required	Default	Description
`column_name`	Yes	—	The column to test.
`regex`	Yes	—	The regular expression pattern to match.
`is_raw`	No	`false`	Whether the regex is a raw string. Honored on Snowflake, BigQuery, Spark and Databricks; ignored elsewhere.
`flags`	No	`""`	Optional regex flags (adapter-dependent).
`context_columns`	No	`none`	List of additional columns to return with failing rows. Omit to return all columns.

models:
  - name: customers
    columns:
      - name: email
        data_tests:
          - elementary.expect_column_values_to_match_regex_with_context:
              regex: "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"
              context_columns: [customer_id, name, created_at]

expect_column_values_to_match_regex_list_with_context

elementary.expect_column_values_to_match_regex_list_with_context Expects column values to match any or all of a list of regular expressions. Extends dbt_expectations.expect_column_values_to_match_regex_list.

Not supported on SQL Server or Microsoft Fabric. T-SQL has no regular expression functions, so this test raises a clear compilation error on those platforms rather than running.

Parameters

Parameter	Required	Default	Description
`regex_list`	Yes	—	List of regular expression patterns.
`match_on`	No	`"any"`	`"any"` passes a row that matches at least one pattern, `"all"` requires every pattern. Any other value raises an error.
`is_raw`	No	`false`	Whether the patterns are raw strings. Honored on Snowflake, BigQuery, Spark and Databricks; ignored elsewhere.
`flags`	No	`""`	Optional regex flags. Flags the platform does not support are dropped with a warning.
`context_columns`	No	`none`	List of additional columns to return with failing rows. Omit to return all columns.

models:
  - name: contacts
    columns:
      - name: phone
        data_tests:
          - elementary.expect_column_values_to_match_regex_list_with_context:
              regex_list:
                - "^\\+1[0-9]{10}$"
                - "^\\+44[0-9]{10}$"
              match_on: any
              context_columns: [contact_id, country]

models:
  - name: products
    columns:
      - name: sku
        data_tests:
          - elementary.expect_column_values_to_match_regex_list_with_context:
              regex_list: ["^SKU-", "-[0-9]+$"]
              match_on: all
              context_columns: [product_id, name]

relationships_with_context

elementary.relationships_with_context Validates referential integrity between a child and parent table. Extends dbt’s built-in relationships test.

Parameters

Parameter	Required	Default	Description
`column_name`	Yes	—	The foreign key column in the child model.
`to`	Yes	—	The parent model (use `ref()` or `source()`).
`field`	Yes	—	The column in the parent model to join to.
`context_columns`	No	`none`	List of additional columns from the child model to return with failing rows. Omit to return all columns.

models:
  - name: orders
    columns:
      - name: customer_id
        data_tests:
          - elementary.relationships_with_context:
              to: ref('customers')
              field: id
              context_columns: [order_id, order_date, amount]

models:
  - name: orders
    columns:
      - name: customer_id
        data_tests:
          - elementary.relationships_with_context:
              to: ref('customers')
              field: id

accepted_range_with_context

elementary.accepted_range_with_context

Deprecated, and scheduled for removal in the next release. Use dbt_utils.accepted_range instead. Running it logs a warning.

dbt_utils.accepted_range selects every column already, so unlike the other tests on this page this one cannot add context to a stored sample. The only thing it can do is narrow the sample to a chosen subset, which is not what context_columns is for. If you were using it to keep columns out of a stored sample, there is no direct replacement. Use the sampling controls instead: the show_sample_rows and PII tags, disable_test_samples, or test_sample_row_count.

Migration

Before

models:
  - name: orders
    columns:
      - name: amount
        data_tests:
          - elementary.accepted_range_with_context:
              min_value: 0
              max_value: 10000

After

models:
  - name: orders
    columns:
      - name: amount
        data_tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 10000

Elementary dbt package

Elementary Data tests

Anomaly Detection Tests

Schema Tests

AI Data Tests (Beta)

Other Tests

dbt Package Release Notes

not_null_with_context

Parameters

expression_is_true_with_context

Parameters

not_empty_string_with_context

Parameters

expect_column_values_to_not_be_null_with_context

Parameters

expect_column_values_to_be_unique_with_context

Parameters

expect_compound_columns_to_be_unique_with_context

Parameters

expect_column_pair_values_A_to_be_greater_than_B_with_context

Parameters

expect_column_values_to_match_regex_with_context

Parameters

expect_column_values_to_match_regex_list_with_context

Parameters

relationships_with_context

Parameters

accepted_range_with_context

Migration

​not_null_with_context

​Parameters

​expression_is_true_with_context

​Parameters

​not_empty_string_with_context

​Parameters

​expect_column_values_to_not_be_null_with_context

​Parameters

​expect_column_values_to_be_unique_with_context

​Parameters

​expect_compound_columns_to_be_unique_with_context

​Parameters

​expect_column_pair_values_A_to_be_greater_than_B_with_context

​Parameters

​expect_column_values_to_match_regex_with_context

​Parameters

​expect_column_values_to_match_regex_list_with_context

​Parameters

​relationships_with_context

​Parameters

​accepted_range_with_context

​Migration

not_null_with_context

Parameters

expression_is_true_with_context

Parameters

not_empty_string_with_context

Parameters

expect_column_values_to_not_be_null_with_context

Parameters

expect_column_values_to_be_unique_with_context

Parameters

expect_compound_columns_to_be_unique_with_context

Parameters

expect_column_pair_values_A_to_be_greater_than_B_with_context

Parameters

expect_column_values_to_match_regex_with_context

Parameters

expect_column_values_to_match_regex_list_with_context

Parameters

relationships_with_context

Parameters

accepted_range_with_context

Migration