Nightly ETL Job Monitoring Workflow
Instrument batch jobs with heartbeats, volume sanity checks, and completion gates so silent partial failures page a human before the business notices.
Objective
Detect failed, partial, or silently wrong nightly ETL runs through layered checks, with page-worthy conditions clearly separated from noise.
Environment
- A scheduled ETL job (cron, Airflow, or similar)
- A metrics or heartbeat store reachable by the job
- Historical volume data for sanity baselines
Steps
1. Emit heartbeats and stage metrics
Have the job emit a heartbeat per stage with rows processed, bytes read, and duration, sent to the monitoring system as it progresses.
Expected state: Each stage reports in real time, so a hang is visible as a stopped heartbeat, not discovered at deadline.
2. Set completion gates
Add an end-of-run gate that verifies the job actually finished all stages, wrote its completion marker, and updated the watermark.
Expected state: A run that exits zero but skipped stages fails the gate and alerts.
3. Add volume sanity checks
Compare row counts and byte volumes against historical bands (accounting for weekday or seasonality), alerting outside expected ranges.
Expected state: A run processing 40 percent of normal rows triggers a warning even though it completed cleanly.
4. Distinguish page from noise
Route hard failures and deadline misses to the pager; volume anomalies to a morning digest for triage, keeping on-call sleep intact.
Expected state: The on-call receives actionable failures only; anomalies arrive with context at triage time.
5. Post-run reconciliation signal
Have the first downstream consumer (report or dashboard) verify freshness and row count, catching corruption the job itself cannot see.
Expected state: Downstream freshness checks confirm the pipeline's output is both present and plausible.
Expected outcome
Nightly pipelines whose failures, hangs, partial runs, and volume anomalies are each detected by a specific check, with paging reserved for what truly needs a human now.
Verification
Kill the job mid-run in a drill: the heartbeat stops paging within minutes; run a volume-anomaly injection: it lands in the morning digest, not the pager.
A quiet batch job is not a healthy batch job
Nightly ETL fails in three quiet ways: it hangs, it exits cleanly having done part of the work, or it processes wrong data at full speed. Heartbeats, completion gates, and volume bands each catch one of these that a simple success cron-mail would miss.
nightly_orders ETL (00:30Z)
heartbeats: extract 00:31 (2.1M rows)
transform 00:34 (2.1M in, 2.0M out)
load 00:39 (2.0M upserted)
DONE marker 00:41 watermark 10-21
checks:
completion gate PASS (all stages + marker)
volume band 2.0M vs [1.8M, 2.3M] PASS
downstream dashboard fresh 00:42 PASS
anomaly example (caught):
transform out 0.9M (55% of band) -> morning digest
cause: upstream partial outage 10-21 21:40ZClean exit is not completion
The most dangerous ETL failure mode is the partial run that exits zero: a stage skipped by an error path, a watermark advanced past unprocessed rows. The completion gate checks what should exist afterward — markers, stage outputs, watermark — rather than trusting the exit code.
Volume bands encode what normal means
Row counts and byte volumes within historical bands are the cheapest corruption detector available. A pipeline that read 40 percent of normal rows completed successfully on broken input; only the band check notices, and it costs one comparison.