A report's total is wrong, the column prints normally, and it is held as text. How do you find the step that changed it?
answer
- the values will not tell you
- one reading per step, not per row
- it changes at exactly one point
- bisect on the reading, not the numbers
basics
~20 sRecord the column's declared representation at each step and find the first point where it is no longer numeric. The values cannot answer this: a column of digits held as text prints and aggregates much like a numeric one.
solid answer
~50 sMeasure the representation, not the numbers. How a column is held is recorded per column in most designs, so reading it is cheap, and unlike the values it changes at exactly one point in the file. Walk the steps in order — or bisect them if the file is long — taking that reading before and after each one; the first step whose output is no longer numeric is the one that did it. Do not use the results as evidence: every intermediate answer looked sensible, because ordering, grouping and extremes on text all return real values out of the data. Look first at the steps that *rebuilt* the column rather than merely read it — a fill with a written-out placeholder, a value joined to a label, a conversion aimed at the wrong column.
go deeper
Recall that a wrong number does not always mean wrong data. How a column is held is a separate thing to look at, and it is recorded rather than visible in the rows.
Explain why the representation is the right measurement: it is cheap to read and changes exactly once, whereas the values are supposed to change at every step and look plausible throughout.
Demonstrate the walk — a reading per step, bisecting a long file, starting from the steps that rebuilt the column — and say why you distrust every sensible-looking intermediate result.
The judgment is whether the team pays for this diagnosis every time, or pays once for boundary claims so the run stops at the step that caused it.
## Why the numbers cannot answer this The instinct is to scroll the data and look for the bad value. It fails here for a specific reason: there usually is no bad value. A column of digits held as text renders the same as the numeric column it replaced, holds the same information, and returns real values from the data at nearly every step. The output of each step **looked right**, which is exactly why the defect got as far as the report. Worse, the values change at every step by design. A quantity that is doubled, filtered, grouped and summed is different at each point on purpose, so there is no stable property of the values you can watch for a discontinuity. You need something that is supposed to stay constant. ## The measurement that can The column's **declared representation** — the tool's own record of how the whole column is held — has exactly the property you need: - it is a single fact per column, not one per row; - it is bookkeeping the tool already has, so reading it costs nothing on a materialised table and is usually answerable from the plan on a deferred one, without running anything; - it is supposed to be constant across the steps you did not intend to change it, so it has exactly one transition to find; - it is unambiguous: numeric or not, with no judgment call about whether a value looks odd. That is a clean bisect target, which the values never are. ## The walk 1. Take the reading at the point the column first exists. If it is already not numeric there, the change is upstream of everything else in the file and the rest of the walk is unnecessary. 2. Take the reading at the point the wrong total is computed, to confirm it really is text there and you are not chasing a different defect. 3. Bisect between the two: take the reading in the middle, and keep the half that contains the transition. A file of thirty steps is four or five readings, not thirty. 4. At the step where the transition falls, look at what that step does to the column rather than at what it returns. If the file is short, walk it in order and skip the bisect; the reading is cheap enough that ten of them cost less than reasoning about which half to keep. ## What a sensible-looking intermediate result proves Nothing. This is worth stating flatly, because it is the trap that makes people stop the walk early: | intermediate observation | what people conclude | what it actually shows | |---|---|---| | the largest value looks plausible | the column is numeric here | the largest **spelling** in character order is a real value from the data | | the group counts add up to the row count | the grouping was correct | grouping by spelling also partitions every row exactly once | | the filter returned about the expected number of rows | the comparison was numeric | a character comparison also returns a subset, of no particular size | | the table wrote out cleanly | the types were fine | writing is not a type check and refuses almost nothing | A step's output being believable is the defect, not evidence against it. ## Where to look first Before bisecting, spend thirty seconds on the short list of steps that **build** a column rather than read one, because those are the only ones that can change its representation: - a value filled in as written-out characters where a number was expected; - a column assembled by joining a number to a label, a unit or a code; - a conversion applied to the wrong column, or applied and then discarded because its result was not assigned anywhere; - a step that reassembled the table from parts where one part's column was text and the other's was not; - a step whose output overwrote the column under the same name, so nothing about the file's shape hints that anything happened. Most occurrences are on that list, and finding it there costs less than the walk. ## After you find it Repairing the column at the end of the file — converting it back just before the total — makes today's number right and leaves the cause in place. Three things are worth doing instead: - fix the step, so the column leaves it numeric; - check whether the same step rebuilt **other** columns the same way, because a step that produces text once often produces it several times; - leave a claim beside that step saying the column is held numerically on the way out, so the next occurrence stops the run there instead of arriving as a wrong total in a report. The point of the walk is not this one number. It is that you now know which step to distrust, and the file can be made to say so.
- Why is a value-by-value scan usually the wrong first move here?It answers a different question, expensively. A scan tells you whether every value could be read as a number, and in this defect every value can — the column is digits, just held as characters. It costs a full pass and comes back clean while the column is still text.
- You find the step. Why is converting the column back not the whole fix?Because the step runs again tomorrow, and because the conversion only repairs the column you thought to repair: a step that produced text once often did it to more than one column. The conversion also forces a choice — raise on a value that will not convert, or turn it absent — which is a decision, not a cleanup.
- Does the same walk work on a pipeline that has not executed anything yet?Usually yes, and more cheaply. Designs that build a plan before running it know each step's output representation from the plan, so the readings can be taken without the data moving at all. A value-level check has no such shortcut and forces the execution you were trying to avoid.
saying these in an interview costs you the question
- Hunts for bad rows instead of the step that changed the column
- Takes a sensible-looking intermediate result as proof the type was fine
- Converts the column at the end and never finds the cause
- Assumes the step named in the error is the step that changed it
- Scans every value when a single recorded property answers it