[v2 Bug] Blank CSV unit-test fixture cells become empty strings instead of NULL for string columns

[danmawdsleyoaknorth]

Is this a new bug in dbt v2.x compared to the latest version of dbt 1.x?

Yes. The two unit tests below pass on dbt-core 1.12.4 and fail on dbt 2.0.4 and 2.0.5. I searched the existing issues and could not find this one — the closest are #9881 and #10117, which established and implemented the 1.x behaviour this diverges from.

Current Behavior

In dbt v2 a blank cell in a format: csv unit-test fixture is materialised as an empty string when the column is a string type, rather than NULL. Numeric columns are unaffected. It happens on both the given and the expect side.

On dbt-core 1.x the same blank cell is parsed to None — dbt/parser/unit_tests.py does None if v == "" else v, with no type dependence. That behaviour was requested in #9881 ("csv with empty value should be treating as null"), which was labelled type:bug and closed as completed in May 2024, and implemented via #10117.

Expected Behavior

A blank CSV fixture cell is NULL for every column type, matching 1.x.

Steps To Reproduce

A two-model project. passthrough just selects from src, so nothing in the model can account for the difference.

models/src.sql

select cast('x' as string) as id, cast('y' as string) as txt, cast(1.0 as float64) as num

models/passthrough.sql

select id, txt, num from {{ ref('src') }}

models/models.yml

unit_tests:
  # A blank CSV cell on the `given` side should arrive as NULL, as it does on 1.x.
  - name: blank_given_cell_is_null
    model: passthrough
    given:
      - input: ref('src')
        format: csv
        rows: |-
          id,txt,num
          a,,
    expect:
      format: dict
      rows:
        - {id: "a", txt: null, num: null}

  # A blank CSV cell on the `expect` side should match a NULL actual.
  - name: blank_expect_cell_matches_null
    model: passthrough
    given:
      - input: ref('src')
        format: dict
        rows:
          - {id: "a", txt: null, num: null}
    expect:
      format: csv
      rows: |-
        id,txt,num
        a,,

dbt run && dbt test

Relevant log output

dbt 1.12.4 — both pass:

2 of 2 PASS passthrough::blank_given_cell_is_null ..... [PASS in 3.38s]
1 of 2 PASS passthrough::blank_expect_cell_matches_null [PASS in 3.92s]
Done. PASS=2 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=2

dbt 2.0.5 — both fail. txt (STRING) diverges in both directions; num (FLOAT64) matches, so the divergence is string-only:

Test failed: blank_given_cell_is_null
+----+----------+------+
| id | txt      | num  |
+----+----------+------+
| a  | NULL ->  | NULL |
+----+----------+------+

Test failed: blank_expect_cell_matches_null
+----+----------+------+
| id | txt      | num  |
+----+----------+------+
| a  |  -> NULL | NULL |
+----+----------+------+

Environment

- OS: macOS 15 (arm64)
- Python: 3.11.15
- dbt: 2.0.5 (also reproduced on 2.0.4); compared against dbt-core 1.12.4 with dbt-bigquery 1.12.0

Which database adapter are you using?

bigquery

Is this a discrepancy vs. dbt 1.x?

Yes — dbt-core 1.12.4 passes both tests.

Additional Context

The given-side half is the more damaging one. An empty string survives an is not null filter where a NULL would not, so the model under test emits rows it should have dropped and the failure presents as a logic bug in the model rather than a fixture-parsing difference. In a suite of 395 unit tests this accounted for roughly 70 failures, and each one looked like a genuine regression until traced back here.

One thing that makes this awkward to work around: there is no spelling of NULL in a CSV fixture that is correct on both engines. Tested on both:

expect form	1.12.4	2.0.x
blank cell	PASS	FAIL
null token	FAIL	PASS
""	PASS	FAIL
omit the column from every row	PASS	PASS
format: dict with an explicit null	PASS	PASS
format: dict, key omitted on some rows only	ERROR	PASS

So a project that needs to run on both has to convert its CSV fixtures to format: dict, which is what we ended up doing. Worth noting for anyone else mid-migration.

Also note that on 1.x the comparison builds both sides with agate Text(null_values=("null","")), so 1.x cannot distinguish '' from NULL at comparison time either way. v2 can, which is arguably the better behaviour — but it makes the parse-side divergence visible where 1.x masked it.

Happy to test a fix against the repro above.