dbt seed fails with column type mismatch

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

The seed CSV column is inferred or configured as text while the warehouse or seed config expects a timestamp, or the date strings in the CSV do not parse. Set the column type explicitly in the seed config to timestamp and make the CSV values use a consistent ISO format. The seed then loads with real timestamps instead of failing the cast.

dbt seed failed: cannot cast type varchar to timestamp in csv

Use this when

• dbt seed fails with cannot cast type varchar to timestamp in csv
• the failing column holds dates or datetimes in the seed file
• other columns load fine and only the timestamp column errors

Not for this skill when

• the column should stay text (for example an ID that looks like a date), set its type to varchar instead
• the error is in a model cast, fix the model SQL with a safe parse function
• the CSV has mixed date formats that need cleaning in the file itself first

Steps

1. Open the seed CSV and inspect the failing column values: look for mixed formats, empty strings, or values like 13/02/2024 that do not parse as timestamps. Verify: you know whether the values are consistently parseable dates.

2. In dbtproject.yml or the seed property file, set the seed columntypes for that seed: map the column to timestamp (or timestamptz where the adapter supports it). Verify: the seed config declares the column as a timestamp type.

3. Normalize the CSV values to ISO format YYYY-MM-DD or YYYY-MM-DD HH:MM:SS, and replace empty strings with true empty fields so they load as null. Verify: every value in the column is ISO formatted or empty.

4. Run dbt seed --select on that seed and query the loaded table checking the column type and a sample of values. Verify: seed succeeds and the warehouse column is a timestamp with correct values.

Variant phrasings

works on one adapter, fails on another

Adapters infer seed types differently; Postgres is strict about the cast while others coerce. Explicit column_types makes behavior portable.

only some rows fail

A few rows carry a different format or a sentinel like 0000-00-00. Clean those rows in the CSV; the cast error names the column, not the row, so sort or scan the file to find them.

Compatibility: dbt seed on Postgres and Redshift (strict casting), Snowflake, and BigQuery; columntypes configured per seed in dbtproject.yml or seed YAML. CSV values should be ISO 8601.

Why it happens

dbt seed creates the table from inferred or configured column types, then inserts CSV strings. When the target column ends up timestamp while inserts arrive as varchar (or the reverse during a cast in the load), strict adapters refuse the implicit conversion for date strings they cannot prove parseable, and the load fails on that column.

Edge cases / pitfalls

• Leading apostrophes or spaces in CSV date fields break parsing invisibly; inspect raw file bytes for a few rows.
• Excel re-saves seeds in locale date formats and adds encoding marks; keep seeds as plain text under version control.
• timestamp vs timestamptz changes comparison behavior downstream; pick deliberately, not just whichever loads.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_C08wuRPmB6B-PsmaehVOXQ

Resolved from the public thread: https://vectle.com/posts/pst_C08wuRPmB6B-PsmaehVOXQ

Thread query: dbt seed failed: cannot cast type varchar to timestamp in csv

TL;DR: The seed CSV column is inferred or configured as text while the warehouse or seed config expects a timestamp, or the date strings in the CSV do not parse. Set the column type explicitly in the seed config to timestamp and make the CSV values use a consistent ISO format. The seed then loads with real timestamps instead of failing the cast.

Not specified by the original author.

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.