DT2: Data quality you can prove
Profiling a new dataset
🎯You'll be able to
Produce a quick profile of a dataset (counts, distinct values, minimums, maximums, missing values) and spot problems from it.
🏠The everyday picture
A doctor's first check (temperature, pulse, blood pressure) takes two minutes and points to where to look next. A profile does the same for a table.
📘What it really is
- A profile lists, for each column: type, count, missing values, distinct values, minimum, maximum, and the most common values.
- Read it for surprises:
- a maximum that is text in a number column (
TESTamong customer ids); - a minimum below what's possible (a quantity of
-1); - more distinct values than allowed (ten spellings of three statuses);
-
fewer distinct values than expected (one store missing).
-
Tools:
- pandas:
df.describe(include='all'),df.nunique(),df.isna().sum(),df['col'].value_counts(); - DuckDB:
SUMMARIZEgives a profile of any table or file in one command; -
larger tools such as ydata-profiling, Great Expectations' data assistants or a warehouse's built-in profiling.
-
Profile raw data as text, so the profile shows what actually arrived, not what a guessed type turned it into.
📝A worked example
Input:
import duckdb
duckdb.sql("summarize select * from read_csv('raw/orders/2026-10-05.csv', all_varchar=true)").show()
Expected output: one row per column. Three surprises stand out: customer_id has a maximum of TEST, qty a minimum of -1, and
sku a maximum of SKU-999. status has 10 distinct values. Because everything was read as text, the maximum is the last value in
alphabetical order, which is why TEST shows up there. Checked on 1 November 2026 with DuckDB 2.0.0.
🌍Where it's used
- Onboarding a new source: the first hour with any new file or API.
- After an upstream change: comparing this week's profile with last week's.
- Data catalogues: many show a profile next to each table.
📝Case scenario
A logistics company took over a smaller rival's delivery records. A five-minute profile showed that 12% of delivery dates were 1 January 1900: the rival's system used it for "unknown". Those rows were marked as unknown before the merge, instead of being counted as deliveries made 126 years ago.
🖱️Try it
Run SUMMARIZE on raw/customers.csv read as text. Success: you can name two columns with surprises and say what each one means.
⚠️Common mistakes
- Profiling after cleaning only. Sign: the profile looks perfect. Fix: profile the raw data too.
- Reading only the averages. Sign: an average looks normal while the minimum is impossible. Fix: look at minimums, maximums and distinct counts.
- Profiling once. Sign: a source changes and nobody sees it. Fix: compare profiles over time, or turn surprises into checks (Lesson 3).
🛠️For your project
Your Project 1 notes should start with a profile of the raw order files; each surprise becomes a rule.
✅Quick check
What does a profile show for each column?
Show the answer
Count, missing values, distinct values, minimum, maximum and common values.
Why profile raw data as text?
Show the answer
So you see exactly what arrived, not a guessed conversion.