Data Cleaning Exercises

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 · Published · 10 min read

A grid of table cells where messy amber cells turn into clean green ones, next to the title How to practice data cleaning

The most useful way to practice data cleaning is to work on small, deliberately messy tables where the correct result is known. That way every attempt gets checked instead of just "looking fine". Six more habits build on that: profile before you change anything, solve the same task in SQL and pandas, keep rejected rows in an exception table, reconcile your counts, clean one real public dataset end to end, and explain each rule in a sentence. Together, those are the seven habits this guide covers.

Reading about trimming whitespace or parsing dates teaches the vocabulary. Skill comes from making the decisions on real-looking data and finding out, quickly, whether they were right.

Key Takeaways

  • Practice on data with a known correct answer. Without a check, you can't tell a correct clean from a plausible one.
  • Profile first and write down questions before you change a single value.
  • Solving the same task in SQL and pandas exposes defaults that silently change results, such as how missing join keys behave.
  • Don't drop bad rows. Move them to an exceptions table with a reason, then prove that rows in equals clean rows plus exceptions.
  • Once the basics are routine, clean one real public dataset end to end to build judgment.
  • Practice explaining each rule out loud. Interviews and real handoffs test the explanation as much as the code.

Why does data cleaning need deliberate practice?

Cleaning is a large share of real data work, so it pays to be good at it. In Anaconda's 2022 State of Data Science survey of 3,493 people in 133 countries, respondents' answers put data preparation and cleansing at around 38% of their time (I Programmer, November 2022). That is a self-reported estimate, not a time study, so treat it as a rough indication rather than a measurement.

The harder problem is feedback. A cleaned table rarely announces that it is wrong. A date parsed as month-first instead of day-first still looks like a date. A join that dropped forty orders still returns a tidy result. Unlike a failing unit test, a bad clean usually passes silently until someone downstream notices a wrong number. Good practice therefore needs a way to tell you when you were wrong, and the seven habits below are built around that.

1. Practice on data with a known correct answer

Pick exercises where your output is compared against an expected result, row by row. It separates practice that builds skill from practice that only builds confidence.

Graded exercises work well because the messiness is planted on purpose: padded ids, blank strings next to real missing values, a duplicate account with two update times. When your output differs from the expected one, you see exactly which rows disagree and can work out which assumption was wrong.

Good starting points are the free order-line export exercise, which mixes type, whitespace and lookup problems in one small table, and picking a surviving record per account, which makes you apply a stated three-step tie-break order.

What to look for in any exercise source:

  • A checked result, not just a sample solution you compare by eye.
  • Small tables (dozens of rows, not millions), so you can read every exception.
  • A stated policy for each column. Real cleaning is applying a rule someone agreed to, not guessing what the data "should" be.

2. Profile before you change anything

Before writing a single transformation, spend five minutes describing the data: row counts, distinct values per column, how many values are missing, blank or whitespace-only, and the minimum and maximum of anything numeric or date-like. Then turn what you saw into questions.

This habit matters because many cleaning mistakes are decided before the first line of code. If you never noticed that customer_id has both NULL and '', you'll treat them the same by accident. The guide to profiling a new dataset has a query set you can reuse.

A practice drill: before you open the instructions of an exercise, profile its input and write three questions you'd ask the data owner. Then compare them with the policy the exercise states. Repeated often enough, the comparison shows which kinds of issue you tend to miss.

3. Solve the same task in SQL and in pandas

Doing one exercise twice, once in DuckDB SQL and once in pandas, is one of the quickest ways to find assumptions you didn't know you were making. The two tools disagree on several defaults, and the disagreements are exactly where cleaning bugs live.

A small example: both tables contain a row whose customer key is missing.

import duckdb
import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3], "customer_id": ["C1", None, "C9"]})
customers = pd.DataFrame({"customer_id": ["C1", None], "segment": ["retail", "unknown-row"]})

print(orders.merge(customers, on="customer_id", how="left"))

print(duckdb.sql("""
    SELECT o.order_id, o.customer_id, c.segment
    FROM orders o
    LEFT JOIN customers c USING (customer_id)
    ORDER BY o.order_id
"""))

Output with pandas 3.0 and DuckDB 1.5:

   order_id customer_id      segment
0         1          C1       retail
1         2         NaN  unknown-row
2         3          C9          NaN
┌──────────┬─────────────┬─────────┐
│ order_id │ customer_id │ segment │
│  int64   │   varchar   │ varchar │
├──────────┼─────────────┼─────────┤
│        1 │ C1          │ retail  │
│        2 │ NULL        │ NULL    │
│        3 │ C9          │ NULL    │
└──────────┴─────────────┴─────────┘

pandas matched the missing key in orders to the missing key in customers and gave order 2 a segment it doesn't have. The pandas merge documentation warns about this: null keys on both sides are matched against each other, unlike usual SQL joins. SQL follows the rule that NULL never equals NULL, so order 2 gets nothing. Neither tool has a bug, but SQL's behavior is usually the one a business rule wants: an order with no customer key shouldn't inherit a segment. Practicing in one tool alone, you might never notice.

The SQL versus pandas guide lists the other differences worth testing, and the free join without matching blanks exercise is built around this trap.

4. Keep an exception table instead of deleting rows

An exceptions table is a table of rows that failed a rule, each stored with the rule it broke. When a row fails, practice moving it there rather than filtering it out. Deleting bad rows is easy to write and impossible to audit. Quarantining them forces you to name the rule each row broke.

