Performing custom ‘not empty’ tests

less than 1 minute read

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           

Updated: