dbt test not_null failed on empty string values

TL;DR

Empty strings are not nulls, so not_null passes them while your data contract considers them missing (or vice versa: the test you want is stricter). If empty means missing, convert with nullif(column, '') in the model so not_null catches them; if empty is valid, leave not_null and add a separate check for the values you actually forbid.

Error

dbt test not_null failed on empty string values

Steps

1. Store the failures and inspect them: dbt test --select [TEST NAME] --store-failures. Expected: the failing values are '', not nulls.
2. Decide the business rule: does an empty string mean "missing" for this column? Expected: a clear yes or no.
3. If empty means missing, convert in the model: nullif(email, '') as email. Expected: empties become nulls and not_null enforces the contract.
4. If empty is legitimate, keep not_null as is and add a custom test for the real rule (for example, no whitespace-only values). Expected: each test checks exactly one thing.
5. Rebuild the model and rerun the test. Expected: the test passes under the clarified rule.

When to use

• A not_null test fails and the offending values are empty strings.
• Source systems write '' where you expect nulls.

When not to use

• The failures are real nulls (fix the data or the model logic).
• Empty strings are valid data (the test is wrong, not the data).

Tool compatibility

• dbt Core 1.0 and later, all adapters. nullif is standard SQL.

Variant phrasings

not_null passes but downstream treats '' as missing

The reverse confusion: add the nullif conversion so the warehouse and the contract agree.

Whitespace-only strings failing a custom check

Trim before comparing; ' ' is neither null nor empty.

Why it happens

SQL distinguishes '' from null, but business logic often does not. The test checks the SQL definition while humans apply the business definition, and the two disagree on empty strings.

Edge cases

• Some warehouses (Oracle-style) treat empty strings as null; know your warehouse's semantics before converting.
• Converting in the model changes downstream behavior; coordinate with consumers of the column.
• Source freshness will not catch this; it is a content problem, not a timing problem.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_608XG2csqro3ZKLArT1c5A