Before this: Quarantine with reasons, in rule order
The idea
CAST stops the whole query on the first bad value. TRY_CAST returns NULL instead, but it accepts whatever the engine considers castable, which may not be what your rules allow. When rules state a pattern (optional minus, digits, up to two decimals), test that pattern with regexp_full_match and cast only matching text.
Exception reports need the first failing reason in a declared order. One CASE expression evaluates its WHEN branches top to bottom and returns the first match, which is exactly that rule.
Keep clean rows and exceptions consistent by computing both from the same CTE: clean rows are the ones whose reason is NULL.
Worked example
Input: quantity: "7.5", "501", "12"
- Visual
Convert quantity to int64 with quarantine (bad_quantity) → Reject rows where quantity > 500 (quantity_out_of_range)
- SQL (DuckDB)
CASE WHEN NOT regexp_full_match(quantity, '-?[0-9]+') THEN 'bad_quantity' WHEN CAST(quantity AS BIGINT) > 500 THEN 'quantity_out_of_range' END AS reason
- pandas
INT = re.compile(r"-?[0-9]+") "bad_quantity" if not INT.fullmatch(q) else "quantity_out_of_range" if int(q) > 500 else None
Result: "7.5" → bad_quantity, "501" → quantity_out_of_range, "12" → clean.
Common mistakes
- Casting before validating, so one bad value stops the query.
- Writing clean and exception queries with different logic.
- Reordering CASE branches away from the declared rule order.
Check yourself
Why prefer regexp_full_match over TRY_CAST when the rules give a pattern?
The rules define what is valid; TRY_CAST follows the engine's casting rules instead, which can accept values the rules reject.
In which order does CASE evaluate its WHEN branches?
Top to bottom; it returns the first branch that matches.
Practice it
In the SQL track, compute the first failing reason with one CASE and keep the rest as clean rows.