Data Validation vs Data Cleaning: Finding Against Fixing

Validation is a sentence written before the record arrives, saying what a cell may contain. Running it raises flags and changes nothing. Cleaning is a change somebody decided on and wrote down. On the Neelbagh stall record, invented, six rules raise seven flags and move no figure at all. Three of the nine faults raise no flag anywhere.

A rule is a sentence somebody sat down and wrote, in ordinary words, about what a cell in a particular column is allowed to hold. Writing one takes no test, no fitted line and no statistic of any kind. Where a rule carries a cutoff, a person picks the number rather than working it out from a spread. Two things are taken as given below rather than rebuilt from scratch. The first is the written column listThe short document that says, column by column, what a record holds and in what form. The people who keep the record write it down, and the list sets out the form the record is supposed to take rather than the form it happens to take. for this record, which says what each of the eight columns holds. The second is the run that put the record right afterwards, whose net effect on the average takings figure appears once below as a contrast.

What is validation, and what does running it change?

Validation is checking a record against sentences written in advance. Somebody decides, before a single row arrives, what a cell in each column may and may not contain. The takings column holds whole rupees and is never empty. The pitch column holds an area in square feet somewhere between a stall big enough to stand in and a stall big enough to park a van in. Then the record turns up, the sentences are run over it, and every cell that breaks one gets a mark against it. The mark against a cell is called a flag. Running a set of rules produces a list of flags and nothing else at all: the record afterwards is byte for byte the record that was handed over.

The school exam hall on the day the answer sheets come back from the invigilator has the same shape. Before anybody grades anything, one clerk goes down the pile checking that each sheet has a roll number written on it, that no roll number appears twice, and that no sheet is blank. She puts a slip of paper in the ones that fail. She does not write a roll number on the blank sheet. She does not choose between two sheets with the same number. At the end of an hour the pile is exactly as heavy as it was, carries exactly the same marks on the sheets, and now holds eleven slips. Validation is complete at that point, and everything difficult still lies ahead of it.

What is cleaning, and what does doing it change?

Cleaning is the other half, and it is the half where something actually happens to the record. Somebody reads the flags, decides what each one deserves, makes the change and writes down what was changed and why. The order matters inside that sentence: a decision comes between the flag and the change, and the decision belongs to a person who can be named afterwards. Validation says a cell cannot be right; cleaning says what was done about it, signed.

How the cleaning is done and what each step costs is covered separately. The size of the difference between the two acts is easy to state on this record. The run that put this record right moved the average takings figure down by Rs 14,838.90/-, from Rs 69,035.45/- to Rs 54,196.55/-, both figures on one convention, stated here so that it can be checked: every takings cell that holds a usable number is added up, and the total is divided by the count of those cells. An empty cell holds no number. The office code meaning no return arrived holds no number either. A genuine zero does hold one, and it counts. The two averages come from Rs 21,40,099/- over the 31 takings cells that carried a number and Rs 15,71,700/- over the 29 that carried a usable one, each division rounded once at the end. Against that, the six rules below move the same figure by exactly nothing. Moving figures is not what rules do.

Where does a rule come from, if not from the data?

Where a rule comes from separates a rule set worth running from one that is theatre, and the answer is short. Every rule worth having is a line of the written column list turned around into a claim about the file at hand. The list says the takings column holds a whole number of rupees and is never left empty; turned around, that is a rule saying this cell is not empty. The list says Rs 99,999/- is what the market office writes on the form when a stall never sent its return in; turned around, that is a rule saying a takings figure is not that no return codeA fixed value an office writes into a cell to mean something other than a quantity, here to mean the paperwork never arrived. A code of that kind looks like money and is not money, and only the column list says which values are codes.. The list says one row covers one stall for one month. The stall and the month together are the file's keyThe combination of columns that is supposed to pick out exactly one row. Here it is the stall and the month together, so two rows carrying the same stall and the same month are two rows too many.; turned around, that is a rule saying no two rows share a stall and a month.

Now the failure. Somebody working at speed writes rules by looking at the file: the smallest figure in the takings column is Rs 0/-, the largest is Rs 4,80,000/-, so the rule becomes takings between Rs 0/- and Rs 4,80,000/-. The rule will pass every row, every time, for ever. A sentence like that was never a rule at all, but a description of the file wearing a rule's clothes, and a description approves whatever it describes, faults included. A rule that came out of the data is not a rule, and it will approve the record's faults with the same untroubled confidence it approves everything else. The test is simple and it is about time rather than content: could this sentence have been written before the file existed? If not, it has nothing independent to say.

