Question 3Data and SQL
How would you design a daily pipeline that feeds customer data into our product?
Tests basic data engineering: scheduling, failures, re-runs and monitoring.
“Data Engineering & Integration: Proficiency in complex SQL, ETL/ELT pipeline design, and experience building system integrations connecting AI solutions to enterprise and telecom data sources.”Senior Engineer, Forward Deployment · T-Mobile
The answerFour steps
- 1
Extract with timestamps or change tracking, not full dumps, when possible
Say it like this
“I'd pull only rows where updated_at is newer than the last successful run, with an hour of overlap so late writes aren't missed.”
Why it scores
Incremental pull with an overlap. Sounds like someone who's been bitten by late rows.
- 2
Transform in steps you can re-run without creating duplicates
Say it like this
“Each run writes to a staging table, then merges into the main table on store_id and sku. Re-running Tuesday replaces Tuesday, with no duplicates.”
Why it scores
Safe to re-run by design. Re-runs are routine, so they can't create a mess.
- 3
Validate row counts and key fields before loading
Say it like this
“Before the merge I check that the row count is within 30% of yesterday, no SKUs are null, and no prices are negative. If a check fails, nothing loads and yesterday's data stays live.”
Why it scores
Checks gate the load. Bad data that never lands is cheaper than a cleanup.
- 4
Alert a person on failure, with enough detail to fix it
Say it like this
“The alert goes to a person in Slack with the failed check, the counts and the command to re-run, not just “job failed.””
Why it scores
An alert someone can act on at 7am without opening the code.
StrongAll together
About a minute, out loud. The marked parts are the four steps; hover one to find it above.
Say a retailer sends us store inventory every night, about 2 million rows. I'd pull only rows where updated_at is newer than the last successful run, with an hour of overlap so late writes aren't missed.1 Each run writes to a staging table, then merges into the main table on store_id and sku. Re-running Tuesday replaces Tuesday, with no duplicates.2 Before the merge I check that the row count is within 30% of yesterday, no SKUs are null, and no prices are negative. If a check fails, nothing loads and yesterday's data stays live.3 The alert goes to a person in Slack with the failed check, the counts and the command to re-run, not just “job failed.”4
WeakThe answer that fails
Sounds fine. Common. Scores low.
“I'd use Airflow to schedule a nightly job that pulls the full export, transforms it with pandas and writes it to our database, with retries if a task fails.”
Interviewer's note
Names tools, skips the hard parts. A re-run duplicates data, and a half-empty export loads without anyone noticing.
PracticeTwo minutes, then they push back
Your turn
How would you design a daily pipeline that feeds customer data into our product?
Answer like you're in the room: about two minutes, no notes. Say it out loud, even alone. It feels different from thinking it. Then check your answer against what interviewers score.