As asked
A downstream analyst reports that yesterday's signup count doubled. The source system says it emitted the correct number of events. How do you investigate?
Sample answer outline
Trace the count through each pipeline boundary: source extract, landing zone, staging table, transform, and mart. Look for retry behaviour, non-idempotent appends, joins that multiply rows, and missing uniqueness tests on event_id or user_id plus timestamp. Use run metadata to identify whether the same file or API page was processed twice. Fix the immediate data issue with a controlled dedupe or partition rebuild, then add tests and run-level safeguards. Interviewers want calm, evidence-based debugging rather than guessing that the analyst's query is wrong.
Expect these follow-ups
- How do you distinguish duplicate source events from duplicate loads?
- What test would have caught this before the analyst did?
- How do you explain the corrected metric to business users?
debuggingduplicatesdata-quality