A green checkmark on a load job means the process exited with a zero. It says nothing about whether the table holds today’s rows, or all of them.
Crashes are the cheap failures. Someone gets an error, reruns the job, and the table is right by mid-morning. The expensive ones are quiet: the job finishes on time with 60 percent of the rows, or with a second copy of yesterday. Nobody is told, and the first person to notice is whoever reads the report and thinks revenue looks odd.
Data observability tools usually list five things to watch: freshness, volume, schema, distribution and lineage. I’d start with the first two and stop there for a while. They need no knowledge of what the columns mean, and they fit on a page of SQL you can run on the warehouse you already have. The SQL below is PostgreSQL flavored; the interval and date arithmetic is the part you’d adjust elsewhere.
Two numbers per load
Every load writes one row to a history table: how many rows arrived and the newest event time among them. A second table holds the per-table settings.
create table table_stats (
table_name text not null,
load_date date not null,
loaded_at timestamptz not null,
row_count bigint not null,
max_event_time timestamptz,
primary key (table_name, load_date)
);
create table table_checks (
table_name text primary key,
expected_lag interval not null,
min_rows bigint not null,
severity text not null check (severity in ('page', 'ticket')),
owner text not null
);
insert into table_checks values
('payments', interval '3 hours', 50000, 'page', 'payments-data');
The collector is the last step of the load job. It assumes the loader stamps each row with a load_date:
insert into table_stats
(table_name, load_date, loaded_at, row_count, max_event_time)
select 'payments', current_date, now(), count(*), max(paid_at)
from payments
where load_date = current_date
on conflict (table_name, load_date) do update
set loaded_at = excluded.loaded_at,
row_count = excluded.row_count,
max_event_time = excluded.max_event_time;
The on conflict clause is for reruns: the second attempt overwrites the first one’s numbers. Take the event time from the source system, paid_at here, and never the load timestamp. A job that reloads stale data stamps it with a brand new load time, which hides exactly the failure you’re trying to see.
expected_lag is how old the newest event should be, on a healthy day, at the moment the check runs. If you’ve already written a latency budget for the table, it’s that number. min_rows is a hard floor for the weeks when the baseline has no opinion yet.
Freshness: how old is the newest event
with latest as (
select table_name, max_event_time,
row_number() over (partition by table_name
order by loaded_at desc) as rn
from table_stats
)
select c.table_name, c.owner, c.severity,
now() - l.max_event_time as event_age,
c.expected_lag
from table_checks c
left join latest l
on l.table_name = c.table_name and l.rn = 1
where l.max_event_time is null
or now() - l.max_event_time > c.expected_lag * 1.5;
The 1.5 multiplier is slack for ordinary lateness, so a job that runs twenty minutes long doesn’t wake anyone. The left join does more than it looks like: a table with a config row and no stats at all comes back as a breach, which covers the job nobody scheduled.
The comparison is against the clock, so a job that simply didn’t run today gets caught too. Its latest stats row keeps getting older.
Volume: compare Tuesday with Tuesdays
The usual first attempt is “alert if today is 30 percent below the 7-day average”. It fails on any table with a weekly rhythm, and most tables fed by human activity have one. So the baseline here is the same weekday over the past six weeks. I use the median and the median absolute deviation (MAD) because one bad day in the history shouldn’t move the baseline or widen the band.
with today as (
select table_name, load_date, row_count
from table_stats
where load_date = current_date
),
history as (
select t.table_name, h.row_count
from today t
join table_stats h
on h.table_name = t.table_name
and h.load_date < t.load_date
and h.load_date >= t.load_date - 42
and extract(dow from h.load_date) = extract(dow from t.load_date)
),
baseline as (
select table_name,
percentile_cont(0.5) within group (order by row_count) as median_rows,
count(*) as samples
from history
group by table_name
),
spread as (
select h.table_name,
percentile_cont(0.5) within group
(order by abs(h.row_count - b.median_rows)) as mad
from history h
join baseline b on b.table_name = h.table_name
group by h.table_name
)
select t.table_name, c.owner, c.severity,
t.row_count, b.median_rows,
round((100.0 * (t.row_count - b.median_rows)
/ nullif(b.median_rows, 0))::numeric, 1) as pct_off
from today t
join table_checks c on c.table_name = t.table_name
left join baseline b on b.table_name = t.table_name
left join spread s on s.table_name = t.table_name
where t.row_count < c.min_rows
or (b.samples >= 4
and abs(t.row_count - b.median_rows)
> 4 * greatest(1.4826 * s.mad, 0.05 * b.median_rows));
The 1.4826 factor scales MAD so it’s comparable to a standard deviation. The greatest is there because very regular tables have a MAD close to zero, and without a floor a 2 percent wobble would count as an anomaly. With it, nothing fires inside 20 percent of the median. The check is two-sided on purpose, since a table that doubles overnight is usually a duplicate load.
It only looks at tables that have a stats row for today. A load that never ran has no row, and that’s the freshness check’s job.
With fewer than four samples the baseline stays silent and only min_rows applies.
A quiet weekend and a short Tuesday
Ostrander Parking doesn’t exist. I made it up to put numbers through the queries. Its payments table gets 180,000 to 210,000 rows on weekdays and about 95,000 on Saturdays and Sundays. Under the flat rule the five weekdays hold the seven-day average above 155,000, and 95,000 is never within 30 percent of that. The check pages every weekend. Within a month somebody mutes it, and a muted alert is worse than none because people still believe they’re covered.
With the weekday baseline, Sunday is compared with Sundays and the paging stops.
Then a Tuesday. An upstream API changes how it paginates, the extract reads the first pages and stops, and the job finishes green with 118,400 rows. Say the six previous Tuesdays have a median of 200,700 and a MAD of 2,250. The 5 percent floor wins, so anything more than 40,140 rows off the median fires. Today is 82,300 short, 41 percent low. The check runs at 06:10 and pages the owner, almost two hours before the 08:00 finance report goes out.
The freshness case is duller. An export gets stuck and the loader keeps picking up the same file. Row counts look normal, so the volume check stays quiet. The newest paid_at is 26 hours old against a 3-hour expected lag, far past the 4.5-hour threshold.
Routing, and what slips past
Schedule both queries right after the loads and send every returned row to the owner in the config, with severity deciding between a page and a ticket. A shared channel that everyone can see and nobody owns is where these alerts go to be ignored.
Two checks won’t catch a load with the right count and wrong values, or dropped rows offset by duplicates. Public holidays will fire the volume check until you add a calendar. Those gaps are the argument for column-level checks or a bought tool later, and by then you’ll have six weeks of history showing which tables deserve them.