DBT CLI Selector – Run tests for a specific column on a specific model

Question

I have a DBT model with many tests. I want to run tests for only 1 specific column. How can I do that from the CLI?

Example model:

my_table.yml 


models:
  - name: my_table
    columns:
      - name: col_1
        tests:
          - unique
          - accepted_values:
            values:
              - 1
              - 2
      - name: col_2
        tests:
          - not_null
          - accepted_values:
            values:
              - 3
              - 4

Selector I can use to run only not_null tests:

dbt test -s my_table,test_name:not_null

What I want (which isn't supported, I don't think) – a selector that will run all tests for col_1 only:

dbt test -s my_table,column_name:col_1

Answer 1

When using dbt, selecting a specific column directly with the select attribute isn't supported. Instead, you must reference a resource from a defined set: models, sources, seeds, snapshots, or tests.

To test a single column, you can create a custom singular test.

A singular test operates in a straightforward manner: if you can formulate a SQL query that identifies failing rows, you can save that query within a .sql file in your test directory. This saved query becomes a test, executed using the dbt test command.

To define a singular test, create a file named my_col_1_null_test.sql in your test folder (by default it is the tests folder) with the following content:

# you can put any custom logic
SELECT * FROM {{ref('my_table')}} WHERE col_1 IS NULL

This SQL query selects all records where col_1 is null. The test fails if any records are fetched matching this criterion.

Execute the test using dbt test --select my_col_1_null_test.

For further details, refer to the documentation on test selection examples and singular tests.