Monitoring and troubleshooting pipelines

Friday, October 30, starts with a real incident, which Aisha says is the best kind of lesson. At 05:52 the on-call phone received an alert: curated.sales had not been updated for October 29. Mateo watches as Aisha works through it, and then they spend the rest of the session turning what she did by instinct into a checklist he can follow on his own shift next month.

What to watch

A pipeline can fail in more ways than an error message. Aisha groups the signals she watches into four families. Errors are the obvious ones: a job or task that ended in failure. Freshness asks whether the data is as recent as it should be: has today's partition appeared, and when was the table last modified? Volume asks whether the amount of data is plausible: 40 stores normally produce about 30,000 sale lines a night, so 3,000 is a problem even if every job succeeded. Duration asks whether a run is taking much longer than usual, which is often the first sign of a growing table, a bad join or a quota limit, hours before anything fails.

The four families need different tools. Errors appear in logs. Freshness and volume need queries against the data or its metadata. Duration appears in job metrics. A pipeline with only error alerts will miss the most damaging failures, the ones where everything succeeds and the numbers are still wrong.

Cloud Logging and Cloud Monitoring

Almost everything in Google Cloud writes to Cloud Logging: BigQuery jobs, Dataflow workers, Workflows executions, Airflow tasks, Cloud Run requests, Eventarc deliveries. The Logs Explorer lets you filter by resource, severity, time and text, and save queries for reuse. Logs can be routed by sinks to other places: to a BigQuery dataset for analysis over months, to a Cloud Storage bucket for cheap long-term keeping, or to Pub/Sub for other tools. Birchline routes all pipeline-related logs to a BigQuery dataset called ops_logs, which is how Aisha can ask questions such as 'which jobs failed most often last quarter?'

Cloud Monitoring collects metrics: numbers measured over time, such as the number of BigQuery jobs, slots in use, Dataflow system lag, Pub/Sub messages waiting, or Workflows executions that failed. It draws dashboards and evaluates alerting policies, which compare a metric with a condition, such as 'failed executions greater than zero in five minutes', and send a notification through a notification channel: email, a chat app, SMS, a paging service or a Pub/Sub topic. A log-based metric turns matching log entries into a metric, so a specific error message can drive an alert. A log-based alert fires directly on a matching log entry.

-- Logs Explorer query: failed BigQuery jobs in the data project in the last day
resource.type="bigquery_project"
resource.labels.project_id="bl-data-prod"
protoPayload.metadata.jobChange.job.jobStatus.jobState="DONE"
severity>=ERROR

Monitoring BigQuery from the inside

BigQuery describes its own activity in INFORMATION_SCHEMA views. INFORMATION_SCHEMA.JOBS lists every job in a project or region for the last 180 days, with who ran it, when, how long it took, how many bytes it processed, how many slot-milliseconds it used and any error. INFORMATION_SCHEMA.TABLES and the PARTITIONS view show when tables and partitions were last modified and how many rows they hold. These views are ordinary tables to query, so a freshness check is a short SQL statement.

-- Freshness and volume check for yesterday's partition of curated.sales
SELECT
  partition_id,
  total_rows,
  last_modified_time,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), last_modified_time, HOUR) AS hours_since_update
