Two versions of a transform return the same 40,000 rows in a different order, yet a row-by-row comparison reports 39,000 differences. Why?
answer
- order is not part of the claim
- position-by-position measures sequence
- several designs leave group order unspecified
- name the columns that identify a row
- align first, then compare pairs
basics
~20 sNothing promises that two versions emit rows in the same sequence, so a position-by-position comparison is measuring order rather than correctness. Align both outputs on the columns that identify a row, then compare the matched pairs.
solid answer
~50 sA row-by-row comparison lines up the first row with the first row and the second with the second, so it answers "were these emitted in the same sequence?" — not "is this the same result?". Row order is not part of the claim and nothing guarantees it: several designs leave the order of groups in a grouped result unspecified or derive it from a hash of the key, any partitioned or parallel execution combines results in whatever order they finish, and a rewrite that moved a step changes the sequence as a side effect. The repair is to stop depending on order: name the columns that identify one output row, assert that key is unique on both sides, align the two outputs on it, and compare values inside each matched pair. Keys found on only one side are a separate finding from values that disagree.
go deeper
Recall that the order rows come back in is not part of the result and not promised by anything. Comparing two outputs means matching rows by what identifies them, not by where they sit.
Explain the mechanisms that move the sequence — unspecified group order, results combined as pieces finish, a rewrite that reordered steps — and walk through naming the key, proving it unique on both sides, and aligning.
Show that you split the verdict into keys only on the old side, keys only on the new side, and keys in both that disagree, and that you know the alignment and its counts cost a pass over deferred pipelines.
Frame the key as a commitment: deciding what one output row means, and requiring it to be stated and proved unique, is what makes every later comparison of that output cheap and arguable rather than a scroll through rows.
## The claim a diff has to support Rewriting a transform makes one claim: **for the same input, the new output is the same result as the old one**. "The same result" is a statement about a set of records identified by something — one row per account per month, one row per order line — not about a sequence of positions. A comparison that walks both outputs from the top and lines row 1 up with row 1, row 2 with row 2, answers a different question: *were these two outputs emitted in the same sequence?* Both questions return a verdict, and only one of them is the claim you meant to support. ## Nothing promises the sequence A positional comparison happens to work under one specific set of conditions: single-threaded execution over a fully ordered, materialised input, with a rewrite that did not change the operation order. Break any of them and the sequence moves without a single value changing. - **Grouped results.** Several designs leave the order of groups in a grouped result unspecified, or derive it from a hash of the key. The same groups can come back in a different sequence between two versions. - **Partitioned or parallel execution.** When pieces of the work finish in a different order, the order their results are combined in varies from run to run. - **A rewrite that reordered on purpose.** Moving a condition earlier, replacing a per-row pass with one expression evaluated over a whole column, or swapping two steps changes the emitted sequence as a side effect. - **A different reader, or a different source ordering,** for the same logical input. None of these is a defect, and each of them turns a positional comparison into a difference count in the tens of thousands. ## Align on a key instead 1. **Name the key** — the smallest set of columns that identifies one row of this output. It is a decision about what one output row means, and it belongs written down beside the diff. 2. **Assert the key is unique on both sides.** State the claim over the named columns: no two rows share those values. If the key repeats, the pairing is not well defined, and every difference reported afterwards may be an artefact of which copy got paired with which. 3. **Align the two outputs on that key**, keeping the keys that appear on one side only rather than quietly discarding them. 4. **Compare values inside each matched pair**, on terms you state. ## What each comparison actually reports | situation | positional comparison | key-aligned comparison | |---|---|---| | same rows, different order | a difference wherever a row moved | no differences | | one extra row in the new output | every row after it reported | one entry under "new only" | | one value genuinely changed | reported, buried among artefacts | reported on its own | | a re-labelled output | total disagreement | unaffected | | what it requires | nothing | a key, proved unique on both sides | The aligned form also splits the answer into three findings that a single verdict collapses: keys present only in the old output, keys present only in the new one, and keys in both whose values disagree. Those are three different defects with three different causes, and reporting them as one number hides all three. ## The limits of the repair - **It costs a pass.** On a materialised table in memory the alignment and its counts are cheap. On a pipeline built as a plan that computes nothing until something asks for a result — deferred evaluation — each count forces the plan to execute, so gather everything you need in one pass instead of sprinkling counts through the file. - **Some outputs have no key.** You can still compare the two as multisets — which distinct rows occur, and how many times — and learn *how many* rows differ, but not *which*. That is a much weaker claim, and the missing key is itself worth reporting. - **Alignment fixes order and nothing else.** Inside each matched pair the comparison still needs its own terms: how far apart two numbers may be before they count as different, and what to do with cells where both sides hold no value at all. Without those, even a key-aligned diff can answer "not equal" about two results that agree.
- Why assert that the key is unique on both sides before aligning the two outputs?Because a repeated key makes the pairing ambiguous: the alignment can produce more pairs than either side had rows, and differences then reflect which copy was matched with which rather than anything about the rewrite. State the uniqueness claim over the named columns first; until it holds, the diff's output is not evidence.
- What can you still do if the output genuinely has no set of columns that identifies a row?Compare the two outputs as multisets: which distinct rows occur and how many times each occurs. That survives reordering and tells you how many rows differ, but it cannot tell you which rows or by how much, and it degrades badly once values are only approximately equal. Record the absence of a key as a finding in its own right.
- Does putting both outputs into the same order first solve the problem?It removes the symptom on well-behaved data but keeps the dependency: ties order arbitrarily, cells with no value land differently under different rules, and a row present on one side only shifts everything after it again. Aligning on a key removes the dependency instead of re-establishing it.
Two people fill a trolley from the same shopping list. Checking them by pulling one item from each trolley in turn proves almost nothing; matching by what each item is proves everything. Row order is the trolley; the key is the item.
saying these in an interview costs you the question
- Assumes two outputs of the same input always come back in the same sequence
- Scrolls the first twenty rows of each output and calls them equal
- Reports the positional difference count as the number of wrong rows
- Treats the row's position, or a generated sequence number, as a key
- Reports rows present on only one side as if they were value differences