Before this: Ranges and the order of rules
The idea
A required field must have a value. Trim and convert blanks to missing first, otherwise ' ' passes the check.
Report missing required values with a specific reason (missing_account), checked before format and range rules.
Worked example
Input: account: ' ', '00042'
- Visual
Trim → Blank to missing → Require account (missing_account)
- SQL (DuckDB)
CASE WHEN NULLIF(trim(account), '') IS NULL THEN 'missing_account' END
- pandas
df["account"].str.strip().replace("", pd.NA).isna()
Result: ' ' → missing_account
Common mistakes
- Checking before trimming.
- Generic reasons.
Check yourself
Why trim before checking required fields?
So whitespace-only values count as missing.
Practice it
Referential integrity with explicit exceptions.