Unit test fixture (csv) returns "" for empty value about dbt-core HOT 8 CLOSED

Acceptance Criteria

• My seeds and fixtures should treat empty values the same

col_a,col_b,col_c,col_d
1,blue,doug,hat
2,red,,shoe

• ,, becomes NULL
• there is no current way to specify an empty string using csv format

from dbt-core.

Thanks for trying out unit testing and reporting this @leesiongchan !

How dbt seeds treat empty values

dbt-labs/docs.getdbt.com#4867 describes how dbt seeds handle empty strings by converting them to SQL NULL values.

It also describes that there are two values that you can't load directly with CSV seeds:

1. An empty / blank string ("" / '')
2. A string with the value "null"

It totally makes sense that you'd expect CSV-formatted unit test fixtures to behave the same way as dbt seeds.

But we'll need to take another look at this and choose between a couple options for moving forward.

Our options

What's your take on this @graciegoheen? We'd need to choose between one of these two options:

1. Update the behavior of blanks in CSV-formatted unit test fixtures to align with CSV-formatted seeds, accepting that it won't be possible to represent blank strings. Users that need to represent blank strings for unit tests will need to use dict rather than csv for the format.

2. Keep current behavior of blanks in CSV-formatted unit test fixtures, accepting that it is different than CSV-formatted seeds. We'd want to add appropriate caveats in our documentation.

To me the 1st option has more congruence and the 2nd option has more dissonance. What do you think, Grace?

animal,legs
duck,2
snake,""

animal,legs
duck,2
snake,

select 'duck' as animal, 2 as legs union all
select 'snake' as animal, null as legs

unit_tests:

  - name: test_a_9881
    description: https://github.com/dbt-labs/dbt-core/issues/9881
    model: animals
    given: []
    expect:
      # This doesn't work as expected
      format: csv
      fixture: my_model_fixture

  - name: test_b_9881
    description: https://github.com/dbt-labs/dbt-core/issues/9881
    model: animals
    given: []
    expect:
      # But this works as expected
      rows:
        - {animal: duck, legs: 2}
        - {animal: snake, legs: null}

dbt show -s seeds/my_seed.csv --output json

dbt build -s my_seed animals --full-refresh

from dbt-core.

So I think this is going to be dependent on the warehouse you're using. For example, Snowflake is totally fine with try_cast('null' as NUMBER(38,0)), so this actually works fine for Snowflake (defining a numeric column as null in a fixture file).

In postgres (which is what @dbeatty10 is using), cast('null' as integer) does not work but cast(null as integer) does.

Things get funky as well when trying to cast to a string, because try_cast('null' as text) will result in just the actual word "null" rather than NULL.

@dbeatty10 did a deep dive on what you can and can't cast NULL to in Snowflake here.

We've had a tradeoff for seeds for awhile where:

• you cannot define an empty string in a seed
• you can define NULL

I understand it's confusing for us to decide the opposite for fixtures:

• you can define an empty string
• you cannot define NULL

That being said, you can define NULL using the other formats available (currently dict and coming soon sql). I believe this is because we're wrapping the values inputted in the csv fixture in single quotes, but we don't do that for the other formats.

At minimum, I think we have a few things to do here:

• add a test to core to cover this case
• update our docs on data types for unit testing with an example of how you'd supply an intended null in each of the warehouses

In addition, we should decide how we want to handle this. A few options include:

• allowing seeds and fixtures to support both empty strings and explicit NULLs

col_a,col_b,col_c,col_d
1,blue,doug,hat
2,null,,shoe

• making fixtures consistent with seeds (not support empty strings, but support NULLs)
• documenting that fixtures don't support NULLs (must use alternative format for this case)
• something else?

I'm going to move this over to technical refinement to get input from our engineers! Thanks for the discussion.

from dbt-core.

Hello! @leesiongchan - thanks for opening up this request. I'd like to know more about your use case here. We've designed unit tests such that you only have to supply input mock data for the columns that are relevant to your unit test.

If we imagine a unit test that only needs col_a and col_c…

Instead of:

# tests/fixtures/my_fixture.csv

col_a,col_b,col_c
1,,doug
2,,grace

You should do:

# tests/fixtures/my_fixture.csv

col_a,col_c
1,doug
2,grace

Now, that won’t be relevant if you do need all columns but want to test 1 case where col_b is null.

In that case, could you explicitly call out the null like so:

# tests/fixtures/my_fixture.csv

col_a,col_b,col_c
1,blue,doug
2,null,grace

Seeds behave differently than fixtures (though understand the comparison since they're both csv files). I am also hesitant to convert empty values to null for fixtures, if it would prevent someone from using an empty string in their mock input/output data for a unit test.

from dbt-core.

@graciegoheen how does this behave for you when you add it as a fixture?

animal,legs
duck,2
snake,null

Here's the error message I get when I use the project files described in #9881 (comment):

16:15:10    Runtime Error in unit_test test_a_9881 (models/_unit.yml)
  An error occurred during execution of unit test 'test_a_9881'. There may be an error in the unit test definition: check the data types.
   Database Error
    invalid input syntax for type integer: "null"
    LINE 29:     cast('null' as integer)  

But maybe I have a bjorked local environment somehow?

from dbt-core.

In that case, could you explicitly call out the null like so:

# tests/fixtures/my_fixture.csv

col_a,col_b,col_c
1,blue,doug
2,null,grace

If this is supported perhaps it is the best solution for both worlds (seeding & unit test fixture)? In our case we need all columns because our update/delete event consists only partial of the data.

from dbt-core.

dbt-labs/dbt-snowflake#894 (comment) has the deep dive of casting NULL (without quotes) across all known standard data types for the following databases:

• snowflake
• bigquery
• redshift
• postgres
• databricks
• spark

from dbt-core.

Opened a new issue in dbt-labs/docs.getdbt.com: dbt-labs/docs.getdbt.com#5476

from dbt-core.