Search for a command to run...
AI-Augmented Data Engineering — Article 3 of 7
Median/MAD over mean plus stddev is the right call for pipeline metrics, since one giant batch or a retry storm wrecks a z-score but barely moves the median. The same-weekday baseline is the detail I'd steal, because a Monday reprocessing job looks nothing like a Sunday and averaging across the week hides both. How do you handle holidays and one-off backfills, do they poison the trailing-8 window, or do you exclude them explicitly?
Consistency and continuous learning make all the difference. Thanks for sharing this valuable breakdown.
Modern data platforms have outpaced our ability to see them clearly. This series explores how AI restores that clarity — across data quality, observability, root cause analysis, metadata intelligence, and cost optimisation. Written for engineers who build and leads who decide, each article pairs implementation depth with an honest reckoning of risk. The lens is Advaita: viveka — discriminative discernment — applied to the hardest problems in data engineering.
Series: AI-Augmented Data Engineering | Article 1 of 7 There is a quiet crisis in most data engineering teams. Pipelines fail at 2 AM. The on-call engineer spends three hours tracing a root cause tha
AI-Augmented Data Engineering | Article 2 of 7 | tech4nirvana.com
Series: AI-Augmented Data Engineering | Article 1 of 7 There is a quiet crisis in most data engineering teams. Pipelines fail at 2 AM. The on-call engineer spends three hours tracing a root cause tha
The Invisible Failure Most data platform programs don't fail loudly. They fail quietly — one missed dependency, one unreported pipeline issue, one status update that said 'green' while the underlying
There is a pattern in mature engineering: the more you try to control, the more brittle the system becomes. Liquid Clustering is Delta Lake's answer to over-engineering. Vedanta figured this out mille
Most pipeline observability today is a wall of thresholds. Row count dropped below X. Job ran longer than Y minutes. Null percentage exceeded Z. Someone picked those numbers months ago, often by guessing, and the alerts have been drifting out of relevance ever since.
The result is a familiar failure mode: real incidents slip through because they don't cross a static threshold, while noisy, low-value alerts fire constantly and get muted. Engineers stop trusting the monitoring, which means they stop looking at it — until a downstream report is wrong and someone asks why nothing caught it.
This isn't a tooling gap. Most teams already have Databricks system tables, Delta Lake transaction logs, and job-run metadata sitting there, rich with signal. The gap is that nobody's watching it with anything more sophisticated than IF value > threshold.
Observability is fundamentally a pattern-recognition problem, and that's exactly where statistical and ML methods outperform static rules.
Three shifts are now practical at reasonable engineering cost:
From fixed thresholds to learned baselines. Time-series models (seasonal decomposition, Prophet-style forecasting, or simpler rolling z-scores) learn what "normal" looks like for a given pipeline — including weekly and monthly seasonality — and flag deviations from that learned baseline instead of a number picked six months ago.
From single-metric checks to correlated signals. A row-count dip alone might be noise. A row-count dip combined with a schema change upstream and a spike in null rates is a pattern worth an engineer's attention. Lightweight anomaly detection across correlated features catches this; single-metric thresholds cannot.
From "did it fail" to "will it fail." Job duration trending upward over several runs, or memory usage creeping toward cluster limits, is a leading indicator. Forecasting these trends gives you a warning before the 2 AM page, not after.
None of this requires a dedicated ML platform. It requires treating your existing job-run and lineage metadata as a first-class dataset.
A practical starting point on Databricks: build an observability feature table from system tables and job-run history, then apply a lightweight anomaly model per pipeline — one that respects two realities a naive rolling z-score ignores: pipeline metrics are rarely Gaussian, and most have weekly seasonality.
from pyspark.sql import functions as F
from pyspark.sql.window import Window
# Pull job run metrics, tagged by day-of-week to respect weekly seasonality
run_metrics = (
spark.table("system.lakeflow.job_run_timeline")
.filter(F.col("result_state") == "SUCCESS")
.withColumn("run_date", F.to_date("period_start_time"))
.withColumn("day_of_week", F.dayofweek("run_date"))
.groupBy("job_id", "run_date", "day_of_week")
.agg(F.avg("run_duration_seconds").alias("avg_duration"))
)
# Baseline computed per (job_id, day_of_week) — trailing 8 same-weekday
# occurrences (~8 weeks), so a Saturday batch run is compared to past
# Saturdays, not to Tuesday's lighter load.
baseline_window = (
Window.partitionBy("job_id", "day_of_week")
.orderBy("run_date")
.rowsBetween(-8, -1)
)
scored = (
run_metrics
# Median + MAD instead of mean + stddev: robust to the skewed,
# multi-modal distributions that duration and row-count metrics
# typically have (batch-size variance, cache effects, cluster
# contention). A rolling z-score on raw mean/stddev is the single
# most common way this kind of check misfires in production.
.withColumn("baseline_median", F.expr("percentile_approx(avg_duration, 0.5)").over(baseline_window))
.withColumn("abs_deviation", F.abs(F.col("avg_duration") - F.col("baseline_median")))
.withColumn("mad", F.expr("percentile_approx(abs_deviation, 0.5)").over(baseline_window))
.withColumn("history_count", F.count("*").over(baseline_window))
.withColumn(
# 0.6745 scales MAD to be comparable to a stddev-based z-score
"robust_z_score",
F.when(F.col("mad") > 0, 0.6745 * F.col("abs_deviation") / F.col("mad"))
)
# Automated cold-start handling: a pipeline only enters adaptive
# mode once it has 8 same-weekday data points; until then it's
# explicitly flagged for static-threshold monitoring instead of
# silently producing an unreliable score.
.withColumn(
"monitoring_mode",
F.when(F.col("history_count") >= 8, F.lit("adaptive")).otherwise(F.lit("static_fallback"))
)
.filter((F.col("monitoring_mode") == "adaptive") & (F.abs(F.col("robust_z_score")) > 3.5))
)
This is deliberately simple — median/MAD and day-of-week partitioning, not a neural network. For most pipeline-level metrics (duration, row count, null rate), this level of sophistication catches the majority of real anomalies without the distributional assumptions a plain z-score makes. Reserve heavier models (isolation forests, autoencoders, or explicit seasonal decomposition) for high-dimensional cases like multi-column data drift or pipelines with irregular, non-weekly cycles, where a single metric or a fixed weekday lag can't capture the pattern.
One scale caveat: percentile_approx inside an open-ended window clause is a non-additive, shuffle-heavy operation. Recomputing it over raw run-logs inline is fine for dozens of pipelines; at thousands of jobs running daily, pre-aggregate into a daily per-(job_id, day_of_week) state table and compute the rolling median/MAD against that smaller table instead of the raw log.
Route flagged anomalies into a Delta table feeding your existing alerting channel, tagged with the contributing signals — not just "duration anomaly" but "duration anomaly, coincides with upstream schema change at 03:14."
Cold start, now explicit rather than hidden. The monitoring_mode flag stops a pipeline from getting an unreliable score during its first 8 weeks, but someone still has to wire the static_fallback branch to an actual threshold check — the code makes the state visible, it doesn't eliminate the manual setup.
MAD is more robust, not distribution-free. Median/MAD tolerates skew and outliers far better than mean/stddev, but it still assumes a single dominant mode. A pipeline with genuinely bimodal runtime behavior — a fast path when Spark hits cache versus a slow path when it spills to disk — will settle the median between or on one mode, producing erratic scores either way. That needs a segmented or mixture-aware approach, not this one.
Shuffle cost at scale. percentile_approx in an open-ended window clause is non-additive and shuffle-heavy. Fine for dozens of pipelines computed inline against raw logs; at thousands of jobs running daily, pre-aggregate into a daily state table first rather than recomputing the full window over raw run-logs.
Baseline drift as false comfort. If a pipeline's behavior degrades slowly, a rolling baseline "learns" the degradation as the new normal and stops flagging it. This is the single biggest failure mode of adaptive monitoring — it can quietly raise your tolerance for bad behavior instead of catching it.
Alert fatigue, relocated not solved. Swapping static thresholds for statistical ones doesn't guarantee fewer false positives if the underlying metric is inherently noisy (e.g., source system volume genuinely varies day to day). Tuning still matters.
Who watches the watcher. An anomaly-detection layer is itself a system that can fail silently — a stale baseline table, a broken feature pipeline. It needs its own basic health check, or it becomes a false sense of security.
Cap baseline windows and set a floor (e.g., require 30 days of history before adaptive scoring kicks in; use static thresholds before that).
Add a long-window drift check alongside the short-window anomaly check — compare this month's baseline to the baseline from three months ago. A quiet, gradual shift over that longer window is worth a human look even if no single day tripped an alert.
Keep a human-in-the-loop feedback mechanism: when someone dismisses an alert as noise, capture that as a label. Be honest about what this buys you — feeding binary dismissal labels back into a median/MAD threshold isn't a natural fit; treat the labeled data as the seed of a future supervised model, not a live input to today's statistical check.
Monitor the monitoring pipeline itself with a simple heartbeat check — last successful run time, row counts written — using the same basic tooling you'd apply to any production job.
For engineers: start with your job-run system tables and one pipeline's duration metric. A rolling z-score is a day of work, not a project, and it will outperform a static threshold within a month.
For leads: the real gain isn't fewer alerts — it's alerts that mean something. Track the ratio of actioned-to-dismissed alerts before and after adaptive monitoring goes in; that number, not alert volume, is the metric that tells you whether observability is actually working.
In Advaita Vedanta, avidya isn't the absence of information — it's the presence of information you've stopped truly seeing. A threshold breached ten times a day for six months isn't ignorance; it's information the team has learned to look past. Adaptive observability doesn't add data. It restores the seeing.
Karthik Darbha is a Senior Data Engineering & AI Leader with 23 years of professional experience, including 21+ years building enterprise data platforms across Healthcare, Pharma, Retail, Insurance, and Financial Services. He writes about data engineering, program management, and the intersection of technology and philosophy at tech4nirvana.com.