Unit 09.04: Documenting normalisation so results reproduce

Unit ID: SQL-M09-U05 - Estimated active time: 12-15 minutes Objective: record cleaning decisions so another analyst reproduces your number exactly.

Cleaning is a judgement, so it must be visible

Two analysts count Delhi customers. One writes city = 'New Delhi' and reports 535. Another writes LOWER(TRIM(city)) = 'new delhi' and reports 1,604.

Neither made an arithmetic mistake. They applied different - undocumented - normalisation rules, and there is no way to tell from the numbers which is which.

Normalise inside the query, not before it

-- Reviewable: the rule is in the query
SELECT LOWER(TRIM(city)) AS city, COUNT(*) AS customers
FROM customers
GROUP BY 1
ORDER BY customers DESC;

If the cleaning happens in a spreadsheet, a manual edit, or a script nobody can find, the result cannot be reproduced or challenged. A rule written in SQL is a rule someone can disagree with - which is what makes it evidence.

The normalisation note

Add these lines to the evidence note from Module 1:

Question:      How many customers per city?
Source:        customers table, all 4,812 rows
Normalisation: city lowercased and trimmed before grouping
               (6 raw values collapse to 4 real categories)
Grain:         one row per normalised city
Result:        new delhi 1,604 - mumbai 1,070 - bengaluru 1,069 - chennai 1,069
Limitation:    normalisation is case and whitespace only; genuine alternative
               names such as Bangalore/Bengaluru are not merged

The limitation line is the honest part. Lowercase-and-trim does not merge Bangalore with Bengaluru, and a reader deserves to know the cleaning stopped there.

Make it reusable

Put the rule in one place so every query shares it:

WITH clean_customers AS (
  SELECT customer_id,
         country,
         LOWER(TRIM(city)) AS city
  FROM customers
)
SELECT city, COUNT(*) AS customers
FROM clean_customers
GROUP BY city
ORDER BY customers DESC;

Now the rule is defined once and any later query built on clean_customers inherits it - instead of each analyst re-deciding and diverging.

Practice

Write the normalisation section of an evidence note for a report on customers by country, given that the column is already clean.

Normalisation: none applied; country is a controlled code list
               (COUNT(DISTINCT country) = COUNT(DISTINCT LOWER(TRIM(country))) = 4)
Coverage:      4,512 of 4,812 customers have a country (93.8%);
               300 unknown, reported as their own category

Takeaway

Put the cleaning rule in the query, state it in the evidence note, and name what the rule does not fix. Undocumented normalisation is why two correct analysts report different numbers.

---

Mark complete