tests

• Models
• Sources
• Seeds
• Snapshots
• Analyses

models/<filename>.yml

version: 2

models:
  - name: <model_name>
    tests:
      - <test_name>:
          <argument_name>: <argument_value>
          config:
            <test_config>: <config-value>

    columns:
      - name: <column_name>
        tests:
          - <test_name>
          - <test_name>:
              <argument_name>: <argument_value>
              config:
                <test_config>: <config-value>

Related documentation

• Testing guide

Description

The tests property defines assertions about a column, table, or view. The property contains a list of generic tests (referenced by name), which can include the four built-in generic tests available in dbt. It can also include any arguments or configurations passed to those tests.

Once these tests are defined, you can validate their correctness by running dbt test.

test_name

not_null

This test validates that there are no null values present in a column.

models/<filename>.yml

version: 2

models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - not_null

unique

This test validates that there are no duplicate values present in a field.

models/<filename>.yml

version: 2

models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique

accepted_values

This test validates that all of the values in a column are present in a supplied list of values. If any values other than those provided in the list are present, then the test will fail.

The accepted_values test supports an optional quote parameter which, by default, will single-quote the list of accepted values in the test query. To test non-strings (like integers or boolean values) explicitly set the quote config to false.

schema.yml

version: 2

models:
  - name: orders
    columns:
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned']

      - name: status_id
        tests:
          - accepted_values:
              values: [1, 2, 3, 4]
              quote: false

relationships

This test validates that all of the records in a child table have a corresponding record in a parent table. This property is referred to as "referential integrity".

The following example tests that every order's customer_id maps back to a valid customer.

schema.yml

version: 2

models:
  - name: orders
    columns:
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: id

The to argument accepts a Relation – this means you can pass it a ref to a model (e.g. ref('customers')), or a source (e.g. source('jaffle_shop', 'customers')).

Additional examples

Testing an expression

Some tests require multiple columns, so it doesn't make sense to nest them under the columns: key. In this case you can apply the test to the model (or source, seed or snapshot) instead:

models/orders.yml

version: 2

models:
  - name: orders
    tests:
      - unique:
          column_name: "country_code || '-' || order_id"

Define and use a custom generic test

If you define your own custom generic ("schema") test, you can use that as the test_name:

models/<filename>.yml

version: 2

models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - primary_key

Check out the guide on writing a custom generic test for more information.