Reference · SQL (DuckDB)
Fill missing values in SQL (DuckDB)
Replace missing values with a stated value: a visible label for a category, or a fitted value with a flag for a measurement.
When to use it
Reporting labels such as "unknown"; imputation only when an analysis requires it and the fill is fitted on training data.
How
COALESCE(x, value).
COALESCE(NULLIF(trim(region, ' ' || chr(9) || chr(13) || chr(10)), ''), 'unknown')
- For a fitted fill, compute the value in a CTE over the training table only.
Pitfalls
- Filling measurements with 0.
- Fitting the fill value on test data.
- Not flagging imputed values.
Lesson: Fill labels, not measurements
Practise it: Label unknown categories, keep missing numbers missing