Data freshness monitoring for a business whose data arrives in bursts

Seven o'clock on a Monday, six weeks out from a show, and the pacing dashboard says 412 registrations for yesterday. Nobody in the room can tell whether that is a real Sunday or a pipeline that stopped at midnight on Saturday and has been serving the same numbers since. The registration platform is a vendor's, the extract runs on a schedule somebody set two years ago, and the only evidence either way is a number that looks plausible.

Data freshness monitoring exists to make that question answerable in one second instead of forty minutes. On an events business it is harder than it looks, because the correct answer changes with the calendar.

What a freshness check measures, precisely

Take the maximum update timestamp in a table, subtract it from now, and compare the result against how long a gap you were expecting. That is the whole mechanism.

Barr Moses of Monte Carlo set out five pillars of data observability in December 2020: freshness, distribution, volume, schema and lineage. She frames freshness as a set of questions rather than a metric, asking whether the data is up to date, what its recency is, and whether there are gaps in time when the data has not been updated. That third question is the one that matters for an event business, because gaps are normal here and the monitor has to know which gaps were expected.

The check needs one thing the table often does not have, which is a trustworthy timestamp. Three candidates usually exist and they measure different events. The business timestamp records when the registration was created. The source system's modified timestamp records when the vendor last touched the row. The load timestamp records when your pipeline wrote it. A freshness check built on the load timestamp will report a table as fresh after a pipeline run that loaded nothing, which is the most common way this control gets built wrong.

Use the business timestamp where it exists and the source modified timestamp where it does not, and store the load timestamp separately so you can tell the two failure modes apart.

Why does one threshold page you all summer?

Because the arrival rate of event data varies by two orders of magnitude across a single year, and a threshold is a statement about arrival rate.

Registration on a large annual show might land several times an hour through the on sale weeks, a few times a day through the middle of the campaign, and then nothing at all for a fortnight in the quiet period after the previous edition closed and before the next one opens. Exhibitor contract data behaves differently again, clustering around the space draw. Badge scan data does not exist at all until the doors open, and then arrives at thousands of rows an hour for three days.

Put a six hour threshold on the registration table and count what happens in the quiet period. If the check runs hourly and the table legitimately goes twelve weeks without a row, the monitor fires on every run after the sixth hour, which is 84 days of 24 runs, or 2,016 alerts about a system that is working exactly as designed. Nobody survives that. What actually happens is that somebody mutes the check in week one and forgets to unmute it, and the control is gone by the time it would have mattered.

Loosen the threshold to a week and the quiet period goes silent, which is correct, and show week now takes seven days to notice that the scan feed died, which is useless.

Bind the threshold to the show calendar

The fix is to make the expected arrival window a function of where each event sits in its cycle. Days to show open is the natural index, because it is the variable everything else in the business is already indexed on.

A workable ladder for a registration table looks like this. More than 180 days out, expect something within seven days. Between 180 and 60 days out, expect something within 48 hours. Between 60 and 14 days out, expect something within 12 hours. Inside the final fortnight and through show week, expect something within two hours.

Those numbers are a starting point and they should be replaced by yours. Derive them from history: for each prior edition, compute the actual gap between consecutive registration rows, take the ninety-fifth percentile of that gap within each phase, and use it as the threshold for that phase. A threshold set at the ninety-fifth percentile of normal behaviour fires on roughly one normal gap in twenty, which you then tune, and it is defensible in a way that a round number chosen in a meeting is not.

The same calendar drives every other feed with a different shape. Scan data has no expectation before doors open and a very tight one during the show. Lead retrieval files behave differently again, and a feed that lands once a year defeats this whole approach, which is why K16 handles it separately.

What does the tooling give you here?

dbt has source freshness built in, and it is worth knowing exactly what it does and does not offer.

You declare a loaded_at_field on the source and a freshness block containing warn_after and error_after, each taking a count and a period such as hour or day. Running the freshness command compares the maximum of that field against those thresholds and returns a warning or an error. dbt Labs positions it as a way to understand whether pipelines are healthy and to define service level agreements for the warehouse.

The two tier warn and error structure is the useful part, and it maps onto the calendar problem better than it first appears. The limitation is that a source declaration holds one pair of thresholds, so a phase dependent window needs either several source definitions selected by the run, or a check written yourself against the event dimension. Neither is difficult. Both need somebody to decide the phases first, which is the actual work.

Whatever runs the check, it should write its result to a table rather than only to a log. A history of freshness results is what lets you answer the question at the top of this post retrospectively, and it is also the input to any conversation with a vendor about whether their export has been late.

Alerting so that a quiet August stays quiet

Google's Site Reliability Engineering book, published in 2016 under Beyer, Jones, Petoff and Murphy, is blunt about the economics: "Paging a human is a quite expensive use of an employee's time", and the chapter on monitoring insists that every page should be actionable. One of its contributors puts the human limit plainly, saying they can only react with a sense of urgency a few times a day before becoming fatigued.

Apply that to a portfolio. Nine shows, each with several monitored feeds, gives you dozens of freshness checks, and the number of them that deserve to wake somebody is small. The split that works is by consequence rather than by severity label. A feed whose failure changes a decision within hours pages. A feed whose failure changes a decision within days warns into a daily digest that one person reads with coffee. A feed whose failure changes nothing until the next edition goes in a weekly report and nowhere else.

Write that classification down next to each source, review it once a year, and treat any check that has paged three times without anybody acting as a bug in the check.

The message itself decides whether the page is useful. A freshness alert that says a table is stale sends somebody hunting. A freshness alert that names the table, the event, the days to show open, the expected window, the actual gap and the last three load timestamps lets them decide in twenty seconds whether to get up. That content costs one query to assemble and it is the difference between an alert somebody reads and an alert somebody swipes away.

Where this stops

Freshness proves arrival. It proves nothing about content.

A vendor can send yesterday's file again under today's name, and the load timestamp updates, the row count looks normal, and the freshness check is green while the warehouse quietly holds stale data with a fresh label. The defences are a hash of the file contents compared against the previous load, and a check on the maximum business date inside the file rather than the maximum load date, and both are cheap. Neither is a freshness check, which is why the pillar list has five entries.

The other limit is the calendar itself. A phase based threshold depends on the event dimension holding correct show open dates, and on somebody maintaining it when a show moves. A show that shifts from March to May without the dimension being updated gets show week thresholds applied nine weeks early and quiet period thresholds applied during its actual show week. Make the calendar a monitored asset in its own right inside the unified data record, or you have moved the single point of failure rather than removed it. Volume is the other half of the picture, and an expected range built from prior editions is K18's subject.

Start this week by pulling the maximum registration timestamp per day for the last twelve months on one show, plotting it against days to show open, and reading off the longest gap that was genuinely normal in each phase. That plot is your threshold ladder, and it takes about an hour to produce.