Interview questions
Duplicates and survivor rules
junior
1. What is the grain of a table, and why does it matter for deduplication?
What it tests: Core vocabulary and its consequences.
Model answer and scoring
The grain is what one row represents, for example one line of one order. The columns that identify that thing form the key.
Deduplication must use that key. If the grain is (order_id, line_id) and I deduplicate on order_id, I delete real lines of multi-line orders.
Strong answers
- Defines grain and links it to the dedupe key.
Red flags
- Deduplicates on all columns or on whichever column looks unique.
Practise it: Deduplicate order lines at the right grain
mid
2. Three systems send the same account with different values. How do you choose which record survives?
What it tests: Deterministic survivor rules.
Model answer and scoring
I agree a survivor rule with the data owner and make it deterministic: for example the latest update as an instant, then the higher priority, then the lowest id as a final tie-break.
Timestamps are compared as instants (not text) and priorities as numbers. The losing records go to an exception table with a reason so the decision is auditable.
Strong answers
- A full ordering ending in a unique tie-break.
- Typed comparisons.
Red flags
- "Keep the first one" without defining first.
Practise it: Pick one surviving record per account
mid
3. Two identical rows appear in a transactions file. Should you remove one?
What it tests: Whether you treat exact repeats as errors by default.
Model answer and scoring
Not automatically. Two identical rows can be two real purchases of the same item at the same price. I check the grain: if there's a transaction id and both rows share it, one is a duplicate; if there's no id, I ask the source whether repeats are possible before deleting anything.
Strong answers
- Asks about grain and source behaviour before deleting.
Red flags
- Always runs DISTINCT.
senior
4. Marketing wants customer records merged when names are similar. How do you respond?
What it tests: Entity resolution judgement and risk communication.
Model answer and scoring
I'd explain that names are weak evidence: spellings vary and different people share names, and a false merge mixes two customers' orders and consent. I propose merging on strong identifiers such as a normalized email, and sending similar-name pairs without a shared identifier to a review queue.
I'd report how many records each rule merges so the business can see the trade-off.
Strong answers
- Distinguishes strong and weak evidence.
- Proposes review for uncertain matches.
Red flags
- Applies fuzzy matching with an arbitrary threshold and merges automatically.
Practise it: Resolve customer records by normalized email
junior
5. Your dedup keeps version 9 over version 10. What happened?
What it tests: Type awareness in ranking.
Model answer and scoring
The version column was compared as text, and "9" sorts after "10" lexically.
I convert versions to integers before ranking and add a unique tie-break, then recheck the survivors.
Strong answers
- Identifies text comparison.
- Converts before sorting.
Red flags
- Blames the database.
Practise it: Deduplicate order lines at the right grain
mid
6. Why not simply delete duplicate rows?
What it tests: Evidence and reconciliation.
Model answer and scoring
Deleted rows can't be audited. If the rule was wrong, you can't recover what was lost, and counts no longer reconcile.
I keep the losers in an exception report with the reason duplicate and the survivor's key, so input rows equal survivors plus exceptions.
Strong answers
- Mentions auditability and reconciliation.
Red flags
- Treats duplicates as noise to throw away.
Practise it: Deduplicate order lines at the right grain
mid
7. Two customer rows differ only by a trailing space in the name. Duplicates or not?
What it tests: Separating normalization from identity.
Model answer and scoring
After the declared normalization (trim) they have the same name, but a name alone doesn't prove identity.
If they share a strong identifier such as a customer id or normalized email, I deduplicate by the survivor rule; otherwise I route them to review.
Strong answers
- Normalizes first, then requires a strong key.
Red flags
- Merges on name equality.
Practise it: Resolve customer records by normalized email
junior
8. How do you keep the latest record per account when timestamps have different offsets?
What it tests: Comparing instants.
Model answer and scoring
I parse the timestamps with their offsets into UTC instants and order by the instant, not by the text.
I add a deterministic tie-break such as priority and then the smallest id.
Strong answers
- Converts to instants.
- Deterministic tie-break.
Red flags
- Sorts timestamp strings.
Practise it: Pick one surviving record per account
senior
9. A nightly job sometimes loads the same file twice. How do you make dedup safe?
What it tests: Idempotent loading.
Model answer and scoring
I record each delivery with a checksum and refuse to load the same file twice, and I deduplicate at the defined grain with a deterministic survivor rule.
Because the pipeline rebuilds from raw inputs, rerunning produces the same output instead of doubling rows.
Strong answers
- Checksums or delivery tracking.
- Rebuild from raw; deterministic rule.
Red flags
- Manual cleanup after the fact.
junior
10. How do you measure how many duplicates a table has?
What it tests: Basic profiling.
Model answer and scoring
I define the key that should be unique, then count rows minus distinct keys, and list keys with more than one row.
I also check whether duplicates are exact copies or differ in some columns, because that changes the rule.
Strong answers
- Defines the key first.
- Distinguishes exact copies from conflicts.
Red flags
- Counts fully identical rows only.
Model answers are original study material for practice. They are not employer-endorsed and don't guarantee an outcome.