Duplicate Records: How They Arise and What They Distort

A duplicate is two rows answering the same question, and an exact copy is the rare case. In the Neelbagh stall record, invented for teaching, stall NB-03 filed month 2 twice: Rs 36,400/- on day 5 and Rs 39,700/- on day 19. A check that matches whole rows finds nothing. Five ways of settling it give three different published figures.

One quantity runs through everything below, so it is worth fixing before anything else moves. The sum of whichever takings cells hold a figure the convention allows, divided by how many cells went into that sum, is the arithmetic averageThe sum of the numbers divided by how many were added. The average is the only quantity needed anywhere below, and it is set out in full where this record was first printed. takings for each stall monthOne trader in one month. It is the thing a single row of this record is about, so a count of stall months and a count of rows are supposed to be the same count.. A cell left empty holds no number. Nor does the code standing in for a return that never reached the desk, however convincingly it sits in a column of rupees. A true zero does hold one. Every figure printed below carries its divisor alongside, since this record gives a different answer the moment the divisor changes.

Two counts from the opening notes here carry forward. The file the market office handed over has 32 rows, and those 32 rows carry 31 distinct combinations of stall and month. Under the convention just stated, the cleaned record gives Rs 54,196.55/- over 29 usable figures. Everything below is an argument about the gap between the first two of those numbers, and about what that gap does to the third.

What makes two rows duplicates, and why is an exact copy the rare case?

Two rows are duplicates when they answer the same question, whatever the rest of them says. The definition turns on the question a row answers, and says nothing about the contents. The definition directs attention to the columns that say what a row is about, and sets aside, for the purpose of this one test, every column that says what the answer was.

Consider the drawer where a household keeps its paper. The electricity bill for August arrives, gets opened, gets put in the drawer. Three weeks later the meter reading is queried and revised, and a corrected bill for August arrives. The corrected bill also goes in the drawer. Now there are two pieces of paper in there, and they are not copies of each other: the amounts differ, the dates differ, one carries the word revised across the top. Nobody would call them identical. But when the drawer is tipped out at the end of the year and every bill in it is added up, August lands in the total twice, and the year looks more expensive than it was.

The drawer is the shape of the whole subject. The two pieces of paper are duplicates because they answer one question, what August cost, and not because they look alike. The set of columns that carries the question has to be named first. Since the Neelbagh stall record is built to hold one row for each trader for each month, the key is the stall together with the month. Once that is named, the test is mechanical. Any pair of rows sharing a stall and a month is two answers where the record allows one, and a record that allows one and holds two has stopped being a record.

An exact copy, where every cell in one row matches every cell in the other, is the easy case and by a long way the rarer one. It happens when a file is appended to itself, or when the same import is run twice by mistake. An exact copy is easy: any tool finds it, and no judgement is needed about which copy to keep when the two are the same. Almost every duplicate that reaches a working record is not that. The ordinary duplicate is two rows that disagree somewhere, and the second one exists precisely to say something the first one got wrong.

What are the two rows in this record, and how do they differ?

NB-03 Harit Greens sent its month 2 return in on day 5 of the following month, reporting Rs 36,400/-. On day 19 the stall sent a revised returnA second form covering a period already reported, sent in to replace what the first one said. The word revised is doing real work: it means the sender is correcting themselves. for the same month, reporting Rs 39,700/-. The market office typed both of them into the record, in the order they arrived. Typing in what arrives is the only sensible thing a clerk can do with two pieces of paper. The whole of month 2 as it now stands appears below, printed so that the two rows sit together.

Across the two shaded rows, exactly how much of them agrees is visible. Stall the same. Name the same. Month the same. Two cells differ: the takings figure, by Rs 3,300/-, and the filed on dayThe column that records which day of the following month a form reached the office. It is a fact about the paper arriving, not about the trading it reports. column, 5 against 19. Nothing else in either row moves.

Neither of those rows is an error, and that is what makes this hard. Both are real filings. Both were typed in correctly. A visit to the market office to see the paper would turn up two forms, both signed, both genuine. There is no cell to correct, nobody to tell off and nothing to apologise for. The fault is not in a row at all. The record now answers one question twice and offers no way of choosing between the answers. A record that does that is a different kind of problem from a typing slip, and it needs a different kind of fix.

Why does a check on the whole row find nothing?

Handed this file and asked for duplicate rows, almost any tool will, unless told otherwise, compare every cell of every row against every cell of every other row and report back the pairs that match all the way across. Run against this record, that check returns zero. The only repeated key in the file belongs to two rows that disagree in two columns, so not one pair of rows in the Neelbagh stall record agrees in all eight.

