Add is_null generic data test to test for null values
- 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
Assessment
This issue has not been assessed yet.