Data Pipelines: From Raw Source to Analysis-Ready Table
Two people are handed the same file. Both work carefully, neither guesses at anything, and the two figures they arrive at are Rs 14,838.90/- apart. The gap is not a story about carelessness. A file is a pile of characters, and everything anybody does to it afterwards happens somewhere nobody wrote down, so a gap of that size is what happens by default. Writing it down is what closes the gap. One invented record runs the whole way below, with a row count at every stop and no arithmetic harder than a sum divided by a count.
The Neelbagh stall record. An invented covered market, an invented market office, and that office keeping one row per stall per month across four months. The file it hands over has 32 rows and 8 columns. The file already carries every one of its faults, and those faults are settled elsewhere: a month with no row, a takings cell left empty, an office code sitting where a figure should be, one stall month typed in twice, a stall name spelled two ways, a five figure typing error, and a pitch area recorded in the wrong unit. None of that is diagnosed again here. The record is used here as it stands.
The counting rule, and it travels with every figure. The average takings per stall month is the sum of every takings cell that holds a real figure, divided by however many cells were added. Two kinds of cell are kept out of both halves of that. An empty box is one, holding no figure at all. The office code standing in for no return received is the other, a label wearing digits rather than an amount. A zero is a figure somebody wrote down on purpose, so a stall that opened its shutter and took nothing keeps its zero. The rule is a conventionA choice about how something is counted, written down in advance so that a second person can repeat it and land in the same place. rather than a discovery. A convention has to be stated in the same breath as any figure it produces.
Counting, and nothing harder. Every quantity below is a count of rows, a count of cells, or one of those counts divided into a sum. Nothing below needs any other arithmetic.
What is a data pipeline, and what is it for?
A data pipeline is a named path with named stages, run in a fixed order, where every stage says what it read, how many rows went in, how many came out, and why. The definition ends there. There is nothing clever inside it and nothing technical about the idea. The writing down is not a feature of a pipeline, it is the entire thing that separates a pipeline from a person who once did some work on a file.
Here is the everyday version. Think of a household running on one salary that keeps every bill in one box, writes the month on each one as it goes in, and never throws an original away. At the end of the year somebody asks what the household actually spent on electricity. The paper that produced every figure is still there and still labelled, so the answer is recoverable. Now think of the household next door. Just as careful, it adds each bill into a notebook the day it arrives, and then bins the bill. The second household has the totals and only the totals. If a figure looks wrong, there is nothing to go back to. Both households did the work. Only one of them can prove it.
A record travelling through an office is the same shape of problem at a larger size. Somebody receives it, somebody removes the rows that should not be counted, somebody corrects a cell, somebody produces a figure, and the figure goes into a note. Every one of those hands did something reasonable. If none of them wrote down what they did and how many rows they had before and after, the figure at the end is a number with no history, and a number with no history cannot be defended or corrected. A number with no history can only be repeated.
So what a pipeline is for is not speed and it is not automation. A pipeline exists so that a figure can be walked backwards to the cell that made it. Speed is a side effect of writing things down properly. The purpose is that when two people disagree, the disagreement can be located.
A pipeline and a set of steps somebody carried out once on a file are not the same thing. What does the first one have that the second one does not?
Where does a record arrive from, and what does each doorway settle?
A record arrives through one of three doorways. One is a file somebody hands over. Another is the answer to a request sent to an address. The third is a table already sitting in a store that somebody else set up. The Neelbagh stall record arrives here through the first of the three: the market office types it up and passes it across, once, whole.
The second and third doorways are real and they matter. The shape of a request, the shape of the answer that comes back, and what happens when the answer arrives a batch at a time rather than all at once, are covered separately. Where a stored table actually sits, what it declares about itself before a single row is read, and what it turns away at the door, is also covered separately. Naming a doorway is not the same as teaching it.
The pipeline needs the same three facts from every doorway, and there are only three. Which record arrived. When it arrived. How many rows it had. None of the three is the record itself. All three are facts about the delivery, and they are the first three lines of the written trail, before a single row has been touched. If all three cannot be answered about a file already in hand, the pipeline has already lost its first stage.
The doorway does change one thing worth flagging, and it is the thing readers most often assume away. A handed over file tells nothing at all about when it was last changed. The file has a name and it has contents, and the name is frequently a lie about the contents. A stored table usually knows something about when it was last written to, for the simple reason that somebody built it to know. An answer to a request usually arrives stamped with the moment it was assembled. The doorway that inspires the most confidence is often the one that tells the least.
A record can arrive as a handed over file, as an answer to a request sent to an address, or from a store somebody else set up. Which of the three usually tells the least about when it was last changed?
What is a CSV file, and what does it fail to declare?
A comma separated file is about as simple as a data format gets. Everything in it is plain textA file holding nothing but ordinary characters, with no hidden formatting, colours, formulas or settings of its own. Opened in any text editor, what appears is all there is.. One line per row. A delimiterThe character a file uses to say where one column stops and the next one starts. A comma is the usual choice, which is where the name comes from. between the columns. A header line on top carrying the column names. The header is how the columns are told apart. The Neelbagh file has that header line and 32 data rows across 8 columns, making 256 cells in total.
Now the important half, and it is the half that gets skipped. A comma separated file declares no type, no key and no meaning for an empty cell, so every one of those three becomes a decision somebody makes silently while reading it. Take them one at a time on this record.
No type. The file has a column of licence numbers holding values like 2104 and 2743. Licence numbers are digits, so anything reading the file will treat them as quantities, and their average is 2426.375. The average of a licence number is not money, not an area, not a count of anything and not a fact about the market. A licence number is an identifierA value whose only job is to name a thing. It never means anything as a quantity, so adding two of them together or averaging them produces a number with no meaning at all., and the file has no way to say so. The month column has the same problem in a quieter form: months numbered 1 to 4 will happily average to 2.5.
No key. Nothing in the file says that a stall and a month together should appear once. So NB-03 in month 2 appears twice, once at Rs 36,400/- filed on day 5 and once at Rs 39,700/- filed on day 19, and the file raises no objection whatsoever. It cannot. Nobody told the file that a stall month is meant to be unique, and there is nowhere to tell it.
No meaning for an empty cell. NB-05 in month 2 has a row and an empty takings cell. NB-04 in month 2 has a row and a takings cell reading zero. The two cells look almost identical at a glance, and they mean completely different things: one is a stall that traded nothing, the other is a stall whose figure never got typed. The file cannot say which is which. A human decided, later, somewhere else.
None of this makes a comma separated file a bad thing to be handed. The format is portable, it opens anywhere, and it will still open in twenty years. The point is narrower and more useful than that: a file carries data and it carries no promises about that data, so every promise relied on afterwards is one the reader made alone. Writing those promises down is the first real stage of the pipeline.
The Neelbagh file has a column of licence numbers stored as whole numbers. What will a reader do with that column sooner or later, and what would stop them?
What are the stops, and how many rows survive each one?
Here is the whole path in one table. Seven stops, counting the file as handed over as stop zero. Read the rows column first and ignore the money entirely on the first pass. The row count is the part almost nobody records and the part that settles almost every argument.
| Stop | What ran | Rows | Carrying a number | The figure after | The move |
|---|---|---|---|---|---|
| 0 | as handed over | 32 | 31 | Rs 69,035.45/- | |
| 1 | drop the blank row | 31 | 31 | Rs 69,035.45/- | no change |
| 2 | drop the office code row | 30 | 30 | Rs 68,003.33/- | down Rs 1,032.12/- |
| 3 | drop the withdrawn duplicate | 29 | 29 | Rs 69,093.10/- | up Rs 1,089.77/- |
| 4 | correct the typing error | 29 | 29 | Rs 54,196.55/- | down Rs 14,896.55/- |
| 5 | fix the two spellings | 29 | 29 | Rs 54,196.55/- | no change |
| 6 | fix the pitch unit | 29 | 29 | Rs 54,196.55/- | no change |
The row count falls three times, from 32 to 31 to 30 to 29, and then holds for the rest of the path. Two of the seven stops change a cell without changing the row count at all, and the last one changes a cell in a column the figure never touches. A stage that changes the row count and does not say so is the commonest way a figure goes wrong quietly. Nothing about the output looks different afterwards. A table with 29 rows and a table with 30 rows look identical at a glance, and they answer the same question differently.
The column headed carrying a number is the second thing worth staring at. The count in it is the denominatorThe count sitting underneath an average. It decides what the average is an average of, so two people using different ones will get different answers from identical data and both will be right. underneath every figure in the table. One takings cell is empty, so at stop zero there are 32 rows and only 31 of them carry a number. From stop one onward the two counts move together. The single row of divergence at the top is why the first cleaning step moves the figure by nothing at all.
At one stop the row count falls from 30 to 29 and the figure goes up rather than down. Which stop is it, and why can dropping a row raise an average?
Three of the six cleaning steps move the answer by nothing at all. Which three, and what do they have in common?
What does each cleaning step move the answer by?
Six steps run between stop zero and stop six. Written out as words, the moves are: no change, down Rs 1,032.12/-, up Rs 1,089.77/-, down Rs 14,896.55/-, no change, and no change. Add those and the record ends Rs 14,838.90/- below where it started.
The first surprise is that three of the six steps move the answer by absolutely nothing, and every one of the three was still worth running. Dropping the blank row removes a row that was never in the denominator to begin with, so the arithmetic cannot move. Fixing the two spellings of NB-03 changes a name column that no figure in the ladder reads. Fixing the pitch area from 14 to 150 square feet changes a column about floor space, not money. A step that moves the answer by nothing has done something valuable anyway: it has made it possible to say, in writing, that it moved the answer by nothing. Before it runs, that is not known. Afterwards it can be proved, and the next person does not have to wonder.
The second surprise is that one step moves the answer up. Dropping the withdrawn return takes the record from 30 rows to 29 and lifts the figure from Rs 68,003.33/- to Rs 69,093.10/-. NB-03 filed twice for month 2, Rs 36,400/- on day 5 and Rs 39,700/- on day 19, and the one that was withdrawn is the smaller of the two. Remove the smaller of two figures and what is left averages higher. Nothing was invented and nothing was inflated. A row that should never have been counted stopped being counted, and the answer went up.
The direction of that step is worth holding on to. The instinct almost everybody carries into this work is that cleaning means shrinking. It does not. Cleaning a record is a walk, not a reduction, and the direction of any single step is a fact about which cells that step touched rather than about cleaning in general. On this record the walk goes flat, down, up, a long way down, flat, flat. On a different record with the same six kinds of step it could go the other way at any of them.
A colleague looks at the table and argues that a step which moved the answer by nothing was a waste of time. What is the one sentence that answers them?
The calculator below walks the record from as handed over to fully cleaned. Does the figure fall at every step, hold at some and fall at others, or actually move in both directions?
The record stop by stop, with the trail written along the way
One control, seven stops. Three things redraw together: the rows still in the record, the average takings figure against a fixed line at the reading as the file was handed over, and the move from the stop immediately before. Underneath, every stop landed on writes itself into a trail that stays on screen. A trail like that is the whole of what makes a path checkable. Landing on all seven builds the audit trail by hand.
Educational illustration. The denominator never moves across the seven stops: only a takings cell holding a figure somebody could actually bank is counted, so an empty box and the office no return code stay out of it while a genuine nil stays in. The full wording of that rule is set out in the opening panel. Every quantity below the bars is a count of rows, a count of cells, or one divided into the other.
What happens when the record is actually walked, stop by stop?
The panel above moves through the path. The same walk is written out below, in words that survive without the panel and can be read straight through.
Stop zero, as handed over. 32 rows, 8 columns, 256 cells, and 31 of the takings cells carrying a number. The 31 cells sum to Rs 21,40,099/-, and over 31 that gives Rs 69,035.45/-. The figure is arithmetic and it is correct, and it is answering a question nobody asked. One of the 31 cells is an office code, and one stall month is in there twice.
Stop one, drop the blank row. 31 rows. An empty cell was never inside the sum or the count, so neither one changes. The figure stays at Rs 69,035.45/-. Cost of running it: one line of writing. Value of running it: it is now on paper that the blank was not the problem.
Stop two, drop the office code row. 30 rows. NB-02 in month 4 carried 99999. The market office writes that code into the box when no return came in at all, rather than any sum of money. Taking it out leaves Rs 20,40,100/- over 30, and that is Rs 68,003.33/-. The figure comes down by Rs 1,032.12/-. One five digit code among thirty genuine figures moved the answer by about a thousand rupees. A move of that size is easy to miss and impossible to explain later.
Stop three, drop the withdrawn return. 29 rows. Rs 20,03,700/- over 29 is Rs 69,093.10/-, and the figure has gone up by Rs 1,089.77/-. Worth pausing on: the record is now strictly more correct than it was at stop zero, and it reads higher than it did at stop zero. Half cleaned is not the same as half way to the answer.
Stop four, correct the typing error. 29 rows still. NB-08 in month 3 was typed as Rs 4,80,000/- when the return slip in the day book reads Rs 48,000/-, so one cell loses a digit. The sum falls to Rs 15,71,700/-, and over 29 that is Rs 54,196.55/-. Correcting that one cell moves the answer by Rs 14,896.55/-, more than the whole net move of the entire path. NB-07 in month 3 also reads Rs 4,80,000/- and is left exactly as it is. The NB-07 figure is a real month.
Stops five and six, the two spellings and the pitch unit. 29 rows, and the figure holds at Rs 54,196.55/- through both. One file, seven stops, a row count written down at every one, and a net move down of Rs 14,838.90/- that two of the six steps produced between them.
How to build an audit-friendly data pipeline: what does every stage write down?
The recipe at the centre of all this is five lines long. Every stage in the path writes down what it read, how many rows went in, how many came out, the rule it applied in one sentence, and the figure afterwards. Five lines, and the recipe is complete. There is no tooling in it and no vocabulary to learn.
The five lines look almost trivially small until it becomes clear what having them makes possible. The test that separates an auditable path from a merely repeatable one is this: a second person, given the file as handed over and nothing else, reaches Rs 54,196.55/- on their own, and can point at the exact stage where any figure of the first person's differs from theirs. Repeatable gets the first half. Only the written counts get the second half, and the second half is the half that ends arguments.
The everyday version again, and this one is easy to feel. A stall keeper writes the day's takings on the back of the same delivery slip the goods arrived on. The two numbers are physically attached to each other, and that keeper can settle any argument about that month. The keeper who copies a total onto a fresh sheet each evening has a tidier book and can settle nothing. The total and the slip that produced it now live in different places, and nobody can tell which one moved.
One of the five lines does the most work: the figure afterwards. Rows in and rows out show that something changed. The rule shows what was intended. But without the figure recorded at each stage, two people comparing their work can only tell each other that they disagree, and cannot tell each other where. The written figure at each stage is what turns a disagreement about a number into a disagreement about a rule, and a rule is something two people can actually settle.
One more thing this discipline buys, and it is the reason it survives contact with real work. When somebody asks six months later why the figure is Rs 54,196.55/- and not Rs 69,035.45/-, the answer is not a memory and not a reconstruction. The answer is a line in a written record that says a row carrying the office code was dropped, and another that says a five figure cell was corrected against the day book. Nobody has to be trusted. The record does the trusting.
A stage in an auditable path writes down five things. Which of these is the complete set?
What should be asked of any table handed over as ready?
Most of the time the path will be somebody else's work. A table arrives, described as ready, and the sender waits. Six questions cover almost everything that can be wrong with it, and all six can be asked in under a minute without opening the file.
Take them in order. How many rows arrived and how many are here now? The row count is the one question that most often has no answer at all, and it is the one that makes the other five answerable. What was dropped, and under which rule? Rows leaving is normal. Rows leaving without a stated rule is not. Was anything changed in place rather than dropped, and where is the original? A corrected cell is invisible afterwards. NB-08 reading Rs 4,80,000/- and NB-07 reading Rs 4,80,000/- are dangerous sitting next to each other for exactly that reason: one of them got fixed and the other did not, and the finished table shows no sign of either.
What does an empty cell mean in this table? If nobody can answer, then somewhere upstream a person decided, and the decision is now inside the figure without a name. What is the denominator behind every figure quoted from it? The same numerator over a different count produces a different answer and both are defensible. Which count belongs underneath a figure is set out under the choice of denominator. And can it be rebuilt from the source without asking anyone a question? If the honest answer is no, the table has a person inside it, and people leave.
One word covers all six questions. ProvenanceThe written record of where a figure came from and what was done to it on the way, kept alongside the figure itself rather than in somebody's memory. is not paperwork and it is not a formality. A table handed over without a row count is not a small gap in the record of where it came from. A table without a row count is a table nobody can check, and every figure taken from it is a figure taken on trust. Lenders reading a borrower's monthly sales file, analysts reading a schedule inside a set of accounts, and anybody comparing two months of anything all live and die by this, and the discipline is the same at every size. Ask what came in, ask what left, ask under which rule.
A colleague hands over a table and says it is ready. There is time for three questions before it is used. Which three buy the most?
What does it cost when nobody wrote the row count down?
The failure: two figures from one file, and no way to tell which stop they parted at
An analyst runs the cleaning on the Neelbagh stall record, gets Rs 54,196.55/-, writes it into a note and sends it. Three weeks later somebody else opens what they believe is the same file, runs what they believe is the same work, and gets Rs 69,035.45/-. Both of them are competent. Neither of them wrote down a row count at any stage. Neither can say who is wrong.
The cost here is not the gap of Rs 14,838.90/-, large though that gap is. The cost is that the gap cannot be attributed to anything. With the stops recorded, the two runs are laid side by side, they agree down to some stop and disagree from the next one, and the conversation is over in a minute: one of them never dropped the office code row, or one of them never corrected the typing error against the day book. Without the stops, the only honest option is to do the entire thing again and hope the second attempt agrees with itself.
Notice how ordinary the failure is. Nobody was careless, no rule was broken, and no arithmetic was wrong anywhere. The two figures are both correct answers to two slightly different questions, and the difference between the questions was never written down. An unwritten difference between two questions is the whole of the failure.
The fix costs one line per stage: rows in, rows out, the rule, and the figure afterwards. Four short items, written once, at the moment the stage runs rather than reconstructed afterwards. Reconstructed row counts are worth nothing. The person reconstructing them already knows the answer they are trying to reach.
Where this guide stops. The shape of a request and the shape of the answer that comes back are covered separately, as is where a stored table sits and what a declared key turns away at the door. Holding a table in memory and grouping it, putting two tables side by side, asking a stored table a precise question, and the ordered cells that let somebody else rebuild an answer from scratch, each have their own treatment elsewhere. Judging whether a particular figure in the record is an error, and what to do once that judgment is made, is covered separately.
Which record was used here, and how can the arithmetic be redone?
The arithmetic above rests on one invented file and one stated counting rule, both printed in full above, and it can be redone with a pencil: take the file, apply the rule, and the seven stop ladder falls out. If it does not fall out, the stop where the two runs part is nameable.
| Used here | What it is | Where it can be checked |
|---|---|---|
| The Neelbagh stall record | An invented office file, 32 rows across 8 columns, one row per stall per month over four months | Printed whole in the block on comma separated files |
| The counting rule | Which cells count towards the average, and which are kept out of both the top and the bottom of it | Set out in full in the opening panel, and named beside every figure above |
| The seven stop ladder | Rows in, rows out and the figure after, recorded at each stop | The ladder table, and the panel that walks it |
The Neelbagh covered market, its market office, its stall record, its day book and all eight stall names are invented.
Educational material. Not advice on any investment, tax, budget or market position.