Two joined inputs are each cut to a random one percent independently, and the join comes back almost empty - why?

middlemust knowfreq 51%

answer

a pair needs both of its rows
p squared, not p
sample keys, not rows
one key set applied to every input
match rate in the cut against match rate in the whole

basics

~20 s

Each row survives on its own side only, so a kept row on one side keeps its counterpart on the other with about one percent probability - roughly one in ten thousand pairs survives. Draw one key set and restrict every input to it instead.

solid answer

~40 s

Cutting rows independently treats the two inputs as unrelated, but a join is a statement about shared keys. If each side keeps a row with probability p, a pair survives only when both of its rows do - probability p squared - so at one percent the join retains about one ten-thousandth of its pairs and looks empty. The fix is to cut by key rather than by row: draw the key set once, then restrict every joined input to exactly that set, keeping all rows for each key kept. Every pair that existed among those keys still exists, per-key aggregates in the cut equal the real ones for those keys, and the development run behaves like a smaller version of the real one rather than like a different problem.

Cutting rows independently treats the two inputs as unrelated, but a join is a statement about shared keys. If each side keeps a row with probability p, a pair survives only when both of its rows do - probability p squared - so at one percent the join retains about one ten-thousandth of its pairs and looks empty. The fix is to cut by key rather than by row: draw the key set **once**, then restrict every joined input to exactly that set, keeping all rows for each key kept. Every pair that existed among those keys still exists, per-key aggregates in the cut equal the real ones for those keys, and the development run behaves like a smaller version of the real one rather than like a different problem.

code

sql · 21 lines

sql

-- 1. Draw the key set ONCE, from the keys themselves, not from rows.
CREATE TABLE sampled_keys AS
SELECT customer_id
FROM   (SELECT DISTINCT customer_id FROM orders) AS all_keys
WHERE  key_is_selected(customer_id)          -- any deterministic 1-in-N test on the key
UNION
SELECT customer_id FROM keys_kept_on_purpose; -- the heaviest key, one of each rare shape

-- 2. Restrict EVERY input joined on that key to the same set.
CREATE TABLE orders_cut AS
SELECT o.* FROM orders o JOIN sampled_keys s ON o.customer_id = s.customer_id;

CREATE TABLE customers_cut AS
SELECT c.* FROM customers c JOIN sampled_keys s ON c.customer_id = s.customer_id;

CREATE TABLE addresses_cut AS
SELECT a.* FROM addresses a JOIN sampled_keys s ON a.customer_id = s.customer_id;

-- 3. Sanity check: the match rate in the cut should resemble the match rate in the whole.
SELECT COUNT(c.customer_id) * 1.0 / COUNT(*) AS match_rate
FROM   orders_cut o LEFT JOIN customers_cut c ON o.customer_id = c.customer_id;

go deeper

for a junior

Remember that a pair survives only when both of its rows survive, so independent draws multiply: one percent on each side leaves about one ten-thousandth of the join.

for a middle

Explain the repair mechanically - one key set derived by a deterministic rule on the key value, then every joined input restricted to that set, all rows for each key kept.

for a senior

Demonstrate the check you run before trusting a cut: compare the join match rate in the cut against the match rate measured on the full input, and treat a large gap as a broken cut.

for a principal

Own the trade: whole keys give assertable per-key totals but let a heavy key inflate the cut, so the team needs a stated rule for capping it and for what the cut may never be used to prove.

The arithmetic of two independent draws

A join is not a property of one input; it is a property of the keys two inputs share. Cutting each side independently ignores that relationship, and the arithmetic is unkind. If each side keeps a given row with probability p, then a matching pair survives the cut only if both of its rows do. For independent draws that is p squared:

p = 0.1 keeps about 1 percent of pairs
p = 0.01 keeps about 0.01 percent of pairs - one in ten thousand
p = 0.001 keeps essentially nothing

