An orchestrator runs jobs on a schedule, in dependency order, with retries and alerting. Tool Notes SQL Server Agent schedules SSIS packages and T-SQL jobs Apache Airflow Python DAGs, rich operators, backfills Azure Data Factory / Synapse managed pipelines, copy activities, triggers Prefect / Dagster Python-native, strong typing and observability cron + scripts fine for one or two simple jobs What a good schedule needs Dependencies — load dimensions before facts. Retries with backoff for transient failures; alerts on final failure. Parameterised run dates so you can backfill a past day. Idempotent tasks so retries and backfills are safe. SLAs — alert if a job hasn't finished by a deadline.
Key Takeaways
Orchestrators handle scheduling, dependencies, retries and alerts.
Pass the logical run date into tasks so backfills reprocess the right data.
Load dimensions before facts and run quality checks last.