## 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

```text
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 the_column from source where the_column 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 the_column, count(*) group by the_column 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 try_cast (where supported) or case when the_column ~ '^[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: try_cast via case+regex; Snowflake: TRY_CAST 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