TL;DR: Data observability boils down to tracking four things per table — freshness, volume, schema, and distribution — and alerting on deviations from learned, not static, expectations. Static thresholds either flood you with false positives or miss real breakage; the actual engineering problem is building lightweight, table-specific baselines that account for seasonality, without needing a $50k/year vendor platform to get started.
This is one of the more common data engineering problems we get pulled into: a pipeline silently breaks — a filter bug drops 90% of rows, an upstream API starts returning nulls, a scheduled job just stops running — and nobody notices until someone in finance opens a dashboard that's clearly wrong and asks what happened. By then the bad data has often already fed into a report, a model, or a decision. Data observability is the practice of catching that class of failure automatically, before a human stumbles into it.
Commercial data observability platforms package this well, but the underlying technique is straightforward enough to build incrementally with SQL, a scheduler, and a bit of statistics — worth understanding even if you eventually buy a platform, because the failure modes of naive implementations (mainly: static thresholds that generate too much noise to be useful) are the same whether you build or buy.
The four dimensions that actually matter
Most data observability frameworks converge on the same core signals, because these are the ones that correlate with real breakage:
- Freshness — is data arriving when expected? A table that updates daily at 6am but hasn't updated by 9am is a freshness anomaly, independent of whether the data that does eventually arrive looks correct.
- Volume — is the row count in the expected range? A daily transaction table that drops from 500,000 rows to 5,000 overnight is almost certainly a filter bug or an upstream outage, not a real 99% drop in transactions.
- Schema — did columns get added, removed, renamed, or change type unexpectedly? This is the one most teams already partially cover with dbt tests or a schema registry, because it's the easiest to check deterministically.
- Distribution — do the values inside columns still look statistically normal? Null rates spiking, a numeric column's mean shifting sharply, a categorical column suddenly seeing a value it's never had before — these catch problems that pass both freshness and volume checks cleanly (the pipeline ran, on time, with a normal row count, but the data itself is wrong).
The order matters for where to start: freshness and volume checks are cheap (a COUNT(*) and a MAX(updated_at) per table) and catch a large share of real incidents on their own. Schema and distribution checks require more infrastructure and are worth adding once the basics are in place.
Freshness: don't hardcode a threshold, model the expected cadence
The naive version — "alert if the table hasn't updated in 24 hours" — breaks the moment you have tables on different cadences, or a table that legitimately doesn't update on weekends. The workable version tracks, per table, the actual historical update cadence and alerts on deviation from that table's pattern, not a fleet-wide constant.
-- Freshness check: compare time-since-last-update against
-- this table's own historical p95 gap between updates
with update_gaps as (
select
updated_at,
updated_at - lag(updated_at) over (order by updated_at) as gap
from table_update_log
where table_name = 'transactions'
),
expected_gap as (
select percentile_cont(0.95) within group (order by gap) as p95_gap
from update_gaps
where updated_at > now() - interval '30 days'
)
select
now() - max(t.updated_at) as current_gap,
e.p95_gap as expected_max_gap
from transactions t, expected_gap e
having now() - max(t.updated_at) > e.p95_gap * 1.5;This is a small amount of SQL, but it's the difference between an alert that fires reliably on real staleness and one that either misses a Tuesday-morning outage (because the static threshold was set loose enough to survive weekends) or pages someone every Saturday (because it wasn't).
Volume: baseline against the same period, not yesterday
Comparing today's row count to yesterday's is the most common naive volume check, and it's wrong for anything with weekly or monthly seasonality — Monday's row count for a B2B SaaS product is routinely different from Sunday's, and comparing Monday to Sunday will fire a false alarm every single week.
The fix is comparing against the same day-of-week (or day-of-month, for monthly-seasonal data) over a trailing window, with a statistically reasoned band rather than a fixed percentage:
with historical as (
select
extract(dow from load_date) as day_of_week,
row_count
from volume_history
where load_date > current_date - interval '8 weeks'
),
today_baseline as (
select
avg(row_count) as mean_count,
stddev(row_count) as stddev_count
from historical
where day_of_week = extract(dow from current_date)
)
select
t.row_count as today_count,
b.mean_count,
b.stddev_count,
abs(t.row_count - b.mean_count) / nullif(b.stddev_count, 0) as z_score
from todays_load t, today_baseline b
where abs(t.row_count - b.mean_count) / nullif(b.stddev_count, 0) > 3;A z-score-based threshold (commonly 3 standard deviations, tuned per table) adapts automatically as a table's normal volume grows or shrinks over time, without anyone manually revisiting a hardcoded number every quarter.
Distribution: the check most teams skip, and shouldn't
Distribution checks catch what freshness and volume checks structurally cannot: the pipeline ran on schedule, with a normal row count, and the data is still wrong. Common patterns worth tracking per column:
- Null rate drift — a column that's normally 2% null suddenly at 40% null usually means an upstream field got renamed or a join started failing silently.
- Categorical cardinality/value drift — a
statuscolumn that's only ever had five values suddenly has a sixth, or the distribution across the existing five shifts sharply (90% of orders switching fromstatus=shippedtostatus=pendingovernight is a signal, even though every value is individually valid). - Numeric range/mean drift — an
order_totalcolumn's mean shifting from $45 to $4,500 usually indicates a currency or unit conversion bug, not a real change in customer behavior.
These are exactly the kind of checks Great Expectations was built to express cleanly — column value ranges, distribution checks, and statistical thresholds that are awkward to hand-roll repeatedly in raw SQL or dbt test YAML. A reasonable layering, consistent with how most mature data platforms actually structure this: dbt tests (or a data contract enforced at ingestion) catch structural issues — table exists, keys are unique, required columns are present — while Great Expectations or an equivalent framework catches behavioral issues — the data inside those columns still looks like the data that's supposed to be there.
Why static thresholds fail, specifically
It's worth being explicit about why "just set an alert threshold" doesn't hold up, because it's the single biggest driver of data observability programs getting abandoned: research on pipeline monitoring alerting has found that 60–80% of alerts in naively-thresholded systems are noise, and teams predictably start ignoring or muting alerts once the false-positive rate crosses a threshold of trust — at which point the system is providing zero actual protection despite technically "monitoring" everything.
The fix isn't a fundamentally different technique, it's the discipline of learning per-table, per-metric baselines instead of setting one global rule: seasonal decomposition (even a simple day-of-week or day-of-month bucketing, as in the volume example above) accounts for the majority of the seasonality-driven false positives, and per-table z-score or percentile-based thresholds (rather than fixed percentages) adapt as each table's normal behavior evolves.
A practical build order
| Stage | What to add | Effort |
|---|---|---|
| 1 | Freshness + volume checks on your most business-critical 10–20 tables, with day-of-week baselines | Low — SQL + scheduler |
| 2 | Schema change detection (most warehouses/orchestrators expose this natively, or via dbt) | Low-medium |
| 3 | Null-rate and categorical drift checks on key columns | Medium |
| 4 | Numeric distribution checks (mean/stddev drift) on financially or operationally critical columns | Medium |
| 5 | Lineage-aware alerting (know which downstream dashboards/models are affected by a given table's anomaly) | High — usually where a vendor platform starts paying for itself |
Most teams get meaningful protection from stages 1–3 built in-house; stage 5 (full lineage-aware impact analysis) is typically where the cost-benefit tips toward an off-the-shelf data observability platform rather than continued custom build.
Building data observability into a warehouse or pipeline is a common early step in the data engineering and business intelligence work we do with clients — it's cheap to start and tends to pay for itself the first time it catches something before a stakeholder does.
Sources
- Data Pipeline Monitoring & Anomaly Detection: The Essential Guide for 2026 — dqLabs
- Data Observability Metrics That Matter in 2026 — Promethium
- What Are Data Contracts? A Practical Guide — DataHub
- Data Quality with dbt & Great Expectations — Orchestra
- Implement dbt data quality checks with dbt-expectations — Datadog
- How to Implement Anomaly Detection Integration — OneUptime
- Data Drift in Streaming: Detecting and Managing Unexpected Changes — Conduktor