A nightly revenue report has quietly matched only 94% of transactions to accounts for months — why is that worse than a match that returned nothing at all?
answer
- the loud failure is the cheap one
- plausible output gets signed off
- the loss is not random
- a defect follows its producer
- assert the unmatched share
basics
~20 sBecause it ships. An empty result stops everything and is fixed the same day; a 94% match returns a plausible table whose loss is systematic rather than random, so the totals are consistently wrong, consistently believable and consistently signed off.
solid answer
~50 sAn empty result is self-announcing: every total is zero, someone shouts, and the cause is found on day one. A partial match produces a table of exactly the right shape with a number that sits inside the range people attribute to the business, so nothing prompts anyone to look. Worse, the missing rows are not a random sample. A key defect follows its source - one producer that pads its codes, one legacy system that stores them upper case, one era of accounts created before a format change - so entire subpopulations vanish together and every downstream figure is biased in the same direction for as long as it runs. The fix is to stop treating the match as something you inspect and start treating it as something that asserts: count the rows that found no partner, compare that share against a stated limit, and fail or alert when it moves.
go deeper
Remember that a match can return a perfectly normal-looking table that is missing rows, and that nothing raises when that happens.
Explain why the lost rows cluster: a key defect belongs to a producer, so whole subpopulations disappear together and the resulting bias has a direction.
Show the bracket you put around the operation - expected row count in, unmatched share out, a limit stated in advance, the stranded key values kept where a person sees them.
Take a position on what a pipeline does when the expectation is violated: fail the run and block the report, or publish with the shortfall attached, and who is accountable for the choice.
## The two failures are not the same failure Both cases are the same defect - key values that do not compare equal - and they behave completely differently once they leave the process. | | Empty result | 94% result | |---|---|---| | Who notices | everyone, immediately | nobody | | When | the first run | when someone reconciles against another source, if ever | | What the output looks like | obviously broken | correct shape, plausible number | | Cost | one morning | every decision taken on the figure since it started | | Chance of being shipped | near zero | near certain | That is the whole answer in outline: the loud failure is cheap and the quiet one is expensive, and they differ only in how much of the key space was affected. ## Why the missing rows travel together A partial match feels like it should lose a random six per cent. It does not, because the causes of unequal keys are **properties of a producer**, not accidents of individual records: - One source system pads its codes to a fixed width and the others do not, so everything from that source disappears. - One producer stores codes in upper case, so its entire feed fails against a mixed-case reference. - Accounts created before a format change carry the old shape, so the loss is an era of the business rather than a slice of it. - One channel's codes pass through a step that dropped their leading zeros, so that channel alone vanishes. The practical consequence is that the six per cent is a **subpopulation with a shared business meaning** - a region, a channel, a partner, a vintage - and every derived figure inherits a directional bias. A missing region does not make the total noisy; it makes it low, every night, by roughly the same amount. ## What the number on the report then does The figure is low and stable, so it establishes itself as the baseline. People explain the level with a business story. A later comparison against another system produces an argument about which of two numbers is right, and the reconciliation costs more than the original defect would have. Meanwhile the pipeline is healthy by every signal anybody watches: it ran, it produced output, it raised nothing. ## The bracket that catches it The habit that separates a senior answer is refusing to treat a match as an operation you inspect afterwards. Put numbers around it: 1. **Record the input row count** of the fact side before the match. 2. **Count the rows that found no partner** - either by keeping that side whole and counting rows whose added columns came back absent, by using a marker saying which side each row came from where the tool has one, or by a membership test against the other side's key values. 3. **Express it as a share** and compare it against a limit you stated in advance. The limit is a decision, not a discovery: it can be zero for a reference that is supposed to be complete, or a small percentage where a genuine tail is expected. 4. **Fail, or alert loudly, when the share exceeds the limit** - the point is that a threshold breach is an event, not a number in a log nobody reads. 5. **Keep the stranded key values somewhere a person can see them**, with a count of the rows behind each. That is what turns "6% unmatched" into "everything from this producer since the fourteenth". 6. **Record the share every run**, so the drift is visible as a series. This defect almost always starts on one identifiable day. Step 3 is the one people skip. Counting without a declared expectation only produces another number on a dashboard; the expectation is what makes the count able to fail. ## What varies between tools - Whether the match itself can tell you which rows found no partner varies: some designs can add a column naming the side each output row came from, others offer nothing at all. Where there is nothing, the membership test against the other side's distinct key values gives the same information and cannot change the row count. - Whether the output row order is meaningful varies too, so do not build the check on positions; build it on counts and on key values. - What does **not** vary is the arithmetic: a key with no partner contributes no pair, and the share of unpartnered rows is computable in every tool in this family. ## The habit to demonstrate Before running a match, say aloud what row count you expect and what share of unmatched rows you would accept. After running it, check both. A candidate who does that catches this defect on the night it starts; a candidate who reads the output table catches it at the next audit, and a candidate who does neither is the reason the report has been wrong for months.
- What limit would you set on the unmatched share, and how do you choose it?It is a stated decision rather than a measurement. Where the reference is meant to cover every fact, the limit is zero and any unmatched row fails the step. Where a genuine tail exists - late-arriving accounts, say - set the limit just above the observed steady state and alert on the change rather than the level, because the level is the thing that will drift.
- The unmatched share has sat at 6% since the pipeline was written. How do you tell a defect from a real tail?Look at the stranded key values rather than the number. A real tail is scattered: many distinct codes, few rows each, no shared pattern. A defect is concentrated: the stranded codes share a length, a case pattern, a prefix or a producer, and a handful of them account for most of the lost rows. Weighting each stranded key by the rows behind it settles it quickly.
- Why is comparing the output row count against last night's run not enough on its own?It moves with real volume, so a genuine business change looks like a defect and a defect that starts small hides inside ordinary variation. The unmatched share is normalised against the input, so it is stable when the business grows and moves only when the pairing itself changes - which is the event you are trying to catch.
saying these in an interview costs you the question
- Calls a partial match the mild version of an empty one.
- Assumes the unmatched rows are a random sample of the data.
- Checks the output looks reasonable instead of counting what failed.
- Relies on someone downstream noticing a wrong total.
- Counts unmatched rows but never states a limit to compare against.
- Expects the match itself to warn about rows that found no partner.