A monthly report groups spend by region and by a size band derived from the amount column; what must be true of that derivation for two months' reports to be comparable?
answer
- the key need not be a stored column
- identity is the derived value
- same input, same band, every run
- edges read from the data move with it
basics
~20 sThe derivation must be deterministic per record, against band edges fixed outside the run. Edges computed from each month's own data make the bands mean different things each time, so one band label no longer denotes the same records.
solid answer
~50 sA grouping key does not have to be a stored column: it can be any expression evaluated per record, and once it is, the group identity is that derived value. The source it was computed from is not in the result unless you put it there. That is fine as long as the derivation is deterministic and depends only on the record, so the same amount always lands in the same band. The trap is edges chosen from the data itself: bands recomputed from each run's own distribution mean a record of identical value can fall into a differently-named band next month, and two reports that look comparable are not. Pin the edges as constants, carry them next to the output, and treat a change to them as a change to the report's grain rather than a cosmetic tweak.
go deeper
Know that a grouping key can be an expression computed from a column rather than only a stored column, and that the result is then keyed on the computed value.
Explain what a derived key does to the result: the identity is the computed value, several source values collapse into one group, and the source is gone unless you keep an aggregate of it.
Show the failure you have lived through. Bands whose edges were recomputed each run made this month's report look comparable with last month's while the labels covered different records entirely.
Treat a band definition as a published contract with a version, owned somewhere the reports can cite. Changing it silently invalidates every trend built on top of it, and nothing in the output will say so.
## A grouping key does not have to be a column The grouping key is whatever expression decides which records belong together, and a stored column is only the simplest case. A key can equally be the first few characters of a code, a value normalised to upper case, a flag computed from two columns, or a continuous amount cut into named bands. The grouped operation does not distinguish: the expression is evaluated once per record, and its value is that record's part of the identity. When one of two grouping keys is derived, the result's identity is a pair whose second part is the **derived value**. That is the fact everything else follows from. ## What a derived key does to the result - **The identity is the derived value, not the source.** Collapsing to one row per group keeps one row per identity, and several amounts map to the same band, so there is no single source amount for the row to carry. It is folded away like any other non-key column. - **You can still keep a representative, but you must ask.** A minimum, a maximum or a count within the band are ordinary aggregates and survive; the raw values do not. - **The result cannot be re-derived from itself.** Once amounts have become bands, nothing in the output tells a later reader where the boundaries were. If the definition is not carried alongside, the report is unreadable in six months. - **The row count is bounded by the number of bands, not by the number of amounts.** That is usually the point: turning a continuous key into a handful of named groups is what makes the result readable at all. ## Fixed edges against edges read from the data Here is the failure that makes this a senior question. Compare two ways of defining the band edges over two consecutive months: | | edges fixed as constants | edges computed from each run's own distribution | |---|---|---| | what the band "medium" means in month 1 | a stated numeric span | whatever the middle of month 1's data was | | what it means in month 2 | the same stated span | whatever the middle of month 2's data was | | a record of identical amount in both months | same band both times | may change band with no change to the record | | comparing the two reports | valid | invalid, and it looks valid | | effect of a shift in the underlying data | visible as records moving between bands | invisible, because the bands moved with the data | The last row is the sharp one. Data-derived edges do not just make comparison unsafe; they **hide the very movement the report exists to show**. If the whole distribution shifts upward, fixed bands show records migrating from lower bands to higher ones, which is the signal. Bands recomputed from the data redistribute themselves around the new distribution and show almost nothing changed. The same applies to any derivation that is not purely a function of the record: one that reads the current date, one that consults an external table that is itself changing, one that depends on how the records happened to be ordered. Each produces a key that is stable within a run and unstable across runs, which is the worst combination because every single run looks internally consistent. ## A checklist for a derived key in a stored report 1. **Make it a pure function of the record.** Same inputs, same band, every time, on every machine. 2. **Pin the constants outside the run.** Band edges, thresholds and lookup values live in configuration or in code, not in a computation over the input. 3. **Carry the definition with the output.** Write the edges beside the result, or version them somewhere the result can cite, so a reader a year later can tell what "medium" meant. 4. **Treat a change to the edges as a change to the grain.** It is not a formatting tweak. Anything built on the old bands is comparing two different definitions under identical labels, and the labels will not warn anybody. 5. **Normalise before deriving, not after.** If the source is text, fix casing and whitespace in the derivation itself, so two spellings of one thing do not become two bands. ## When moving edges are legitimate None of this makes data-derived bands wrong in themselves. For a one-off exploration, where the question is precisely how the current data distributes and there is nothing to compare against, edges read from the data are the right tool and fixed edges would be arbitrary. The rule that separates the two cases is simple and worth saying in an interview: compute edges from the data only when the output is not going to be stored or compared, and pin them the moment it is. The difficulty in practice is that the exploratory notebook turns into the monthly report without anybody revisiting that decision.
- Why does a derived key make the source column disappear from the result?Because a collapse keeps one row per identity, and the identity is the derived value. Several source amounts map to the same band, so there is no single source amount the row could carry, and it is folded away like any other non-key column. If you need a representative, compute it explicitly as an aggregate such as a minimum, a maximum or a count within the band.
- Is there any legitimate use of band edges computed from the data?Yes, for a one-off exploration where the question is how the current data distributes and there is nothing to compare against. It stops being legitimate the moment the output is stored or set beside another run. The practical rule is to compute edges from the data only while the result is disposable, and to pin them as constants as soon as it is not.
- The band definition has to change. How do you make that safe?Treat it as a change of grain rather than a tweak. Version the definition, keep the old one producing output for a period so the two can be set side by side, and label every stored result with the version it used. Then anyone comparing across the boundary can see they are comparing two definitions rather than two months.
saying these in an interview costs you the question
- Says a grouping key must be an existing column of the table
- Recomputes band edges from each run's data and calls the reports comparable
- Assumes the source amounts survive in the result beside the derived band
- Uses a derivation that depends on when the job ran
- Thinks changing band edges is a formatting change rather than a grain change
- Cannot say where the band definition should be recorded