A 2-million-row column of near-unique order identifiers is held as codes over a value set — why does that cost more, not less?
answer
- two bills, one scales with rows
- distinct count against row count
- near-unique values lose outright
- the value set is still every value
- decoding gives the saving straight back
basics
~10 sNear-unique values defeat the arrangement: every distinct value is still stored once, and now every row stores a code too. As the distinct count approaches the row count, the codes are pure addition.
solid answer
~50 sHolding repeated values as codes — each distinct value kept once, with a short whole number per row pointing at it — produces two bills instead of one. The **value set** is paid once per *distinct* value; the **code buffer** is paid once per *row*. The bet only pays when the first bill is small, so the deciding property is the distinct count against the row count, not the row count and not the column's type. With 1.9 million distinct identifiers over 2 million rows, you keep essentially every identifier *and* add a code for every row, so the arrangement strictly adds. There is a second way it stops paying even on a good column: designs differ on whether operations stay in coded form, and where a design materialises full values at the first step, the saving lasts only while the column sits untouched.
go deeper
Recall that the saving comes from repetition. If almost every row holds a different value there is nothing to repeat, so the arrangement adds a code per row and saves nothing.
Explain the two costs and which one scales with rows, then state the break-even as a ratio of distinct values to rows rather than as a fixed number, because it depends on how large the values are.
Demonstrate that you measure after the operations, not just after the build: where a design decodes at an operation boundary, the run holds the full values anyway and the reported saving was never real.
The tradeoff to own is whether the team declares value sets up front. Declaring buys refusal at the boundary and a stable saving; it also means a change request every time the business adds a value.
## Two costs, and only one of them grows with rows A plain column pays for a value on every row. A **dictionary-coded column** — repeated values held as codes, each distinct value kept once with a short whole number per row pointing at it — splits that into two bills: - **The value set**, paid once per *distinct* value. It does not grow when you add rows that repeat a value already in it. - **The code buffer**, paid once per *row*. It grows with the data exactly as the plain column does, just in a narrower unit. The arrangement wins when the first bill is small and the second is genuinely cheaper per row than storing the value was. Both conditions have to hold, and the order-identifier column fails the first one outright. ## Where the break-even sits The deciding property is the distinct count against the row count: | Column | Distinct against rows | Verdict | |---|---|---| | Country of an order, ten million rows | a couple of hundred against ten million | a clear win: each name paid once, each row pays a code | | Status of a ticket, one million rows | a handful against a million | a clear win, and equality and splitting can run on codes | | Free-text note, one million rows | close to one-to-one | a loss: every note is still stored, plus a code per row | | Order identifier, two million rows | 1.9 million against two million | a loss, and the identifiers keep arriving, so it worsens | There is no universal threshold, and you should not offer one as if there were. The exact point at which it flips depends on how much larger a value is than a code, which is a property of the values: a column of long text names pays back at a far higher distinct ratio than a column of short ones. What is safe to state is the shape of the rule — orders of magnitude fewer distinct values than rows is a win, roughly as many distinct values as rows is a loss, and the middle has to be measured rather than guessed. ## The second way it stops paying: decoding Size is not the only thing that can disappoint. A coded column is only cheap while it stays coded, and this is exactly where designs in this family diverge: - Some carry the coded form through filtering, equality and splitting rows by the column, so the saving survives a whole pipeline. - Others materialise full values at the first operation that cannot be expressed over codes, and from that point the run is holding the plain column you were trying to avoid, plus the value set. So the honest answer to *how much did it save* is two numbers: what it saved at rest, and what survived the operations you actually run. A candidate who has only measured the first has measured the easy one. ## Three shapes that cost more than the plain column 1. **Near-unique values.** Identifiers, free text, timestamps at full precision. The value set is almost the whole column and the codes are added on top. 2. **Values already as narrow as a code.** A truth column, or a small fixed-width whole number, has nothing to give up. The arrangement adds a value set and a lookup and returns nothing for them. 3. **A heavily filtered column that kept its whole value set.** Filtering removes rows and therefore codes, but many designs retain every member of the value set, including members no remaining row uses. The code buffer shrinks with the data; the value set does not, unless something prunes it. ## What the arrangement still buys when the size case is marginal Size is not the only reason to reach for it, so do not throw it away on the size figure alone: - **Speed on equality and splitting**, where the design keeps the codes: narrow fixed-width whole numbers compare faster than values, and dense small codes make ready-made slots for a split. - **An explicit set of permitted values.** Where a design lets you declare the value set up front, it becomes a stated property of the column and a value outside it can be refused or recorded as absent at the point it arrives, rather than discovered three steps later. ## Answering it well State the two bills and which one scales with rows. Give the deciding ratio instead of a number. Then volunteer the two failure modes an interviewer is fishing for: values that are nearly all distinct, and a pipeline that decodes. Finish with how you would settle it — measure the column both ways on a real sample, then measure again after the step you actually intend to run, because the second measurement is the one that decides.
- Is there a distinct-to-row ratio you would quote as the cut-off?Not as a rule. The flip point depends on how much larger a value is than a code, so a column of long text names pays back at a much higher ratio than a column of short ones. Quote the shape instead — orders of magnitude fewer distinct values is a win, one-to-one is a loss — and measure the middle.
- A column started with twelve distinct values and now has sixty thousand. What should have caught that?A check on the distinct count over time, not a one-off measurement at build. Where the design lets you declare the value set, an arriving value outside it is refused or marked absent at the boundary, which turns silent growth into a visible event rather than a slow loss of the saving.
- Does filtering a coded column down to a few rows shrink it proportionally?The code buffer shrinks with the rows; the value set generally does not. Many designs keep every member that was there when the column was built, so a heavily filtered column can carry a value set far larger than the data remaining behind it until something explicitly prunes it.
saying these in an interview costs you the question
- Says holding repeated values as codes always shrinks a column
- Judges the payoff from the row count rather than the distinct count
- Forgets the value set still stores every distinct value once
- Assumes a size measured at build survives the whole pipeline
- Applies it to an identifier column because the values are text
- Quotes a fixed distinct-count threshold as if it were universal