What do six rules actually catch on this record?

Here is the whole rule set, six sentences, every one of them a line of the written column list turned around. One: a takings cell is not empty. Two: no two rows carry the same stall and the same month. Three: every stall in the grid has a row for every month. Four: a takings figure is not the market office code for no return received. Five: a takings figure is not more than five times that stall's own smallest other month above nothing. Six: a pitch is between 50 and 200 square feet.

Rule five deserves a sentence of explanation because it is the only one carrying a number that is not straight off the column list. The five is a thresholdA fixed cutoff written into a rule, on one side of which a value is let through and on the other side of which it is marked. Somebody chooses the number and can be asked why they chose it., and it was chosen by a person at the market office who thought about how much a stall's month can plausibly jump and wrote down five. The five is not worked out from anything. Four or eight would have been just as defensible, and a different choice would have raised a different set of flags. The number therefore sits in the sentence where anybody can argue with it, rather than buried in a machine.

The six rules run over the record produce seven flags. Six sentences, seven flags, and afterwards every one of the thirty two rows holds exactly the figure it held before the run started. Rule one lands on NB-05 Bansi Flour's month 2, where a row exists and the takings cell is empty. Rule two lands on NB-03 Harit Greens' month 2, filed twice: once on day 5 reading Rs 36,400/- and once on day 19 reading Rs 39,700/-. Rule three lands on NB-04 Peetal Utensils' month 3, a month with no row at all. Rule four lands on NB-02 Chandan Tea's month 4, reading Rs 99,999/-. Rule five lands twice, on NB-07 Sundari Chaat's month 3 and NB-08 Peeli Mithai's month 3, both of which read Rs 4,80,000/-. Rule six lands on NB-05 Bansi Flour's pitch, recorded as 14 in a column that holds square feet.

Why does the code for no return walk past the size rule?

Rule four and rule five look at the same column and feel like they overlap. The two rules do not overlap, and this record shows why in one figure. NB-02 Chandan Tea's month 4 reads Rs 99,999/-, not money at all but the office code meaning the return never arrived. Rule four catches it because rule four knows the code. Now ask whether rule five would have caught it as well, if rule four had never been written. NB-02's other three months are Rs 31,000/-, Rs 33,000/- and Rs 32,000/-. The smallest is Rs 31,000/-, and five times Rs 31,000/- is Rs 1,55,000/-. Rs 99,999/- sits comfortably below that. Next to that stall's own months a figure of Rs 99,999/- is unremarkable, so the size rule lets the code straight through.

The general shape is worth holding on to. A size rule knows nothing except how big things are. A dictionary rule knows what particular values mean in this office. Neither substitutes for the other, and a rule set with only one kind has a hole in it shaped exactly like the other kind. The everyday version: a bank statement line reading Rs 1/- might be a real one rupee test transfer or might be the code the branch uses for something else entirely, and no amount of staring at how big it is will settle which.

What happened when one of those rules was written badly?

The most useful fact about rule five was not found by reading the rule. Somebody found it by running the rule and counting what came back. Rule five was first written without three words at the end. The sentence said a takings figure is not above five times that stall's own smallest other month, and it stopped there. Run over the record it raised nine flags rather than seven.

The two extra flags both landed on NB-04 Peetal Utensils. NB-04 really did take nothing at all in month 2. A recorded zero is not a fault and not a missing value but a correct figure, and a real zero is money in exactly the way a blank is not. So NB-04's smallest other month was Rs 0/-, and five times nothing is nothing. Every figure NB-04 ever recorded is above nothing. Its month 1 of Rs 26,000/- and its month 4 of Rs 28,000/-, two entirely ordinary months for a small utensils stall, both got flagged. Two false positivesA mark raised against something that turns out to be perfectly fine. The mark is real, the thing it is pointing at is not, and the cost is the time somebody spends looking., caused not by a wrong figure but by a right one.

