In a match keeping the orders table whole, a row shows the absent-value marker in every reference-side column: what two causes explain it?

## Where the empty cells came from When you choose a match shape that keeps the orders table whole, you are saying: return every order row, partnered or not. A row that found no partner still has to be rectangular with the rest of the output, so the operation fills the columns the reference table would have contributed with the **absent-value marker** — whatever token the tool uses to say "there is no value here". That is the key point about this leaf: **the match manufactured absence out of rows that were complete before it ran**. No source system produced those cells, nobody deleted anything, and the input data had no hole in it. The hole is an artefact of a decision you made at the call. ## The two causes, and why they look identical Once the output exists, a reader cannot tell the two apart by looking: 1. **The key found no partner.** The customer identifier on that order does not appear in the reference table at all. The empty cells are manufactured. 2. **The key found a partner whose values are absent.** The order did pair with a reference row, and that row genuinely carries nothing in those columns — a customer record created with the address fields never filled in, say. The empty cells came from the source. Both arrive as the same marker in the same columns. This is why "we have a lot of missing values after the match" is not yet a diagnosis, and why the two need different repairs: | Cause | What it tells you | Where the fix lives | |---|---|---| | No partner | Your reference data does not cover the keys you see in transactions | Upstream coverage: a stale extract, a new entity, a filtered reference source | | Partner with absent values | Your reference data covers the key but is thin | Content quality in the reference table itself | | Absent key on the order row | The order row had nothing to match on in the first place | A validation gap before the match ever ran | That third line is worth naming, because it hides inside the first: a row whose *key itself* is absent cannot find a partner in most designs, so it surfaces exactly like cause 1 while its real problem sits a step earlier. ## Telling them apart Three techniques, in order of how much they depend on your tool: 1. **Ask for a provenance marker.** Where the tool can attach an extra column saying which side each output row came from, this answers the question outright: rows recorded as coming from one side only had no partner, and rows recorded as coming from both did. Every remaining empty cell on a both-sides row is genuine source absence. 2. **Compare the key sets directly.** Take the distinct key values of the orders table and the distinct key values of the reference table, and look at how many values each side holds that the other does not, with an example or two. This works in every tool and it is the technique to reach for when no provenance marker exists. It also gives you a number to put in a report rather than an impression. 3. **Count instead of eyeballing.** Report the number of output rows with no partner alongside the result, not the proportion of empty cells. The proportion mixes the two causes together and moves whenever either changes. ## Where designs differ - **Absence in the key column itself.** In some designs the absent marker is treated as an ordinary comparable value, so rows with an absent key pair with each other across the two tables; in others absent never compares equal to anything, so those rows simply never partner. You do not want to depend on which rule you got: remove or route rows with an absent key before the match, deliberately. - **How absence is represented.** Some designs borrow a numeric sentinel for absence in numeric columns; others carry a separate validity bit alongside the values. The behaviour you can rely on across all of them is only that the tool has *some* representation for "not there" and that the match will use it to fill the manufactured cells. - **Whether a provenance marker exists at all.** Some tools offer one as an argument to the match; some require you to build it by adding a constant column to each side before matching; some cannot do it directly. Only the key-set comparison is available everywhere. ## The habit to demonstrate An interviewer is checking whether you distinguish *a key with no partner on the other side* from *a cell holding the absent-value marker*. They arrive together and they are not the same thing. The answer that lands is: name both causes, say they are visually identical in the output, name the instrument that separates them, and then report the unpartnered count as a number next to the result — because the moment that count changes, something upstream has changed, and nothing in the pipeline will raise to tell you.