Now ask a different question. Group byGather the rows that share a value and treat each gathering as one thing. Doing it in code belongs with the notes on programming for finance; here it is only the counting. stall and month, count the gatherings, and compare that count against the number of rows. The file has 32 rows and 31 gatherings. The whole finding is the gap of one between those two counts, and it is invisible to any test that looks at rows one at a time.

There is a trap sitting underneath this, and it is worth walking into on purpose. Eight stalls over four months is 32 stall months, so a clean record of this market would have exactly 32 rows. The Neelbagh stall record has 32 rows. A check of the row count alone would have found it correct and moved on. The count reads 32 because two faults pull opposite ways and land on nothing. NB-03's month 2 sits in the file twice, worth one row extra. NB-04's month 3 is not in the file at all, worth one row short. A month filed twice against a month never filed, and the total settles back exactly where a clean file would put it.

Faults in opposite directions hide inside a row count, so a row count on its own says almost nothing. The count of distinct keys does not cancel that way. Thirty one gatherings against a grid that expects 32 says a row is missing; 32 rows against 31 gatherings says a row is doubled. Both numbers are needed, and needed side by side, before either of them means anything at all.

Where does the key come from in the first place?

Everything above depends on having named the key correctly, so it is worth being exact about where a key comes from. A key comes from what one row is supposed to be about. The Neelbagh stall record was set up to hold one row for each trader for each month, and that sentence, written down before any data existed, is the key. The key appears in the column listThe written description of a file, saying what each column holds and what one row stands for. Whoever set the record up wrote it, and it is meant to be read before the data is. that came with the file, alongside the note that stall_id is the identifier and month runs 1 to 4.

A key is a decision somebody made about the shape of the record, and it is never something to go looking for in the data. The tempting move is the opposite one: sitting with the file, hunting for a column or a combination of columns that happens not to repeat, and calling that the key. Hunting for the key in the data feels rigorous. The move is exactly backwards, and this record shows why in one step.

Widening the existing key, stall and month, by adding filed_on_day makes NB-03's two rows differ inside the key itself, day 5 against day 19. Counted again, the gatherings come to 32 distinct keys over 32 rows. The gap closes. The tool reports a clean file. Nothing about the record has changed: the two forms are still sitting in the office, the market still traded eight stall months in month 2 and not nine. All that has changed is that the key was widened until the repeat could not be seen, and the reassuring number was read off the screen.

The same trap dresses up in other clothes. A column is unique today only because nobody has yet filed the second row that would repeat it. Two events rarely land on the same instant, so adding a timestamp to a key means almost nothing ever repeats. A record like that is not a record without duplicates. The key has been cut so fine that the question stopped being asked. The key comes from the written description of the record, it is fixed before anything is run, and the count then says what it says.

What do five ways of settling one repeated row give?

The repeat has been found. The remaining decision is which figure the record is going to carry for NB-03 in month 2, and there are several defensible answers. Keep whichever return came in later, on the grounds that a revision replaces what it revises. Keep whichever came first, on the grounds that a record should carry what was originally reported. Keep the larger figure. Keep the smaller figure. Keep both rows and let the arithmetic take them both.

Any of those five is a rule somebody could argue for in a room. Which one is chosen matters less than when it is chosen. A rule for settling a repeat has to be written down before anybody looks at the two figures, because a rule chosen after they have been seen is not a rule at all, it is a preference wearing a rule's clothes. The rule is stated in words first, and then applied, and the arithmetic only ever comes afterwards.

The five, worked against the cleaned record, appear below. The middle column is where the difference actually happens, so it matters as much as the right one: four of the rules change what goes into the total, and the fifth changes the divisor too.

Three answers from five rules, spread across Rs 593.22/- from the highest to the lowest. Every one of them is correct arithmetic on the record it was worked out from, under the same convention, with its divisor named beside it. There is no error anywhere in that table to find and put right. There is, instead, a decision somebody has to make and write down, and whoever reads the published figure will never see the two rows that produced it.

Two of those rules agree, so does that confirm anything?

Look again at the table. Keep the later filing gives Rs 54,196.55/- over 29 usable figures. Keep the larger figure gives Rs 54,196.55/- over 29 usable figures. It is very tempting to read that as reassurance: two independent ways of settling the repeat, both arriving at the same place, so the figure must be sound.

The two rules agree because NB-03's revised return happened to be the larger of its two figures, and for no other reason. The stall reported Rs 36,400/- on day 5 and then corrected itself upward to Rs 39,700/- on day 19. The later filing and the larger figure are the same row in this record. They are not the same rule, and nothing forced them together.

