A monthly figure moved after duplicate records appeared upstream, yet the widening that builds the report raised nothing — how do you find and fix that?
answer
- the cosmetic step computed
- rows against distinct pairs, per period
- reconcile one cell by hand
- averaging hides exact duplicates
- state the reduction, decide the grain
basics
~20 sSuspect the layout step. Count the input's rows against its distinct key-and-header pairs for the affected period: a gap means the widening resolved occupied cells with a reduction nobody wrote. Fix it by stating the reduction and deciding the grain deliberately.
solid answer
~50 sA widening needs one value per cell, and where a tool supplies a default reduction it applies that arithmetic silently. So a step that reads as cosmetic is the step that changed the number. Confirm it by counting the rows of the exact table being widened against its distinct key-and-header pairs, for a trusted period and the bad one; the shortfall is the count of absorbed values. **Which way the figure moved is a clue**: an averaging reduction leaves exact duplicates invisible, so an unchanged cell is not proof of health, while a summing one grows with the duplication. The fix is not a better default. It is to state the reduction in the call so the arithmetic is reviewable, and to decide what a second value means — correction, finer grain, or bad data — outside the layout step.
go deeper
Remember that a widening can only put one value in a cell, so duplicated input forces arithmetic. A layout change is capable of changing a number.
Explain the confirmation: count rows against distinct key-and-header pairs on a good period and a bad one, then reconcile a single affected cell by hand to identify the arithmetic in force.
Show the judgment that an unchanged figure is not evidence of health under an averaging reduction, and separate the code fix from the data decision about what a second value means.
The standing question is where a grain assertion belongs. A pipeline that lets a layout step answer data questions implicitly will keep doing it, so the check and the stated reduction are the durable outcome, not the one repaired report.
## Why the layout step is the suspect A **widening** turns one column's distinct values into new headers, filled from a second column, and each cell is one slot addressed by the row key plus a header. The moment the input holds two rows for one address, a rearrangement is impossible and something resolves the cell. Where the surface carries a default **reduction** — the function applied when more than one value lands in the same cell — that resolution happens with no diagnostic at all. This is why the step attracts no suspicion during the investigation. Reviewers scan a pipeline for the steps that compute, and a layout change is not one of them by appearance. The code did not change; the input's grain did. ## Confirming it Work on the exact table that is handed to the widening — after every filter and match that shapes it, not the raw source: 1. **Count rows and distinct key-and-header pairs** for a period whose figure is trusted and for the period that moved. Trusted period: the two counts match. Bad period: distinct pairs fall short, and the gap is the number of values absorbed. 2. **Pull the offending addresses** — the row-key-and-header combinations appearing more than once — and look at the values. Are they identical, near-identical, or genuinely different measurements? 3. **Reconcile one cell by hand.** Take one affected address, take the input values for it, and work out which arithmetic produces the number in the report. That identifies the reduction actually in force, which is more reliable than assuming one. ## Reading the direction of the move The direction of the change is evidence about the reduction, and it is also why 'the number looks fine' is not a defence: | Reduction in force | Exact duplicates | Differing values | |---|---|---| | An average | The cell is unchanged — the duplication leaves no trace at all | The cell shifts toward whichever value repeats | | A sum | The cell grows in proportion to the duplication | The cell grows | | Keep one of them | Unchanged | The cell depends on input order, so it can move between runs with identical data | The first row is the uncomfortable one. Under an averaging reduction, an exactly-duplicated input produces an identical report, so a whole class of upstream breakage passes every eyeball check and every 'the totals look right' review, until the day the duplicates are not exact. ## What the fix is not - **Not a better default.** Any default is arithmetic chosen by somebody who never saw your data, and the next person to read the pipeline still cannot see it. - **Not a post-hoc total comparison.** By the time you compare totals the reduction has already run, and under some reductions the totals agree anyway. - **Not deduplicating inside the layout step.** Collapsing the extra rows at the point of the widening answers a data question with a code shrug, and it keeps the upstream change invisible while it continues. ## The fix 1. **Count the pairs against the rows immediately before the widening, every run,** and stop on a gap. This is the only part that is genuinely preventive, and it works whichever disposition the tool has — refuse, reduce or keep both. 2. **State the reduction where you want one.** A written reduction is a reviewable sentence about the data — these two readings are averaged, or the later one wins — and it shows up in a diff. An inherited one appears nowhere. 3. **Decide the grain.** A second value for one address means one of three things, and each has a different owner: a correction that should supersede, a genuinely finer grain that means the report's grain was wrong, or accidental duplication that is an upstream defect. Settle that question above the layout change, not inside it. 4. **Re-derive the affected periods** once the rule is explicit, and say in the report which rule produced the figures. ## What this looks like in an interview answer The strong answer names the mechanism before the tooling: a widening requires one value per cell, so duplication in the input forces arithmetic, and whether you hear about it depends on the tool's disposition rather than on your care. It then gives a check that does not depend on that disposition, and it separates the code fix — state the reduction — from the data decision — what a second value means. A weaker answer goes hunting through the aggregation steps downstream, because those are the steps that look like they compute, and never examines the one that actually did.
- The figures did not move at all, but you know the input gained duplicates. Can you conclude the report is fine?No. Where the reduction in force averages, an exactly-duplicated input gives an identical cell, so the report is unchanged while the grain assertion it rests on is already false. The next batch of duplicates that are not exact will move the number, with no upstream change to point at.
- Why reconcile a single cell by hand rather than reasoning about which reduction is in force?Because the reduction is a property of the surface you called and its defaults, and beliefs about it are exactly what is unreliable here. One address, its input values and the reported number settle it in minutes and produce evidence you can show, rather than an argument about what the tool probably does.
- The extra values turn out to be legitimate repeated measurements. What changes?Then the report's grain was wrong rather than the data. The aggregation stops being an accident and becomes a decision to make explicitly — one number per subject per measure, produced by a stated rule — and the layout change goes back to being a rearrangement of an input that already has one value per cell.
saying these in an interview costs you the question
- Searches the downstream aggregation steps and never suspects the layout change
- Concludes the widening is innocent because it raised no error
- Treats an unchanged figure as proof that duplication did no damage
- Deduplicates inside the layout step and calls the incident closed
- Changes the default reduction instead of writing the reduction down
- Compares totals after the fact instead of counting pairs before