A revenue total jumped after a step matched two tables, yet the distinct customer count is unchanged - which checks belong either side of that match?

seniorshould knowfreq 57%

answer

one number grew, another did not
copies raise sums, not distinct counts
assert uniqueness on the side before
predicted count and an invariant after

basics

~20 s

A sum that grew while a distinct count held steady means rows were copied, not added. Bracket the match: before it, assert the side that should be unique really is unique on the key; after it, compare the output row count and an unchanged total against what you predicted.

solid answer

~50 s

That pair of numbers is a fingerprint. Copied rows raise every additive total while leaving any distinct count untouched, so a repeated key value on one side of the match is the first hypothesis. The repair is a bracket around that one step. Before: state which side is supposed to hold one row per key value and assert it - its row count must equal its distinct key count - and where the tool lets you declare the expected relationship on the call itself, declare it and take an exception instead of a number. After: check the output row count against the count you predicted from the occurrence figures, and re-measure one quantity that the match cannot legitimately change, such as the number of distinct orders or the revenue total carried in from one side. Both checks are three lines, they run on every execution, and they fail at the seam where the assumption lives rather than in a report a week later.

That pair of numbers is a fingerprint. Copied rows raise every additive total while leaving any distinct count untouched, so a repeated key value on one side of the match is the first hypothesis. The repair is a bracket around that one step. **Before**: state which side is supposed to hold one row per key value and assert it - its row count must equal its distinct key count - and where the tool lets you declare the expected relationship on the call itself, declare it and take an exception instead of a number. **After**: check the output row count against the count you predicted from the occurrence figures, and re-measure one quantity that the match cannot legitimately change, such as the number of distinct orders or the revenue total carried in from one side. Both checks are three lines, they run on every execution, and they fail at the seam where the assumption lives rather than in a report a week later.

code

pseudocode · 12 lines

pseudocode

# before: the side that should be unique must actually be unique
assert row_count(customers) == distinct_count(customers.customer_id)

# remember what this step is not allowed to change
orders_rows   = row_count(orders)
orders_amount = sum(orders.amount)

result = match_on_key(orders, customers, "customer_id", KEEP_ORDERS_WHOLE)

# after: with a unique reference and every order kept, both hold exactly
assert row_count(result)   == orders_rows
assert sum(result.amount)  == orders_amount

go deeper

for a junior

Recall that copying a row raises any sum over it but leaves a count of distinct entities unchanged, so those two numbers moving apart points at duplicated rows rather than at new data.

for a middle

Explain which side you would measure and how: a side is unique on the key exactly when its row count equals its distinct count over the columns being compared.

for a senior

Demonstrate the bracket as code that runs every night - an assertion before, a predicted count and an invariant after - and say why one number after the step is not enough.

for a principal

Weigh what should happen when the assertion fires at three in the morning, and who decides whether a change in the upstream data is a defect or a new normal.

The symptom names the defect

Two numbers moved differently, and that asymmetry is diagnostic:

an additive total - revenue, quantity, minutes - counts each row it sees, so duplicating a row duplicates its contribution;
a distinct count of an entity counts each value once however many rows carry it, so duplicating a row leaves it exactly where it was.

When the first grows and the second does not, no new entities arrived. Existing rows were copied. Inside a match, there is only one mechanism that copies rows: a key value that occurs more than once on one side, pairing with every occurrence on the other. That is why the diagnosis can be made before opening the data, and why naming the two numbers apart matters - 'the count went up' is not a symptom, it is four different numbers wearing one word.

The check before the match

The assumption being violated is almost always unwritten: the reference holds one row per customer. Writing it down is the check.

Decide which side is supposed to be unique on the key columns being compared.
Assert that its row count equals its distinct count over exactly those columns.
Run that assertion in the step, not in a notebook you ran once in March.

This catches the defect one line before it happens, at the point where the belief is held, and it names the offending table rather than leaving a number to be traced backwards through everything downstream.

Declaring the relationship instead, where the tool offers it

The after-the-fact count comparison is the portable technique: it works in every tool because it only uses counting. Some tools offer something better - the expected relationship between the two sides is an argument to the match itself, so a violation raises at the call instead of returning a plausible table. Where that exists, prefer it: it removes the gap between the assumption and its test. Where it does not, or where the tool merely emits a warning that a log will swallow, the bracket above is what you have. The important habit is to know which of the two you are relying on, because assuming a declaration you never made is how the check quietly is not there.

The check after the match

One number is not enough, because the output row count is a net of copies and drops that can cancel. Measure two:

the output row count against the predicted one, computed from the per-key occurrence figures;
an invariant - a quantity the match is not allowed to change. Distinct orders in equals distinct orders out. A revenue total carried in from one side equals the same total out, under a shape that keeps that side whole and a unique other side.

An invariant is stronger than a row count because it survives the cancelling case: the step that dropped 50 rows and copied 40 shows a near-normal row count and an obviously wrong distinct count.

When the expansion is correct

Not every multiplication is a defect. If the second table genuinely holds three shipment rows for one order, a match that returns three rows per order is doing what it was asked. The defect in that case is downstream: a total computed over the expanded table counts each order's amount three times. Two honest repairs exist, and the choice is yours to make explicitly - reduce the repeating side to one row per key before the match, or keep the expansion and compute the total from the unexpanded side. What is not acceptable is leaving both the expansion and the naive total in place because the number looked plausible.

