Design a job scheduler ETL pipeline system
Company: Microsoft
Role: Software Engineer
Category: System Design
Difficulty: hard
Interview Round: Onsite
## System Design Prompt
Design a **Job Scheduler + ETL pipeline** system.
The system should allow users (or internal services) to:
- Define ETL jobs (extract → transform → load)
- Schedule jobs (cron / fixed interval / one-off)
- Run jobs on a fleet of workers
- Track job/run status and logs
- Support retries and failure handling
### Requirements
1. **Job definition**: store metadata (owner, schedule, source/sink, parameters, code/artifact location).
2. **Scheduling**: trigger runs on time; avoid duplicate runs.
3. **Execution**: dispatch runs to workers; support horizontal scaling.
4. **Reliability**:
- at-least-once execution with dedup where needed
- retries with backoff
- handle worker crashes mid-run
5. **Observability**: job/run status, logs, metrics, alerting.
6. **Multi-tenancy** (basic): isolate teams with quotas/limits.
### Discussion points
Explain key components (API, scheduler, queue, workers, metadata DB), data model, scaling strategy, and how you’d use/load-balance caches/queues. Call out major tradeoffs (exactly-once vs at-least-once, latency vs throughput).
Quick Answer: This question evaluates a candidate's ability to architect scalable, reliable job scheduling and ETL orchestration systems, testing competencies in distributed systems, scheduling, fault tolerance, observability, multi-tenancy, and operational metadata management.