So a cut that looks generous on each side individually produces a join result that is empty or nearly so. The developer then sees zero rows, assumes the transformation is broken, and spends the afternoon debugging code that is correct. The opposite mistake is just as common: the join is an outer one, the counterparts are missing rather than the pairs, and the output fills with nulls that look like a data-quality problem in the source.

Cut by key, once, for every input

The repair is to make the key set the unit of sampling rather than the row:

Derive the key set once. Take the distinct keys of the input the join is anchored on, apply a deterministic rule to each key value - a stable one-in-N test that always selects the same keys - and keep the keys it selects. Determinism matters: the cut must be reproducible by a colleague and stable between rebuilds.
Add the keys you must not lose. The key carrying the most records goes in on purpose. So does at least one key exhibiting each rare shape you know about.
Restrict every joined input to that same set. Each input keeps every row whose key is in the set, and no others.

Because the selection rule depends only on the key value, the two sides make the same decision about the same key without coordinating, and every pair among the selected keys survives.

What whole keys buy that rows do not

property	independent row draws	one shared key set, whole keys
surviving pairs of a join	about p squared of them	all pairs among the kept keys
per-key aggregates	wrong, silently	identical to the real ones for the kept keys
counting distinct keys	roughly right	exactly right over the kept set
grouping and deduplication logic	under-exercised	exercised fully within a key
a key with exactly one record	usually dropped	kept if the rule selects it

The second column is the real prize. A cut of whole keys lets you assert a value, not merely that the job finished: the total for a kept key in the cut is the total that key will have in production. A row-level draw gives numbers that are wrong by an unknown factor, so the only available assertion is that nothing threw an error.

The edges this leaves

Inputs keyed on something else. An input joined on a different column - orders selected by customer, then payments joined on order identifier - is not covered by the customer key set. It needs its own set, derived from the rows already kept, and chaining that derivation is where a cut starts to grow.
Many-to-many joins. If both sides can have many rows per key, keeping whole keys keeps the whole cross product for that key. A single heavy key can therefore make the cut far larger than intended; this is a reason to know the key frequencies before selecting, not a reason to go back to rows.
Keys that are absent by design. Real inputs contain rows whose counterpart genuinely does not exist. The cut should keep some of those, because how the job treats a missing counterpart is behaviour worth testing - but it should be a kept case rather than an artefact of cutting.
Volume still does not transfer. The cut proves the join matches; it says nothing about what the join costs. Whether the matching rows have to be brought together over the network, and how expensive that is, depends on the runtime and the real data volume, and no cut reproduces it.

A cheap sanity check

Before trusting a new cut, run the join on it and compare two numbers: the fraction of left-side rows that found a counterpart in the cut, and the same fraction measured on the full input. If the full input matches 98 percent of rows and the cut matches 40, the cut is broken, not the code. That single comparison catches almost every version of this defect, and it costs one aggregate on each side.

