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.

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