Data Quality: The Dimensions That Actually Break Analysis

Data quality is whether a record can carry the question being asked, and it is checked by counting the record before reading it. Six counts of the Neelbagh stall record, invented, expose four faults in it. A fifth needs a rule that knows the market office codes. The sixth, two stalls deleted when they closed, shows up in no count at all.

Counting and division are the whole of the arithmetic below, and one idea sits under all of it: a figure exists to answer a question, and a figure that has forgotten which question it was answering cannot be used for anything.

What does data quality actually mean, and what does it not mean?

The version most readers arrive with is worth throwing out first. Data quality is not tidiness. Quality is not whether the columns line up, whether the spellings are consistent, or whether somebody has run a check and given the file a score out of ten. A file can be immaculate and useless. A record has quality against a question, and against nothing else.

Consider the till roll at the nearest shop. At the end of the day it says exactly what came in and at what time. Asked what the shop took today, it answers completely, without argument, to the rupee. Asked who bought the mustard oil, it has nothing whatever to say. No buyer was ever written on it. The till roll did not get worse when the second question was put to it, and the roll was never built for that question. Neither answer makes the roll good or bad, and both of them say what it is for.

So the first move is not to open the file. The first move is to write down, in one sentence, the question the file is about to be asked. A record cannot be judged until the question it is going to be asked has been said out loud. Until then there is nothing to judge it against. Almost everything that goes wrong later is decided in the ten seconds nobody spends on this.

Two separate things can then go wrong. A record can be faulty on its own terms: a row typed twice, a cell left empty, a name spelled two ways in four months. A record can also be perfectly consistent, perfectly tidy, and still be the wrong record for the question, on account of what was taken out of it before it ever arrived. The first kind announces itself the moment the counting starts. The second kind never announces itself at all.

What is the record in front of the analyst, and what does one row of it say?

One record carries everything that follows, and it is worth learning properly. The Neelbagh market is an invented covered market with fourteen pitches, of which ten traded. The market office keeps one line for each stall for each month, over four months numbered one to four, with no calendar date anywhere in it. The collection of those lines is the Neelbagh stall record.

Three words first. A ROW is one line of the record, and it is one complete observation: everything the office knows about one stall in one month. A COLUMN is one field running down every row, holding the same kind of thing each time. A CELL is where a row meets a column, and it is the smallest thing in the record that carries a meaning of its own. The Neelbagh stall record is thirty two rows and eight columns. Eight columns over thirty two rows is 256 cells, and the check that follows runs at the cell.

One row says which stall and which month, what the stall took in takingsThe money a stall took in over a month, before anything is taken out of it. It is what came across the counter, not what was left at the end., and when the returnThe sheet a stall fills in and hands to the office at the end of a month. The record is typed up from these, which is why the day one arrived is worth keeping. reached the market office. Read line one as a sentence and it says this: stall NB-01, Kadamba Idli, licence 2104, cooked food, month 1, took Rs 42,000/-, on a pitch of 120 square feet, and its return reached the office on day 6 of the following month. Eight cells, eight separate claims, each of which can be right or wrong on its own.

Now the whole thing. A record small enough to print is a record a reader can check, and that is the only reason this one is thirty two rows rather than thirty two thousand. Everything below is done on paper first. A check that cannot be done by hand on thirty two rows does not become trustworthy by being run on a bigger file; it just becomes harder to disbelieve.

Five cells and one gap are marked in red, and the market office marked none of them. The record as the office handed it over carries no marks at all, and that is the difficulty. The band between line 20 and line 21 is not a row of the record: it is drawn to show where a row should have been.

How to Profile a Financial Dataset: which six counts come before reading a single figure?

Before a figure is read out of a record, the record is counted. Counting is the whole of it. The record is asked a handful of questions whose answers can be compared with each other, and, this is the part everybody skips, the expected answer to each is written down before the count is run. Six counts do almost all of the work on a record shaped like this one.

Not one of those six counts is a finding on its own. Thirty two rows is neither good news nor bad news. Nine names is not a fault until the number of identifiers is also known. Every one of the six is one half of a comparison waiting for its other half. Writing the expectation down first is therefore not a formality. A count with no expectation attached cannot surprise anybody, and a count that cannot surprise has said nothing.

The panel above says the same thing from the other end. Read the record straight down in the order the office wrote it and the faults surface at lines 12, 14, 20, 24 and 26. Five of them. Read on past line 26 to the end and nothing further turns up, and at every one of the thirty three settings of that control the count of faults found stops at five of six.

SQL for Financial Data Analysis: how much of it does counting a record actually need?

Almost none, and it is worth being exact about why. When a record is small enough to print it is counted by eye, exactly as above. When it is not, it usually lives in a stored collection of records that a program looks after, and questions are put to it in a query languageA way of writing a question down so that the program looking after a stored collection of records can answer it. Which questions to ask matters more than how they are typed.: a way of writing a question down so the machine holding the record can answer it.

