Before this: Validate before you cast in SQL
The idea
Writing separate WHERE clauses for clean rows and for each exception type invites gaps and overlaps. Instead compute one reason with a CASE whose branches follow the declared rule order.
Clean rows are WHERE reason IS NULL; exceptions are the rest. Every input row lands in exactly one place, and the first failing rule wins.
Use TRY_CAST inside the CASE so unparseable values become reasons, not query errors.
Worked example
Input: quantity: '', 'abc', '0', '5'
- Visual
Steps in order: Require quantity → Convert type (bad_quantity) → Reject rows where quantity < 1
- SQL (DuckDB)
CASE WHEN q IS NULL THEN 'missing_quantity' WHEN TRY_CAST(q AS BIGINT) IS NULL THEN 'bad_quantity' WHEN TRY_CAST(q AS BIGINT) < 1 THEN 'quantity_out_of_range' END AS reason
- pandas
np.select([q.isna(), n.isna(), n < 1], ["missing_quantity", "bad_quantity", "quantity_out_of_range"], default=None)
Result: '' → missing_quantity (after blank to missing), 'abc' → bad_quantity, '0' → out of range, '5' → clean
Common mistakes
- Overlapping filters.
- CAST that fails the whole query.
- Branches out of the declared order.
Check yourself
Why one CASE instead of separate filters?
It guarantees one first reason per row and no row in both outputs.
Practice it
Validate quantities and prices against ranges.