Database Error: invalid input syntax for type in dbt model

TL;DR

A string column holds values the target type cannot parse. Find the offending values, then cast defensively: filter them out, or use try_cast / a regex guard so bad rows become null instead of killing the model. Add a test so new bad values get flagged.

Error

Database Error: invalid input syntax for type in dbt model

Steps

1. Identify the cast from the error (it usually names the type, e.g. numeric) and the column. Run a probe: select distinct thecolumn from source where thecolumn is not null limit 50, eyeball the weird ones. Expected: You see the bad values: empty strings, 'N/A', commas, currency symbols.

2. Write a stricter probe: select thecolumn, count(*) group by thecolumn for values that fail the cast pattern, to size the problem. Expected: You know how many rows are affected and what the bad patterns look like.

3. Fix the model: use trycast (where supported) or case when thecolumn ~ '^[0-9.]+$' then the_column::numeric end, routing bad rows to null. Expected: The model builds; bad rows become null instead of raising.

4. Add a dbt test counting non-null originals that cast to null, so data quality regressions alert instead of crash. Expected: dbt test fails loudly on new bad values; the model itself stays green.

When to use

• warehouse raises invalid input syntax for type on a dbt model
• casting varchar source columns to numeric, date, or timestamp
• the error started after a new source file or API payload arrived

When not to use

• model contract type mismatch (that is a contract violation, different error)
• the column is missing entirely (that is a schema error)
• the cast works but returns wrong values (that is a logic bug)

Tool compatibility

• Postgres/Redshift: trycast via case+regex; Snowflake: TRYCAST native; BigQuery: SAFE_CAST native
• dbt singular tests are ideal for the bad-value probe turned permanent
• dbt seeds: clean the CSV before it lands if the source is a file

Variant phrasings

invalid input syntax for type timestamp

Same pattern with dates: mixed formats like '01/02/2024' vs '2024-01-02'. Normalize with a parsing case statement before casting.

invalid input syntax for type boolean

Source has 'yes'/'no' or 'Y'/'N'. Map explicitly: lower(col) in ('y','yes','true','1').

Why it happens

Source string columns are free text until you cast them. One 'N/A' or empty string in a million rows is enough to fail a strict cast, and dbt runs the cast over the whole table at build time. Defensive casting plus a test is the durable fix; cleaning one file by hand is not.

Edge cases

• Empty string '' fails numeric casts on Postgres but try_cast handles it; decide whether '' should be null or 0 for your metric.
• If most rows are bad, the source schema changed; fix the extraction, not just the cast.

Provenance

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