Before matching a table to a lookup table on a key, what must you assert about the lookup side, and why beforehand?
answer
- prove it before you rely on it
- distinct combinations against row count
- state it over every match column
- fail at the match, not downstream
basics
~20 sAssert that the key columns identify a row: the number of distinct combinations of those columns equals that side's row count. Stated before the match, a failure names the table, the columns and the step instead of a row count that grew later.
solid answer
~50 sA lookup table is only a lookup if its key identifies exactly one row, and that is a claim, not a fact — the column may be named like an identifier and still repeat. So before the match I write a uniqueness assertion: a check, stated over a named set of columns, that no two rows share those values, expressed as `distinct_count(lookup, keys) == row_count(lookup)`. Stating it first is the point. Afterwards the only symptom is a result with more rows than the input, several steps from the cause, and ambiguous about which side is at fault. Before, the failure names the table, the exact columns and the line. Some matching facilities also let you declare up front how many rows each side may contribute, so a violation raises at the match itself; where no such facility exists you write the claim yourself. Either way the assertion stops at the failure — what to do about the repeated key is a separate decision.
code
pseudocode · 8 lineskeys = ["region_code", "valid_from"]
if distinct_count(regions, keys) != row_count(regions):
raise_error("lookup side repeats a key: " +
row_count(regions) + " rows, " +
distinct_count(regions, keys) + " distinct " + keys)
result = match_on(sales, regions, keys)go deeper
Recall that a lookup table is only a lookup if its key identifies one row, and that this is something you check rather than assume. The check compares distinct key combinations against the row count.
Explain the ordering: stated before the match, the failure names the table and the columns; taken afterwards, you have a row count that grew and no idea which side caused it. Note that the claim is over the combination of key columns.
Show where you put the claim so it protects every later match against that input, say what changes when the tool can declare a cardinality on the match itself and when it cannot, and stop at the failed assertion rather than silently resolving the repeat.
The judgment is which inputs carry standing claims and who owns them: a claim written once where a reference table is loaded protects every file that uses it, while the same claim copied into nine files rots in seven.
## The claim, stated precisely A **uniqueness assertion** is a check, stated over a named set of columns, that no two rows share those values. On a lookup table it is the claim that the key columns **identify a row**, and it is checkable with two numbers that any tool can produce: > the number of distinct combinations of the key columns == the row count of that table If those agree, every row has its own combination and the key identifies a row. If the distinct number is lower, at least one combination appears more than once. Note the word **combinations**: when the match uses two columns, the claim is over the pair. A key that is unique on each column separately can still repeat as a pair, and a key that repeats on one column can be perfectly unique as a pair — checking the columns one at a time answers a different question. ## Why it goes before the match, not after Both orderings detect the same underlying problem. They do not give you the same evidence. | when the claim is made | what a failure hands you | what remains to be worked out | |---|---|---| | before the match | this table, these columns, this line | the decision about the repeated key | | after, from the result's row count | the result is larger than its input | which side repeated, on which columns, and at which step | | not at all | a total that is too high, noticed by somebody downstream | everything | Three things make the earlier claim better: 1. **It names the cause, not a symptom.** A row count that grew is a symptom shared by several causes, and it appears at the step *after* the one that broke. 2. **It is a claim about an input, so it can be made once.** The lookup side is usually the small, slowly-changing table. Checking it where it is loaded protects every match made against it later in the file. 3. **It stops the run before the large intermediate exists.** A result you have to inspect to learn what went wrong may be considerably larger than either input. ## Declaring the expectation on the match itself Some matching facilities accept **a declared cardinality**: you tell the match up front how many rows each side may contribute — one to one, one to many — and a violation raises at the match rather than showing up as a row count later. Where a tool offers this, it is the tightest form of the claim, because it cannot drift away from the match it guards. Where a tool offers nothing of the kind, the assertion you write by hand is the same claim; it just sits a line earlier. Do not assume either behaviour: designs in this family genuinely differ, and a claim that quietly relies on the match to police itself is no claim at all on a tool that does not. ## When the lookup side is not unique and cannot be made unique This happens, and the answer is not to drop the check. It is to move the claim to whatever *is* true: - **Assert the output instead.** If the match is supposed to preserve the row count of the driving side, state that: the result's row count equals the input's row count. - **Narrow the key.** If the table is unique on the key *plus* a validity period or a source column, the match was written against the wrong key and the assertion has just told you so. - **Reduce the lookup first, then assert.** If the reduction is deliberate, it is a step like any other, and it gets its own claim. ## Where the claim stops The assertion detects; it does not resolve. Which columns count as defining a repeat, which occurrence should survive, whether the repeat is dropped, marked or merely counted — those are decisions about the data, made deliberately and visibly, and no assertion should make them silently on your behalf. Equally, the assertion does not explain *why* the key repeats: a source system that emits corrections as new rows, a load that ran twice, two systems whose identifiers collide. A failing uniqueness claim gives you the table, the columns and a number. That is the point at which a person decides, and it is an enormously cheaper place to be standing than in front of a total that is eleven percent too high with no idea which step produced it.
- Which columns should the uniqueness claim be stated over?Exactly the columns the match uses — all of them, as a combination, and no others. A claim over fewer columns tests something stricter than the match needs and will fail on data that is fine; a claim over more tests something weaker and will pass on data that breaks the match.
- The lookup table is not unique and you cannot change it. What claim do you write instead?Move the claim rather than deleting it. Either narrow the key until it does identify a row — often the missing column is a validity period or a source — or assert the property you actually need from the match: that the result's row count equals the driving side's row count. Both fail loudly at the step, which is what you were buying.
saying these in an interview costs you the question
- Checks the result's row count instead of the key beforehand
- Assumes a column named like an identifier must be unique
- States uniqueness one column at a time for a two-column key
- Reports the number of repeated keys and carries on
- Believes every matching facility can declare its expected cardinality
- Drops the claim because the lookup side turned out to repeat