Silent data failures your monitoring misses (and the SQL patterns that catch them)

There's a whole class of production failure that trips no alert. The server answers. The deploy is green. Error rates are flat. And the database has quietly stopped being right — a feed went stale, an import wrote nothing, one customer's data stopped arriving while everyone else's papered over the gap. Nobody pages, because nothing in your monitoring is looking at outcomes. It's looking at whether the machine is up.

That gap has a shape. On one side is uptime monitoring, which answers "is the server reachable?" and stops there. On the other is enterprise data observability — Monte Carlo, and the broad platforms — which answers everything but arrives as a rollout project with a sales cycle and a bill to match. In between is the ordinary, unglamorous question most teams actually have: is the data still doing what it's supposed to? You can answer that with SQL. This post is the patterns for doing so.

Two things up front. First, none of these is a check you paste in. Your tables, entities, cadence, thresholds, and business semantics aren't mine, and a query tuned to my data would misfire on yours — the transferable part is the shape and the reasoning about its failure modes, not the literal SQL. Second, every query here runs as well from psql and a cron line as from any product. That's deliberate: the DIY version is the honest incumbent, and the interesting question isn't "SQL or a tool" but where the SQL stops being enough. We'll get there at the end.

The schemas below are toys — orders, events, payments, invented columns. Swap in yours.

Pattern 1 — Freshness: how long since the newest row?

The most common silent failure is a feed that stops. The table stays queryable, still full of last week's rows; a health check confirms the job ran, which tells you nothing about whether it did anything. The only honest signal is the age of the newest row:

SELECT EXTRACT(EPOCH FROM now() - max(updated_at)) / 60
         AS minutes_since_last_row
FROM orders;
-- Healthy: minutes_since_last_row < 30

The trade-off is the threshold, and the threshold is a guess about your data's natural rhythm. Set it tight and a normal quiet Sunday pages someone at 3am; set it loose and a feed that died at lunch isn't caught until dinner. A freshness check is only as good as how it handles expected quiet. The tuning that separates a useful check from a muted one is usually a clock guard — only evaluate after the window when data should have arrived — and comparing against the same weekday last week rather than a flat number, so "quiet because it's the weekend" doesn't read as "quiet because it broke."

Pattern 2 — Row-count delta vs a baseline

Freshness catches stopped. It misses shrank. An import that used to write 40,000 rows and now writes 12 is fresh — the newest row is seconds old — and completely broken. What you want is today's volume against what's normal:

WITH today AS (
  SELECT count(*) AS n
  FROM daily_orders_import
  WHERE loaded_at >= current_date
),
baseline AS (
  SELECT avg(daily) AS n
  FROM (
    SELECT count(*) AS daily
    FROM daily_orders_import
    WHERE loaded_at >= current_date - interval '14 days'
      AND loaded_at <  current_date
    GROUP BY loaded_at::date
  ) d
)
SELECT today.n AS rows_today,
       baseline.n AS rows_expected
FROM today, baseline;
-- Healthy: rows_today > baseline.n * 0.5   (alert on a >50% drop)

The trade-off is that data legitimately moves, and a naive delta punishes it for it. Real volume has weekly seasonality, launch spikes, holiday troughs. A fixed "±50%" band will cry wolf every Monday and sleep through a slow leak that never trips it. This is the exact seam where the DIY approach and the enterprise one diverge: platforms sell learned, seasonally-aware baselines here. You don't need ML to get most of the value — a same-weekday comparison and a percentage band catch the dramatic drops, which are the ones that matter — but be honest that a static threshold is a coarse instrument, and the false-positive rate is the price.

Pattern 3 — Group by entity to find the one that went dark

Here's the failure a whole-table check is structurally blind to. Your total volume is healthy. Your freshness is green. And one tenant — often a big, important one — stopped producing rows after a config change or a broken integration on their side. Every other tenant's volume hides the hole. The aggregate says fine; the customer who went dark is the one who emails you.

You can't assert "tenant X must report today" — you'd maintain that list against every signup and churn forever. Derive the expected set from recent history, then diff today against it:

WITH active_recently AS (
  SELECT DISTINCT tenant_id
  FROM events
  WHERE created_at >= now() - interval '14 days'
    AND created_at <  current_date
),
seen_today AS (
  SELECT DISTINCT tenant_id
  FROM events
  WHERE created_at >= current_date
)
SELECT a.tenant_id
FROM active_recently a
LEFT JOIN seen_today s USING (tenant_id)
WHERE s.tenant_id IS NULL;
-- Any rows returned = a tenant that was active but is silent today

