Before this: Quarantine with reasons, in rule order
The idea
A range rule encodes what is physically or commercially possible: quantity between 1 and 500, a price above zero. It can only be checked on a typed value, so the type check (is it a whole number at all?) comes first.
When a row breaks several rules, report the first one in the declared order. Consistent ordering means two analysts produce the same exception report, and each problem has one owner.
Comparisons with a missing value are never true in SQL semantics, so a range check alone lets missing values through. Check for missing values explicitly, before the range.
Worked example
Input: quantity "0", unit_price "0.00", country "UK"
- Visual
Require quantity → Convert type (bad_quantity) → Reject rows where quantity < 1 → … → Allowed values for country
- SQL (DuckDB)
CASE WHEN quantity IS NULL THEN 'missing_quantity' WHEN qty IS NULL THEN 'bad_quantity' WHEN qty < 1 OR qty > 500 THEN 'quantity_out_of_range' ... END
- pandas
if not 1 <= int(quantity) <= 500: return "quantity_out_of_range"
Result: The row breaks three rules but is reported once, as quantity_out_of_range, the first in the declared order.
Common mistakes
- Range checks on untyped text.
- Letting missing values pass a range check.
- Checking rules out of the declared order.
Check yourself
Does quantity < 1 catch a missing quantity?
No. A comparison with a missing value is not true, so check missing values separately.
Why check the type before the range?
A range only makes sense for a number; "abc" must be reported as a bad value, not as out of range.
Practice it
Validate the inventory feed and report the first failing rule for each invalid row.