skip to content

Why can two different real account numbers end up with the same masked value?

level: seniorimportance: nice to knowfreq 22%

answer

  1. More real values than available outputs
  2. The field's width caps the outputs
  3. Duplicates start well before the space fills
  4. Two customers becoming a single row
  5. Distinct counts before and after the job

basics

~20 s

Substitutes come from a finite output space, so two inputs can land on one value - a narrow field, a truncated result, a small lookup set, or two spellings normalised into one. Downstream, two customers silently become one.

solid answer

~50 s

A substitution maps a large set of real values into a set of replacements, and nothing guarantees it is one-to-one. Four causes account for almost all of it: a **lookup set** far smaller than the population; a **field too narrow** to hold distinct outputs, so a format-preserving replacement is squeezed into the available digits; a derived value **truncated** to fit; and **normalisation collapsing** two different real values into one canonical input. Random draws collide long before a space is full, so the danger arrives far earlier than the field width suggests. The damage is that two customers merge into one row in the copy - per-customer totals mix two people, distinct-customer counts fall, and a test asserting an account has three orders sees seven. **Detect it in the job that caused it**: compare distinct input values against distinct output values per field and fail the copy on any shrinkage.

code

pseudocode · 11 lines
pseudocode
# runs inside the job that produces the copy, per field records relate on
for field in relating_fields:
    source_distinct = count_distinct(source[field])
    output_distinct = count_distinct(output[field])

    if output_distinct < source_distinct:
        merged = source_distinct - output_distinct
        fail("collision in " + field + ": " + merged + " real values merged")

# and the population the copy claims to hold
report(field, source_distinct, output_distinct, max_rows_per_value(output, field))

go deeper

for a junior

Know that a replacement value can repeat: two different real values may end up sharing one substitute, especially when the replacements come from a short list of plausible names.

for a middle

Explain the causes in mechanical terms - a small lookup set, a narrow field, truncation, over-eager normalisation - and why duplicates appear long before the output space is anywhere near full.

for a senior

Show the diagnosis and the guard. Describe what a merged pair does to counts, totals and row-count assertions, and put a distinct-count comparison inside the job that produces the copy rather than waiting for a test to trip over it.

for a principal

Own the trade-off between a value that still looks and parses like the real thing and one with enough distinct outputs to stay unique. Decide where collisions are acceptable, record that decision, and make the population figures visible with every copy.

## Collisions are ordinary, not exceptional Replacement takes every real value in a column and maps it onto a value from some set of possible outputs. Nothing about that operation promises the map is one-to-one, and two effects make duplicates far likelier than intuition suggests. **The pigeonhole effect.** If the possible outputs number fewer than the distinct inputs, duplicates are certain. Replacing the surnames of two hundred thousand customers from a list of two thousand plausible surnames guarantees that roughly a hundred customers share each name. Nobody is surprised by this one. **The birthday effect, which does surprise people.** Even when the output space is far larger than the population, randomly distributed draws start colliding at around the square root of the space. Suppose the field holds eight digits - one hundred million possible values - and there are three million distinct account numbers to replace. The space is more than thirty times the population, so it feels safe; in fact duplicates are effectively certain, because the number of pairs that could collide grows with the square of the population. The rule of thumb worth carrying: **collisions become likely once the population approaches the square root of the output space**, so a space needs to be enormously larger than the data, not merely larger. ## The four ways it actually happens 1. **A lookup set smaller than the population.** Names, cities and job titles are usually replaced from a curated list. The list is always small, so repetition is guaranteed and normally harmless - until something is related on that field. 2. **A narrow field holding a format-preserving replacement.** Keeping the output valid for parsers and check-digit rules means keeping it inside the field's length and alphabet, which caps the number of distinct outputs regardless of how large the transformation's own output was. 3. **Truncation to fit.** The transformation produces a long value and the job cuts it down to the field's width. Every discarded character multiplies the collision rate, and this is the cause most often added late, as a quick fix for a load that was rejecting rows. 4. **Normalisation collapsing distinct inputs.** Canonicalising before transforming is necessary for consistency, but if it is too aggressive - stripping leading zeros, folding away a suffix, ignoring a branch prefix - two genuinely different real values become one canonical input and are then guaranteed to share an output. ## What a collision corrupts | Symptom in the copy | What it looks like to the engineer | |---|---| | Two customers share one identifier | One customer with twice the history | | Per-customer aggregates | Totals mixing two people's records, so a calculation appears wrong | | Distinct-customer counts | A population smaller than the source, quietly skewing any volume-based test | | A test asserting a fixed number of related rows | Intermittent failures on the few merged accounts, passing everywhere else | | A separation-of-access test | A check that one customer cannot see another's data becomes meaningless when both are the same row | | A load with a uniqueness rule | Rejected rows - the loud, and therefore lucky, outcome | The pattern to notice is that most outcomes are **silent**. The copy loads, the suite mostly passes, and the few tests that fail look like flaky product defects. An engineer can spend a day inside application code before anyone thinks to question the data. ## Detect it in the job that produced it The check is cheap and belongs in the job, not in the test suite: - For every field that relates records, count distinct values in the source and distinct values in the output. They must be equal. Any shortfall is a collision count, and it names the field. - Compare the maximum number of rows per identifier before and after. A jump means two populations merged under one value. - Keep the uniqueness rule enabled when loading the copy. A rejected load is a good day; a silently merged pair is a bad month. - Report the numbers with the copy, so a team can see that the population it is testing against is the population it expects. Inferring a collision later - from a strange total, from a test that fails only for one account - is the expensive path, because the evidence has to be traced back through the copy to the job that made it. ## What to do when the check fires Widen the output space first: use the full field width, stop truncating, and prefer a wider related field where the schema allows. Where the field genuinely cannot hold enough distinct values, combine a plausible prefix from the lookup set with a derived suffix, so the value still reads naturally but carries enough distinct outputs. Where neither is possible, accept the collision **only** for fields nothing is related on, and record the decision, so the next engineer who reads a duplicated surname knows it was chosen rather than missed.

  • Which fields deserve a collision check, and which honestly do not?
    Any field two datasets are related on, and any field a test asserts uniqueness or a row count on. A surname or a city replaced from a small list will always repeat, and that is fine as long as nothing joins on it. The check follows the relationships, not the sensitivity of the field.
  • A load into the masked copy rejects rows for duplicate identifiers. Good news or bad?
    Good news, awkwardly delivered. A rejection is the collision surfacing loudly at the moment it can still be fixed cheaply. The bad version is the copy with no uniqueness rule, where the two populations merge silently and the first symptom is a confusing total or an intermittent failure weeks later.

Sorting a thousand letters into a hundred pigeonholes: long before the holes are full, two letters have to share one, and whoever reads that hole sees a single correspondent.

saying these in an interview costs you the question

  • Assumes a one-way transformation cannot produce duplicates
  • Believes a large output space makes collisions impossible
  • Truncates the substitute to fit the field and moves on
  • Only finds collisions when a strange test failure appears
  • Thinks no rejected rows proves no values merged