Cleaning real data without destroying it

What a real export looks like

Here is a payments file, 12,400 rows, straight from a provider’s dashboard. Every one of these is real and common:

• The city column contains Lagos, lagos, LAGOS , Lagos State and Lagos, NG
• Dates read 03/04/2026. That is 3 April or 4 March depending on which country exported it, and the file does not say
• Amounts appear as ₦12,500.00, 12500, 12,500 and (1,200): that last one is accounting notation for negative
• Phone numbers with +234, without it, and one with a trailing tab character
• 380 rows that look like duplicates, because someone re-ran the export and pasted it underneath
• The string N/A sitting in a column that is otherwise numbers
• A name rendered Renée, which is UTF-8 read as something else

You cannot fix this with a list of tricks. You need a method.

Profile before you fix

Count things first. This takes four minutes and tells you what you are dealing with.

SELECT COUNT(*), COUNT(DISTINCT reference), COUNT(amount) FROM raw_payments;

SELECT city, COUNT(*)
FROM raw_payments
GROUP BY city
ORDER BY COUNT(*) DESC;

Then, 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.

Clean in a query, not in the file

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:

CREATE VIEW payments_clean AS
SELECT
  reference,
  lower(btrim(city))                            AS city_key,
  to_date(paid_on_text, 'DD/MM/YYYY')           AS paid_on,
  replace(replace(amount_text, '₦', ''), ',', '')::numeric AS amount
FROM raw_payments;

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.

Deciding what a duplicate means is not a technical question

Two rows with the same reference, the same amount, four seconds apart is almost certainly a retry, and you should keep one.

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.

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.

This 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.

Never drop silently

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:

SELECT COUNT(*) FROM raw_payments
WHERE to_date(paid_on_text, 'DD/MM/YYYY') IS NULL;   -- 62

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.

A pipeline that fails loudly is better than one that quietly discards three percent of your business.

The one habit

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.