skip to content

Before widening a table, which count comparison tells you in advance whether any cell will receive more than one value?

level: middleimportance: should knowfreq 45%

answer

  1. two numbers, one comparison
  2. rows against distinct pairs
  3. pairs include the header source
  4. the difference counts absorbed values
  5. answers duplication only, not coverage

basics

~20 s

Count the distinct combinations of identifying values and header-source value, and compare that with the number of input rows. Equal means one value per cell; fewer distinct pairs than rows means some cell will be resolved rather than filled.

solid answer

~50 s

The check is two counts. The first is the input's row count. The second is the number of **distinct key-and-header pairs** — distinct combinations of the identifying columns together with the column supplying the new headers. If the two numbers are equal, every value has its own cell and the widening is a pure rearrangement. If the distinct count is lower, the difference is exactly how many values are going to land on a cell that already has one, and something — a refusal, an inherited reduction, or a cell holding a collection — is going to deal with them. This is arithmetic you own rather than behaviour you hope for, so it works the same whichever tool you are on. It answers the duplication question only; it says nothing about which cells will have no value at all.

code

pseudocode · 9 lines
pseudocode
R = row_count(input)
P = count_distinct(input, identifying_columns + [header_source])

if P < R:
    extra = R - P            # values that will land on an occupied cell
    report(extra, examples = addresses where row_count > 1)
    stop_or_widen_with_stated_reduction()
else:
    widen(input)             # every value has its own cell

go deeper

for a junior

Remember that the cell's address is the identifying values plus the header value, so the count of distinct addresses is what you compare against the row count.

for a middle

Run the two counts and read the difference as a quantity: that many values will be resolved rather than moved. Explain why identifier uniqueness and result row count are the wrong comparisons.

for a senior

Treat the difference as a question about the data, not the code: correction, finer grain, or accidental duplication each demand a different response, and only one of them belongs inside the layout step.

for a principal

The point is that a grain belief goes stale while the code does not change. A check that runs every time survives an upstream change; a reasoned argument about the grain does not.

## The two numbers A **widening** turns one column's distinct values into new headers, filled from a second column, and a cell is addressed by the row key — the identifying values — together with one header. So there are only two numbers worth comparing before you run it: - **R**, the number of rows in the input you are about to widen. - **P**, the number of distinct combinations of the identifying columns and the header-source column across those rows. `P` can never exceed `R`. The comparison reads: | Comparison | Meaning | |---|---| | `P = R` | Every input row has its own cell address; the widening is a rearrangement and the values pass through unchanged | | `P < R` | `R - P` values will land on a cell that already has one, and each of those cells will be resolved by something | That second line is the whole point: the difference is not a vague warning, it is a count of how many values are about to be decided rather than moved. ## Why this check and not another Several near-miss checks feel like they should work and do not: - **Are the identifying columns unique?** They are not supposed to be. In the long layout — one row per measurement, with the measure's name in one column and its number in another — the identifiers repeat once per measure by design. Their repetition is the shape working. - **Does the input row count match the result row count?** The result has one row per distinct row key, which is smaller by design. The shrink is expected and tells you nothing about duplication. - **Do the totals match before and after?** Sometimes, and only after the damage: a reduction that averages identical values leaves totals looking plausible, and by the time you compare, the originals are gone from the result. The pair count is the only one of these that is a statement about the cell address, which is what the layout actually constrains. ## What the check does not tell you It answers the duplication question and nothing else. In particular, `P = R` does not mean every cell of the result will be filled — a row key that never carried a given header still produces a cell the widening had to create, and that is a separate property with separate consequences. Nor does the check say anything about whether the layout change is a good idea. It says only: is this a rearrangement, or is it arithmetic wearing a rearrangement's clothes? ## Running it, and what to do with the answer ``` R = row_count(input) P = count_distinct(input, identifying_columns + [header_source]) if P < R: # R - P values will be resolved into cells that already hold one inspect(addresses where row_count > 1) decide() # correction? duplicate delivery? finer grain? widen(input, reduction = chosen) else: widen(input) # pure rearrangement ``` The `decide()` step is the one people skip, and it is the only one that is about the data rather than the code. `R - P` extra values can mean at least three different things, and the right response differs: 1. **A correction or a retry** — the second value supersedes the first. The reduction is 'keep the later one', and it should be written, not assumed. 2. **A genuinely finer grain** — several legitimate readings per subject per measure. Then the widening's implied grain is wrong, and the honest move is to decide the grain explicitly and aggregate as a declared step rather than letting the layout change do it. 3. **Accidental duplication** — the same record present twice. Then the layout step is the wrong place to fix it; a data problem resolved implicitly by a reduction stays invisible while it grows. ## Making it routine The reason to run the two counts rather than to reason about the data is that the reasoning is about what you believe the input's grain to be, and the counts are about what it is. Grain beliefs are exactly the thing that goes stale: the input arrives from somewhere that changes, and the widening that was a rearrangement for a year quietly becomes arithmetic the month the upstream grain moves. The counts cost one pass and answer the question directly, every run, on every tool.

  • The difference between the two counts is 60 on a 1,000-row input. What exactly does 60 mean?
    Sixty values will land on a cell address that already holds a value, so sixty of the input's numbers will be resolved by something rather than moved into a slot of their own. It does not mean sixty rows disappear, and it does not say how the affected cells are spread — one address may account for all sixty.
  • Where in a pipeline should this check sit?
    Immediately before the widening, over the exact table being widened, after every filter and match that shapes it. Run earlier it tests a different table; run later the values have already been resolved and the evidence is gone.

saying these in an interview costs you the question

  • Compares input row count with result row count and calls that the check
  • Counts distinct identifying values and omits the header-source column
  • Thinks the check also proves every result cell will be filled
  • Plans to compare totals after the widening instead of checking before
  • Skips deciding what a second value means and just picks a reduction