The first questions worth asking in one are exactly the six counts above, and that is the entire connection between counting a record and querying one. How many rows. How many different values sit in this column. How many rows have nothing in this cell. The counting does not change when the record grows from thirty two rows to thirty two million; only the tool that does the counting changes. Because the rows can no longer be seen, the expectation written down first matters more, not less.

How such a question is actually written, what it is built out of and how to put one together is covered separately, under programming for finance. Anyone who can count thirty two rows on paper already knows what to ask of a record of thirty two million, and the rest is typing.

What does each gap between two counts show?

Four faults are sitting in the six numbers above, and each one is a gap of one between two counts. Take them in order.

Thirty two rows against thirty one distinct stall and month pairs is one row too many. Somewhere in the record, two rows are answering one question. The pair is NB-03 Harit Greens in month 2, filed on day 5 showing Rs 36,400/- and filed again on day 19 showing Rs 39,700/-. The two rows are not copies of each other: they differ in the figure and in the day. A check that matches whole rows against each other would pass both of them.

Thirty two rows against thirty one takings cells carrying a number is one cell with nothing in it. The empty cell is NB-05 Bansi Flour in month 2. The row exists, the stall exists, the month exists, and the cell where the figure should be is empty. Nine distinct stall names against eight distinct stall identifiers is one stall written down two ways, carrying a closing s for its first two months and losing it for the last two. And thirty two expected stall months against thirty one present is one row that never arrived, NB-04 Peetal Utensils in month 3.

The first of those six counts is the quiet one. Thirty two rows is exactly what a clean eight stall record would have given. The count agrees with the expectation and appears to say nothing is wrong. The total comes to thirty two because one row is doubled and one row is absent, and the two cancel each other out exactly. A count landing on the expected number is not the same as no fault, and here two faults hid inside one figure that looked right. They separate only when that count is put beside the one under it.

Four of this record's faults are each a gap of one between two counts, and nobody had to read a single takings figure to find any of them. That is the return on four minutes of counting, and it is available before any judgement about money is made.

One warning, and it matters more than it looks. A gap names the fault and never names the fix. The counts show that two rows answer one question about NB-03 in month 2. Which of the two to keep is a separate question, and no arithmetic anywhere settles it. The answer depends on why the second one was filed. Somebody has to go and ask. The effect of that decision on the published figure is covered separately.

Which faults do the six counts never reach?

Two, and they are the two that cost the most. Start with the cheaper one. The month 4 takings cell for NB-02 Chandan Tea reads Rs 99,999/-, and that is not money. Rs 99,999/- is the market office code for no return received, written into the cell by a clerk following the office form. Every one of the six counts treats it as takings. Rs 99,999/- is a perfectly ordinary number, and a count has no way of knowing what any particular number was written to mean. The code is a placeholderA value written into a cell to stand for something that is not a measurement, such as nothing arrived. It occupies the cell exactly as a real figure would. wearing the clothes of a measurement, and only a rule that already knows the office codes catches it.

Two bars stand at the top of that drawing. Lines 23 and 24 both read Rs 4,80,000/-, one for NB-07 Sundari Chaat and one for NB-08 Peeli Mithai. One of them is a real month and the other is a typing error. The two cells hold the identical figure, so anything reading only the takings column reads them alike. Reading them alike is arithmetic rather than a discovery. Which of the two is which gets decided somewhere other than that column, and it is covered separately.

Now the expensive one, and the record is spotless about it. Two stalls, NB-09 Roshni Juice and NB-10 Amber Rolls, gave up their pitches at the end of month 2, and the office then took both of them out of the record altogether, their two trading months along with them. Nothing in the file says so. There is no note, no blank row, no gap in the line numbers. Ten stalls traded and the record counted so far describes eight.

Here is the proof that no count reaches it, and it is worth doing rather than believing. Put the four deleted rows back and run the same six counts again. The six counts become thirty six, thirty five, thirty five, ten, eleven and thirty six. Every number moved. Now take the four gaps: one, one, one and one, exactly as before. A profile finds what is wrong inside a record and never what is missing from it.

The fifth of the five questions below is worth learning by heart for exactly that reason. Every other fault on this record leaves a mark somewhere in it. A deletion leaves its mark only in what somebody decided to keep, and that decision was taken before the record was handed over. The effect of the missing stalls on a published figure, and the same fault where nobody deleted anything at all, is covered separately.

What is the average takings figure, and which denominator is it using?

Only one quantity is needed below, and nowhere near as much of it as might be feared. An arithmetic average is the plainest thing in the subject: the figures are added up, and the total is divided by how many figures were added. The number divided by has a name, the denominator, and very nearly all the trouble with an average lives in it rather than in the addition.