What the bracket costs

Two counts per side before the step, and two after. Cheap next to the work of the match itself.
A step that can now fail. That is the point, but it is also a real cost: somebody is woken by a violation that the business considers a legitimate change in the data.
A number written down in advance. The prediction is the part people skip, and it is the part that turns the after-check from a reading into a test.

The habit worth taking away is smaller than it sounds: before a match, know which side is unique and assert it; after a match, know what the count should be and check something that must not have changed.

## The symptom names the defect Two numbers moved differently, and that asymmetry is diagnostic: - an **additive total** - revenue, quantity, minutes - counts each row it sees, so duplicating a row duplicates its contribution; - a **distinct count** of an entity counts each value once however many rows carry it, so duplicating a row leaves it exactly where it was. When the first grows and the second does not, no new entities arrived. Existing rows were copied. Inside a match, there is only one mechanism that copies rows: a key value that occurs more than once on one side, pairing with every occurrence on the other. That is why the diagnosis can be made before opening the data, and why naming the two numbers apart matters - 'the count went up' is not a symptom, it is four different numbers wearing one word. ## The check before the match The assumption being violated is almost always unwritten: *the reference holds one row per customer*. Writing it down is the check. 1. Decide which side is supposed to be unique on the key columns being compared. 2. Assert that its row count equals its distinct count over exactly those columns. 3. Run that assertion in the step, not in a notebook you ran once in March. This catches the defect one line before it happens, at the point where the belief is held, and it names the offending table rather than leaving a number to be traced backwards through everything downstream. ## Declaring the relationship instead, where the tool offers it The after-the-fact count comparison is the **portable** technique: it works in every tool because it only uses counting. Some tools offer something better - the expected relationship between the two sides is an argument to the match itself, so a violation raises at the call instead of returning a plausible table. Where that exists, prefer it: it removes the gap between the assumption and its test. Where it does not, or where the tool merely emits a warning that a log will swallow, the bracket above is what you have. The important habit is to know which of the two you are relying on, because assuming a declaration you never made is how the check quietly is not there. ## The check after the match One number is not enough, because the output row count is a net of copies and drops that can cancel. Measure two: - **the output row count against the predicted one**, computed from the per-key occurrence figures; - **an invariant** - a quantity the match is not allowed to change. Distinct orders in equals distinct orders out. A revenue total carried in from one side equals the same total out, under a shape that keeps that side whole and a unique other side. An invariant is stronger than a row count because it survives the cancelling case: the step that dropped 50 rows and copied 40 shows a near-normal row count and an obviously wrong distinct count. ## When the expansion is correct Not every multiplication is a defect. If the second table genuinely holds three shipment rows for one order, a match that returns three rows per order is doing what it was asked. The defect in that case is downstream: a total computed over the expanded table counts each order's amount three times. Two honest repairs exist, and the choice is yours to make explicitly - reduce the repeating side to one row per key before the match, or keep the expansion and compute the total from the unexpanded side. What is not acceptable is leaving both the expansion and the naive total in place because the number looked plausible. ## What the bracket costs - Two counts per side before the step, and two after. Cheap next to the work of the match itself. - A step that can now fail. That is the point, but it is also a real cost: somebody is woken by a violation that the business considers a legitimate change in the data. - A number written down in advance. The prediction is the part people skip, and it is the part that turns the after-check from a reading into a test. The habit worth taking away is smaller than it sounds: **before a match, know which side is unique and assert it; after a match, know what the count should be and check something that must not have changed.**

Why is a distinct count a better after-check than the output row count alone?
Because the row count is a net. Copies and dropped unpartnered rows happen in the same step and can offset, so a step that lost 50 rows and duplicated 40 looks almost normal. A distinct count of the entity the step must preserve does not offset: it is wrong the moment rows vanish, and unmoved when rows are copied, which separates the two failures.
The reference genuinely holds two rows per customer and both are needed. What do you check instead?
Then the expansion is intended and uniqueness is the wrong assertion. Predict the expanded count from the occurrence figures and assert that instead, and move the invariant to a quantity the expansion cannot touch - distinct orders in against distinct orders out - while computing any additive total from the side that was not expanded.
Where should the assertion live if the same two tables are matched in three different steps?
At each seam. The property being asserted is not 'this table is clean' but 'this step assumed one row per key value here', and those assumptions can differ: one step may legitimately want the expansion another forbids. An assertion sitting next to the match it protects also names the step in its failure message.

saying these in an interview costs you the question

Concludes new customers appeared because the revenue total grew
Checks the output row count only and calls the step verified
Puts the uniqueness assertion after the match rather than before
Treats every expansion as a defect, including a genuine one-to-many
Assumes the tool would have raised an error if the match were many-to-many

A revenue total jumped after a step matched two tables, yet the distinct customer count is unchanged - which checks belong either side of that match?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The symptom names the defect

The check before the match

Declaring the relationship instead, where the tool offers it

The check after the match

When the expansion is correct

What the bracket costs

▾common follow-ups3 questions

▾red flags5 flags

The symptom names the defect

The check before the match

Declaring the relationship instead, where the tool offers it

The check after the match

When the expansion is correct

What the bracket costs

answer

go deeper