skip to content

Two key columns hold the same codes, one as text and one as numbers — will the match raise an error?

level: middleimportance: should knowfreq 55%

answer

  1. silence is not evidence
  2. the whole column, not some values
  3. refuse, match nothing, or convert
  4. a code is a label
  5. converting drops leading zeros

basics

~20 s

Do not count on it. Some designs refuse a cross-representation key comparison at the call, others compare, find nothing equal and hand back an empty or badly thinned result, and some convert one side - which can change the code itself.

solid answer

~40 s

There are three behaviours in this family and you do not choose which one you get. A strict design refuses the comparison and fails the call, which is the outcome you want. A permissive design compares the two columns, finds nothing equal, and returns an empty or thinned result with no diagnostic at all. A third converts one side into the other's representation and compares the converted values - which can work, and can also alter the value, because a code of digits is a label rather than a quantity and its leading zeros are not part of any number. So silence is not evidence that the comparison was meaningful. Whichever behaviour you got, the diagnosis is identical: read the representation of each key column, then compare the two key value sets directly.

go deeper

for a junior

Know that a column holds one representation for all its rows, and that a code made of digits is a label, so leading zeros are part of the value and not decoration.

for a middle

Explain all three behaviours - refuse, pair nothing, convert - and why a conversion that appears to succeed can alter the code it converted.

for a senior

Reject silence as evidence. Read the representation of both key columns as a first move, and settle the representation at the producing step on both sides rather than at the match.

for a principal

Decide whether your pipeline declares key representations up front and pays the friction, or infers them and pays for the incidents; say which failure you would rather have.

## One column, one representation In this family a column carries a single representation for all of its rows: the whole column is text, or the whole column is numbers. That makes a cross-representation key comparison a property of the *columns*, not of individual values - which is good news, because it is one fact to read rather than a million values to inspect, and bad news, because when it is wrong it is wrong for every row at once. The common origin is a code made of digits. An account number, a postal code, a product code and a store number are **labels that happen to be written with digits**. Nothing about them is a quantity: you never add two of them. But they look numeric, so somewhere along the way one side of the match ends up holding them as numbers while the other still holds them as text. ## Three behaviours, and you do not pick one | Behaviour | What you see | What it costs you | |---|---|---| | **Refuse at the call** | the call fails, naming the two representations | nothing - this is the outcome you want, caught at the seam | | **Compare and find nothing equal** | an empty or badly thinned result, no message | the worst case: a plausible-looking table and no diagnostic | | **Convert one side, then compare** | it appears to work | the conversion may have altered the code, so some values pair and others silently do not | The second behaviour is why "it ran" proves nothing. A run that produces no error and a table of the right shape is exactly what a broken key comparison looks like on a permissive design. ## Why converting is not free Turn a textual code into a number and everything that was not part of the number disappears: - **Leading zeros go.** `00742` becomes `742`. If the other side kept the textual form, the two never pair again, and if you convert back you get a different code than you started with. - **Anything non-numeric goes or fails.** A code with a letter, a hyphen or a space either refuses to convert or becomes an absent value, silently thinning the column before the match even runs. - **Very long codes lose their tail.** A long numeric identifier can exceed what a numeric representation holds exactly, and the low digits are quietly altered - producing codes that are nearly right, which is worse than codes that are obviously wrong. - **Nothing warns you.** The converted column still looks like a column of codes. The general rule is worth saying plainly: a code is a label, so hold it as text everywhere, on both sides, from the moment it arrives. ## "It ran" is not evidence Three things a weak answer treats as proof, none of which are: 1. **No error was raised.** On the permissive designs there is nothing to raise; equality simply never holds. 2. **The output has the right columns.** Column structure comes from the two inputs' column sets and is unaffected by whether any row paired. 3. **A few sampled rows look correct.** They are drawn from the subset that paired, which is by construction the clean part. ## The diagnosis does not depend on which behaviour you got 1. **Read the representation of both key columns** before anything else. Two different representations is the finding, and you are done. 2. **If they agree, compare the two key value sets** - distinct values per side, shared values, and what each side holds alone. A cross-representation defect shows as a near-total gap on one side. 3. **Sample the stranded values and compare their character lengths** against the length the code should have. A short length on one side is the signature of lost leading zeros. 4. **Then look at where the column was produced**, because that is where the representation was decided and that is where it is settled. ## What this leaves for the step before This question is about noticing, not cleaning. The repair lives at whatever step produced the key column - and the important part of the answer is that the repair is applied on **both** sides, to one agreed representation, rather than converting one side at the match to make the numbers come out. Converting at the point of the match is how a diagnosis becomes an undocumented transformation that the next person has to rediscover.

  • Why is it worse for a design to quietly match nothing than to refuse the comparison outright?
    A refusal happens at the seam, names both representations, and stops the run before anything downstream exists. Matching nothing produces a result object of the correct shape whose only symptom is a row count nobody was told to expect, and that result flows on into totals, charts and files. The failure is the same; only the distance between the cause and the symptom changes.
  • Why hold a numeric-looking code as text on both sides rather than as a number?
    Because it is a label, not a quantity. A textual representation preserves leading zeros, tolerates a letter or a separator appearing later, and never loses low digits on a long identifier. You give up arithmetic you were never going to do, and you gain a value that survives every hop between producers unchanged.

saying these in an interview costs you the question

  • Assumes a differently-represented key comparison always raises an error.
  • Treats a run that produced no error as proof the keys compared meaningfully.
  • Converts a code column to numbers so that it can be compared.
  • Says leading zeros are cosmetic and safe to lose.
  • Checks a few paired rows and concludes the key columns agree.
  • Fixes the representation at the match rather than where the column was produced.