Process Data from Dirty to Clean
Core concepts
Cleaning is where analysts spend most of their time. Dirty data includes missing values, duplicates, misspellings, inconsistent formats or units, stray whitespace, outliers, and values that fail business rules. Data integrity means the data is accurate, complete, consistent, and valid across its lifecycle.
Know spreadsheet tools: Remove duplicates, conditional formatting, data validation, split text into columns, and functions such as TRIM (removes leading/trailing/extra spaces), LEN (character count), LEFT/RIGHT/MID, CONCATENATE, and VLOOKUP. In SQL, cleaning functions include CAST (change type), COALESCE (first non-null value), TRIM, SUBSTR, CONCAT, and CASE WHEN for recoding.
Understand that null is not zero and not an empty string; COUNT(*) counts rows, while COUNT(column) skips nulls. Aggregate and count functions behave differently on nulls, and this is a frequent exam distinction.
Documentation matters: record every change, keep the raw data untouched, and use versioned copies so the cleaning is reproducible and auditable. Know the difference between a data engineer (builds pipelines and infrastructure) and a data analyst (cleans and interprets data for decisions).
Traps
Distractors often swap TRIM and LEN, or claim that removing duplicates is always correct - duplicate rows may be legitimate transactions. Beware items implying null equals zero or blank.
Rule of thumb
When comparing counts, ask whether nulls should be included. If a column contains nulls, COUNT(column) will be lower than COUNT(*). Test the difference before reporting totals.