FROM `bl-data-prod.curated.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = "sales"
  AND partition_id = REPLACE(CAST(DATE_SUB(CURRENT_DATE("America/New_York"), INTERVAL 1 DAY) AS STRING), "-", "");

Birchline runs a set of these checks as scheduled queries at 06:00. Each one ends with an ERROR() call if the result breaks a rule, which makes the scheduled run fail, which sends the failure notification. Aisha calls this alerting on silence: the absence of data is turned into an error that someone sees.

Monitoring Dataflow, Workflows and Airflow

Each service also has its own view. The Dataflow job UI shows a pipeline as a graph of steps, with the number of elements each step has processed, the throughput, the system lag for streaming jobs (how long the oldest unprocessed element has waited), worker counts and errors. A step whose lag keeps growing is a bottleneck; a step with many errors in its logs is usually a data problem. The Workflows page lists executions with their state, duration, arguments and the step where any failure happened. The Airflow UI shows a grid of DAG runs by date, color-coded by task state, with each task's log one click away.

Mateo notices that the same question, 'which step failed and why?', has a different answer screen in every service. That is why Birchline also writes one summary row per pipeline run into a control table, with the pipeline name, run date, start, end, status and row counts. The 06:00 checks and the morning stand-up both read that one table.

Working through an incident

Aisha's handling of this morning's alert followed the checklist she now writes down for Mateo. First, confirm the symptom: the PARTITIONS view showed no rows for October 29 in curated.sales. Second, find where the chain broke by walking upstream: the Dataform workflow had not started, because the Workflows control step was still waiting for one store. Third, read the error at the point of failure: store 08's file had arrived, but its load had failed with 'too many errors' because the file contained 412 rows with a missing date. Fourth, decide on the fastest safe recovery: quarantine store 08's file, build the curated tables without it, and flag store 08 on the dashboard. Fifth, communicate: a short message to the store operations channel. Sixth, fix the cause during working hours: the store's till had been updated the previous afternoon and wrote dates in a new format.

Mateo models the 06:00 checks to see which rule would have caught which problem, using last week's control-table summaries.

# Evaluate morning checks against one week of pipeline summaries
runs = [  # (date, status, rows loaded, minutes taken, finished at hh:mm)
    ("2026-10-23", "ok", 30410, 74, "03:48"),
    ("2026-10-24", "ok", 31980, 81, "03:55"),
    ("2026-10-25", "ok", 29770, 70, "03:41"),
    ("2026-10-26", "ok", 3050, 22, "03:10"),
    ("2026-10-27", "ok", 30120, 166, "05:16"),
    ("2026-10-28", "ok", 30650, 77, "03:51"),
    ("2026-10-29", "waiting", 0, 0, "--:--"),
]
normal_rows = sorted(r[2] for r in runs if r[1] == "ok")[3]   # median of the 6 ok runs (upper middle)
normal_minutes = sorted(r[3] for r in runs if r[1] == "ok")[3]
for d, status, rows, mins, end in runs:
    problems = []
    if status != "ok": problems.append("not finished (freshness)")
    elif rows < 0.5 * normal_rows: problems.append(f"only {rows} rows (volume)")
    if status == "ok" and mins > 2 * normal_minutes: problems.append(f"{mins} min (duration)")
    print(d, "OK" if not problems else "ALERT: " + "; ".join(problems))

2026-10-23 OK
2026-10-24 OK
2026-10-25 OK
2026-10-26 ALERT: only 3050 rows (volume)
2026-10-27 ALERT: 166 min (duration)
2026-10-28 OK
2026-10-29 ALERT: not finished (freshness)

Three alerts in a week, and only one of them, this morning's, involved a failed or unfinished job. October 26 succeeded while loading about a tenth of the usual rows, the day a storage permission change hid most stores' files; October 27 succeeded but took more than twice the usual time because a join had lost its partition filter. Error alerts alone would have caught neither.

On-call, runbooks and service levels

Alerts are only useful if someone is ready to act on them. Birchline's data team runs a weekly on-call rotation: one engineer carries the phone from Monday morning to the following Monday, with a second person as backup. Mateo will join the rotation in December, shadowing Aisha for his first week. The rotation is written down, the alerting policies notify the rotation's channel rather than named people, and the handover each Monday covers anything still open.

Each alert links to a runbook, a short page that answers four questions: what does this alert mean, what should I check first, what is safe to do at 05:45 without asking anyone, and who owns the data if a business decision is needed. The runbook for the curated.sales freshness alert, for example, says that quarantining one store's file and building without it is always allowed, while delaying the dashboard for everyone needs the store operations manager's agreement.

Behind the alerts sit service level objectives, plain statements of what users can expect. Birchline's sales pipeline promises that the curated tables are complete for the previous day by 06:00 on at least 28 of every 30 days, and that any store missing at 06:00 is shown as missing, never as zero. Measuring the pipeline against that promise each month turns arguments about whether things are 'mostly fine' into a number. Over the last 30 days, Aisha counts 29 complete mornings, so the objective was met, though October 26's quiet failure is the reason she added the volume check.

Finally, every incident that wakes someone up ends with a short post-incident review: what happened, how it was found, what fixed it, and what will stop it happening again. The reviews are blameless, focusing on the system rather than the person, and their actions, such as this morning's new rule that till-system updates must be tested with a sample file before they reach stores, go on the team's backlog like any other work.