dbt-labs / dbt-labs/dbt-utils

Add is_null generic data test to test for null values

Open
#1,047 3 comments 2 reactions 0 assignees View on GitHub
enhancement Stale triage
Dominant language
Makefile
Stars
1.8k
Forks
632
PR merge metrics
No merged PRs in 30d

Description

### Describe the feature
This generic data test would do the opposite of the `not_null` generic test. It would assert that the value of a column `IS NULL`.

It would be particularly useful in conjunction with the [`where` configuration](https://docs.getdbt.com/reference/resource-configs/where) to assert that the value is `NULL` only under certain circumstances, like if another field is set to a particular value. (Essentially the opposite of what #780 requested.)

### Describe alternatives you've considered
The `dbt_utils.expression_is_true` data test can do it, but it's more verbose.

### Additional context
It would be relevant to all database engines.

### Who will this benefit?
Here is an example of how the proposed data test could be used. Note in particular the two data tests for `picture_image_service_pk`.

```
# columns section of a model:
- name: media_type
data_type: string
data_tests:
- not_null
- accepted_values:
values: ['picture', 'video']
- name: picture_image_service_pk
data_type: bigint
data_tests:
# Require that an image is present if the media is a picture.
- not_null:
config:
where: "media_type = 'picture'"
# ----> Demonstration of proposed data test usage is here <----
# Require that an image is NOT present if the media is not a picture.
- dbt_utils.is_null:
config:
where: "media_type != 'picture'"
# Alternative example of how you have to do it without the proposed is_null test
- dbt_utils.expression_is_true:
expression: "picture_image_service_pk IS NULL"
config:
where: "media_type != 'picture'"
```

### Are you interested in contributing this feature?
Sure - if this looks useful, I could contribute a pull request. I don't think I'll need significant assistance to make it, as this seems very straightforward to me.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.