Freshness checks for annual feeds when a source lands once a year

The lead retrieval reconciliation file for a March show arrives once, about ten days after the doors close, as a single export from the scanning supplier. Somebody built a freshness monitor on that table when the warehouse went in. It has been red since the second week of April, every day, for eleven months, and the channel it posts to was muted in May.

Freshness checks for annual feeds break in a specific and predictable way, and the fix is to stop measuring elapsed time and start measuring against the show calendar.

Why an hours based threshold cannot work here

Freshness tooling asks you to express the expected gap as a duration. dbt's source freshness configuration is the clearest example: you declare a loaded_at_field, then warn_after and error_after, each holding a count and a period such as hour or day, and the run compares the newest value in that field against those thresholds.

That structure works beautifully on a table that updates hourly. Put an annual feed through it and the arithmetic collapses.

The threshold has to exceed the longest legitimate gap, which for a once a year file is a little over 365 days. Say you set error_after to 400 days to leave room for a show that moves. The file is due 10 days after show close. If it never arrives, the error fires 400 days after the last one landed, which is roughly 390 days after the moment you needed to know, and by then the show it belonged to has been reported on, invoiced and closed. The check is technically correct and operationally worthless.

Tightening the threshold does not help either. Anything under a year fires during the normal gap, which is how the March show ended up with eleven months of red.

Replace the clock with the event calendar

The expectation for an annual feed is not a duration. It is a date derived from an event.

State it as an assertion: for every edition whose show close date has passed, a lead retrieval file for that edition must exist, and it must have arrived within the agreed number of days after close. Run that assertion daily. For most of the year it evaluates every edition, finds them all satisfied, and says nothing. In the fortnight after a show it becomes the most useful check in the warehouse.

The check needs three inputs and all three should already exist. The show close date, from the event dimension. The list of feeds expected for each event, which is a small table somebody has to write once. The arrival timestamp of the file for that specific edition, which means the landing table has to record which edition each file belongs to rather than only when it turned up.

That third input is where most implementations fall over. A file that lands with no edition key can be counted but cannot be attributed, and an annual check that cannot attribute is back to counting elapsed time.

Getting the edition key onto the file is usually easier than it sounds, and it rarely requires anything from the supplier. The landing process knows which directory or mailbox the file came from and it knows the date it arrived. Match that date against the event calendar, pick the edition whose close date most recently preceded it, and record the result along with a flag saying the key was inferred rather than supplied. Inferred keys are good enough for a freshness assertion and should never be good enough for a financial reconciliation, which is a distinction worth carrying in the column rather than in somebody's memory.

The same landing table should record the file name, the byte count and a hash. All three are free at load time and all three answer questions you will be asked later, usually by a supplier who is confident they sent it.

How many days should the window be?

Work it out from your own history, and be honest about how little of it there is.

Take the last two editions of one show. Show close on 14 March 2024, file received 22 March, a gap of 8 days. Show close on 20 March 2025, file received 3 April, a gap of 14 days. The mean is 11 days and the range is 6 days wide, which on two observations is a description rather than a distribution.

Set the warning at 10 days and the error at 14, and write down that the numbers came from two editions. Then note the second finding, which is more useful than the threshold: the 2025 file was six days later than the 2024 one, and nobody raised it at the time because there was nothing watching. A check set at 10 days would have flagged it on the fourth of April, when the supplier could still remember what happened.

One refinement is worth the effort where the supplier is slow. Count in working days rather than calendar days, because a show that closes on a Thursday and one that closes on a Sunday give the supplier different amounts of time to produce the same export, and a calendar day threshold quietly penalises the first. On the two editions above, the 2024 gap of 8 calendar days is 6 working days and the 2025 gap of 14 calendar days is 10, which narrows the apparent deterioration and still leaves it real.

Five editions of history gives you five observations, which is the ceiling for this kind of feed no matter how mature your warehouse is. That is the honest constraint of annual data, and it argues for setting thresholds from the agreement with the supplier rather than from the sample. If the contract says ten working days, the check asserts ten working days, and the history tells you how often the supplier has met it.

What about the year the check never runs?

An annual assertion has a failure mode that a continuous one does not: it can quietly stop being evaluated and nobody notices, because silence is its normal output.

Three habits cover it. Write the check so it emits a result on every run, including the runs where everything passes, and store those results. A table with 365 rows a year saying "evaluated, nothing due" is proof the check is alive. Second, add an assertion about the assertion: if no freshness evaluation has been written in the last two days, that is itself an incident. Third, test the check against a deliberately withheld file once a year, in the quiet period, and confirm it fires.

That third one takes twenty minutes and it is the only one of the three that proves the alerting path still works. Channels get archived, integrations expire, and a rule nobody has seen fire in fourteen months is a rule nobody should trust. The same reasoning applies to any monitor that spends most of its life idle, which is a large share of them in an events business.

Where the annual feed differs from everything else

Two properties make these feeds worth handling separately rather than folding into the continuous monitoring described in freshness monitoring on tables that update through the campaign, which K15 covers.

The first is that there is no partial signal. A registration table that is broken shows up as a slowdown before it shows up as a stop, and a monitor can catch the slowdown. An annual file either arrives or does not, so the check has exactly one moment to be right.

The second is that the consequence is deferred and then permanent. A missing scan reconciliation file discovered eleven months late cannot be reproduced, because the supplier's retention period has expired, the account manager has changed and the show is two editions ago. That makes the value of the check unusually high relative to its cost, and it makes the cost of the muted channel unusually high too.

Barr Moses of Monte Carlo, writing in December 2020, named freshness as one of five pillars of data observability alongside distribution, volume, schema and lineage, and framed it around whether there are gaps in time when the data has not been updated. On an annual feed the gap is the normal state, which inverts the check without changing the question.

Where this stops

The whole approach rests on the event calendar being correct, which moves the risk rather than removing it.

If nobody updates the event dimension when a show shifts by three weeks, the check compares arrival against the wrong close date and either fires early or stays silent through the window that mattered. The mitigation is to treat the calendar as monitored data in its own right within the unified data record, with its own assertion that every event with a passed close date has an actual close date recorded rather than a planned one.

There is a second limit worth stating to whoever owns the supplier relationship. Arrival is not content. A file can land on day eight, satisfy every check, and contain the previous edition's data because somebody at the supplier ran the wrong export. Freshness cannot see that, and the cheap detection is a row count compared against the badge scan totals you already hold, plus a check that the maximum date inside the file falls inside the show dates. What a record's age means once it is safely landed is a different question, and the age at which a record starts to mislead belongs to K17.

This week, list every feed that arrives fewer than twelve times a year, and for each one write down the event it belongs to, the number of days after show close it is expected, and whether anything currently checks it. Most teams find two or three feeds on that list with no check at all, and one with a check that has been muted since the last edition.