Cumulative counters, daily deltas, and the data you can lose forever

Music platforms report totals. A track has streams since release, an artist has followers, a video has views. Nobody publishes a dated daily series, so a system that wants one has to snapshot the total daily and subtract.

Simple, and it fails in three specific ways.

Failure one: advancing the baseline on a failed write

This is the expensive one.

The pipeline reads today's total, computes the difference against yesterday's stored total, and sends that daily figure somewhere: an analytics service, a downstream store, a chart. Then it stores today's total as the new baseline.

If that send fails and the baseline is stored anyway, tomorrow's difference is computed from the new baseline as though nothing was missed. The day that failed to send is gone. Not delayed, not retried, gone, and nothing errored because from the pipeline's point of view everything succeeded.

We shipped that. The fix is one column.

Store a flag on each snapshot recording whether that reading was successfully used as a confirmed baseline. The baseline lookup only considers confirmed rows. Now a failed send leaves the previous confirmed baseline in place, and tomorrow's run computes a two day difference covering the gap. Nothing is lost, and the retry is automatic.

Failure two: dumping a multi-day gap on one day

Once gaps are retried, the naive version dumps the entire missed period onto the day the pipeline recovered.

Charted, that is a spike. Interpreted, it is a marketing win that never happened, and someone will reforecast on it.

The honest treatment is to spread the difference evenly across the real days in the gap, put the remainder on the last day so the total still reconciles, and flag every generated point as estimated. The flag is what matters: downstream consumers, chart tooltips and AI summaries can then say the value was spread across a span rather than measured.

Even spreading is a guess. It is the least wrong guess available, and being clear that it is a guess is the difference between a defensible number and a fabricated one.

Failure three: unbounded gap fill

A corrupted baseline, or a project onboarded with an ancient reading, produces a difference spanning hundreds of days. Spread that and you have manufactured a year of fake daily data in one run.

Cap it. Ours refuses to spread across more than 90 days and records the event instead. Beyond that horizon the honest answer is that the history is not recoverable from a counter, and something else has to supply it.

Clamping, and when not to

Counters are monotonic upstream, so a negative difference means an upstream correction: fraud filtering, a restatement, a merge of duplicate identifiers.

For a counter, clamp at zero. Negative streams are not a thing, and the correction is not a day's activity.

For a level, such as a follower count, never clamp. Followers genuinely fall, and hiding that is a worse error than showing an unflattering week. This is the same distinction as in the 7 day problem.

The first snapshot is not a baseline

When a source is first connected, the first reading may be partial, may lag, or may be a warm-up value from an upstream crawl.

If that reading becomes a baseline, the second day's difference includes the warm-up error and produces a spectacular fake first day. Skip it: never let the earliest stored reading serve as a window or delta baseline. It costs one day of history and removes a whole class of onboarding artefact.

Idempotency

Runs get retried, sometimes twice in a day. Two things make that safe.

Write snapshots with an upsert keyed by source, identifier and date, so a second run in the same day overwrites rather than duplicating.

Give every downstream event a deterministic identifier derived from the same key, so a duplicate send is deduplicated rather than double counted.

Without those, an ordinary retry after a timeout produces double the day's numbers, which is a harder bug to notice than a missing day and a worse one to explain.