You inherit an extract whose numeric column mixes real zeros, cells with nothing recorded and one repeated round number - how do you establish what each means?

## Why this is a separate step Every figure you compute from this column inherits whatever you assumed about its blanks and its suspicious round number. Doing that reasoning inside the analysis means it is done implicitly, differently in each script, and never written down. Doing it first, as a short profiling pass with a written conclusion, costs an hour and is the difference between a number you can defend and a number you can only publish. The core difficulty is that four states can live in one delivered column at once: a measured zero, a cell with nothing recorded, a present value of length zero where the column admits text, and a stand-in code the producer wrote to mean no data. Three of the four are present values, so only one of them is visible to anything that looks for absence. ## The four passes 1. **Count the holes per column.** How many cells the absence check flags, as an absolute number and as a share of rows. A column that reports none is not thereby complete - it is a column where absence, if any, is being carried as a value. 2. **List the distinct values with their counts.** For a numeric column, the extremes plus the most frequent values are enough. One exact value with a count far out of line with its neighbours is the clearest single signal there is. 3. **Check the extremes against an external range.** Not against the data - against what the instrument can read, what the business can sell, what a date can plausibly be. Only an outside range can tell you that a value is impossible. 4. **Slice by time and by source.** Run the first three per month and per producing system. Conventions change: a feed that reported holes honestly for two years and started writing a code after a migration will look consistent in aggregate and obvious once split. ## Reading what the passes tell you | What you see | What it suggests | What it does not prove | |---|---|---| | One value repeating far outside a defensible range | a stand-in code | which of several reasons it stands in for | | Whole rows at zero across unrelated measures | a reporting gap filled with a default | that every zero in the column is suspect | | Holes concentrated in one period or one source | a collection or delivery failure | that the values elsewhere are trustworthy | | A frequent but plausible value | nothing on its own | anything at all without an external range | The last row is the discipline that keeps this honest. Frequency alone is not evidence; frequency plus impossibility is. ## The limits of profiling Profiling separates a stand-in code from a reading only when the code is impossible. When the producer chose a value that genuinely occurs - zero in a column where zero happens, or the first day of the month as a default date - the two are identical in storage and the column has no further information to give. At that point the work moves off the data: - the operational record of when a device, shop or feed was down; - a companion status or quality field delivered alongside; - the correlation between candidate rows and other columns that failed at the same time; - the producing team, who can simply tell you. And if none of that resolves it, the deliverable is the ambiguity itself: how many rows are affected, which figures they touch, and what each competing interpretation would do to the headline number. ## Closing it out Finish with three things, in this order. Settle the convention with the producer in writing, so the next delivery is interpretable without repeating this. Convert to one representation at a single boundary - where the data enters your process - so every downstream step sees the same meaning for a hole. And record what you concluded next to the dataset, including the values you treated as codes and the date the convention was confirmed, because the single most common way this decays is that somebody fixes it, tells nobody, and the upstream convention changes six months later. ## What an interviewer is listening for The signal is that you do not start by computing. A strong answer names concrete, cheap passes, insists that plausibility comes from outside the data, admits the case profiling cannot settle, and ends with the producer conversation rather than with a clever heuristic. A weak answer proposes a rule - treat anything below some threshold as absence - without ever asking what the producing system actually does.