Catching the pipeline run that processed zero rows

The dashboard is a day stale. You check the pipeline: last run finished at 03:12, exit code 0, no errors in the task log. It also loaded zero rows, because the upstream export it reads from was empty, and nothing in the chain treated “zero rows” as a problem.

Data pipelines fail quietly more than most scheduled work, because “the job ran” and “the job moved data” are further apart than they look. An expired warehouse credential, a source table that stopped updating, a filter that now matches nothing, a schema change that silently drops a column: each one produces a run that a scheduler and an exit code call a success.

The heavyweight answer, and why you might skip it

Data-observability platforms (Monte Carlo, Metaplane, Elementary, and the freshness and volume tests built into Great Expectations or dbt itself) exist for exactly this. They profile every table, learn its normal row count and update cadence, and alert on drift. If you run a large warehouse with many consumers, that is the right tool.

For a handful of pipelines on a cron or a small Airflow instance, that is a lot of surface area to adopt. The failure you actually care about is narrow: this job should load more than zero rows, and it should do it every morning. That is a row-count assertion plus a missed-run check, and you can get both without a new platform.

Assert the row count in the job

The first move is to make the pipeline fail itself when the result is empty. In a shell wrapper, query the destination after the load and exit non-zero if it looks wrong:

#!/bin/bash
set -euo pipefail

./run-load.sh   # dbt build, a Python script, an rsync, whatever

rows=$(psql "$WAREHOUSE_URL" -tAc \
  "select count(*) from analytics.orders where loaded_at::date = current_date")

if (( rows < 1 )); then
  echo "orders load produced $rows rows for today" >&2
  exit 1
fi

For dbt, the same check is a test rather than a script. A row_count or not_null test, or a dbt_utils.recency check on a timestamp column, turns an empty or stale model into a failing dbt build. Airflow users can put the assertion in a final PythonOperator or a SQLColumnCheckOperator so the DAG goes red when the count is off.

This handles the case where the job runs. It does nothing for the morning the scheduler misfires, the worker is down, or the DAG is paused and nobody notices. There is no run, so there is no assertion and no exit code to catch.

Report the count to something outside the pipeline

The dead man's switch pattern covers the missing run: the job checks in on a schedule, and something off your infrastructure alerts when the check-in does not arrive. Extend it by sending the row count along with the check-in instead of a bare ping:

if ./run-load.sh; then code=0; else code=$?; fi

rows=$(psql "$WAREHOUSE_URL" -tAc \
  "select count(*) from analytics.orders where loaded_at::date = current_date")

curl -fsS -m 10 -d "rows=$rows" \
  "https://illari.dev/ping/YOUR_KEY/$code"

Now the check has two failure modes and one place to configure them. If no ping arrives on schedule, the run was missed. If a ping arrives reporting rows=0, the run happened and did nothing. On illari, the second one is a metric rule on the monitor (rows is less than 1); the alert names the metric, the value, and the rule, and the count shows on every check-in so you can see the normal range at a glance. The threshold lives next to the run history rather than hard-coded in each pipeline.

The same shape works for the other quiet failures. Send bytes after a warehouse unload and alert if it drops below a floor. Send error_count from a dbt run summary and alert if it is above zero. Send the age of the newest source row in seconds and alert if it crosses a day.

What this does not do

A fixed threshold fires on the exact condition you set, so rows < 1 catches an empty load and misses a load that came back at 12% of its usual size. For pipelines where the healthy range shifts week to week, or where you cannot name a floor, the profiling platforms earn their keep. For “this should never be zero” and “this should never be stale,” a threshold and a schedule check are enough, and they take an afternoon.

A pipeline that loaded nothing should page you the same morning, not the afternoon you go looking for the data it was supposed to produce.