Profile Before You Clean: Turn Suspicion Into Measurable Defects

Cleaning without profiling is editing by anecdote. A few strange rows may be real edge cases, while a quiet formatting difference may affect half the dataset. Profiling establishes the shape of the data before a repair changes the evidence.

Start with grain and identity

Before counting nulls, write one sentence describing a row:

One row represents one menu record as catalogued by the source.

Then identify the candidate key and test it:

SELECT id, COUNT(*) AS n
FROM menu
GROUP BY id
HAVING COUNT(*) > 1;

If the supposed key is duplicated, every later count, join, and repair may be ambiguous.

A minimum useful profile

For each column, collect:

• row count, null count, blank count, and distinct count;
• minimum and maximum for numeric or temporal values;
• length distribution for text;
• most common values and their frequencies;
• values that fail an expected pattern or domain;
• cross-column rules relevant to the intended use.

SELECT
  COUNT(*) AS rows,
  SUM(place IS NULL) AS null_places,
  SUM(TRIM(COALESCE(place, '')) = '') AS blank_places,
  COUNT(DISTINCT TRIM(place)) AS distinct_places
FROM menu;

Null and blank are counted separately because they may come from different source behaviors. Sentinel values such as ?, N/A, or 0001-01-01 require their own counts.

Classify the defect before choosing a tool

Class	Example	Useful technique
Syntactic	Several date formats	Regex, parser, normalization
Domain	Year outside 1850–2012	Range constraint
Relational	Author references no paper	Foreign-key check
Semantic	Ship name stored as a city	Rules plus context
Duplicate	Same entity under variants	Blocking and entity resolution
Quantitative	Extreme value in a distribution	Statistical method

A regex can detect a shape; it cannot establish that a plausible-looking city is geographically correct. Match the technique to the defect class.

Profile combinations, not only columns

Many defects appear only across attributes:

• end_date < start_date;
• a resolved city with no source rule;
• the same location mapping confidently to several cities;
• a country code incompatible with a postal-code pattern.

SELECT location, COUNT(DISTINCT canonical_city) AS cities
FROM observations
WHERE canonical_city IS NOT NULL
GROUP BY location
HAVING COUNT(DISTINCT canonical_city) > 1;

This query discovers ambiguous mappings that a per-column profile cannot see.

Preserve a baseline

A profile is most useful when it can be compared before and after cleaning. Store machine-readable counts, the query or code that produced them, and the dataset fingerprint. A repair should explain each change in terms of:

before violations - repaired + newly exposed = after violations

If row count falls, say which rejection rule removed rows. If cardinality collapses from 3,708 raw place strings to 233 cities, retain the mapping and unresolved set.

Review card

• State row grain and verify identity first.
• Count null, blank, sentinel, distinct, range, and frequency separately.
• Choose a technique after classifying the defect.
• Cross-column and cross-row profiles reveal semantic problems.
• Preserve the pre-cleaning profile as evidence.
• Every changed, rejected, and unresolved row should be reconcilable.

SELECT id, COUNT(*) AS n
FROM menu
GROUP BY id
HAVING COUNT(*) > 1;

SELECT
  COUNT(*) AS rows,
  SUM(place IS NULL) AS null_places,
  SUM(TRIM(COALESCE(place, '')) = '') AS blank_places,
  COUNT(DISTINCT TRIM(place)) AS distinct_places
FROM menu;

SELECT location, COUNT(DISTINCT canonical_city) AS cities
FROM observations
WHERE canonical_city IS NOT NULL
GROUP BY location
HAVING COUNT(DISTINCT canonical_city) > 1;

before violations - repaired + newly exposed = after violations