## The arithmetic of two independent draws A join is not a property of one input; it is a property of the *keys two inputs share*. Cutting each side independently ignores that relationship, and the arithmetic is unkind. If each side keeps a given row with probability p, then a matching pair survives the cut only if both of its rows do. For independent draws that is p squared: - p = 0.1 keeps about 1 percent of pairs - p = 0.01 keeps about 0.01 percent of pairs - one in ten thousand - p = 0.001 keeps essentially nothing So a cut that looks generous on each side individually produces a join result that is empty or nearly so. The developer then sees zero rows, assumes the transformation is broken, and spends the afternoon debugging code that is correct. The opposite mistake is just as common: the join is an outer one, the counterparts are missing rather than the pairs, and the output fills with nulls that look like a data-quality problem in the source. ## Cut by key, once, for every input The repair is to make the *key set* the unit of sampling rather than the row: 1. **Derive the key set once.** Take the distinct keys of the input the join is anchored on, apply a deterministic rule to each key value - a stable one-in-N test that always selects the same keys - and keep the keys it selects. Determinism matters: the cut must be reproducible by a colleague and stable between rebuilds. 2. **Add the keys you must not lose.** The key carrying the most records goes in on purpose. So does at least one key exhibiting each rare shape you know about. 3. **Restrict every joined input to that same set.** Each input keeps every row whose key is in the set, and no others. Because the selection rule depends only on the key value, the two sides make the *same* decision about the same key without coordinating, and every pair among the selected keys survives. ## What whole keys buy that rows do not | property | independent row draws | one shared key set, whole keys | |---|---|---| | surviving pairs of a join | about p squared of them | all pairs among the kept keys | | per-key aggregates | wrong, silently | identical to the real ones for the kept keys | | counting distinct keys | roughly right | exactly right over the kept set | | grouping and deduplication logic | under-exercised | exercised fully within a key | | a key with exactly one record | usually dropped | kept if the rule selects it | The second column is the real prize. A cut of whole keys lets you assert a *value*, not merely that the job finished: the total for a kept key in the cut is the total that key will have in production. A row-level draw gives numbers that are wrong by an unknown factor, so the only available assertion is that nothing threw an error. ## The edges this leaves - **Inputs keyed on something else.** An input joined on a different column - orders selected by customer, then payments joined on order identifier - is not covered by the customer key set. It needs its own set, derived from the rows already kept, and chaining that derivation is where a cut starts to grow. - **Many-to-many joins.** If both sides can have many rows per key, keeping whole keys keeps the whole cross product for that key. A single heavy key can therefore make the cut far larger than intended; this is a reason to know the key frequencies before selecting, not a reason to go back to rows. - **Keys that are absent by design.** Real inputs contain rows whose counterpart genuinely does not exist. The cut should keep some of those, because how the job treats a missing counterpart is behaviour worth testing - but it should be a kept case rather than an artefact of cutting. - **Volume still does not transfer.** The cut proves the join *matches*; it says nothing about what the join costs. Whether the matching rows have to be brought together over the network, and how expensive that is, depends on the runtime and the real data volume, and no cut reproduces it. ## A cheap sanity check Before trusting a new cut, run the join on it and compare two numbers: the fraction of left-side rows that found a counterpart in the cut, and the same fraction measured on the full input. If the full input matches 98 percent of rows and the cut matches 40, the cut is broken, not the code. That single comparison catches almost every version of this defect, and it costs one aggregate on each side.

Why does the selection rule have to be deterministic rather than a random draw per input?
Because the two sides must reach the same verdict on the same key without exchanging anything. A rule computed from the key value does that by construction, and it also makes the cut reproducible: a colleague rebuilding it next month gets the same keys, so a failure found in the cut is still reproducible after the rebuild.
A one percent key set makes the cut far larger than one percent of rows. Why?
Because keys are not equal in weight. Selecting one percent of keys keeps one percent of the *keys* but whatever share of rows those keys carry, and if the heaviest key is among them - which you wanted - it brings all of its records. Choose the rate from the key frequencies you measured, and cap a dominant key by keeping a stated fraction of its rows, recording the factor so nobody reads its total as real.
Should the cut contain rows whose counterpart is genuinely missing in the source?
Yes, on purpose. Missing counterparts happen in production, and how the job handles them - dropping the row, emitting a null, raising an error - is behaviour worth exercising. The rule is that such rows are kept deliberately and recorded as such, so an empty or null-heavy result is read as a known property of the cut rather than as a defect.

saying these in an interview costs you the question

Sample both inputs at the same rate and the join will be fine
The join returning few rows just means the cut is small
Using the same random seed on each side makes the draws agree
Cut each input to the same row count so neither side dominates
Per-key totals in a row-level draw are close enough to assert on

Two joined inputs are each cut to a random one percent independently, and the join comes back almost empty - why?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The arithmetic of two independent draws

Cut by key, once, for every input

What whole keys buy that rows do not

The edges this leaves

A cheap sanity check

▾common follow-ups3 questions

▾red flags5 flags

The arithmetic of two independent draws

Cut by key, once, for every input

What whole keys buy that rows do not

The edges this leaves

A cheap sanity check

answer

go deeper