[CT-271] [Feature] not_null test selects column instead of *

[willbowditch]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

The built in test for not_null writes SQL equivalent to:

SELECT *
FROM model
WHERE column IS NULL

On databases like BigQuery this ends up being quite expensive when compared to:

SELECT column
FROM model
WHERE column IS NULL

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

PR ready to go

Anything else?

No response

[jtcohen6]

@willbowditch Good call!

Two quick thoughts:

• It's occasionally reasonable for databases to reimplement the built-in generic tests, because of an attribute of that database. For instance, BigQuery doesn't support a fairly common group by syntax, which led us to reimplement bigquery__test_unique over there: Created bigquery__test_unique to handle expressions passed to BQ uniq… dbt-bigquery#10
• It's desirable for this test to include the full row output when using --store-failures. If the query result stored in the database contained just the null values of the null column, it can't do much to contextualize why those rows are null. Maybe we could have it both ways?

{% 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 %}

The first approach would require a code change in the dbt-bigquery repo; the second one could happen here. I lean toward the second, even if it risks making the code in the built-in test look slightly more tricky. I'm going to mark this a good first issue—it sounds like you've got a PR at the ready.

[willbowditch]

Thanks @jtcohen6. I've just raised a PR to core with the additional store-failures check added in