{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"messy-data","title":"Cleaning real data without destroying it","url":"https://addaly.com/learn/data-and-sql/messy-data","position":44,"of":66,"minutes":9,"answer":"Keep the raw data, clean it in a query you can re-run, and count every row you drop.","keyIdea":"Keep the raw data, clean it in a query you can re-run, and count every row you drop."},"body":{"format":"markdown","text":"## What a real export looks like\n\nHere is a payments file, 12,400 rows, straight from a provider's dashboard. Every one of these is real and common:\n\n- The city column contains `Lagos`, `lagos`, `LAGOS `, `Lagos State` and `Lagos, NG`\n- Dates read `03/04/2026`. That is 3 April or 4 March depending on which country exported it, and the file does not say\n- Amounts appear as `₦12,500.00`, `12500`, `12,500` and `(1,200)`: that last one is accounting notation for negative\n- Phone numbers with `+234`, without it, and one with a trailing tab character\n- 380 rows that look like duplicates, because someone re-ran the export and pasted it underneath\n- The string `N/A` sitting in a column that is otherwise numbers\n- A name rendered `Ren√©e`, which is UTF-8 read as something else\n\nYou cannot fix this with a list of tricks. You need a method.\n\n## Profile before you fix\n\nCount things first. This takes four minutes and tells you what you are dealing with.\n\n```sql\nSELECT COUNT(*), COUNT(DISTINCT reference), COUNT(amount) FROM raw_payments;\n\nSELECT city, COUNT(*)\nFROM raw_payments\nGROUP BY city\nORDER BY COUNT(*) DESC;\n```\n\nThen, and this is the part people skip, **look at the tail of that list, not the head.** The top five values are the ones you already knew about. The errors live among the values that appear twice. Sort ascending and read the bottom forty rows. That is where `Lagos, NG` and the empty string and the row where someone typed the amount into the city column are hiding.\n\n## Clean in a query, not in the file\n\nKeep the raw table exactly as it arrived. Never edit it. Do your cleaning in a view or a query that you can re-run:\n\n```sql\nCREATE VIEW payments_clean AS\nSELECT\n  reference,\n  lower(btrim(city))                            AS city_key,\n  to_date(paid_on_text, 'DD/MM/YYYY')           AS paid_on,\n  replace(replace(amount_text, '₦', ''), ',', '')::numeric AS amount\nFROM raw_payments;\n```\n\nThe reason is not tidiness. It is that next month the provider sends another file, and a view runs again in one second while forty minutes of manual find-and-replace has to be done again from memory, differently, by whoever is free. It is also the only way to answer \"what did you actually change?\" six months later, when somebody disputes your number.\n\n## Deciding what a duplicate means is not a technical question\n\nTwo rows with the same reference, the same amount, four seconds apart is almost certainly a retry, and you should keep one.\n\nTwo rows with the same customer and the same amount two weeks apart is probably a real repeat payment, and collapsing them steals money from your report.\n\nOnly someone who knows the business can tell you which rule applies. Ask, write the answer into a comment above the query, and do not let the shape of the data decide for you.\n\nThis is also why `SELECT DISTINCT *` is not a deduplication strategy. It removes only rows identical in every single column, so a retry that differs by one timestamp survives, while two genuine separate transactions that happen to match get silently merged. It is wrong in both directions at once.\n\n## Never drop silently\n\nWhen 62 rows have an unparseable date, the tempting move is a filter that excludes them so the query runs. Do not. Route them somewhere you can count:\n\n```sql\nSELECT COUNT(*) FROM raw_payments\nWHERE to_date(paid_on_text, 'DD/MM/YYYY') IS NULL;   -- 62\n```\n\nSixty-two out of 12,400 is 0.5% and is probably fine. But you must know the number, and you must look at ten of the rows. Half the time the unparseable rows are not random: they are all from one branch, or all from one week when a system was misconfigured, and dropping them removes an entire segment from your analysis while the totals still look reasonable.\n\nA pipeline that fails loudly is better than one that quietly discards three percent of your business.\n\n## The one habit\n\nAt the end of any cleaning job, write down two numbers: how many rows came in, and how many came out. If you cannot explain the difference row-for-row, you are not finished."},"check":{"question":"An export has 12,400 rows and around 380 look like duplicates. An analyst runs `SELECT DISTINCT *`, gets 12,020 rows, and moves on. What is wrong with that?","options":[{"text":"DISTINCT is slow on large tables and GROUP BY on the key columns would perform better","correct":false,"tempting":"Objects to the performance of the approach rather than to what it does to the data."},{"text":"DISTINCT only removes rows identical in every column, so retries that differ by a timestamp survive while genuine repeat transactions that happen to match are merged","correct":true},{"text":"The duplicates should be deleted from the source table so the fix does not have to be repeated next month","correct":false,"tempting":"Fixes the problem in place, destroying the raw data and making the cleaning impossible to re-run or audit."},{"text":"Nothing is wrong, since the row count dropped by 380 exactly as expected","correct":false,"tempting":"Treats a matching total as verification, when the same total can be reached by removing the wrong rows."}]},"prev":{"slug":"as-of-joins","title":"As-of joins: the value that was true at the time","url":"https://addaly.com/learn/data-and-sql/as-of-joins"},"next":{"slug":"loading-a-file","title":"Getting a file in without losing a row","url":"https://addaly.com/learn/data-and-sql/loading-a-file"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Cleaning real data without destroying it', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/messy-data (Accessed: 31 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Cleaning real data without destroying it","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"Keep the raw data, clean it in a query you can re-run, and count every row you drop.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"what-a-real-export-looks-like","runs":[{"text":"What a real export looks like"}]},{"t":"p","runs":[{"text":"Here is a payments file, 12,400 rows, straight from a provider’s dashboard. Every one of these is real and common:"}]},{"t":"ul","items":[[{"text":"The city column contains "},{"text":"Lagos","code":true},{"text":", "},{"text":"lagos","code":true},{"text":", "},{"text":"LAGOS ","code":true},{"text":", "},{"text":"Lagos State","code":true},{"text":" and "},{"text":"Lagos, NG","code":true}],[{"text":"Dates read "},{"text":"03/04/2026","code":true},{"text":". That is 3 April or 4 March depending on which country exported it, and the file does not say"}],[{"text":"Amounts appear as "},{"text":"₦12,500.00","code":true},{"text":", "},{"text":"12500","code":true},{"text":", "},{"text":"12,500","code":true},{"text":" and "},{"text":"(1,200)","code":true},{"text":": that last one is accounting notation for negative"}],[{"text":"Phone numbers with "},{"text":"+234","code":true},{"text":", without it, and one with a trailing tab character"}],[{"text":"380 rows that look like duplicates, because someone re-ran the export and pasted it underneath"}],[{"text":"The string "},{"text":"N/A","code":true},{"text":" sitting in a column that is otherwise numbers"}],[{"text":"A name rendered "},{"text":"Ren√©e","code":true},{"text":", which is UTF-8 read as something else"}]]},{"t":"p","runs":[{"text":"You cannot fix this with a list of tricks. You need a method."}]},{"t":"h2","id":"profile-before-you-fix","runs":[{"text":"Profile before you fix"}]},{"t":"p","runs":[{"text":"Count things first. This takes four minutes and tells you what you are dealing with."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT COUNT(*), COUNT(DISTINCT reference), COUNT(amount) FROM raw_payments;\n\nSELECT city, COUNT(*)\nFROM raw_payments\nGROUP BY city\nORDER BY COUNT(*) DESC;"},{"t":"p","runs":[{"text":"Then, and this is the part people skip, "},{"text":"look at the tail of that list, not the head.","b":true},{"text":" The top five values are the ones you already knew about. The errors live among the values that appear twice. Sort ascending and read the bottom forty rows. That is where "},{"text":"Lagos, NG","code":true},{"text":" and the empty string and the row where someone typed the amount into the city column are hiding."}]},{"t":"h2","id":"clean-in-a-query-not-in-the-file","runs":[{"text":"Clean in a query, not in the file"}]},{"t":"p","runs":[{"text":"Keep the raw table exactly as it arrived. Never edit it. Do your cleaning in a view or a query that you can re-run:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE VIEW payments_clean AS\nSELECT\n  reference,\n  lower(btrim(city))                            AS city_key,\n  to_date(paid_on_text, 'DD/MM/YYYY')           AS paid_on,\n  replace(replace(amount_text, '₦', ''), ',', '')::numeric AS amount\nFROM raw_payments;"},{"t":"p","runs":[{"text":"The reason is not tidiness. It is that next month the provider sends another file, and a view runs again in one second while forty minutes of manual find-and-replace has to be done again from memory, differently, by whoever is free. It is also the only way to answer “what did you actually change?” six months later, when somebody disputes your number."}]},{"t":"h2","id":"deciding-what-a-duplicate-means-is-not-a-technical-question","runs":[{"text":"Deciding what a duplicate means is not a technical question"}]},{"t":"p","runs":[{"text":"Two rows with the same reference, the same amount, four seconds apart is almost certainly a retry, and you should keep one."}]},{"t":"p","runs":[{"text":"Two rows with the same customer and the same amount two weeks apart is probably a real repeat payment, and collapsing them steals money from your report."}]},{"t":"p","runs":[{"text":"Only someone who knows the business can tell you which rule applies. Ask, write the answer into a comment above the query, and do not let the shape of the data decide for you."}]},{"t":"p","runs":[{"text":"This is also why "},{"text":"SELECT DISTINCT *","code":true},{"text":" is not a deduplication strategy. It removes only rows identical in every single column, so a retry that differs by one timestamp survives, while two genuine separate transactions that happen to match get silently merged. It is wrong in both directions at once."}]},{"t":"h2","id":"never-drop-silently","runs":[{"text":"Never drop silently"}]},{"t":"p","runs":[{"text":"When 62 rows have an unparseable date, the tempting move is a filter that excludes them so the query runs. Do not. Route them somewhere you can count:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT COUNT(*) FROM raw_payments\nWHERE to_date(paid_on_text, 'DD/MM/YYYY') IS NULL;   -- 62"},{"t":"p","runs":[{"text":"Sixty-two out of 12,400 is 0.5% and is probably fine. But you must know the number, and you must look at ten of the rows. Half the time the unparseable rows are not random: they are all from one branch, or all from one week when a system was misconfigured, and dropping them removes an entire segment from your analysis while the totals still look reasonable."}]},{"t":"p","runs":[{"text":"A pipeline that fails loudly is better than one that quietly discards three percent of your business."}]},{"t":"h2","id":"the-one-habit","runs":[{"text":"The one habit"}]},{"t":"p","runs":[{"text":"At the end of any cleaning job, write down two numbers: how many rows came in, and how many came out. If you cannot explain the difference row-for-row, you are not finished."}]}],"check":{"question":[{"text":"An export has 12,400 rows and around 380 look like duplicates. An analyst runs "},{"text":"SELECT DISTINCT *","code":true},{"text":", gets 12,020 rows, and moves on. What is wrong with that?"}],"options":[{"text":[{"text":"DISTINCT is slow on large tables and GROUP BY on the key columns would perform better"}],"correct":false,"tempting":[{"text":"Objects to the performance of the approach rather than to what it does to the data."}]},{"text":[{"text":"DISTINCT only removes rows identical in every column, so retries that differ by a timestamp survive while genuine repeat transactions that happen to match are merged"}],"correct":true,"tempting":null},{"text":[{"text":"The duplicates should be deleted from the source table so the fix does not have to be repeated next month"}],"correct":false,"tempting":[{"text":"Fixes the problem in place, destroying the raw data and making the cleaning impossible to re-run or audit."}]},{"text":[{"text":"Nothing is wrong, since the row count dropped by 380 exactly as expected"}],"correct":false,"tempting":[{"text":"Treats a matching total as verification, when the same total can be reached by removing the wrong rows."}]}],"order":[0,2,3,1],"why":null},"prev":{"slug":"as-of-joins","title":"As-of joins: the value that was true at the time","minutes":10},"next":{"slug":"loading-a-file","title":"Getting a file in without losing a row","minutes":9}}}