Katkat
Back

Design a job scheduler ETL pipeline system

MicrosoftSoftware Engineer · Onsite
HardSoftware EngineerOnsiteSystem Design

Last updated

This question tests the ability to design a distributed, fault-tolerant job orchestration system capable of handling complex task dependencies, retries, and high-throughput data processing.

Design a Job Scheduler ETL Pipeline

Design a distributed ETL (Extract, Transform, Load) orchestration system that manages millions of daily jobs across a global infrastructure. The system must support complex directed acyclic graph (DAG) dependencies, where a task can only execute once its parent tasks have successfully finished.

Requirements

Functional Requirements:

  • Users should be able to define, schedule (cron-like or trigger-based), and pause ETL workflows.
  • The system must track job state (Pending, Running, Succeeded, Failed, Retrying) and execution metadata.
  • Support task-level retry policies (e.g., exponential backoff).

Non-Functional Requirements:

  • Reliability: No job should be lost due to worker or scheduler failures. Tasks must be idempotent.
  • Scalability: The system should handle spikes in job volume and scale to thousands of concurrent task executions.
  • Observability: Provide a dashboard to view the lineage and latency of data pipelines.

Scope

  • Define the high-level system architecture and the role of the scheduler, worker nodes, and metadata store.
  • Explain how you would handle task state transitions and prevent race conditions.
  • Discuss how to ensure fault tolerance in the event of a worker node crashing during a task.
  • Specify the data model used for tracking dependencies and job execution history.

Hints

Hint 1

Consider using a distributed queue or a partitioned database to manage the task execution lifecycle and ensure no two workers claim the same job.

Hint 2

Think about how a state machine approach handles retries and how you would store the DAG structure to allow the scheduler to identify 'ready' tasks efficiently.

Hint 3

Address the 'split-brain' problem by using a distributed consensus mechanism or optimistic locking when updating the status of a task.

Follow-up questions

  • How would you handle backfilling historical data for a pipeline without affecting the production schedule?
  • If a specific data source becomes unavailable, how would you implement a circuit breaker pattern to pause dependent downstream jobs automatically?
  • How would you evolve this system to support multi-tenancy, ensuring one team's massive job backlog doesn't starve another team's latency-sensitive pipelines?