Performing custom ‘not empty’ tests
Performing custom ‘not empty’ tests
dbt is shipped with essential schema tests which I covered in one of my previous posts on CORE DWH test suite. There are plenty of additional tests and handy macros shipped with the dbt-utils package, including equal_rowcount, expression_is_true, recency, at_least_one, etc.
But still if you need something special you can easily add it yourself. For me this is signalling if one of my source tables is empty. I have added a simple macro which serves as a custom schema test test_not_empty():
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- NON_EMPTY
{% macro test_not_empty(model, column_name) %}
with validation as (
select
count(1) as row_count
from {{ model }}
),
validation_errors as (
select
row_count
from validation
where row_count = 0
)
select count(*)
from validation_errors
{% endmacro %}
Since it is intended to be informational only, I configure it with severity level = warn:
1
2
3
4
- name: stg_ym_sessions_facts
tests:
- not_empty:
severity: warn