Two coded columns of country labels, built from different files, are compared row by row and identical labels come back unequal. Why?
answer
- the code is not the value
- each column numbers its own set
- first-appearance numbering is file-dependent
- safe within one set, unsafe across two
- share a declared value set at construction
basics
~20 sA stored code is only an offset into its own column's value set, so the same number stands for different values in two separately built columns. Comparing codes rather than decoded values compares two unrelated numbering schemes.
solid answer
~50 sIn a dictionary-coded column — each distinct value kept once, with a short whole number per row pointing at it — the code carries no meaning of its own. It is an offset into *that column's* value set, and each column numbers its own set independently, usually as values are first encountered. Two files that list countries in different orders, or that simply contain different subsets of them, therefore produce two columns where the same label sits at different offsets. Designs then diverge on what a comparison does: some decode both sides and compare values, which is correct and pays for the decode; some compare codes directly, which is fast and silently wrong whenever the two value sets differ; and some refuse the comparison outright. The repair is to make one value set authoritative and build both columns against it, or to compare decoded values.
go deeper
Recall that the code standing in for a value is meaningful only inside its own column. The same number in a different column can mean a different value entirely.
Explain why separately built columns number their value sets independently, and why a comparison that runs on codes rather than on decoded values is then comparing two unrelated schemes.
Show the diagnosis in order: compare the two value sets first, read one shared label's code in each, then decode and re-compare. Then pick the fix that belongs at construction, not at the comparison site.
The angle is ownership of the value set. A stated, shared list of permitted values makes numbering comparable across every reader and turns a new value into an event; leaving each reader to infer its own set guarantees this recurs.
## A stored code is an offset, not an identity The single fact this failure turns on: in a **dictionary-coded column** — each distinct value kept once, with a short whole number per row pointing at it — the code is an *offset into that column's value set* and nothing else. It is not derived from the value, it is not a hash of the value, and it is not shared with any other column. A column numbers its own set, and two columns built separately have no reason to agree. The usual numbering rule makes this worse rather than better. Where codes are assigned as values are first encountered, the numbering is a function of the order the rows arrived in. Two files listing the same countries in a different order produce two different numberings of the same set of labels; two files containing different subsets produce numberings that cannot agree even in principle. ## What a design can do with the comparison | Behaviour | What you get | How it shows up | |---|---|---| | Decode both sides and compare values | The right answer | Correct, at the cost of materialising the values for the comparison | | Compare the codes directly | Wrong wherever the two value sets differ | Silent: identical labels come back unequal, and different labels can come back equal | | Refuse the comparison | No answer | An error on the line, which is the loudest and safest of the three | The middle row is the one in the question, and it is the dangerous one precisely because it is the fast one. Nothing raises. The comparison returns a full-length truth column, the row count is right, and the answer is wrong. ## Confirming it in a running pipeline 1. **Compare the two value sets, not the two columns.** Read each column's set of distinct values and check whether they hold the same members in the same order. Different membership or different order is the diagnosis. 2. **Take one label present in both, and read its stored code in each column.** If the two codes differ, any comparison running on codes is comparing two different numbering schemes. 3. **Decode both columns and repeat the comparison.** If the result now agrees with what you expected, the codes were the cause; if it still disagrees, the labels themselves differ in a way you have not seen yet, such as casing or padding, which is a different problem. ## Fixing it - **Build both columns against one declared value set.** Where the design lets you state the permitted values and their order up front, both columns then share a numbering and code comparison becomes meaningful. This is the durable fix, because it moves the guarantee to construction instead of to the comparison site. - **Decode before comparing.** Correct everywhere and always available, at the price of materialising values. Reasonable for a one-off check, wasteful as a pipeline step that runs every hour. - **Normalise upstream.** If the set of countries is genuinely fixed, it should be a stated list that every reader builds against, not whatever each file happened to contain. That also turns an unexpected new value into a visible event at the boundary. ## Where the rule still holds, and where it does not bite Not every comparison involving a coded column is suspect, and a good answer says which ones are safe: - **Equality against a literal value inside one column is safe.** The design resolves the literal against *that column's own* value set before comparing, so no cross-set assumption is being made. What designs differ on is the case where the literal is not a member of the set at all: you may get no rows matched, absence, or an error. - **Comparisons inside one column are safe.** Two rows of the same column share a value set by construction. - **Arithmetic on codes is never meaningful**, in any design, even when the values themselves are numbers. Averaging offsets summarises nothing. - **A code is not a stable key.** It is not something to store, to log as an identity, or to pass to another process expecting it to mean the same thing there. ## What the interviewer is listening for They want to hear that the candidate knows a code is an intra-column encoding rather than an identity. The strong answer states that first, then names what the comparison did as a consequence, then separates the safe cases from the unsafe ones — a literal against one column is fine, one column's codes against another column's codes is not. The weak answer reaches for the labels and looks for a typo, which is a reasonable second hypothesis and the wrong first one.
- Would the comparison be safe if both files contained exactly the same countries?Only if they also produced the same numbering. Where codes are assigned as values are first encountered, two files with identical membership but different row order still number them differently. Identical membership is necessary but not sufficient; identical membership in the same declared order is what makes code comparison meaningful.
- Is comparing a coded column against a plain text value equally risky?No. The design resolves that literal against the column's own value set before comparing, so no cross-set assumption is made. The only divergence is what happens when the literal is not in the set: designs variously return no matches, absence, or an error, so it is worth knowing which yours does.
- Why not just decode everywhere and stop worrying about it?Because decoding materialises the full values, which is exactly the cost the arrangement existed to avoid, and it has to be remembered at every comparison site. Making one declared value set authoritative fixes the whole class once, at construction, and leaves the coded form's speed and size intact.
saying these in an interview costs you the question
- Assumes two columns holding the same labels must use the same codes
- Treats a stored code as a stable identity to log or store
- Blames a typo in the labels before checking the two value sets
- Thinks equal value-set sizes guarantee equal numbering
- Believes every design decodes before comparing, so this cannot happen
- Fixes it by widening the code representation on both columns