The orchestrator is green. Every task in the DAG is a tidy tick. The job exited 0, the run log says “completed in 4m12s”, and the alert that would have fired on failure never fired, because nothing failed.

And analytics.orders has no rows for yesterday.

This is the most common shape of a silent data incident, and it happens because almost every team measures the wrong object. A pipeline monitor watches a process. What the business consumes is a table. The two agree most of the time, which is exactly what makes the disagreement expensive: you stop looking.

The job succeeded. Here is what still went wrong

Success in a scheduler means “the process terminated without raising”. It says nothing about rows. A catalogue of the ways a clean exit produces an empty table:

  • The source extract came back empty. The query ran, returned zero rows, and the loader dutifully loaded zero rows. Writing nothing is not an error to most loaders — it is a valid outcome, and a genuinely quiet day looks identical.
  • A date filter drifted across a timezone boundary. The extract asks for where created_at >= current_date - 1. The source moved to UTC, or the warehouse session timezone changed, or the clocks went forward. The window still exists, it just no longer overlaps the data.
  • An upstream API paginated to zero. A token expired, a cursor was mishandled, or a rate limit returned a 200 with an empty payload. The client saw a well-formed response, iterated zero pages and finished cleanly.
  • A MERGE matched nothing. The join key changed type or casing upstream, so nothing matched and nothing qualified as new. MERGE reports rows affected; nobody checks that number, and zero is a legal value.
  • A partition was written to the wrong day. The data arrived, in full, filed under the day before. The table is not empty — it is wrong in a way that only shows up when someone filters by date, which is to say, in every dashboard.
  • A retry succeeded on stale input. The first attempt failed halfway; the retry picked up a cached or partially-written source file and completed. The scheduler records only the final state: success.

Every one of these is a healthy-looking run, and none of them is visible from the run.

Measure the table, not the job

The only reliable definition of freshness is a property of the data itself. There are three signals worth tracking, and they answer different questions:

  1. max(event_date) — how recent is the newest business date? Catches the empty load and the missing day. Misses a backfill written into the past.
  2. Last row insert time (max(loaded_at), or the warehouse’s table metadata) — when did this table last change? Catches a table that has quietly stopped receiving anything, including one whose business dates look fine because the last successful load was a week ago.
  3. Row count for the expected period — did the period we care about get filled? Catches the partial load and the wrong-day partition. Most teams skip it, because it requires knowing what “expected” means for that table.

You want all three. A table can have a fresh loaded_at and no rows for yesterday — the job ran and wrote nothing but a control record. It can have a healthy max(event_date) and not have been touched in days, because someone backfilled a future-dated row. Together they are hard to fool:

select
  max(event_date)                                          as latest_business_date,
  max(loaded_at)                                           as last_row_inserted,
  count(*) filter (where event_date = current_date - 1)     as rows_for_yesterday
from analytics.orders;

That is a thirty-second query and it beats a week of green ticks.

The timezone trap, explicitly

This deserves its own section because it is the failure that survives every review. current_date is not a fact about the world; it is a fact about the session that asked. A warehouse in UTC, a source system in Europe/London, and a business that closes its day at local midnight disagree for one hour in summer and not at all in winter — so the bug ships in November and surfaces in March.

Three rules keep it contained:

  • Store timestamps as timestamptz (or your warehouse’s equivalent instant type), never as a naive local timestamp. An instant is unambiguous; a wall-clock reading is not.
  • Convert once, at the boundary, and name the zone. Compute the day window in the business’s timezone and compare against instants:
-- rows belonging to yesterday *in the business's timezone*,
-- regardless of what the warehouse session thinks the date is
select count(*)
from analytics.orders
where created_at >= ((current_date - 1)::timestamp at time zone 'Europe/London')
  and created_at <  ((current_date    )::timestamp at time zone 'Europe/London');
  • Never compare a date column to a timestamp without saying which zone the date belongs to. An implicit cast will pick one for you, and it will pick UTC.

If a partition column was derived upstream in a different zone from the one you filter in, an hour of every day lands on the neighbouring partition. Most days that is a rounding error. On the day the clocks change, or the day a batch runs late, it is a missing day.

