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

2 answers

  • answered 2020-10-29 02:09 tom

    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
    

  • answered 2020-10-29 02:22 Rajib Deb

    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;