Design a recent user activity query pipeline
PremiumData pipelineMedium
- Multi resolution rollups
- Event time windows
- Deduplication
Quick answer
This data pipeline interview question asks you to design a recent user activity query pipeline. Aim for a design that moves data from sources to consumers reliably and on time, then explain ingestion, processing, storage and trade-offs. Interviewers want to see how you handle volume, late or bad data and failures along the way.
The problem
Design a recent user activity query pipeline that ingests app events and answers per-user questions such as how many failed logins in the last 10 minutes, hour or day. The hard parts are durable ingestion, time-bucketed storage, duplicate events, late events and saying how fresh each answer is.
Three consumers read it, always for one user at a time: a risk rules service (for example, block after 5 failed logins in 10 minutes), a support tool that shows a user timeline, and the recent activity page in the app. Nobody asks for totals across all users.
What must a recent user activity query pipeline do?
- Windowed counts: For one user, return counts per activity type over the last N minutes (up to 120), N hours (up to 48) or N days (up to 90), ending now.
- Freshness field: Every answer includes complete_through, the event time up to which counts are complete, and whether late events may still change it.
How well must it do it?
- Durable ingestion: An event acknowledged to the client is never lost, even if a processor, broker or storage node fails.
- Exact counts: Client retries never double count; counts match a batch recount from the raw lake.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- Multi resolution rollups
- Event time windows
- Deduplication
- Late data
- Watermarks
- Kafka
How you’ll practise
- Requirements
- Data volume
- Ingestion
- Storage and processing
- Serving and freshness
- Wrap-up
Lala, personalized to you, gives a hint when you ask and reviews your design when you are ready.
What do interviewers look for?
In any data pipeline design round.
- Clarifies sources, consumers and freshness needs
- Chooses batch, streaming or both with reasons
- Handles late, duplicate and malformed records
- Makes each stage safe to retry
- Explains storage formats and partitioning
- Plans monitoring, backfills and data quality checks
Questions people ask about this interview question
- Which design type is this question?
- It is a data pipeline question. You design a recent user activity query pipeline and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Medium. Entry-level engineers can practise it, but it expects you to reason about requirements and trade-offs, not only list components.
- What should I cover first?
- Start with the data sources, the consumers and how fresh the data must be. Agree on scope with the interviewer before you design anything.
- Can I practise it for free?
- It is a premium question. Sign up and unlock it once for 10 credits; it then stays unlocked. When you finish, Lala, your personalized AI mentor, can review your design for 30 credits.
Related questions
Updated · Written by the SevyDevy team