[Feature] Add reference column to "not_null" generic test

[gecko655]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-adapter functionality, rather than a Big Idea better suited to a discussion

Describe the feature

We have not_null generic test that checks a column that must not have null value.

dbt-adapters/dbt/include/global_project/macros/generic_test_sql/not_null.sql

Lines 1 to 9 in 2a99e36

	{% macro default__test_not_null(model, column_name) %}
	{% set column_list = '*' if should_store_failures() else column_name %}
	select {{ column_list }}
	from {{ model }}
	where {{ column_name }} is null
	{% endmacro %}

Using it without --store_failures option, dbt show -s [test_name] will show the null column name with its value "null".

For example we have a table definition:

version: 2

models:
  - name: sample_table
    columns:
      - name: id
        tests:
          - not_null
      - name: sample_column
        tests:
          - not_null

and we have following table data, which fails the test.

id	sample_column
1	x
2	y
3

In this case, dbt show -s not_null_sample_table_sample_column command shows the failure of sample_column with invalid sample_column value "null".
But we cannot get the information of the id column that fails from dbt show . We have to query again to find which row is wrong.

05:20:08  Previewing node 'not_null_sample_table_sample_column':
| sample_column |
| ------------- |
|               |

I thought it will be good to SELECT reference column together when testing not_null.

The output will look something like this:

05:20:08  Previewing node 'not_null_sample_table_sample_column':
| sample_column | id |
| ------------- | -- |
|               |  3 |

Describe alternatives you've considered

Declaring the following generic test by my own repository also solves the problem.

{% macro default__test_not_null(model, column_name, ref_column_name=null) %}

{% set column_list = '*' if should_store_failures() else column_name %}
{% set column_list = ([column_name, ref_column_name] | join(',')) if ref_column_name != null else column_name %}

select {{ column_list }}
from {{ model }}
where {{ column_name }} is null

{% endmacro %}

But I thought it will be good to place it in dbt-labs/dbt-adapters repository.

Who will this benefit?

dbt test command users that use not_null generic test but don't use --store_failures option

Are you interested in contributing this feature?

Yes

Anything else?

If I get some 👍 s to this issue, I'd like to add the SQL macro into this repository.