Joins and referential integrity

1. junior

   1. You join orders to customers on customer_key. Some orders have no key. What happens in SQL, and what happens in pandas?

   What it tests: Whether you know NULL semantics differ between SQL and pandas merges.

   Model answer and scoring

   In SQL a join condition with NULL is never true, so an order with a missing key finds no customer; with a LEFT JOIN it keeps the order with missing customer columns.

   pandas merge matches missing keys to each other, so if the customers table also has a row with a missing key, those orders get that row's attributes. I remove missing keys from the lookup side before merging, or check with indicator=True.

   Strong answers

   • States both behaviours precisely.
   • Mentions a guard and a check (row counts, indicator).

   Red flags

   • Assumes both tools behave the same.
   • Suggests filling missing keys with a placeholder like 0 that could match real data.

   Practise it: Join orders to customers without matching blanks

2. mid

   2. Revenue doubled after you joined orders to shipments. How do you find and fix the cause?

   What it tests: Understanding of join cardinality and fan-out.

   Model answer and scoring

   A doubled total usually means a one-to-many join: an order with two shipments appears twice. I check the cardinality: count rows before and after, and count shipments per order.

   The fix depends on the question. If I need order revenue, I aggregate shipments to one row per order before joining, or join only the columns that are one-to-one. In pandas I add validate="many_to_one" so a future fan-out fails loudly.

   Strong answers

   • Checks row counts before and after.
   • Aggregates the many side or validates cardinality.

   Red flags

   • Uses DISTINCT on the result without understanding why rows multiplied.

3. junior

   3. Some payments reference account numbers that don't exist in the accounts table. What do you do with them?

   What it tests: Referential integrity and exception handling instead of silent drops.

   Model answer and scoring

   They are orphans. I don't drop them with an inner join, because the totals would quietly shrink. I quarantine them with a reason, such as unknown_account, separate from payments with no account at all (missing_account), and report the counts.

   Then I ask the data owner whether the accounts table is late or the account numbers are wrong.

   Strong answers

   • Distinguishes missing from unknown keys.
   • Keeps orphans in an exception report.

   Red flags

   • Uses an inner join and moves on.

   Practise it: Referential integrity with explicit exceptions

4. senior

   4. How would you prove to a reviewer that a lookup join didn't change the number of rows or the totals?

   What it tests: Validation habits around joins.

   Model answer and scoring

   Before the join I record the row count and the sum of the key measures. After the join I compare: a lookup must keep the row count exactly (no fan-out, no drops) and leave sums unchanged.

   I also check that the lookup key is unique on the right side and report how many rows found no match. In SQL that's a few COUNT and SUM queries; in pandas, validate="many_to_one" plus indicator=True.

   Strong answers

   • Concrete checks: counts, sums, key uniqueness, unmatched count.

   Red flags

   • "I eyeball the result."

5. junior

   5. Before joining a lookup table, what do you check and why?

   What it tests: Habit of validating the lookup key.

   Model answer and scoring

   I check that the lookup key is unique and has no missing or padded values, because duplicate keys multiply rows and missing keys behave differently in SQL and pandas.

   I count rows and distinct keys, and in pandas I use validate="many_to_one" so a duplicate fails the merge.

   Strong answers

   • Checks uniqueness and nulls of the key.
   • Names a mechanism that fails loudly.

   Red flags

   • Joins first and inspects later.

   Practise it: Join orders to customers without matching blanks

6. junior

   6. When would an inner join silently lose data in a cleaning pipeline?

   What it tests: Understanding of join types and their effect on totals.

   Model answer and scoring

   An inner join drops every left row without a match, such as payments for an account missing from the master. The totals then look clean but are wrong.

   I use a left join, then route unmatched rows to an exception report with a reason, so input rows equal clean plus exceptions.

   Strong answers

   • Mentions dropped unmatched rows.
   • Uses left join plus exception reporting.

   Red flags

   • Says inner join is always safer.

   Practise it: Referential integrity with explicit exceptions

7. mid

   7. Orders must be joined to prices that change over time. How do you join them correctly?

   What it tests: Temporal (as-of) joins and validity ranges.

   Model answer and scoring

   The price table has a valid_from and valid_to per product. I join on product and on order date between valid_from and valid_to, with a declared rule for whether the end is inclusive.

   I then check that every order matched exactly one price row: zero means a gap in the price history, two means overlapping ranges, and both go to exceptions.

   Strong answers

   • Uses date ranges in the join condition.
   • Checks for gaps and overlaps.

   Red flags

   • Joins on product only and takes the latest price.

8. junior

   8. A join matches far fewer rows than expected, although the keys look identical. How do you investigate?

   What it tests: Debugging invisible differences.

   Model answer and scoring

   I compare lengths and code points of a few unmatched keys. Common causes are padding, invisible characters such as no-break spaces, different case, or one side stored as a number without leading zeros.

   I normalize both sides with the same declared rule (trim, case, text type) and recheck the match rate.

   Strong answers

   • Checks length/code points.
   • Normalizes both sides identically.

   Red flags

   • Uses fuzzy matching immediately.

   Practise it: Join orders to customers without matching blanks

9. senior

   9. Two tables both have repeated keys. A colleague wants to join them. What do you say?

   What it tests: Recognising many-to-many joins and their consequences.

   Model answer and scoring

   A many-to-many join produces every combination for a key, so row counts and totals explode in a way that has no business meaning.

   I'd ask what one output row should represent, then aggregate one side (or both) to that grain before joining, or use a bridge table that defines the valid pairs.

   Strong answers

   • Defines the target grain first.
   • Aggregates or uses a bridge table.

   Red flags

   • Adds DISTINCT to hide the explosion.

10. mid

    10. How do you report join quality to a stakeholder?

    What it tests: Communicating referential integrity.

    Model answer and scoring

    I report rows in, matched, unmatched by reason (missing key, unknown key) and rows out, plus the totals before and after.

    I name who can fix each kind of orphan, for example the billing team for unknown accounts.

    Strong answers

    • Gives counts and totals.
    • Assigns owners to fixes.

    Red flags

    • Says the join worked without numbers.

    Practise it: Referential integrity with explicit exceptions

Model answers are original study material for practice. They are not employer-endorsed and don't guarantee an outcome.