Guides
Data cleaning guides
- A data cleaning checklist you can defend
The order of work for cleaning a new export: profile, decide rules, convert, validate, deduplicate, reconcile and hand off.
- SQL versus pandas: the cleaning differences that change results
Missing keys, whitespace, NA strings, decimals and unpivoting behave differently in DuckDB SQL and pandas. Here is what to override in each.
- Quarantine, flag, fix or delete? Choosing what happens to bad rows
A decision guide for invalid and unusual rows, with examples of when each action is the honest one.
- Dates and time zones for analysts
Ambiguous date formats, timestamps with and without offsets, instants versus local times and daylight saving, explained with cleaning rules.
- Deduplication and entity resolution without false merges
How to find the grain, choose a survivor rule, and decide when records describe the same real-world entity.
- Preparing for a data cleaning take-home task
What reviewers look for in a cleaning take-home and how to structure your submission and explanation.
- How to profile a new dataset before cleaning
What to measure in every column before writing a single rule, and how to turn the profile into questions.
- Reading CSV files without changing your data
Delimiters, quoting, encodings and type inference: how to import raw files so nothing changes before your rules run.
- Null, blank and zero: handling missing values on purpose
How to tell unknown, empty and zero apart, and how each tool treats them.
- Whitespace and invisible characters
Why keys that look identical don't match, and how to trim exactly what the rules say.
- Cleaning names without damaging them
Normalization, case and transliteration: what is safe to change in people's names.
- Mapping categories with an approved dictionary
How to turn free-typed categories into a controlled vocabulary without guessing.
- Money, decimals and rounding
Why floating point breaks totals, and how to parse, round and total amounts exactly.
- Parsing dates when formats are ambiguous
ISO, slash dates, locales and impossible days: rules that never guess.
- Identifiers, leading zeros and long ids
Why codes are text, and how to keep them intact through every tool.
- Validation rules and exception reports
Ordering rules, choosing severities and producing an exception report people can act on.
- Checking joins before you trust them
Cardinality, null keys and orphans: a routine that catches silent join errors.
- Appending monthly files and handling schema drift
Declaring required and optional columns, rejecting broken files and keeping lineage.
- Pivot and unpivot without inventing data
Reshaping tables while keeping zeros, gaps and collisions honest.
- Outliers: flag, don't delete
How to handle unusual values without changing the population you report on.
- Reproducible cleaning pipelines
Immutable raw data, idempotent steps and tests that make reruns safe.
- SQL window functions for cleaning
Ranking survivors, finding duplicates and comparing rows without collapsing them.
- pandas nullable dtypes and explicit parsing
Int64, boolean and string dtypes, and why default inference changes your data.
- Avoiding leakage in data preparation
Fitting cleaning statistics on training data only, and other leakage traps.
- Reconciliation: proving nothing was lost
Row counts and totals that show your cleaning kept every record accounted for.
- Writing a data quality report
Structure and wording for a report that stakeholders read and act on.
- Clarifying questions to ask about messy data
The questions that prevent silent assumptions, grouped by topic.
- Survivor rules explained
How to choose which record to keep when several describe the same thing.
- Email normalization policies
What is safe to change in an email address, and what isn't.
- Building a data cleaning portfolio project
How to present a cleaning project that shows judgment, not just code.