Prompts for Data Cleaning and Normalisation

#ai #llm #machinelearning #tutorial

Most data cleaning should never reach a model. The rows that should are the ones a deterministic parser rejected — and the right output for those is not a cleaned value but a proposed rule, because a rule can be reviewed once and applied a million times for free.

The boundary

Draw it by asking one question of each step: does the correct answer follow from the input by a rule you can state? If yes, write the rule. A model that applies a stateable rule is slower, costs money per row, varies between runs, and cannot be diffed when the output changes.

The failure this prevents is specific and quiet. Given 01/02/2024, a model will return a date. It will not tell you which convention it assumed, it may assume differently for the next row, and the result is a column that is correct for most rows and wrong for the eleven days a month where the two readings differ.

What code must do

Step	Description
Whitespace and case	Trim, collapse internal runs, NFKC normalise. Never a model call. NFKC alone resolves most of the invisible differences that make two identical-looking strings unequal.
Date parsing	An explicit ordered list of accepted formats, tried in order, failing loudly. If a value matches two formats with different results, it is ambiguous and belongs in the residue — not resolved by whichever the parser tried first.
Numbers and money	Strip the symbol, decide the decimal convention from the column rather than the row, convert to minor units as an integer. Floating point money is a bug regardless of who introduced it.
Checksums	IBAN mod-97, card Luhn, VAT and national identifiers with published check digits. These are decidable, and a model asserting an IBAN is valid is guessing where arithmetic is available — the same argument made in checksum-verified detection beating regex.
Lookups	Country codes, currency codes, your own category list. If a table exists, join to it. A model recalling a mapping is a lookup with an error rate.
Deduplication	On a normalised key you defined. A model deciding which of two rows is the duplicate is expensive and unrepeatable; use it only to propose the key, never to apply it.
Splitting fixed formats	Anything with a delimiter and a stable shape. A model splitting a CSV field is a regular expression that costs money.

Send to a model only what is genuinely left:

Rows that failed a deterministic parse and are not obviously junk.
Free text that needs a category — which is a classification prompt, not a cleaning one.
Entity resolution among candidates code has already narrowed to a handful.
Unstructured blobs that need splitting into fields, which is extraction.

The prompt for what is left

Every row in <rows> failed deterministic parsing. For each, propose a
correction. Do not correct anything that parsed; those rows are not here.

For each row return:
{"id": "<the row id, copied exactly>",
 "field": "...",
 "raw": "<the input value, copied exactly, unchanged>",
 "proposed": "<the corrected value, or null>",
 "rule": "<the general rule your correction follows, in one clause>",
 "confidence": "high" | "low",
 "ambiguous_because": "<what the raw value does not determine, or null>"}

Rules:
- Never invent a value. If the raw string does not determine the answer,
  "proposed" is null and "ambiguous_because" names exactly what is missing.
- "01/02/2024" does not determine a date. Return null with
  "day/month order not determined by the value".
- A correction that changes anything beyond whitespace, case and punctuation
  is "low" confidence unless the target value appears in <reference>.
- "rule" must generalise. Write "strip a trailing period from an abbreviated
  country name", not "change 'Fr.' to 'France'". If your correction does not
  generalise, that is a signal it is a guess; mark it low confidence.
- Do not merge rows, do not delete rows, do not reorder them. You are
  proposing values, not deciding.
- Return exactly one object per input row, in the same order, with the same
  count. If you cannot process a row, return it with "proposed": null and an
  "ambiguous_because" that says why.

<reference>
{{controlled_vocabulary_or_lookup_table}}
</reference>

<rows>
{{failed_rows_as_jsonl}}
</rows>

The same-order, same-count requirement matters more than it looks. Row alignment failures in batch processing are the bug class that corrupts data silently — every value shifted by one, every row plausible. Assert the count and the ids before you use anything, and reject the whole batch on mismatch rather than trying to realign it.

The rule field is the point

The corrections are the visible output. The rule field is the valuable one, because it is the thing that stops you paying for this call again.

Run a batch. Collect the rule strings.
Group them. Near-identical phrasings collapse: “strip a trailing period from an abbreviated country name” and “remove the full stop after an abbreviation” are one rule.
Any rule accounting for more than about twenty rows is a candidate for code. Write it as a deterministic step, add a test with three of the rows it came from, and put it in the pipeline before the model.
Re-run. The residue is now smaller and the remaining rules are rarer and more interesting.
Repeat until the residue is genuinely ambiguous cases. Those are the ones that need a person, and there will be far fewer of them than you expected at the start.

Read as a whole, the model is a rule-discovery device rather than a cleaner. That reframing changes the economics: a per-row model cost that recurs forever becomes a one-off cost that produces a permanent deterministic step. It also changes what you optimise the prompt for — the quality of the generalisation, not the prettiness of the corrected value.

This is a batch workload with no user waiting, which makes it one of the few genuinely price-shaped model choices: a cheaper model that gets 90% of the rules is worth more than an expensive one that gets 94%, because the rules are reviewed either way. Comparing candidates on price per million tokens across providers is a five-minute decision that recurs on every batch.

Never overwrite the raw value

Every corrected column needs three companions in the row: the original value, what changed it, and when.

country            "France"                     -- the value you use
country_raw        "Fr."                        -- never overwritten
country_source     "rule:strip-abbrev-period"   -- or "model:v4", or "human:jl"
country_changed_at 2026-08-04T09:14:22Z

The reason is not sentiment about provenance. It is that you will get a rule wrong, and you will find out three months later, and the only question that matters then is whether you can undo it. With the raw column you re-run the corrected rule over the affected rows. Without it, the original values are gone and the answer is a restore from backup.

The source column additionally lets you measure each rule: how many rows it touched, and how many of those a human later corrected again. A rule with a high subsequent-correction rate is wrong and should be pulled, and you cannot find it without the column.

When it stops working

The residue stops shrinking. The point of the loop is that each round moves rules into code. A flat residue across three batches means nobody is promoting the rules, and you are paying a recurring cost for a one-off job.
Rules stop generalising. Sample ten. Rules naming specific values rather than patterns mean the model has started memorising this batch, and the promotion step will produce single-purpose code.
confidence: high on substantive changes. The rule says only whitespace, case and punctuation changes may be high confidence unless the reference supports them. Check it mechanically; it is a two-line assertion.
Row counts mismatch. Never accept a partially aligned batch. Reject, split it in half, re-run. A batch size that works today can stop working after a model change, and this is the first symptom.