A Row Dated Forward Outlives Its Own Fix

"A mislabelled row dated FORWARD outlives its own fix, because it sorts as the newest reading and the read model keeps serving it."

The property that makes this bug special

Most data bugs are self-clearing. You fix the writer, the next run produces correct rows, and the wrong ones sink harmlessly into history where nobody looks. That intuition — fix the code and it drains out — is so reliable that it operates below the level of conscious thought.

It fails completely here, and the reason is one line in the read model. The dashboard shows, for each series, the row with the greatest data_date. A row dated into the future is permanently the greatest. It is not sinking into history; it is sitting on top of it, and it will keep sitting there tomorrow, next week, and next year.

So the sequence goes: you find the bug, you fix the writer, you deploy, you reload the dashboard — and you see the same wrong number. The fix worked perfectly and changed nothing visible, which is one of the more disorienting experiences available in this line of work.

It shipped, and then it survived
The forward-dated rows went out. The date fix went in. And the wrong rows kept being served afterward, because "newest by date" is exactly what a mislabelled-forward row wins. They came out only when a repair script deleted them by name. A fix to the writer is a fix to the future; the rows already written are a separate piece of work, and forgetting that is the default.

Two orderings, and the gap between them

The general principle underneath: any store that ranks by a data-derived key inherits the trustworthiness of that key. Sorting by insertion order or by an auto-increment id is monotone and safe from this class of failure — the newest row is genuinely the one written last. Sorting by a business date is more useful and strictly less safe, because a business date can be wrong in a direction that matters.

And the direction is everything. A row dated backwards is nearly harmless: it sorts into the past, gets ignored by the read model, and waits quietly to confuse someone reading history. A row dated forwards is at the front of the queue forever. Same defect, same magnitude, opposite blast radius.

Fixing the writer does not fix the reader's answer. Whenever wrong data has been persisted, there are two distinct pieces of work: stop producing it, and remove what was produced. They are separate, and only the first one feels like progress. Ask explicitly, every time: which rows are already out there, and does anything still serve them?

Guard rails you can add cheaply

Once you have met this failure, the defenses are obvious and small.

The cheapest is a sanity bound on ingest: reject any row whose business date is meaningfully in the future relative to a generous clock. There is no legitimate reason for a market close to be dated tomorrow, and a rule that says so converts a silent corruption into a loud, immediate failure at the exact moment it is easiest to diagnose.

The second is a read-model assertion: if the newest row of any series is dated in the future, that is a defect, not a reading. Surfacing it as such is far better than serving it, because a reader who cannot see the discrepancy has no way to know they should distrust the number.

Code

Why the wrong row wins, and the two guards that stop it·sql

-- The read model, in essence: newest row per series.
SELECT gauge, market, series, data_date, value
FROM (
  SELECT *, ROW_NUMBER() OVER (
             PARTITION BY gauge, market, series
             ORDER BY data_date DESC) AS rn
  FROM gauge_snapshots)
WHERE rn = 1;

-- A row dated 2026-08-06 for the 2026-08-05 session is rn = 1
-- forever. Fixing the writer does not move it.

-- GUARD 1 -- refuse the impossible at ingest:
--   a market close can never be dated after today (UTC, generously).
--   Silent corruption becomes a loud failure, at the one moment
--   the cause is still on screen.

-- GUARD 2 -- assert on read:
SELECT gauge, market, series, data_date
FROM gauge_snapshots
WHERE data_date > date('now');
--   Any result here is a DEFECT, never a reading.
--
--   Note the predicate, because the obvious version is wrong in
--   exactly the way this whole track is about. `> date('now',
--   '+1 day')` looks safer -- a whole day of slack! -- and it
--   MISSES the actual incident, because a row dated one day
--   forward is EQUAL to that bound, not greater than it. The
--   off-by-one costs you the only case you were guarding against.
--   A market close can never be dated after today at all, so
--   today is the bound.

Exercise

Find a table in your systems whose reads take the max of a business-meaning column — latest_version, effective_date, valid_from, published_at. Then write the query that finds rows beyond a plausible bound in that column. If any exist, you have already served them. If none exist, add the ingest check anyway; this defect is silent right up until it is permanent.

Hint

The question that finds these fast: 'if one row of this column were wrong by a day in the wrong direction, would anybody ever notice?' If the read model takes the max, the answer is that it would be noticed immediately and misdiagnosed as a data-source problem.

The property that makes this bug special

Two orderings, and the gap between them

Guard rails you can add cheaply

Code

External links

Exercise

Progress

Comments 0