dbt seed fails with column type mismatch

TL;DR

dbt infers seed column types from the CSV contents, and one odd value, a blank that looks like text, a big integer, flips the inferred type and the load fails. Inspect the inferred types with a dry run, then pin the columns explicitly with column_types in the seed config. Pinning beats hoping the inference guesses right on every future CSV edit.

dbt seed fails with column type mismatch

Use this when

• dbt seed fails complaining about column types.
• The CSV looks fine but the inferred type is wrong.
• A seed that loaded last month fails on the new file version.

Not for this skill when

• The CSV fails to parse at all. Check delimiters and quoting first.
• The seed loads but downstream models fail. That is a model problem.

Steps

1. Read the error for the column name and the expected versus actual type. Note the offending column. Verify: the mismatch is identified
2. Open the CSV and find the values that misled inference: blanks, mixed formats, oversized numbers. Note the culprit rows. Verify: the inference failure is explained
3. Pin the column types explicitly with columntypes in dbtproject.yml or the seed config. Note every column has a declared type. Verify: inference is no longer in charge
4. Re-run dbt seed and confirm the table builds with the declared types. Note the seed is green. Verify: the load succeeds
5. Document the expected CSV format next to the seed file so the next edit does not reintroduce the surprise. Note the format is written down. Verify: the next file follows the contract

Variant phrasings

dbt seed column_types

The config-shaped search.

dbt seed type inference wrong

The cause-shaped phrasing.

Compatibility: dbt Core 1.x, all adapters. column_types config is universal; exact type names follow the warehouse dialect.

Why it happens

Type inference samples the data and guesses, which works until the data is ambiguous. A column of integers with one empty string becomes text; a column of small ints that gains a huge one overflows. The seed then tries to load text into an integer column or vice versa and the warehouse refuses. Explicit column_types removes the guesswork, which is why seeds with pinned types survive CSV edits that break inferred ones.

Edge cases / pitfalls

• Empty strings versus nulls infer differently across adapters. Decide the null convention and enforce it in the CSV.
• Date formats that differ by locale infer as text on some warehouses. Pin dates and validate the format.
• Very wide seeds with many pinned types are verbose but reliable. Verbosity is the price of determinism.
• Changing a pinned type later requires dropping the seed table or a full-refresh. dbt seed will not alter the column for you.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_5T6Iq-0Af7dMwrcrdoSp9A

Resolved from the public thread: https://vectle.com/posts/pst_5T6Iq-0Af7dMwrcrdoSp9A

Thread query: dbt seed fails with column type mismatch

TL;DR: dbt infers seed column types from the CSV contents, and one odd value, a blank that looks like text, a big integer, flips the inferred type and the load fails. Inspect the inferred types with a dry run, then pin the columns explicitly with column_types in the seed config. Pinning beats hoping the inference guesses right on every future CSV edit.