Repair the sentence by adding the words above nothing and the rule takes NB-04's smallest other month as Rs 26,000/- instead. Five times Rs 26,000/- is Rs 1,30,000/-. Nothing of NB-04's comes near that figure, and the rule leaves the stall alone entirely, including the zero itself. Seven flags again. The lesson generalises past this record: a rule is a piece of work like any other, it can be wrong, and the only thing that found this one wrong was somebody running it, counting the flags, and asking why the count was not what they expected. Reading the sentence a second time would never have done it.

What can no rule reach, however many are written?

Nine faults were put into this record deliberately when it was built for teaching. The seven flags reach six of them: the blank cell, the row that is not there, the office code, the two rows sharing one stall and one month, the two cells reading Rs 4,80,000/-, and the pitch recorded in the wrong unit. Three faults raise no flag at all, and no seventh or eighth rule of the same kind would change that. Each of the three is invisible for a different reason, so name all three exactly.

The first is NB-03, whose name is typed Harit Greens for the first two months and Harit Green for the last two. Every rule in this set keys on the stall identifier NB-03, consistent throughout, and no rule in this set looks at the name column at all. Nothing is out of range, nothing is empty, nothing is duplicated on the key. The record simply carries two spellings of one stall and passes clean. The second is the licence number. The licence number is a four digit whole number in every row, every value is a valid licence, and nothing about it breaks any rule that could be written about a cell. A licence number is a label rather than a quantity. The ten of them added together and divided by ten come to 2510.8, a number that sorts, prints and averages perfectly and means absolutely nothing. The third is the survivorship gapThe distance between a figure computed on everything that existed and the same figure computed on only the part that is still around to be counted. The survivorship gap is covered in its own right elsewhere in this subject area.. NB-09 Roshni Juice and NB-10 Amber Rolls stopped trading once month 2 ended, and the office took them out of the file altogether, their first two months along with them, without leaving a note anywhere saying it had done so.

The last of the three is the expensive one, so put a number on it. Month 1's takings, spread over the eight stalls the office kept, come to Rs 38,500/- a stall: month 1's total for those eight, divided by eight. The same month's takings across the ten stalls that really traded come to Rs 35,000/- a stall, the same arithmetic over a denominator of ten. The gap is Rs 3,500/- a stall a month, 10.00 per cent, and every rule in the set runs over that file and finds it spotless. Validation asks whether a cell is allowed. Validation never asks whether a cell is true, and it never asks whether the record covers what it is supposed to cover.

Which comes first, and what breaks when the two are swapped?

The order is validate, decide, clean, record, and each of the four is a separate act with a separate output. Validate produces flags. Decide produces a judgement on each flag, explicitly including the judgement to leave something alone. NB-04's Rs 0/- and NB-07 Sundari Chaat's genuine Rs 4,80,000/- both deserve a decision saying no change, and that decision is worth as much as any change. Clean produces a different record. Record produces the line that says what happened and who said so.

Cleaning before validating leaves the rules running against a file somebody has already altered, so they approve the alterations, and the only independent check there was has been destroyed. Nothing complains. Nothing fails. The flags simply come back cleaner than they should, and they come back cleaner precisely because the file has been quietly changed. Cleaning first is proofreading a letter after somebody has rewritten the sentences they disagreed with. The letter reads perfectly, and nothing whatsoever has been learned about the letter that was actually sent.

What makes a set of rules worth having?

The same holds at any desk where somebody hands over a file and asks for a number. A lender reading four quarters of a small borrower's sales, an analyst handed a spreadsheet of branch takings, a household going through a year of bank messages before applying for a loan: the four things that separate a rule set worth running from a ritual are the same in all three places.

First, every rule traces to a line of the written column list. Somebody who argues with a flag is then arguing about the list rather than about the flag. Second, every rule was run and its flag count written down before anybody looked at what it caught. The discipline of counting first is what found rule five broken. Third, every flag has a decision recorded against it, including the decision to change nothing. An unrecorded decision to leave something alone is indistinguishable later from nobody having looked. Fourth, somebody has written down the list of what the rules cannot see. The fourth is the one nobody writes, and it is the only one that would have caught the Rs 3,500/- gap. A pipelineA chain of automatic steps a file passes through, each one handing its output to the next without anybody looking in between. Building one is covered separately under programming for finance. that silently fixes whatever a rule flags has skipped the second, third and fourth all at once.