Reference · SQL (DuckDB)
Map values with a dictionary in SQL (DuckDB)
Replace each spelling with its approved value using a reviewed dictionary, and decide what happens to unmapped values.
When to use it
Categories with many spellings for one meaning.
How
Join to a VALUES list (or a mapping table). An inner join keeps mapped rows; a LEFT JOIN with no match finds unmapped ones.
JOIN (VALUES ('e-mail', 'email'), ('web chat', 'chat')) AS dict(raw, canon) ON t.channel = dict.raw- A CASE with many WHENs works but is harder to review than a table.
Pitfalls
- Keeping unmapped values unchanged.
- Mapping before normalizing.
- Guessing mappings for unseen spellings.
Lesson: Map categories with an approved dictionary
Practise it: Map channels with an approved dictionary