skip to content

A tolerant comparison of a rewritten transform's 12-million-row output against the old one returns "not equal". What do you produce next?

level: seniorimportance: must knowfreq 58%

answer

  1. a verdict is not evidence
  2. three buckets, not one count
  3. signed gaps, not the maximum
  4. does the difference concentrate somewhere
  5. accepted differences stated with a reason

basics

~20 s

A verdict is not evidence. Produce the differing rows themselves: keys only in the old output, keys only in the new one, and matched keys whose values disagree, each with a magnitude and a direction, plus a sample a human can read.

solid answer

~50 s

Turn the boolean into a report. Align both outputs on the key and keep the record of which side each row came from, so the three buckets fall out: keys present only in the old output, keys present only in the new one, and keys in both whose values disagree beyond the stated tolerance. For the third bucket compute the signed gap per column, and look at its distribution rather than its maximum — a few large gaps and a systematic small bias are different defects. Then describe the differing rows: do they concentrate on one day, one source, one category, or are they scattered? Finish with a readable sample showing the key and both values side by side. "12,000 rows differ" is not an answer; "all 12,000 are the final day of the month, and the new value is always lower" is.

code

pseudocode · 25 lines
pseudocode
baseline  = load_output_of_previous_version()   // same input
candidate = run_new_version(same_input)

terms = { absolute_floor: 0.005,
          relative_fraction: 0.000001,
          two_absent_cells_count_as_equal: true }

paired = align_on(baseline, candidate,
                  key = [account_id, month])     // keeps one-sided keys

only_in_baseline  = paired where candidate side is missing
only_in_candidate = paired where baseline side is missing
in_both           = paired where neither side is missing

differing = in_both where not agrees(baseline.amount,
                                     candidate.amount, terms)

signed_gap = differing.candidate.amount - differing.baseline.amount

report(count(only_in_baseline),
       count(only_in_candidate),
       count(differing),
       distribution_of(signed_gap),
       differing_counted_by(differing.month),
       first_20_rows(differing))

go deeper

for a junior

Recall that the useful output of a comparison is which rows differ and by how much, not whether the two results were equal.

for a middle

Explain the three buckets that fall out of aligning on a key — rows only on the old side, only on the new, and matched rows that disagree — and why merging them hides the cause.

for a senior

Show the diagnosis: signed distributions rather than a maximum, whether the difference concentrates on a day or a source, a readable sample, and awareness that each count over a deferred pipeline forces another execution.

for a principal

Decide what evidence a rewrite must carry before it replaces the production version, who may accept a difference, and whether the baseline output is kept as a standing artefact — and be honest that the answer costs storage and a pass.