A check that respects the table’s own cadence

A fixed rule — “alert if the newest row is older than 24 hours” — fires every Monday on a table that only loads on business days, and never fires on a table that should load hourly. Cadence has to come from the table’s history, not a constant.

For a daily table that skips weekends, work out the last period it should have data for, then look for it:

with expected_period as (
  -- most recent business day the table is meant to cover
  select max(d)::date as period
  from generate_series(current_date - interval '10 days',
                       current_date - interval '1 day',
                       interval '1 day') as g(d)
  where extract(isodow from d) < 6          -- Mon-Fri only
),
actual as (
  select
    count(*)        as row_count,
    max(loaded_at)  as last_loaded_at
  from analytics.orders, expected_period
  where event_date = expected_period.period
),
baseline as (
  -- what this table normally does on the same weekday
  select
    percentile_cont(0.5) within group (order by daily_rows) as median_rows
  from (
    select event_date, count(*) as daily_rows
    from analytics.orders
    where event_date >= current_date - interval '90 days'
      and extract(isodow from event_date)
          = (select extract(isodow from period) from expected_period)
    group by 1
  ) h
)
select
  expected_period.period,
  actual.row_count,
  baseline.median_rows,
  case
    when actual.row_count = 0                              then 'missing'
    when actual.row_count < baseline.median_rows * 0.5     then 'partial'
    else 'ok'
  end as freshness_state
from expected_period, actual, baseline;

For an hourly table, apply the same idea to gaps rather than days — learn the normal interval instead of guessing a threshold:

with load_gaps as (
  select loaded_at - lag(loaded_at) over (order by loaded_at) as gap
  from (
    select distinct date_trunc('hour', loaded_at) as loaded_at
    from analytics.events
    where loaded_at > now() - interval '30 days'
  ) h
)
select percentile_cont(0.99) within group (order by gap) as normal_max_gap
from load_gaps;

Anything well past normal_max_gap is a real stall. Anything inside it is Tuesday. Weekly tables get the same treatment on a weekly grain, and a genuinely irregular table gets a wider tolerance rather than a suppressed check.

Run the check when the loads have finished, not at a fixed UTC hour

Timing is where well-built freshness checks go to die. A check that runs at 02:00 UTC against an organisation whose ETL finishes at 04:30 local time reports a missing day every day — correctly and uselessly. Within a month the alert is a filter rule in someone’s inbox.

Anchor the check to the organisation’s schedule instead:

  • Run after loads normally complete, in the organisation’s own timezone, with margin for a slow night.
  • Derive the margin from history. If the last load lands between 03:10 and 04:40 on a typical night, checking at 05:00 is honest. Checking at 03:15 is a coin flip.
  • Treat “late” and “missing” as one finding with different urgency. A table two hours behind its usual arrival is worth knowing about; whether it eventually lands sets the severity, not whether there is a problem.

A freshness check that cries wolf is worse than none, because it trains the one person who would have investigated to stop reading.

How Sentry handles it

Freshness is one of Sentry’s six nightly checks, and it is measured exactly this way — on the table.

  • The table is the subject, not the job. Sentry has no view of your orchestrator and needs none. It asks the warehouse what the data says: the newest business date, when the table last received rows, and whether the expected period is populated.
  • Cadence is learned, not configured. Sentry profiles each table’s history to establish its rhythm — daily, hourly, weekly, weekday-only, weekend dips — and judges tonight against that baseline rather than a fixed threshold. Nothing to specify beyond picking the table.
  • Scans run after your data has finished loading. The nightly scan is anchored to your organisation’s timezone, defaulting to 5:00 AM and configurable on every plan, so a check never fires because it arrived early.
  • Findings carry their evidence. Each gives you a severity, a plain-English diagnosis, the queries that were run, and the baseline it was judged against — enough to tell “the source was quiet” from “the MERGE matched nothing” without re-deriving it.
  • Expected quiet days stop being findings. Mark one as expected and it folds into the baseline; mute the check on a table you know is mid-migration.

The green tick tells you a process ran. The digest tells you whether the data showed up.

Pick a plan and get started — read-only credentials, first digest tomorrow morning.