So the convention has to be said out loud, every single time the figure is printed. The average takings per stall monthOne stall in one month. It is the unit this record is kept in, so ten stalls over four months is forty stall months, whether or not every one of them was written down. is the sum of the takings cells that carry a usable number, divided by how many of them carry one. A blank is not a number. The market office code for no return is not a number. A real zero is a number. NB-04 in month 2 really did take Rs 0/-, its pitch shut all month while it was re roofed, and it filed a return saying exactly that.

Run on the record exactly as the office handed it over, before anybody has counted anything and before anybody knows which cells are usable, that gives Rs 21,40,099/- spread over 31 cells, or Rs 69,035.45/-. The raw figure goes down on paper with its denominator attached. The same sum over a different count of cells gives a different answer, and every one of those answers is arithmetically perfect.

A household does this every month without noticing. Somebody wants to know what the electricity costs. The household finds the bills it kept, adds them up and divides by how many it found. If nine bills were kept the denominator is nine, not twelve, and the answer is the average of the months somebody happened to keep the receipt for. The answer is not wrong, but it answers a question nobody asked out loud: what the household spent in the months it filed. Rs 69,035.45/- is that same figure: the average computed on the record exactly as it arrived, before anybody counted anything.

What is that raw figure quietly carrying?

Four things, and it is worth naming them one at a time. The raw figure carries a typing error worth Rs 4,32,000/- in a single cell. NB-08 Peeli Mithai in month 3 reads Rs 4,80,000/- where its return slip reads Rs 48,000/-, and one trailing zero on one cell moves an average of thirty one numbers a very long way. The raw figure carries Rs 99,999/- that is not money at all. The raw figure carries NB-03 in month 2 twice, and the earlier of those two filings is the smaller of them, so it drags the figure down rather than up. And it is silent about eight stall months of a market that traded.

Deal with those four and the same convention, on the same record, gives Rs 54,196.55/-. The two figures are Rs 14,838.90/- apart. Nobody lied, nobody guessed, and every single step between them was arithmetic. Putting a record right in this way is called cleaningThe work that follows a check: deciding what to do about each fault a count has turned up, in a stated order, and writing down what was done to which cell., and the order the steps go in, along with what each one risks, is covered separately. Doing them in the wrong order can move a figure further from the truth rather than closer to it.

On the decimals: one of these figures is a display rather than an identity. Rs 69,035.45/- and Rs 54,196.55/- are both rounded to the paisa. Neither division ends: Rs 21,40,099/- over 31 runs on forever and so does Rs 15,71,700/- over 29. Both were held as exact fractions until the last moment and rounded once, so the distance between them prints as Rs 14,838.90/- rather than as the difference between two separately rounded halves. Redone on a calculator that rounds at every step, the result may land a paisa away, and that calculator is not wrong so much as rounding twice.

Two later subjects turn on one word, so it goes down now. provenanceWhere a figure came from and who wrote it down, as against what the figure says. Two cells holding the same number can have completely different provenance. is where a figure came from, which is a completely different thing from what the figure says. The only reason anybody can correct NB-08 month 3 is that the office holds the stall return slip reading Rs 48,000/- and its own arithmetic on that month agrees with it. The evidence sits in the paper the office keeps beside its file, the return slips and the day bookThe running handwritten log an office keeps beside its file, noting what happened and when. It is paper, it is not the record, and it is often the only place a figure can be traced back to., and not in the record at all. The takings column, read on its own, offers no opinion whatever.

How to Check Data Quality Before Financial Analysis: what is asked before a figure is trusted?

Everything above collapses into five questions. A lender looking at a borrower's sales record, an analyst handed a spreadsheet of monthly figures, an investor reading somebody else's summary and a household adding up its own bills are all doing the same job, and the difference between doing it well and badly is whether these five get asked before the arithmetic starts rather than after it is published.

What question is this record supposed to answer? How many rows are there, and how many were expected before anybody looked? Which cells are empty, and what does empty mean here, nothing happened or nothing was written down? Which values are codes rather than measurements? And who or what left the record between the first row and the last?

The fifth question is the one almost nobody asks, and it is the one that costs the most. The first four can be answered by counting, and a machine will do all four in a second on a file of any size. The fifth cannot be answered from the record at all, at any size, by any tool. The fifth question has to be asked of whoever produced the record, in those words, and the answer has to be written down beside the figure. On this record the honest answer is that two stalls left, and that answer changes the subject of the figure rather than its arithmetic.

There is a habit that makes all five stick. Before the file is opened, the six expected counts go on a sheet of paper. Then they are run. The pleasure of watching four of them come out wrong is what fixes the habit. On a record of any size it takes about four minutes: the counting is done by the machine and the expecting is done by the analyst.