Blog
Data cleaning blog
interviews · 12 min read
25 Data Cleaning Interview Questions (and What Interviewers Look For)
25 data cleaning interview questions on profiling, missing values, duplicates, types, joins and judgment, with what strong answers show and the red flags.
By Tnas ·
practice · 8 min read
A 30-Day Data Cleaning Practice Plan
A day-by-day, 30-day plan for practicing data cleaning in 30 to 45 minutes a day: import and profiling, values and types, rows and joins, validation, then a final project.
By Tnas ·
sql · 9 min read
Cleaning Boolean Columns: Yes, Y, True, 1 and Everything in Between
Turn yes/no, Y/N, TRUE/FALSE and 1/0 into a real nullable boolean in DuckDB SQL and pandas, keep unknown separate from false, and catch values nobody approved.
By Tnas ·
categorical-data · 12 min read
Cleaning Categorical Data: Typos, Synonyms and Rare Categories
Clean messy category columns in DuckDB SQL and pandas: normalise spelling, map synonyms with a dictionary, review typos by edit distance and handle rare values.
By Tnas ·
crm · 13 min read
Cleaning CRM Contact Data Before a Migration
How to clean CRM contacts before moving them to a new system: normalise emails, map picklists, format phones, merge duplicates safely and reconcile every row.
By Tnas ·
sql · 11 min read
Cleaning Currency Values: Symbols, Thousands Separators and Negative Amounts
Parse messy money columns in DuckDB SQL and pandas: extract the currency, handle locale separators, fix negatives like (250.00) and store exact decimals.
By Tnas ·
e-commerce · 10 min read
Cleaning E-commerce Order Data: A Worked Example
A worked example of cleaning a messy order export in DuckDB SQL and pandas: split orders from lines, parse money, drop re-exported rows, reconcile totals.
By Tnas ·
duckdb · 12 min read
Cleaning Messy CSV Files With DuckDB's read_csv Options
Read messy CSV files in DuckDB with read_csv: skip preamble lines, pad short rows, declare types, null strings, decimal commas and dates, and log rejected rows.
By Tnas ·
iot · 11 min read
Cleaning Sensor and IoT Readings: Gaps, Spikes and Unit Errors
Clean sensor time series in DuckDB SQL and pandas: retransmitted readings, error codes, unit mix-ups, range and spike tests, flat lines and gaps, with QC flags.
By Tnas ·
payroll · 8 min read
Cleaning Timesheet and Payroll Data: Hours, Shifts and Overtime
Clean timesheet punches before payroll in DuckDB SQL and pandas: overnight shifts, daylight saving, missing clock-outs, duplicates, overlaps and weekly overtime.
By Tnas ·
machine-learning · 9 min read
Data Cleaning for Machine Learning: What Changes and What Doesn't
Cleaning data for a model uses the same rules as cleaning for a report. What changes is when you learn values, what you delete and how you treat labels.
By Tnas ·
projects · 10 min read
Data Cleaning Projects for Beginners
Six scoped data cleaning projects for beginners, each with a free dataset, one question to answer, the skills it practices and a portfolio-ready deliverable.
By Tnas ·
careers · 7 min read
Data Cleaning Skills on a Resume: What to Write and How to Prove It
How to list data cleaning skills on a resume: name specific techniques, write bullets with a measured result, and link to a project that proves it.
By Tnas ·
data-cleaning · 9 min read
Data Cleaning vs Data Transformation: Where One Ends and the Other Begins
Data cleaning makes values correct; data transformation reshapes correct data for a use. See the boundary, the gray areas and why the order changes your totals.
By Tnas ·
data-quality · 10 min read
Data Contracts Explained for Analysts
What a data contract is, what goes in one, how it differs from a data dictionary or tests, and how an analyst can start one, with an ODCS example and SQL checks.
By Tnas ·
data-quality · 12 min read
Data Quality Checks Every Pipeline Should Run
Nine data quality checks every pipeline should run, from schema and row counts to uniqueness and freshness, with tested DuckDB SQL and pandas code.
By Tnas ·
data-quality · 8 min read
Data Validation vs Data Verification: What Analysts Should Know
Data validation checks values against rules; data verification checks them against the source. See why you need both, with DuckDB SQL examples.
By Tnas ·
data-cleaning · 7 min read
Data Wrangling, Data Cleaning and Data Munging: What's the Difference?
Data wrangling and data munging mean the same thing; data cleaning is one part of both. See where the terms came from and how each step looks in pandas.
By Tnas ·
data-science · 8 min read
Do Data Scientists Really Spend 80% of Their Time Cleaning Data?
The 80% data cleaning claim, traced to its sources: what the 2014 estimate and 2016 CrowdFlower survey measured, and what later time surveys report.
By Tnas ·
datasets · 12 min read
Free Messy Datasets for Data Cleaning Practice
Nine free public datasets for data cleaning practice, each with its license checked at the source, what makes it messy and what to practice on it.
By Tnas ·
data-quality · 11 min read
Handling Late-Arriving and Updated Records in a Cleaning Pipeline
How to handle records that arrive late or change after loading: event vs load time, high-water marks, lookback windows, latest-version rules and change feeds.
By Tnas ·
pandas · 8 min read
How to Clean Data With pandas: A Step-by-Step Walkthrough
A seven-step pandas cleaning walkthrough: read as text, profile, trim, convert types, map categories, deduplicate with a survivor rule and reconcile counts.
By Tnas ·
survey-data · 10 min read
How to Clean Free-Text Survey Responses
Clean open-ended survey answers in DuckDB SQL and pandas: normalize text, separate non-answers, redact personal data and code themes with a codebook.
By Tnas ·
sql · 11 min read
How to Clean Phone Numbers in SQL and Python
Turn messy phone numbers into E.164 in DuckDB SQL and pandas: split extensions, strip formatting, apply a declared country rule and report what can't be fixed.
By Tnas ·
outliers · 9 min read
How to Detect Outliers: IQR, Z-Scores and Domain Rules Compared
Compare IQR fences, z-scores, modified z-scores and domain rules on one dataset in DuckDB SQL and pandas, and learn which outliers each method misses.
By Tnas ·
documentation · 9 min read
How to Document a Data Cleaning Pipeline So Others Can Trust It
What to write down for a data cleaning pipeline: scope, data dictionary, a rule register, decisions, a reconciliation each run, and how to rerun it.
By Tnas ·
sql · 12 min read
How to Find Duplicate Rows in SQL (and Decide Which One to Keep)
Find duplicate rows in SQL with GROUP BY and HAVING, list every copy with a window count, then keep one survivor per key with ROW_NUMBER and a written rule.
By Tnas ·
deduplication · 12 min read
How to Merge Two Customer Lists Without Creating Duplicates
Merge two customer lists in DuckDB SQL: keep lineage, normalise match keys, auto-merge only on strong evidence, review the rest, and avoid the pandas null-key trap.
By Tnas ·
practice · 10 min read
How to Practice Data Cleaning: 7 Ways to Build Real Skill
Seven habits for practicing data cleaning: checked answers, profiling, SQL vs pandas, exception tables, reconciliation, a real dataset and explaining rules.
By Tnas ·
sql · 13 min read
How to Standardize Addresses and Postcodes in a Dataset
Standardize addresses and postcodes in DuckDB SQL and pandas: fix spacing and case, validate UK postcodes and US ZIPs, map street suffixes, flag the rest.
By Tnas ·
interviews · 8 min read
How to Talk About Data Cleaning in a Data Analyst Interview
How to describe data cleaning in a data analyst interview: one story with real numbers, a STAR structure, clear trade-offs, and phrases to avoid.
By Tnas ·
csv · 11 min read
How to Validate a CSV's Schema Before It Reaches Your Warehouse
A step-by-step way to validate a CSV file's header, row width, types and rules before loading it, with tested DuckDB SQL and pandas code and a reject report.
By Tnas ·
data-quality · 10 min read
Idempotent Data Cleaning: Why Running It Twice Should Change Nothing
What idempotent data cleaning means, which steps break it, and the load patterns that make a rerun safe, with tested DuckDB SQL and pandas examples.
By Tnas ·
missing-values · 10 min read
Imputing Missing Values: Mean, Median, Model or Nothing?
When to leave missing values alone and when to fill them with a mean, median, group median or model, with DuckDB SQL and pandas examples and their trade-offs.
By Tnas ·
sql · 12 min read
Moving Data Cleaning From Excel to SQL: A Translation Guide
Translate the Excel cleaning moves you know (TRIM, VLOOKUP, Remove Duplicates, fill down and PivotTables) into DuckDB SQL, with tested examples.
By Tnas ·
pandas · 8 min read
pandas drop_duplicates: keep, subset and the Mistakes That Lose Data
How pandas drop_duplicates really decides which rows to keep, what subset and keep do, and six mistakes that quietly delete real records, with tested code.
By Tnas ·
pandas · 7 min read
pandas fillna Without Regret: When Filling Missing Values Is Wrong
When pandas fillna helps and when it quietly changes your results: zeros that skew averages, forward fills across groups, sentinels and leaky medians.
By Tnas ·
pandas · 10 min read
pandas Interview Questions on Messy Data
Ten pandas interview questions on messy data, tested on pandas 3.0: lost leading zeros, 'false' read as True, NaN join keys, nullable integers and groupby drops.
By Tnas ·
pandas · 9 min read
pandas merge Pitfalls: Duplicated Rows, Lost Rows and Null Keys
Why pandas merge adds rows, drops rows and matches missing keys to each other, and how validate, indicator and a row-count check catch each problem.
By Tnas ·
pandas · 9 min read
pandas String Methods for Cleaning Text Columns
The pandas .str methods that clean text columns: strip, replace, normalize, casefold, extract, split and zfill, plus pandas 3.0 string gotchas.
By Tnas ·
power-query · 8 min read
Power Query vs SQL vs pandas for Data Cleaning
How Power Query, SQL and pandas compare for data cleaning: where each runs, who it suits, how each treats missing values and duplicates, and how to choose.
By Tnas ·
duckdb · 12 min read
QUALIFY in DuckDB: The Cleanest Way to Keep the Latest Row
Keep the latest row per key in DuckDB with QUALIFY and ROW_NUMBER: handle ties and NULL timestamps, choose WHERE or QUALIFY for filters, and compare arg_max.
By Tnas ·
sql · 11 min read
Regular Expressions for Data Cleaning in SQL: A Practical Guide
Use regular expressions in SQL to validate formats, profile value shapes, strip characters, extract codes and split lists, with tested DuckDB examples.
By Tnas ·
machine-learning · 7 min read
Scaling and Encoding Are Not Cleaning: Getting the Order of Data Prep Right
Why scaling and one-hot encoding belong after cleaning and the train/test split, with the right order of steps and examples in DuckDB SQL and pandas.
By Tnas ·
sql · 11 min read
SQL Data Cleaning Interview Questions With Worked Answers
Eight SQL data cleaning interview questions with tested DuckDB answers: keep the latest row, parse messy amounts, map categories, find orphans and reconcile.
By Tnas ·
data-quality · 9 min read
The 10 Most Common Data Quality Problems (and How to Spot Each One)
Ten data quality problems analysts meet again and again, from hidden duplicates to lost leading zeros, each with a DuckDB SQL query that finds it.
By Tnas ·
duckdb · 12 min read
TRY_CAST in DuckDB: Converting Messy Text Without Breaking Your Query
Use DuckDB's TRY_CAST to convert messy text to numbers and dates without query errors, then count, explain and quarantine the values that failed to convert.
By Tnas ·
visual-pipelines · 8 min read
Visual Data Cleaning Pipelines: When a No-Code Approach Makes Sense
When a visual, step-by-step cleaning pipeline is the right tool, when to move to SQL or pandas, and how to build one that others can review and rerun.
By Tnas ·
data-cleaning · 11 min read
What Is Data Cleaning? A Plain-English Guide With Examples
Data cleaning explained in plain English: what it is, what dirty data looks like, the core tasks, and a worked example in DuckDB SQL and pandas.
By Tnas ·
excel · 8 min read
Why Excel Ruins Your Dates and IDs, and How to Prevent It
Excel strips leading zeros, cuts long IDs to 15 digits and turns codes like SEPT2 into dates. Why it happens, how to spot the damage and how to stop it.
By Tnas ·
How these articles are written
- Every article is written by Tnas. Tnas is a software engineer with 8 years of experience in tech. Tnas has worked in and studied system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages, and has worked with many mainstream databases. GitHub: @guccibase.
- Every SQL example is run on DuckDB 1.5 and every Python example on pandas 3.0 before publishing; the output shown is the real output.
- Statistics and factual claims link to their sources: official documentation, standards bodies, government pages and peer-reviewed papers. A claim we can't source is left out.
- Example datasets are invented for teaching and say so.
- Corrections: if you find a mistake, tell us through the contact page. We fix it and change the article's updated date.