The trade-off is per-entity normality. A tiny tenant that reports twice a week isn't "dark" on a quiet day; a high-volume one silent for an hour probably is. One global rule can't be right for both — you either accept some noise on the small accounts or some blindness on the large ones. The grouped shape is also where per-check cron sprawl begins: do this properly and you want per-slice thresholds and per-slice state, which is a lot of query-per-tenant bookkeeping to hand-roll.

Pattern 4 — Dropped partitions or regions

The same "group and diff" logic applies to any dimension that's supposed to be fully populated: regions, shards, partitions, source systems. When one silently drops out, the survivors keep the totals plausible.

SELECT region
FROM (VALUES ('us-east'), ('us-west'), ('eu-west'), ('ap-south')) AS expected(region)
WHERE region NOT IN (
  SELECT DISTINCT region
  FROM events
  WHERE created_at >= now() - interval '1 hour'
);
-- Any rows returned = a region that reported nothing this hour

The trade-off is that hard-coded expectations rot. The VALUES list is honest and readable, but it's a second source of truth you now own: add a region and forget to update it, and the check is blind to the newcomer; decommission one and it pages forever. Deriving the expected set from history (Pattern 3) avoids the stale list but reacts slower to genuinely new dimensions. Neither is free; pick the failure you'd rather have.

Pattern 5 — Business-outcome counts

Freshness and volume are proxies. Sometimes you can check the actual thing the business cares about. Payments are the clean example: you don't want to know that the payments table is fresh — you want to know that money is still being captured.

SELECT count(*) AS captured_last_hour
FROM payments
WHERE status = 'captured'
  AND created_at >= now() - interval '1 hour';
-- Healthy: captured_last_hour > 0 during business hours

The webhook can return 200 all day while a code change quietly routes every charge to failed or leaves it pending. Rows are still landing — so freshness and row-count both pass — but the outcome stopped. A check on the outcome column is the only one that sees it.

The trade-off is that outcomes are the most schedule-sensitive of all. "Zero captures" is an incident at 2pm and completely normal at 4am, so this pattern lives or dies on a business-hours guard and an honest floor. It's also the highest-value check you can write, precisely because it's closest to the thing you'd actually get paged for a human noticing first.

If you take one thing from this: uptime watches the machine, these patterns watch the outcome. The outage that embarrasses you is almost always in the second column, and almost never in the first.

Ready to run one of these against your own database? Connect a read-only Postgres or ClickHouse connection and turn on your first check — the free plan is 5 checks, no card. Then come back for the part nobody warns you about.

The part nobody warns you about: where the cron stops

Every query above runs fine from a cron line and a Slack webhook. For your first check, that is the right answer — it's free, it's yours, and it's the honest floor. This post isn't here to tell you otherwise. It's here to name, precisely, where that floor stops holding, because "just use a tool" without the reasoning is worthless.

Four things break, and they break in order:

• Per-check cron sprawl. One freshness script is fine. But freshness, row-count, per-tenant, per-region, and outcome checks — across a few tables — is a dozen crontab lines, each with slightly different SQL, none tested, all drifting. The count is the problem: nobody owns twelve one-off scripts on a box provisioned in 2021.
• Threshold drift. The 30-minute freshness bound and the 50% volume band were guesses the day you wrote them, and data moves. Without a record of which alerts were real, nobody ever revisits them — so they stay guesses, and a wrong guess is either noise or blindness.
• Mute-and-miss alert fatigue. A stateless cron re-sends every run, or sends once and never re-raises. Both train people to swipe the alert away. The channel gets muted after the third false page, and two weeks later the real failure lands in a room nobody's watching. Alert fatigue isn't a discipline problem; it's the design of a monitor with no memory.
• No ownership or resolution trail. When it does fire, there's no acknowledge, no assignee, no "is this the same problem I already know about," and no history of what the value was at 3pm yesterday. You're grepping a log file to reconstruct an incident.

None of these is a reason to feel bad about the cron. Each is just work — real, stateful, undifferentiated work — you'd have to build to turn a freshness probe into a monitoring system. Add up dedupe, an open-incident concept, tuned thresholds with history, an out-of-band scheduler, and per-slice diagnosis, and you've written a product.

That's the gap Alertee fills, and it fills it with the same plain SQL you already wrote — no black box, just the queries above running on an independent scheduler outside the pipeline they watch, with an inbox where a fire is acknowledged, owned, and resolved, and where classifying it real, transient, or expected feeds back so the next alert is quieter. Keep your cron for the first check. When the four things above start costing more than the query saves, connect a Postgres or ClickHouse database and turn one check on — 5 checks free, no card. It's the SQL you'd have written anyway, minus the monitoring system you'd have had to build around it.