Before this: Validate before you cast in SQL
The idea
Before writing rules, measure: COUNT(*) versus COUNT(col) for missing values, COUNT(DISTINCT col), values with padding, and the most common values.
DuckDB's SUMMARIZE gives a quick overview, but targeted queries answer specific questions like 'how many codes have leading spaces?'.
Keep the queries; they become checks you rerun on every delivery.
Worked example
Input: customer_code column
- Visual
Inspector → customer_code
- SQL (DuckDB)
SELECT COUNT(*) - COUNT(customer_code) AS missing, COUNT(*) FILTER (WHERE customer_code <> trim(customer_code)) AS padded, COUNT(DISTINCT customer_code) AS distinct_codes FROM orders
- pandas
s = df["customer_code"]; s.isna().sum(), (s != s.str.strip()).sum(), s.nunique()
Result: missing 4, padded 37, distinct 212
Common mistakes
- Profiling a sample only.
- One-off queries that aren't kept.
- Ignoring padding.
Check yourself
How do you count missing values with COUNT?
COUNT(*) minus COUNT(column).
Practice it
Profile, then clean the export.