## A verdict is not evidence A comparison that says "not equal" has told you the rewrite is not finished and nothing else. Nobody can act on it, nobody can review it, and the natural next move — relaxing the comparison until it passes — destroys the only instrument in play. The output that a reviewer can actually act on is a description of the difference: **which rows, on which columns, by how much, in which direction, and whether they have anything in common.** ## Build the three buckets Align the two outputs on the key that identifies a row, keeping rows found on one side only, and preserve the record of which side each aligned row came from — either by keeping the marker the alignment produces, or by reconstructing it afterwards from which side's columns are present. That yields three populations that must never be reported as one number: 1. **Keys only in the old output.** The rewrite lost rows. The cause is upstream of the values: a condition that now excludes them, a lookup that no longer matches, records refused on the way in. 2. **Keys only in the new output.** The rewrite gained rows — duplication of a key, a filter dropped, a previously discarded record now surviving. 3. **Keys in both, values disagreeing beyond the stated tolerance.** The logic changed for rows that both versions produced. The first two often explain the third: once rows appear or vanish, any total computed over the output moves as a consequence rather than as a separate defect. ## Describe the differing rows For bucket 3, per column: - **The signed gap**, not its absolute value. Direction is the single most informative field in the whole report: a bias entirely in one direction points at a changed rounding, a dropped term or a conversion; a mix of both signs at last-digit scale points at regrouped arithmetic. - **A distribution, not a maximum.** How many rows are inside ten times the tolerance, how many are order-of-magnitude wrong, how many changed sign. One row that is wildly wrong and ten thousand that are marginal are two separate stories, and a single maximum tells neither. - **Concentration.** Count the differing rows by a column with meaning — the day, the source, the region, the category. A difference confined to one day or one source is a narrow defect with an obvious next step; a difference scattered evenly across everything is a change in the arithmetic itself. - **A readable sample.** Twenty rows with the key, the old value and the new value beside each other. This is what a reviewer reads first and what makes the finding arguable rather than asserted. ## What the investigation costs - **Another pass.** Everything above is a pass over aligned data. On materialised tables in memory the counts are metadata and effectively free; on a pipeline that has built a plan and computed nothing until asked — deferred evaluation — **each count forces the plan to execute, and the next count forces it again**. Collect the buckets, the counts and the sample in one pass rather than asking ten separate questions. - **Memory, but not predictably.** Whether holding both outputs at once roughly doubles the peak depends on the design: on an eager design where each materialised result owns its own buffers, both are fully resident; on a design that is immutable by default and shares untouched buffers between derived objects, the second output pays only for the columns that changed; on a deferred design neither exists until something asks. **Measure rather than assume**, and if the diff is genuinely too large, compare key-aligned subsets of columns rather than whole tables. - **Storage for the baseline.** Comparing against the version being replaced means the old output, computed over the same input, has to exist somewhere when the new one is ready. That is a standing cost of doing rewrites this way, and it is worth deciding deliberately rather than discovering on the day. ## What the report has to say 1. The terms the comparison ran under: the columns the outputs were aligned on, the tolerance and its two components, and the rule for cells where one or both sides hold no value. 2. The size of each of the three buckets, per column where it varies. 3. The signed distribution of the disagreements and whatever they concentrate on. 4. The sample. 5. **Every accepted difference stated as an accepted difference, with its reason** — "the new version rounds at the end rather than per row, which moves 12,000 month-end rows by up to half a penny" is a finding a reviewer can agree with. A tolerance quietly widened until the diff passed is not, and it leaves nothing behind for the next person to read.

  • Why report the signed gap rather than how far apart the values are?
    Because direction separates two different defects. Differences scattered in both directions at the scale of the last digits are consistent with the arithmetic being regrouped by the rewrite. Differences all in one direction, however small, point at a changed rounding step, a dropped term or a conversion applied once too often — and they accumulate when the column is summed, so they matter far more than their per-row size suggests.
  • Does holding both outputs in memory to diff them double the peak footprint?
    It depends on the design, so measure. On an eager design where each result owns its own buffers, both are fully resident and the peak roughly doubles. On a design that is immutable by default and shares untouched buffers between derived objects, the second output only pays for the columns that changed. On a deferred design neither exists until the comparison asks for it, and the peak is set by what the comparison itself materialises.
  • The counts you want are cheap on the table in memory but expensive on the pipeline. Why?
    A materialised table already knows how many rows it holds, so a count is metadata. A pipeline that has only built a plan computes nothing until a result is requested, so every count executes the whole plan — and the next count executes it again. Gather the buckets, the counts and the sample in a single pass instead of asking one question at a time.
  • What makes an accepted difference acceptable?
    That it is stated, explained and agreed before the new version replaces the old — for example, rounding moved to the end of the calculation, shifting month-end rows by under half a penny. The test is whether a reviewer can read the sentence and disagree with it. A tolerance widened until the diff stopped complaining fails that test and leaves no record of what was actually proved.

saying these in an interview costs you the question

  • Reports "12,000 rows differ" and calls that the investigation
  • Relaxes the comparison until it passes instead of describing the difference
  • Reports only the largest difference and never the direction
  • Merges lost rows, gained rows and changed values into one count
  • Asks for a separate count at each step of a deferred pipeline
  • Assumes holding both outputs at once always doubles peak memory