CODEWITHSUNDEEP
nullsnowflake-cloud-data-platformdbt
GenDemo
GenDemo

Reputation: 761

What to do about NULLs that are not recognised as NULL

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

enter image description here

Upvotes: 1

Views: 2679

Answers (3)

GenDemo
GenDemo

Reputation: 761

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.

enter image description here

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.

Upvotes: 0

Rajib Deb
Rajib Deb

Reputation: 1774

You must be having one of them as a SQL NULL and the other not. If it is not a SQL NULL, it will not identify it as NULL. Please run the below and you will know what I am trying to say.

CREATE TABLE NULL_CHECK(VALUE VARCHAR);

SELECT * FROM NULL_CHECK;
INSERT INTO NULL_CHECK VALUES (NULL);  -- This is SQL null
INSERT INTO NULL_CHECK VALUES ('NULL'); -- This is not

SELECT * FROM NULL_CHECK WHERE VALUE is null;

Try your query as below. I am sure it will work

select distinct VALUE
    , case 
        when VALUE is null OR EQUAL_NULL(VALUE,'NULL') then true
        else false
      end as flag
      from NULL_CHECK;

Upvotes: 2

tom
tom

Reputation: 2205

I believe the issue is with your distinct key word.

Try this instead:

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

Upvotes: 1

Related Questions