Suppose the correction had gone the other way. Same two amounts, but the stall reports Rs 39,700/- first and then revises down to Rs 36,400/-, perhaps because a wholesale return was counted in by mistake. Keep the later filing now publishes Rs 54,082.76/- over 29 usable figures. Keep the larger figure still publishes Rs 54,196.55/- over 29. The two rules that seemed to confirm each other are now Rs 113.79/- apart, and nothing in the record would have warned that this was going to happen.

So one is never a check on the other. Two rules landing on one figure is a fact about the record in front of the analyst, not a fact about the rules. The household version is familiar enough: two people work out the shopping bill, one by adding the prices and one by counting the notes left in the purse, and they agree. The two routes share nothing, so agreement between them is a check. Two rules that both happen to point at the same row are not two routes. They are one route with two names on it.

What does keeping both rows quietly do?

Of the five rules, the one that does the most damage is the one that looks like doing nothing. Keep both rows, and no decision has been made, no evidence removed and nothing thrown away. The headline moves from Rs 54,196.55/- over 29 usable figures to Rs 53,603.33/- over 30, a difference of Rs 593.22/-. On a figure of about fifty four thousand rupees that is roughly one part in ninety. Nobody queries it. It looks like ordinary movement.

Meanwhile the divisor has gone from 29 to 30. Every figure the record produces for each row is now spreading itself over a stall month the market never traded. There were eight stalls trading in month 2 and the file now carries nine month 2 rows. The extra one is not a stall that closed, or a form that arrived late, or a pitch nobody took. The extra row is a filing counted twice and dressed up as an extra trader.

The count is the more serious half, and here is why. The total is out by Rs 36,400/-, a number somebody could notice. The divisor is out by one. Nobody checks a divisor, and it quietly reaches every figure computed for each row anywhere downstream: takings for each stall, takings for each square foot, the average printed beside every rule above. A total invites a query because people read totals. A divisor is written once at the bottom of a note and never looked at again.

What is the second kind of duplicate in this record?

There is a duplicate in the Neelbagh stall record that has nothing to do with rows. Group by the stall_name column and count the gatherings: nine. Group by stall_id and count again: eight. One of those is a count of stalls in this market. The other is a count of ways somebody spelled a stall name.

Months 1 and 2 carry Harit Greens against NB-03. Months 3 and 4 carry Harit Green. Somewhere between the second and third month, whoever was typing at the desk dropped a letter, or a different clerk took over, or a form arrived with it written that way. One trader has become two entries, and both counts above are perfectly correct arithmetic. Only one of them is answering the question intended.

Join on the identifierA code issued once and then reused, like a licence number or a stall code. Nobody types it fresh each time, which is the whole reason it can be trusted., never on the name. An identifier is issued once, by somebody whose job it is, and afterwards it gets copied rather than composed. A name gets typed by whoever is on the desk that morning, and typing is where variation comes from: a space, a plural, a full stop, an initial, a title. Every one of those turns one thing into two, silently, in a column that still looks perfectly tidy on the screen.

Typing is also the reason the identifier column exists at all. The column is not decoration, and not a serial number for the office's convenience. The identifier is the one column in the record that a human hand is not free to vary, and that is precisely what makes it safe to joinLine two records up so that rows about the same thing sit together. Doing it in code, and everything that can go wrong along the way, is covered separately under programming for finance. on. Actually lining two records up against each other, and the several ways that goes wrong, is covered separately under programming for finance.

What has to happen before any row comes out?

All of this collapses into four small habits, and none of them requires a tool beyond what is already at hand. The key is written down before the file is opened, rows are counted against distinct keys with both numbers recorded, the rule for settling a repeat is stated in words before the figures are looked at, and whatever is removed is kept.

The first three follow from everything above. The fourth deserves its own sentence. The commonest reason to want a removed row back is not regret about the rule. The commonest reason is discovering that the key was wrong. Somebody realises the record actually holds one row for each stall for each month for each category, or that a stall can trade two pitches, and now the rows deleted as repeats were never repeats at all. Kept in a small separate file with a note saying which rule removed them, they are an afternoon of work. Not kept, they are a phone call to a market office asking whether anybody still has last quarter's forms.

The habit reads differently for each of the people who actually meet the fault. A lender comparing two small traders is looking at a takings figure with a divisor underneath it, and the first question worth asking is how many months that divisor counts and whether any of them is in there twice. An analyst handed a file of monthly numbers should count rows and distinct keys before doing anything else. The comparison is cheap, and it catches both a doubled row and a missing one. And a household keeping its own accounts, working out what it spends each month, is doing exactly the same arithmetic with a shoebox of bills: the divisor is how many months there actually are, not how many pieces of paper are in the box.