Orchestration, and what "it runs every night" costs

A script you run by hand has one failure mode: you notice. A scheduled job has many, and most of them are silent.

What an orchestrator is for#

Cron runs a thing at a time. That is not enough, because real pipelines have dependencies — the revenue model cannot run before the orders load finishes, and the orders load finishes at an unpredictable time.

The naive fix is scheduling by guesswork: load at 01:00, transform at 02:00, hope. This works until the load takes 70 minutes, and then the transform runs on yesterday's data, succeeds, and reports success.

An orchestrator expresses the dependency graph rather than a set of times:

raw_orders     = load_orders()
raw_customers  = load_customers()
stg_orders     = clean(raw_orders)
stg_customers  = clean(raw_customers)
fct_orders     = build_facts(stg_orders, stg_customers)   # waits for both
daily_revenue  = aggregate(fct_orders)

Whatever the tool — Airflow, Dagster, Prefect, dbt's own DAG — the value is the same: the graph decides the order, failures propagate correctly, and a downstream task cannot run on stale inputs.

Idempotent tasks, parameterised by window#

From the pipelines module, and it becomes non-negotiable here: every task takes its window as a parameter and can be rerun safely. A task that internally computes "yesterday" cannot be backfilled, cannot be replayed, and cannot be tested. It will be the source of your worst incident.

Retries: which failures deserve them#

Not all of them.

Retry transient failures: network timeouts, rate limits, a warehouse briefly at capacity. Use exponential backoff with jitter — three retries at 1, 2, 4 minutes, plus randomness so a hundred tasks do not all retry in lockstep.

Do not retry deterministic failures: a schema violation, a failed data test, a missing file. Retrying a bug three times just produces the same failure three times and delays the alert by twelve minutes.

@task(retries=3, retry_delay_seconds=60, retry_jitter_factor=0.3)
def fetch_from_api(window_start, window_end):
    ...

@task(retries=0)          # a contract violation will not fix itself
def validate_schema(df):
    ...

SLAs and freshness, defined outward#

An SLA is a promise to a consumer, not a property of a job. "The revenue table is complete for yesterday by 08:00 local" is an SLA. "The job starts at 02:00" is a schedule.

Alert on the SLA. A job that starts on time and finishes four hours late has met its schedule and broken its promise, and only one of those matters to the person opening the dashboard.

Set the alert on freshness of the output table, not on job status. That catches the whole class of failures where the job succeeds and produces nothing.

What to alert on, and to whom#

The hard part is not detecting failure. It is not detecting so much that people stop reading.

Page a human for: an SLA breach on a tier-one table, a data test at error severity, a pipeline down for more than one cycle.

Send a ticket for: warn-severity tests, cost anomalies, a task that retried and eventually succeeded, slow-degrading freshness.

Log only for: everything else.

Every alert must answer three questions in its text: what broke, what it affects, and what to do first.

[P2] fct_order_lines is 4h stale (SLA 08:00, now 12:10)
     Upstream: raw_orders load failed 3× — vendor API returning 503
     Affects: exec revenue dashboard, finance daily close
     Runbook: docs/runbooks/orders-load.md
     Owner: @data-platform

Compare with "Task failed: dag_orders_v2.task_17". One of these lets a person act at 3am; the other guarantees they wake up someone else.

Backfills as a first-class operation#

You will backfill. Make it routine:

• The same code path as the scheduled run, with a different parameter.
• Bounded concurrency, so it does not saturate the warehouse.
• A dry-run mode that reports what would be processed and what it would cost.
• For anything large: write to a shadow table, verify counts and a few aggregates, then swap.

Cost, because it is a production concern#

An expensive query run once is a curiosity. Scheduled hourly, it is an annual bill.

• Tag every job with an owner and a cost centre. Untagged spend is unowned spend.
• Review the top ten jobs by cost monthly. It takes fifteen minutes and reliably finds something.
• Set byte or slot limits on scheduled jobs where the platform allows. A job that suddenly scans 40× more than usual should fail loudly rather than succeed expensively.
• Ask, for anything hourly, whether daily would do. Very often it would, and nobody has revisited the decision since it was made in a hurry two years ago.