How the interviewer opens it
“Design an ETL platform that extracts data from different sources, transforms it, and loads it into analytical storage. Users need to define and operate their data flows.”
The prompt is vague on purpose. The first minutes are for you. Reduce the prompt to the core features and agree on the numbers before you draw.
What to clarify first
- Scale and traffic
- Sources
- Target capabilities
- Transformations and scope
- Schedule and latency
- Consistency and availability
- Durability and retention
- Initial copies and backfills
- Schema changes
- Source protection and tenant isolation
The interviewer has the answers. In a SysRound round, Alex gives these answers, as a real interviewer does.
Scope of a strong answer
Core requirements
- Create and schedule data flows from supported sources to analytical tables.
- Apply SQL filters, projections, and type conversions.
- Transfer new, changed, and deleted records according to each source's capabilities.
- Recover failed work and handle changes to source schemas.
Usually out of scope
- Build a new execution engine.
- Serve analytical queries.
- Execute arbitrary user code.
- Support joins or aggregates.
- Provide atomic updates across different sources.
What interviewers ask about
- Data completeness
- Sources expose different update and deletion rules. The initial copy can overlap live changes, and source history has a limited lifetime.
- Partial failures and retries
- The source, workers, platform metadata, and warehouse do not share one transaction. A retry can repeat work or overwrite newer data.
- Changes to source schemas
- A source can change independently of a transformation or target table. Silent conversion can corrupt data while every run reports success.
Common mistakes
- Uses one timestamp rule for every source.
- Treats a successful extract as a successful target update.
- Claims exactly-once behavior without a target commit protocol.
- Discards nonmatching updates instead of removing earlier matching target rows.
- Applies schema changes automatically without a compatibility policy.
- Builds a new execution engine instead of defining recovery and connector contracts.
The bar by level
- Senior (L5/E5)
- Specifies snapshot boundaries, deletion transfer, filter transitions, and atomic target commits. Handles uncertain outcomes, backfills, source limits, and schema versions with concrete state transitions.
- Staff (L6+/E6+)
- Defines connector guarantees and identifies unrecoverable history gaps. Specifies target fencing and rebuild publication. Sets tenant isolation rules and budgets recovery capacity against source limits.