One column marks an absent value the same way whether it was never measured, does not apply, or found no match - what is lost?

## Absence has no room for a reason Whatever a tool uses to mark a cell as holding no value, that mark carries one bit of information: there is nothing here. It does not carry why. So the moment three different circumstances all produce an absent cell in the same column, the difference between them stops existing in the data and survives only in whatever the process wrote down elsewhere - which is usually nothing. This is not a defect in any particular design. It is a property of representing absence as a single state, and every design in this family shares it. Some tools give you more than one absence-like value, but those distinguish kinds of absence at the level of the type system, not the reasons a business has for a gap. ## Three reasons, one cell | Reason | What it means | What it calls for | |---|---|---| | Never measured | an observation that should exist and does not | chase the producer, estimate it, or report coverage honestly | | Does not apply | no such quantity exists for this row | keep the row, and exclude it from the measure's population | | Found no match | a lookup ran and the reference data had no entry | treat it as a finding about the reference data, not about the row | Examples make the distinction concrete. A temperature that the sensor failed to report is never measured. A closure date on a case that is still open does not apply - not because anybody lost it, but because the event has not happened. A product category that came back as nothing for an item identifier your catalogue has never seen is a match that found nothing, and what it tells you is that the catalogue is incomplete. ## What goes wrong when they are treated alike - **Filling.** Estimating a value for a never-measured cell is defensible. Estimating one for a does-not-apply cell invents a fact that cannot exist, and it pollutes exactly the rows an analyst most wants to keep separate. - **Removing incomplete rows.** This deletes the structurally inapplicable rows along with the genuinely incomplete ones. Since the inapplicable rows are usually a coherent subgroup - the open cases, the unshipped orders, the trial accounts - the surviving population is a different population, and every later rate is computed over it. - **Denominators.** A closure rate over all rows and a closure rate over closeable rows are different measures. When the reason is gone, nobody can tell which one a published figure used. - **Monitoring.** An alert on the share of holes in a column is meaningless if a large, stable share of them is structural. Either it fires constantly and gets muted, or the threshold is set high enough to hide the collection failure it was built to catch. ## Where the reason still exists The reason is knowable at exactly one moment: the step that produced the absence. The collection job knows the sensor did not report. The domain model knows an open case has no closure date. The lookup knows it searched and found nothing. Every step after that sees only a hole. So the recovery options, in descending order of quality: 1. **Do not let it become absence.** Where the reason is structural, state it. An eligibility column that says which rows the measure is defined for, or a narrower table holding only the rows where the measure exists, makes the structural fact explicit instead of leaving it to be inferred from a hole. 2. **Carry a reason beside the value.** A small coded column, populated by the step that creates the hole, keeps the three apart for everything downstream. 3. **Reconstruct it from other columns.** Sometimes the status column, or a timestamp elsewhere, implies the reason. This works, and it is fragile, and it has to be rewritten every time the schema moves. 4. **Ask the process.** If all else fails, the pipeline's own history and the producing team still know. The column does not. ## Doing it without bloating the table A reason column is not free: it is more storage, more discipline at every step that can create a hole, and another thing to review when the pipeline changes. So spend it where a downstream decision actually turns on the reason - an eligibility-sensitive measure, a regulated field, a feed whose producers you chase - and do not add one beside every column that happens to admit absence. The judgement an interviewer is looking for is that you can say which columns deserve it and why, not that you would instrument everything. ## What an interviewer is listening for That you can name at least two distinct reasons a cell is absent and give a different action for each. That you can point at removing incomplete rows as the step where the conflation becomes a silent population change. And that you put the fix upstream, where the reason is still known, rather than proposing to infer it later from a column that does not carry it.