How do you define a freshness check on a source table feeding a warehouse model?

## The failure freshness exists to catch Every other test in a warehouse examines the data that is present. Freshness asks whether data that should be present is missing. This matters because the most common silent failure in an analytics platform is not a broken transformation — it is a source that quietly stopped arriving while every job continued to succeed. The models build, the tests pass, the dashboards render, and they show last Tuesday. ## The mechanics The check reads the newest timestamp in the table and compares it to now: ```sql select max(updated_at) as newest_row from stg_orders having max(updated_at) < current_timestamp - interval '6' hour ``` A returned row means the source is stale. In practice the threshold is expressed as two levels — a warn threshold and a fail threshold — because a source running an hour behind is a different situation from a source that stopped yesterday. Both thresholds derive from the load cadence, not from a guess. A table loaded hourly should never be more than about an hour old plus the run time of the job plus a margin; a daily overnight extract that lands by 06:00 is stale at 09:00. Setting the threshold from the schedule makes it defensible; setting it from "what feels late" makes it noise within a month. ## Ingestion time and event time are different questions This is the distinction that separates a real freshness check from a decorative one. - **Ingestion timestamp** — when the row landed in the warehouse. Fresh ingestion means the pipe is moving. - **Event timestamp** — when the thing happened in the business. Fresh event time means the business activity is actually being captured. They fail independently. A replication process that is happily copying a frozen upstream table will refresh ingestion timestamps every hour over rows whose event times stopped last Thursday. An ingestion-only check calls that healthy. Conversely, a backfill of six months of historical events will look catastrophically stale on event time while being perfectly correct. So a mature check watches both, with different thresholds and different meanings: ingestion staleness is an infrastructure alert, event staleness is a business-data alert. Timezone handling is a related trap. If the source writes local timestamps and the check compares against UTC, the check is wrong by the offset — usually harmless with a six-hour threshold, quietly fatal with a one-hour one. ## Thresholds must know the calendar A source that is only produced on business days will breach any fixed threshold every weekend, and a check that alerts every Saturday is a check people mute. Real thresholds account for the source's actual schedule: business days, holidays, month-end batch windows when the upstream system is closed. If encoding the calendar is impractical, the alternative is to suppress alerts during known quiet windows rather than to loosen the threshold, which would blind the check on weekdays too. ## Freshness of the source versus freshness of the model A model can be rebuilt on time from stale sources. The pipeline is green, the model's own last-built timestamp is minutes old, and the content is days old. That is why freshness belongs on the *sources* — at the boundary where data enters — and not only on the outputs. Checking a model's build time answers "did the job run", which the scheduler already told you. The strongest posture is to make source freshness a gate: if the source is stale beyond the fail threshold, do not rebuild downstream models at all. Rebuilding over stale input republishes an old number with a new timestamp, which actively misleads anyone who checks when the dashboard last updated. ## What freshness does not tell you, and its natural partners A table can be perfectly fresh and completely wrong. Two complementary checks fill the obvious gaps: - **Row-volume checks.** A load that lands on time with zero rows is fresh. A volume assertion — rows in the last interval must be greater than zero, or within a range of the recent norm — catches an empty or half-sized delivery. - **Timestamps from the future.** A clock skew or a timezone bug produces rows dated ahead of now, which quietly makes `max(timestamp)` useless as a staleness signal forever after. Asserting that no timestamp exceeds now is a one-line check that protects the freshness check itself. ## Where it sits in the suite Freshness is cheap — a single aggregate, often satisfied from metadata — so it runs first, before any expensive structural test. Ordering it that way means a stale-source morning ends in seconds with a clear diagnosis instead of thirty minutes of transformation work followed by an ambiguous reconciliation break.