dbt seed read boolean column as string from csv, test failed

TL;DR

dbt seeds infer CSV column types with the agate library, and a boolean column written as True and False, yes and no, or mixed with blanks can be inferred as text. The values then load as strings, so a test expecting a real boolean fails and downstream filters comparing to true miss rows. Pin the column type explicitly with column_types in the seed config or the seeds block of the project file, drop the seed table, and reload.

dbt seed read boolean column as string from csv, test failed

Use this when

• A test on a seeded boolean column fails even though the CSV looks correct.
• The warehouse shows the seed column as a string or varchar type.
• Downstream models filtering on the flag return no rows or the wrong rows.

Not for this skill when

• The CSV genuinely contains values other than the boolean spellings, such as a notes column. That column is text.
• The failing test is on a model built from the seed with its own cast. Fix the model cast there.

Steps

1. Inspect the loaded seed table definition in the warehouse and note the column type for the flag. Compare it with the type the test assumes. Verify: the actual loaded type is known
2. Look at the raw CSV values for that column, including blanks, mixed case, and any stray values like a header repeated mid file. Note every distinct spelling present. Verify: the inference inputs are fully visible
3. Add a column_types entry for the seed that pins the flag to boolean, using the warehouse type name your adapter expects, in the seed config or the project seeds block. Note the config location so other seeds can reuse the pattern. Verify: the type is declared, not inferred
4. Drop the existing seed table or run the seed command with the full refresh option so the table is recreated with the pinned type, then reload. Note the new column type in the warehouse. Verify: the seed table now stores a real boolean
5. Rerun the failing test and a downstream model that filters on the flag, and confirm counts match a manual count from the CSV. Note the test is green. Verify: boolean behaviour is verified against the source file

Variant phrasings

dbt seed column_types boolean csv inferred as text

The config answer searched directly.

dbt seed true false loaded as string

The value spelling phrasing.

Compatibility: dbt Core and Cloud seeds using agate inference, across adapters. The boolean type name in column_types follows the target warehouse, for example boolean on most platforms.

Why it happens

Type inference is a guess from a sample of text. Agate is conservative: if the values are not unambiguously boolean in its recognised spellings, or if blanks and mixed spellings muddy the sample, text is the safe guess and the seed loads as strings. Nothing errors at load time, because strings are valid data. The mismatch only surfaces later, in a test that compares types strictly or a filter that quietly matches nothing, which is why the failure looks like a test problem instead of a load problem.

Edge cases / pitfalls

• Pin types for every seed column that matters, not just this flag. Inference can flip a numeric id with leading zeros the same way.
• A recreated seed table changes the type for every downstream consumer at once. Warn owners of models that were compensating with their own casts.
• Some adapters store booleans but display them as True and False in query results, which looks like text in a screenshot. Check the information schema, not the result grid.
• Seeds are for small reference data. A CSV big enough to be slow to reload belongs in a raw table loaded by a pipeline, not a seed.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_q-4aXEvXduPgOBkblmmbuw

Resolved from the public thread: https://vectle.com/posts/pst_q-4aXEvXduPgOBkblmmbuw

Thread query: dbt seed read boolean column as string from csv, test failed

TL;DR: dbt seeds infer CSV column types with the agate library, and a boolean column written as True and False, yes and no, or mixed with blanks can be inferred as text. The values then load as strings, so a test expecting a real boolean fails and downstream filters comparing to true miss rows. Pin the column type explicitly with column_types in the seed config or the seeds block of the project file, drop the seed table, and reload.