A table is built from in-memory records whose account identifier is all digits — why state that column's type?
answer
- digits are not always a quantity
- decide before the first value lands
- leading zeros, exactness, sort order
- the first batch is only a sample
- declared at construction, not repaired later
basics
~20 sStating the column's type at construction fixes it as text before any value is stored, so leading zeros survive and long identifiers stay exact. Left undecided, digits are treated as a quantity and both are lost for good.
solid answer
~50 sAn account identifier is a label that happens to be spelled with digits, not a quantity, so nothing about it should ever be arithmetic. When a table is built from records already in memory, most tools pick each column's representation — the one physical form every value in that column is stored in — from the values in front of them, and a column of digits looks exactly like a number. Two things break immediately. A leading zero has nowhere to live in a number, so `00734` becomes `734` and cannot be recovered, because the distinction was never stored. And an identifier with more digits than the numeric representation holds exactly is refused outright under some designs and comes back changed under others. Stating a text type at construction — before any value has been stored — costs one line and removes both.
go deeper
Recall the test: digits are a quantity only if adding two of them means something. Know that a leading zero is gone for good once the column is numeric, and that the place to fix it is where the table is built.
Explain the mechanics: the representation is chosen from the values unless you state a type, and the first batch is a sample. Name the three consequences — lost leading zeros, exactness at the numeric range, and a changed sort order.
Show that you treat construction as a boundary worth defending, and be honest that a value beyond the numeric range is refused under some designs and silently changed under others. Say how you would tell which you are in before promising anything.
The tradeoff is friction against silent wrongness: a declaration per column is maintenance, and a stale declaration refuses good data. Be able to argue where on that line your team should sit and why.
## The statement, and the moment it acts A **declared column type** is the type you state when a table is built, before any value has been stored in it. It is worth distinguishing from the **column representation**, which is the one physical form every value in a column ends up stored in: the representation is the outcome, and the declaration is one possible input to it. Say nothing, and the tool picks the representation from whatever values it is handed. Say something, and you have taken that decision away from the data. The moment matters at least as much as the mechanism. Construction is the last point at which nothing has been lost yet. Every repair after it is a repair of what survived. ## Why an identifier made of digits is the standard example An account identifier, a postal code, a product code and a status code are all written with digits, and none of them is a quantity. Adding two of them means nothing; averaging them means less. But to anything choosing a representation from values, a column of digits is indistinguishable from a column of numbers, and several consequences land at once: - **A leading zero has nowhere to live in a number.** `00734` and `734` are the same quantity and different identifiers. Once the column is numeric the difference is unrecoverable, because it was never stored — converting back to text afterwards gives you `734`. - **A fixed-width numeric column has a range.** Every row occupies the same declared number of bytes, and a value outside that range simply cannot be represented. A long identifier is refused outright under some designs and comes back quietly changed under others; which of the two you get is a property of the tool, not of your data. - **The sort order changes.** Text compares character by character, so `10` orders before `9`; quantities do not. A listing ordered by identifier silently reorders, and nothing reports an error. - **The batch you built from is only a sample.** A column that is all digits in today's records stays all digits until the first identifier containing a letter arrives — and then the decision is made again, differently, on a day nobody is watching. ## What stating it buys, in order 1. **It takes the decision away from the values.** The records in front of the tool are a sample of the records this code will ever see. A representation chosen from a sample is a representation chosen by whoever happened to send the first batch. 2. **It moves the failure to the earliest line that can see it.** Under designs that check the declaration at construction, a value that does not fit stops the program right there — where the offending value is still on hand and the stack tells you which step produced it. 3. **It states intent where a reader looks.** The line that builds the table is the first line a maintainer reads, and a stated type says *this is a label* far more plainly than a comment three files away. ## Stated against decided | | Type stated at construction | Representation decided from the values | |---|---|---| | Who chooses | you, once, in code | the first batch of data | | Leading zeros on an identifier | kept, because the column is text | dropped, unrecoverably | | A value that does not fit | refused at that line, or converted by a rule you chose | absorbed by re-deciding the representation | | Behaviour when the next batch differs | unchanged | may differ from run to run | | Up-front cost | a decision per column, and a wrong declaration blocks good data | none, which is why it is the default | ## How much the statement is worth Designs disagree here, and the disagreement is the interesting half of the subject. Some validate the declared type at construction and refuse a non-conforming value outright, so the statement is a contract checked at the boundary. Others honour it at construction and then let the next operation hand back a column whose representation is decided afresh, so the statement is a starting point rather than a promise. Before you lean on a declaration, know which of the two you are in. Either way the statement is still worth making, because both designs at least *start* where you asked, and starting right is the whole of this particular problem: the identifier is only mangled once, at construction, and after that every step downstream is faithfully carrying the wrong thing. ## What an interviewer is listening for The candidate who has been burned by this says two things without prompting. First, that the test for whether digits are a quantity is whether adding two of them means anything. Second, that the repair has to happen where the table is built, because by the time the report is wrong the information is gone. The candidate who has not been burned says the tool usually gets it right — which is true, and is exactly why the failure reaches production.
- You inherit a table where the identifier column is already numeric and the leading zeros are gone. What is recoverable?Nothing, from the table alone. If the identifier format is known to be fixed-length you can pad back to that length and be right for every row that was only missing zeros — but you cannot tell those rows from genuinely shorter identifiers, and any value that exceeded the numeric range is wrong in a way padding will not touch. The honest fix is to rebuild from the source records with the type stated.
- How do you decide whether a column of digits should be text or a number?Ask whether adding two values together, or comparing them for magnitude, means anything in the domain. A price, a count and a measurement all pass; an account identifier, a postal code and a status code all fail. If the answer is no, it is a label spelled with digits, and the only reason it ever ends up numeric is that nobody said otherwise.
- Is there a real cost to stating every column's type?Yes, and it is worth naming rather than waving away. Every column becomes a decision someone has to make and keep current, and a declaration that is wrong is worse than none under designs that enforce it: perfectly good data is refused at the boundary because the type drifted and the declaration did not. The cost is friction and maintenance, paid up front, against silent wrongness paid later.
Typing a phone number into a spreadsheet cell that is formatted as a number. The leading zero disappears the moment you press enter, and no amount of reformatting afterwards brings it back — the digits were stored as a quantity, and a quantity has no leading zeros to store.
saying these in an interview costs you the question
- Says the tool almost always gets identifier columns right
- Thinks converting back to text later restores the leading zeros
- Treats every column of digits as a quantity
- Confuses a note a checker reads with how values are stored
- Assumes sort order is unaffected once the type is wrong