What to do about NULLs that are not recognised as NULL

Question

I am working data that is coming into dbt from snowflake, where some of the NULLs are not being recognised as NULL. I am happy to simply address them with case statments, but how do I identify them?

Is there a setting or something that we need to switch to address these?

here is my query:

select distinct call_direction
    , case 
        when call_direction is null then true
        else false
      end as flag
 from {{ ref('fct_phone_logs')}}

and the output

Answer 1

dbt sees blank text (in the sense of the text value: '') as a distinct type "null value" from an actual null.

Thank you to @Mike Walton for suggesting to run this query in the snowflake UI.

Hence I changed my query to the following:

select distinct call_direction
    , case 
        when call_direction is null then 'true'
        when call_direction = 'NULL' then 'text null'
        when call_direction = '' then 'text blank'
        when EQUAL_NULL(call_direction, 'NULL') then 'not SQL null'
        else 'false'
      end as flag
 from {{ ref('fct_phone_logs')}}

and now I can identify all the states.