dbt+seed+failed+cannot+cast+type+varchar+to+timestamp+in

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 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 table column was created as varchar(16) (from config or inference) but a CSV value is longer, so Postgres rejects the insert. Either widen the seed column type in column_types, or trim the source values if 16 is the real business limit. Declaring the type explicitly stops inference from picking a width based on short early values.

Database Error: value too long for type character varying(16) in dbt seed

Use this when

• dbt seed on Postgres fails with value too long for type character varying(16)
• the length in the message matches a column_types setting or an inferred width for that seed
• the CSV contains at least one value longer than the stated width

Not for this skill when

• the failing insert is from a model, not a seed, fix the model target column instead
• long values are data errors that must be rejected, keep the width and fix the source extract
• you can move the seed to a warehouse table managed outside dbt, then reference it as a source

Steps

1. Find the longest value in the failing CSV column with a quick scan of the file, and read the seed column_types config for that column if one exists. Verify: you know the true maximum length and where the 16 came from.

2. Decide intent: if the column is a code that must stay 16, fix or reject the over-length source rows with the data owner. If the width was arbitrary, widen it. Verify: the intended column width is decided and recorded.

3. Apply the fix: set the seed columntypes entry to a wider type such as varchar(64) or text, or correct the CSV values. Keep the config in dbtproject.yml or the seed YAML so fresh environments match. Verify: seed config or CSV now agrees with the chosen width.

4. Drop the partially loaded seed table if dbt left one, rerun dbt seed --select for that seed, and verify the column type and the longest loaded value. Verify: seed loads fully and the warehouse column width matches the config.

Variant phrasings

no column_types configured anywhere

Some adapters infer widths from the CSV. Inference can undershoot when long values appear late in the file; explicit types are the durable fix.

error names a different length each run

Different seeds share a generic config block. Scope column_types per seed so one seed widening does not mask another seed problem.

Compatibility: dbt seed on Postgres and Redshift with explicit or inferred varchar widths; Snowflake and BigQuery use different length semantics. Applies to dbt-core 1.x seeds.

Why it happens

Postgres enforces varchar(n) strictly on insert. dbt seed creates the table first using configured or inferred types, then streams CSV rows. A single over-length value aborts the load with this error, and because the table was created narrow, every retry fails identically until the type or the data changes.

Edge cases / pitfalls

• Codes with meaningful leading zeros belong in varchar, but width still applies; padding is not trimming.
• A partial seed load can leave a half filled table that downstream models read; always rerun the seed to completion and check row counts.
• Widening to text everywhere avoids this class of error but loses a useful data quality signal for fixed-width codes.

Provenance

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

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

Thread query: Database Error: value too long for type character varying(16) in dbt seed

TL;DR: The seed table column was created as varchar(16) (from config or inference) but a CSV value is longer, so Postgres rejects the insert. Either widen the seed column type in column_types, or trim the source values if 16 is the real business limit. Declaring the type explicitly stops inference from picking a width based on short early values.