Where do the two grouping-key values end up after a collapse, and can a later step select them by column name?
answer
- layout, not arithmetic
- row-label slot or ordinary column
- one label made of two parts
- only a column is addressable by name
basics
~20 sEither into the result's row-label slot as one label made of two parts, or into two ordinary columns; which happens is a property of the tool. Only the column form is visible to a step that selects by name.
solid answer
~50 sThe group identity is a pair either way, but the layout it arrives in is not universal. Designs that carry a row-label slot compose the two key values into one row label made of two parts, one part per grouping key, with the aggregated columns beside it. Designs with no row-label concept return the two keys as two more ordinary output columns, and there is nothing to promote and nothing to move back. The difference matters for one reason: a later step that addresses columns by name can see a key that is a column and cannot see a key that is a row label. So the first thing to establish about an unfamiliar tool is which model it uses, and whether the promotion is a default you can switch off rather than a step you undo afterwards.
go deeper
Know that after collapsing on two keys the key values are still somewhere in the result, and that where they are decides whether a later step can refer to them by name.
Explain both models: a row-label slot holding one label made of two parts, against two plain output columns. Then say which one your tool uses and how its default is set.
Recognise the failure this causes in a pipeline. A step that selects by name quietly sees nothing where a key should be, and the symptom surfaces one or two steps downstream of the grouped operation.
Make the landing explicit at the boundary between stages rather than leaving it to each author's tool default. A shared convention costs a line per stage and removes an entire class of hand-off bug.
## Two models, one identity After a collapse to one row per group on two grouping keys, every result row is identified by a pair of key values. Where that pair physically sits in the result is not a universal fact about grouping. It is a property of the tool's data model, and the family this subject spans contains both answers: - **Designs that carry a row-label slot.** Such a result has, beside its columns, a structure that labels each row. The grouped operation promotes the key values into it, composing them into **one label made of two parts**, one part per grouping key, with the aggregated columns alongside. The key values are in the result and plainly visible on screen, but they are not columns. - **Designs with no row-label concept at all.** Such a result is only columns. The grouping keys come back as two more output columns beside the aggregates. There is nothing to promote, and nothing to move back afterwards. Both are complete, coherent designs, and prose that describes either one as "what happens after grouping" is wrong for half of this family's readers. The invariant worth memorising is narrower: group identity is a pair, and whether that pair lands as one multi-part label or as two plain columns is a property of the tool. ## Why the difference has teeth Exactly one consequence makes this worth an interview question, and it is not cosmetic. A later step that addresses columns **by name** can see a key that is a column and cannot see a key that is a row label. Everything else follows: - A selection of columns by name silently omits the keys, so the identity does not travel into whatever that selection feeds. - A step that matches this result against another table on the key finds no such column to match on. - A writer that serialises columns writes the aggregates and not the identity, producing an output whose rows cannot be told apart afterwards. - A check that asserts the result's identity is unique has nothing to assert on. The failure is quiet in every case. Nothing is raised; the result simply carries one fewer thing than its author believed, and the symptom appears a step or two downstream of the grouped operation. That distance is why it takes so long to find. ## Reading the symptom backwards | what you observe | the likely cause | how to confirm | |---|---|---| | a key cannot be found by name right after a collapse | it was promoted into the row-label slot | look at the result's labels rather than only its column names | | aggregates are right but the rows are anonymous downstream | a later step kept only columns, dropping the labels with the identity in them | inspect the result at the step before the loss | | the same code works for one colleague and not another | different tools, or the same tool with the promotion set differently | print which columns the result reports having, on both machines | The third row is the one people miss. Several tools that do promote the keys also let you ask for the column form instead, as a setting on the grouped operation itself, so two authors using the same tool can genuinely be looking at different shapes. ## Normalising at the boundary The practical discipline is to stop discovering the landing afresh in every pipeline: 1. **Decide the convention once.** For a pipeline of several stages, settle on "grouping keys are always ordinary columns" and write it down. It is the form both models can produce, and the only form a later step can name. 2. **Ask for it where the tool offers the choice.** Where the grouped operation takes a setting for whether keys become labels, set it explicitly at every call site rather than inheriting a default that a version or a colleague may change. 3. **Convert immediately where it does not.** If the promotion is unconditional, put the keys back into columns in the same expression as the collapse, so no intermediate result in the pipeline ever carries the other shape. 4. **Assert it.** A one-line check that the expected key columns are present converts a quiet failure into a loud one at the place it happened, rather than two steps later. ## What does not change Whichever shape you end up with, the reduction has already run before any of this is decided. Moving the keys out of the label slot and into columns rearranges where values sit and nothing else: it recomputes nothing, changes no aggregated number, and changes no row count. Nor does it change the identity, which is a pair expressed two ways. That is worth stating plainly, because candidates who have been bitten by this sometimes over-learn it and start blaming a layout difference for a numbers difference, which a layout difference cannot cause.
- Does moving a multi-part row label back into ordinary columns change any aggregated value?No. It moves the key values from the label slot into columns and does nothing else; the reduction has already run and its outputs are untouched, as is the row count. What changes is what a later step can address by name, which is the whole reason anyone performs the move. If two results differ in their numbers, the layout is not the cause.
- You are writing a helper that must behave the same whatever a tool does with the keys. What do you do?Normalise at the boundary. Make the grouped operation hand the keys back as ordinary columns, either by asking for that form where the tool offers the choice or by converting immediately where it does not. Every downstream step then addresses the keys the same way and the helper never branches on layout. Add an assertion that the key columns are present so a change to a default fails loudly.
saying these in an interview costs you the question
- Says every tool puts the grouping keys into the result's row labels
- Says the keys are always ordinary columns and no label slot exists anywhere
- Believes moving keys into columns changes the aggregated values
- Cannot explain why a later step failed to find a key by name
- Confuses one label made of two parts with two independent labels
- Assumes the keys were dropped rather than relocated