skip to content

A key match returns far fewer rows than expected — what do you compute on the two key sets to find the cause?

level: middleimportance: must knowfreq 62%

answer

  1. compare sets, not rows
  2. five numbers before any opinion
  3. how many each side lacks
  4. both directions, not one
  5. then length and character codes

basics

~20 s

Compare the key value sets, not the rows: how many distinct values each side has, how many are shared, and how many each side holds that the other does not. Then inspect a sample of the unshared ones.

solid answer

~40 s

The evidence is the rows that are *not* in the result, so reading the result cannot find it. Take the distinct key values of each side and compute five numbers: the size of each set, how many values appear on both sides, how many appear only on the first, and how many only on the second. The shape of those numbers already classifies the defect - a huge one-sided gap means something hit the whole column at once, a scattered handful on both sides means bad individual records, and a large overhang on a reference side alone is usually normal. Then take about ten of the unshared values and report their character length and character codes rather than their rendering, which is what names the actual difference.

code

pseudocode · 13 lines
pseudocode
order_keys   := the set of distinct key values in orders
account_keys := the set of distinct key values in accounts

shared        := order_keys that are also in account_keys
orders_only   := order_keys that are not in account_keys
accounts_only := account_keys that are not in order_keys

report size(order_keys), size(account_keys), size(shared)
report size(orders_only), size(accounts_only)

for value in the first 10 of orders_only:
    report length(value)
    report the character code of each character in value

go deeper

for a junior

Remember that the rows you are hunting are the ones missing from the result, so the result cannot show them; the distinct key values of each side are what you compare instead.

for a middle

Be able to name the five counts and say what each rules in or out, and explain why the difference is computed in both directions rather than one.

for a senior

Go past the counts to the pattern: weight stranded key values by the rows behind them, and read a shared length, prefix or case pattern as a pointer at one producer.

for a principal

Decide whether these counts are something each analyst re-derives by hand at incident time or a standing output of the step, and who is expected to look at them.

## The instrument is the key set, not the rows When a match comes back smaller than expected, the instinct is to open the result and read it. That cannot work, because the evidence is the rows that are **not** there, and they are not in the result to be read. Scrolling the output tells you about the rows that succeeded. The instrument that does work is a comparison of the two **key value sets**. A key value set is simply the distinct values of the key column on one side, with repetitions discarded. It is far smaller than the table, it costs one pass to build, and it is about values rather than about rows - which is exactly the level the failure lives at. ## The five numbers | Number | What it is | What it tells you | |---|---|---| | distinct key values on the transactions side | the space of codes the facts actually use | how much variety the lookup has to cover | | distinct key values on the reference side | the space of codes the reference offers | whether the reference is even in the same space | | values present on both sides | the only values a match can ever pair on | the hard ceiling on what any match shape can find | | values only on the transactions side | facts whose partner is absent | the size of the real loss | | values only on the reference side | reference entries nobody used | usually normal, and not by itself a defect | The fourth number is the finding. The fifth is the one people misread as a finding: a reference table almost always carries entries no fact refers to, and a one-directional look mistakes that overhang for the problem. ## Reading the shape of the two gaps - **A very large gap on one side and a tiny one on the other** - something affected every value in that column at once: its representation changed, padding was applied everywhere, or leading zeros were lost. Not a data-entry problem. - **A scattered few on both sides** - individual bad records. Worth correcting, but not a systemic defect. - **No shared values at all** - the two sides are not speaking the same code space. Either the columns you compared are not the same key, or one of them was transformed. - **A gap whose members share a length, a prefix or a case pattern** - one producer or one era of records. That pattern is the most useful thing the diagnosis can hand you, because it points at where the values came from. ## Then look at the values themselves 1. Take about ten values from the larger gap. 2. Report the character length of each, next to the length the code is supposed to have. 3. Report the character code of each character, so an invisible passenger or a look-alike glyph has somewhere to show up. 4. Put one of them beside a value that *did* pair, and compare position by position. That sequence names the difference. Nothing before it does, because every earlier step is still working with renderings. ## Two other routes, where the tool offers them - **A provenance marker.** Some tools can run the match keeping one side whole and add a column saying which side each output row came from. Filtering on that column gives you the unpartnered rows directly, with their other columns attached, which is often more informative than the bare key values. - **A membership test.** Where there is no such marker, you get the same answer by keeping the rows of one table whose key value is in the other side's set of key values, and the rows whose key value is not. This adds no columns and cannot change the row count, which is precisely why it is a safe diagnostic. Both are alternative renderings of the same set comparison. The counts remain the thing you report. ## Why not just look at the data Three reasons, and they compound: - The defect is invisible in a rendering, which is the whole premise of this failure mode. - Any sample you read by eye is drawn from what came back, and what came back is the clean subset. - A partial match looks entirely normal in a display - correct columns, plausible values, no absent cells if you happened to keep only partnered rows. ## What the diagnosis does not give you It gives you *which* values failed and *how many*, and it hands you a pattern in them. It does not give you the reason a producer emitted them that way, and it is not itself the repair - the fix belongs at whatever step produced the column, not at the match. Keeping those separate is what stops a diagnostic session turning into an improvised cleaning step that hides the evidence.

  • Why compute the gap in both directions instead of only the side that lost rows?
    They answer different questions. Values only on the fact side are records that lost their partner, which is the loss. Values only on the reference side are entries nobody used, which is ordinary and expected. Looking one way makes a healthy reference overhang look like a defect; looking both ways also shows whether the two sides are symmetric, which distinguishes a whole-column difference from scattered bad records.
  • You find 12,000 values stranded on one side and 3 on the other. What does that shape suggest?
    Something that touched every value in one column: a changed representation, padding applied on the way out, or leading zeros lost. Twelve thousand values are not typing errors, and three stranded reference entries is background noise. A per-record defect would instead scatter a small number on both sides, so the asymmetry is the signal.
  • The two key sets share almost everything, yet the match still lost a tenth of the rows. What now?
    Then the loss is concentrated in a few key values that carry many rows each, rather than in many values carrying one row each. Weight the stranded values by how many rows on the fact side use them; a single unmatched code belonging to the largest producer explains a tenth of the table on its own.

saying these in an interview costs you the question

  • Scrolls the result looking for something that looks wrong.
  • Reports only the output row count, never how many keys failed.
  • Concludes the data is fine because a few sampled codes do pair.
  • Compares the two inputs' row counts instead of their key value sets.
  • Treats reference entries nobody used as the defect.
  • Stops at the counts without ever looking at a stranded value.