Before this: Ranges and the order of rules
The idea
Single-column checks miss inconsistencies between fields: an end date before the start, a shipped order with no ship date, a refund larger than the original sale.
Write each cross-field rule with a name, the condition and the outcome, and check it after the single-column rules so both fields are already parsed. Missing values make comparisons unknown, so decide explicitly what happens when one side is missing.
Report the reason and both values, so the source owner can see which field is wrong.
Worked example
Input: start 2026-03-10, end 2026-03-01
- Visual
Add step → Reject rows where → end < start → reason end_before_start
- SQL (DuckDB)
CASE WHEN CAST(end_date AS DATE) < CAST(start_date AS DATE) THEN 'end_before_start' END
- pandas
bad = df["end"] < df["start"]
Result: The row goes to exceptions with reason end_before_start
Common mistakes
- Checking before parsing both fields.
- Forgetting that missing values make comparisons unknown.
- Reporting only one of the conflicting values.
Check yourself
What happens in SQL when end_date is NULL in end_date < start_date?
The comparison is unknown, so the rule doesn't fire; handle missing values with a separate rule.
Practice it
Validate quantities and prices in the declared order.