Before this: Profile before you change anything
The idea
Most import tools guess column types. A column of postcodes becomes integers and loses its leading zeros; the text "NA" or "null" becomes a missing value; a date like 03/04/2026 is parsed under a locale nobody declared. Each guess is an unrecorded cleaning step.
The safe default is to import every column as text and keep only truly empty cells as missing. Then convert each column on purpose, with a rule you can name and quarantine reasons for values that don't fit.
Record where the file came from (name, export date, row count) at import. When a number looks wrong later, provenance tells you whether the source or your pipeline introduced it.
Worked example
Input: members.csv: member_id,postcode,opted_in / 000123, 02139 ,Y
- Visual
Every input column in the workspace arrives as text; the inspector shows type string until you add a Convert type step.
- SQL (DuckDB)
SELECT * FROM read_csv('members.csv', all_varchar = true)- pandas
pd.read_csv("members.csv", dtype="string", keep_default_na=False, na_values=[""])
Result: member_id stays "000123", postcode keeps its padding for an explicit trim, and the text "NA" stays text instead of silently becoming missing.
Common mistakes
- Letting type inference run on identifiers.
- Accepting the tool's default list of missing-value markers.
- Losing the file name and row count at import.
Check yourself
What does pandas do with the text "NA" by default?
read_csv treats it as missing unless you pass keep_default_na=False.
Why not let the tool infer a postcode column as integer?
Leading zeros are deleted and the identifier changes: 02139 becomes 2139.
Practice it
Keep member_id and postcode as text, and convert opted_in only through the declared mapping.