skip to content

In a table holding one measurement per row, which columns together identify a row, and what breaks when one is left out?

level: middleimportance: should knowfreq 52%

answer

  1. say the grain as a sentence
  2. the name column belongs in the key
  3. payload is never part of the key
  4. a missing dimension destroys information
  5. one table, one grain

basics

~20 s

The identifying columns together with the name column: every column saying which subject, which moment and which measure. Leave one out and two rows become indistinguishable, so nobody can tell a duplicate from a correction or a second reading.

solid answer

~40 s

The key is the **identifying columns** — which subject, which moment, and any other dimension the data genuinely varies over — plus the **name column**, which says which measurement the row carries. The value column is payload: it is what the key resolves to, never part of it. That key is the grain, and the grain is a sentence you should be able to say out loud: *one row is one measure, of one store, on one day*. Drop a dimension the producer actually varies over and two rows become indistinguishable, at which point a duplicate, a correction and two genuine readings all look identical and every downstream choice is a guess. The subtler failure is one table quietly carrying two grains, when a daily measure and a monthly one share a single key.

go deeper

for a junior

Learn to read the key off the table: the columns saying which subject, which moment and which measurement, with the number itself left out of that list.

for a middle

Explain why the name column has to be in the key, and what becomes undecidable once a dimension the producer varies over is not recorded anywhere.

for a senior

Show that you look for two grains hiding in one table, and that you know a repeated coarse measure makes totals wrong without raising anything.

for a principal

The call you own is whether measures with different grains belong in one table at all, and what you are asking every future reader to remember if they do.

## The grain is a sentence, not a column list The grain says what exactly one row is. In this arrangement it reads: *one row is one measurement, of one subject, at one moment*. Everything else about the table either follows from that sentence or contradicts it — how many rows there are, what a count of them means, whether two similar rows are a problem. Stating it is not a formality. `One row per store, per day, per measure` is a claim about the thing producing the data: that it emits each measure at most once for a store on a day. If the producer also sends corrections, or reports twice when a till is re-opened, that sentence is false and you want to know before the first aggregate rather than after. ## What is in the key, and what is not - **The identifying columns.** Which subject, which moment, and any other dimension the data genuinely varies over: site, currency, instrument, channel, sensor. Each of them is written again for every measurement. - **The name column.** Which measurement this row carries. Without it in the key, a store-day has many rows and nothing distinguishes them, so it is not an optional extra — it is the part people forget. - **The value column is not in the key.** It is what the key resolves to. Keying on the number makes every row unique by construction, which is not uniqueness, it is camouflage. This is where the arrangement differs from one column per measure. There, the key is just the identifying columns, because each measure already has a header of its own. Here the name column joins the key, and its domain is data rather than schema: the shape of the key is stable, but which values appear in it is whatever arrived. ## What a missing dimension does Suppose readings arrive as `sensor`, `reading_type`, `reading`, once a minute, and no moment was kept. Every reading of that type from that sensor now shares one key and two rows are indistinguishable. Three readings of those rows are all consistent with the data, and nothing lets you choose between them: 1. **A duplicate** — the same measurement delivered twice, one of which should go. 2. **A correction** — a later value replacing an earlier one, where *later* is precisely the information that was dropped. 3. **Two genuine measurements** at different moments, both of which belong. The damage is not storage. It is that every downstream decision — keep one, keep the last, add them up — is now a guess dressed as a rule. Restoring the dimension is the only real repair, and it can only be made where the data is produced. ## When the measures disagree about their own grain | measure | its natural grain | what one shared key forces | |---|---|---| | footfall | one number per store per day | fits the key as it stands | | headcount | one number per store per month | must be carried on some chosen date, or leave the day part unfilled | | price list version | one per chain, changing rarely | repeats across every store and day, or belongs in another table | Three dispositions, each with a consequence worth saying out loud: - **Carry the coarse measure on a chosen date.** Honest, but the number is then present on one day and absent on the others, and anyone averaging per day has to know that. - **Leave the fine part of the key unfilled for coarse rows.** The key no longer has the same shape for every row, and every reader has to handle two shapes. - **Repeat the monthly number on every day.** The most tempting and the most dangerous: a total across days now counts it thirty times, and nothing in the table says so. The general rule is one table, one grain. Where two measures genuinely have different grains, either the key carries the finest one and everybody knows which rows are coarse, or they are two tables. ## What an interviewer is listening for - That you name the key before you name an operation. - That you put the name column in the key without being prompted. - That you treat two identical-looking rows as an ambiguity to resolve rather than a duplicate to discard. - That you notice when one table is quietly carrying two grains, because that is the version of this failure that survives into production. The short form of the whole answer: the key is the identifying columns plus the name column, the value is payload, and a dimension missing from the key is information that has already been destroyed.

  • Two measures in the same table are naturally daily and monthly. What are the options?
    Three. Carry the monthly number on a chosen date and tell every reader it is there once a month; leave the day part of the key unfilled for those rows and accept two key shapes; or split them into two tables, each with one grain. The fourth option, repeating the monthly number on every day, is the one that silently makes totals across days wrong.
  • Does adding another naming column change the grain?
    Yes. Any column that says which measurement a row carries is part of the key, so a table naming a measure and a currency is keyed by both. The grain sentence grows a clause, and a reader who conditions on only one of the two naming columns is reading a mixture.
  • The producer sends corrections for earlier measurements. What does that do to the key?
    It breaks the grain sentence unless something distinguishes the versions, because the correction shares the subject, the moment and the measure with the row it corrects. Either the producer supplies a dimension that separates them, such as when the record was written, or the table cannot represent the correction and is silently ambiguous.

saying these in an interview costs you the question

  • Says the value column is part of what identifies a row
  • Names the subject and the moment but forgets the name column
  • Assumes every measure in the table shares one grain
  • Cannot say whether two identical rows are a duplicate or a second reading
  • Treats the grain as whatever the current data happens to contain