Before this: Validate before you cast in SQL
The idea
CAST stops the whole query at the first bad value. TRY_CAST returns NULL instead, so you can keep going and report the bad rows.
Distinguish two cases: the value was missing to begin with, or it was present but didn't convert. Only the second is a bad value.
Validate format before casting money: CAST('1.234' AS DECIMAL(12,2)) rounds silently, so check the number of decimals first if the rules say to reject.
Worked example
Input: amount: '12.50', 'abc', NULL
- Visual
Add step → Convert type → amount → decimal(12,2) → If a value doesn't parse: Quarantine
- SQL (DuckDB)
CASE WHEN amount IS NOT NULL AND TRY_CAST(amount AS DECIMAL(12,2)) IS NULL THEN 'bad_amount' END
- pandas
n = pd.to_numeric(s, errors="coerce"); bad = s.notna() & n.isna()
Result: 'abc' → bad_amount; NULL handled by the missing rule
Common mistakes
- CAST that fails the run.
- Treating missing as bad.
- Silent rounding of extra decimals.
Check yourself
Why check 'amount IS NOT NULL' in the bad-value test?
So missing values are not reported as unparseable; they have their own rule.
Practice it
Clean an order-line export.