A minimal pattern in DuckDB checks each value against the rule first, records why it failed, and only then converts it. TRY_CAST helps with the conversion because, per the DuckDB cast documentation, it "will never throw an error" and returns NULL when a cast isn't possible. It isn't a validation rule on its own, though: TRY_CAST('2.5' AS INTEGER) returns 3 in DuckDB 1.5, silently rounding. So the whole-number rule is an explicit pattern check:

CREATE TABLE raw AS
SELECT * FROM (VALUES (1, ' 12 '), (2, 'twelve'), (3, NULL), (4, '7'), (5, '2.5')) t(id, qty_text);

CREATE TABLE checked AS
SELECT id, qty_text,
       TRY_CAST(trim(qty_text) AS INTEGER) AS qty,
       CASE
         WHEN qty_text IS NULL THEN 'qty missing'
         WHEN NOT regexp_full_match(trim(qty_text), '-?[0-9]+') THEN 'qty not a whole number'
       END AS reason
FROM raw;

CREATE TABLE clean AS SELECT id, qty FROM checked WHERE reason IS NULL;
CREATE TABLE exceptions AS SELECT id, qty_text, reason FROM checked WHERE reason IS NOT NULL;

Of the five input rows, two pass (' 12 ' and '7') and three land in exceptions: 'twelve' and '2.5' as not whole numbers, and the NULL as missing. Rules are checked in order, so each failing row gets exactly one reason, which keeps the exception report readable. The lesson on quarantining with reasons, in rule order walks through the ordering question.

5. Reconcile: prove that nothing was lost

Finish every practice task with a count check: rows in must equal clean rows plus exception rows. If it doesn't, something was dropped or duplicated, usually by a join or a filter.

Using the five-row raw table and the clean and exceptions tables from habit 4:

WITH counts AS (
  SELECT (SELECT count(*) FROM raw) AS rows_in,
         (SELECT count(*) FROM clean) AS clean_rows,
         (SELECT count(*) FROM exceptions) AS exception_rows
)
SELECT *, rows_in = clean_rows + exception_rows AS reconciled
FROM counts;

┌─────────┬────────────┬────────────────┬────────────┐
│ rows_in │ clean_rows │ exception_rows │ reconciled │
│  int64  │   int64    │     int64      │  boolean   │
├─────────┼────────────┼────────────────┼────────────┤
│       5 │          2 │              3 │ true       │
└─────────┴────────────┴────────────────┴────────────┘

Five in, two clean, three exceptions. In this tiny example the check can't fail, but in a real task the clean table passes through joins, filters and deduplication, and that is where rows go missing or multiply. Running the same check at the end catches join fan-out and accidental filters whatever the task. For sums as well as counts, see reconciliation: proving nothing was lost.

6. Take on one real, messy public dataset end to end

Small graded exercises build the individual moves. A real public dataset builds judgment: deciding what matters, what to leave alone and when to stop. Once the basics feel routine, take one open dataset with a clear license and clean it from raw file to documented output.

Keep the scope tight. Pick one question the data should answer, write the cleaning rules you need for that question, and stop when they are applied and reconciled. For candidates, see the companion list of free messy datasets for practice, and check the license of whichever source you pick.

Real datasets have one weakness as practice material: no answer key. That's why they work best after graded exercises, not instead of them. You need to already trust your basic moves before you can judge the hard calls.

7. Explain every rule in one sentence

The last habit is to write down, or say out loud, why each rule exists: "Blank region becomes Unknown because the dashboard groups by region and the owner asked for an explicit bucket." If you can't explain a rule in a sentence, you probably don't understand the trade-off yet.

Interviews and take-home tasks test this directly. A good answer shows not only that you know strip(), but that you asked before filling a missing value with zero. The stakeholder interview questions are good prompts to practice answering.

How should you schedule practice?

Short, frequent sessions work well here because each one holds a few complete loops of try, check and fix. Try 30 to 45 minutes, four or five days a week:

Session part Time What you do
Warm-up 5 min Profile the input, write your questions
Main task 15–25 min Solve one graded exercise in your main tool
Cross-check 5–10 min Redo one step in the other tool, or reconcile counts
Notes 5 min One sentence per rule you applied, plus any mistake you made

If you want this laid out day by day, the 30-day data cleaning practice plan does that, moving from single-column fixes to joins, duplicates and a final end-to-end project.

Frequently asked questions

Can I practice data cleaning without knowing SQL or Python?

Yes. Spreadsheet tools and visual pipeline builders teach the same decisions: what counts as missing, which duplicate survives, which values map to which category. The decisions transfer directly when you later learn SQL or pandas. Learning the concepts visually first can make the code easier, because you already know what each step should do.

How long does it take to get good at data cleaning?

There is no reliable published figure for this, and it depends on what you already know. A more useful target is concrete: you're in good shape when you profile before changing anything, keep exceptions with reasons, reconcile without being reminded, and can explain every rule. Consistent short sessions with checked answers are a practical way to build those habits.

Should I practice on Kaggle datasets?

Public datasets, including those on Kaggle, are good for the end-to-end practice in habit 6. Kaggle datasets are uploaded by many different people, so check the license on each dataset page before you publish your work, and accept that there is no answer key. For learning individual techniques, small exercises with a checked result give faster and more honest feedback.

Start with one checked exercise

Pick one free exercise from the exercise catalog today, profile it before reading the instructions, solve it, and reconcile the counts. That single loop already uses three of the seven habits; redo one step in the other tool and you've used four.

Sources