The Date Column Is Lying To You

A row that says August, measured in February

A record in your database says it was last updated on August 19. Open it and about half the fields were measured in February. Nothing on the screen says so.

The row is not corrupt. It has no nulls, no gaps, no strange values. It will pass every data quality check you have pointed at it, and it will pass the freshness check hardest of all, because the timestamp genuinely is fresh. That is the whole problem in one sentence.

This is a different failure from stale data. Stale data is a date that is too old, and it announces itself: someone notices the dashboard has not moved. This is a date that is too new. It reports confidence it has not earned, and nobody notices anything at all.

How a row ends up with two ages

The mechanism is ordinary, which is why it is everywhere. A job runs and updates part of a record. Maybe it refreshes pricing but not inventory. Maybe it re-checks a few fields and leaves the rest. Whatever it touches, it writes those fields and then stamps the row with the current time, because that is what an update does.

The fields the job did not touch keep the values they had. Those values were measured weeks or months ago and they are still correct as of then. But they now sit in a row whose one date column says August, so they inherit August by association.

There is no moment where anything went wrong. Each write did exactly what it was told. The row simply ended up asserting a single measurement date over data of mixed ages, and there is nowhere in its shape to say otherwise.

Why every standard check misses it

Run the usual battery and the row sails through. Null checks pass, because every field has a value. Row counts pass, because nothing was added or lost. Anomaly detection passes, because none of the values are unusual. Referential integrity passes.

The freshness monitor is the one that hurts. It is checking whether the timestamp is recent, and the timestamp is recent. What it is actually being asked to answer is whether the data is recent, and it has no way to see that. The timestamp is standing in as a proxy for the age of the row, and in this case the proxy has come apart from the thing it represents.

This is the same shape as a zero that means two different things, which we wrote about in Your Dashboard Cannot Tell You It Is Broken. A single cell is being asked to carry a distinction it was never designed to hold, and the reader has no way to recover the missing half.

Three shapes a row can legitimately have

The fix is not a patch. It is a decision about what a row means, and the team has to actually make it rather than inherit it.

A current-state row. It represents the thing as it is right now. Every field is rewritten on every update, whether or not the value changed. The date column means what it says, because everything in the row was measured at that moment.

A point-in-time observation. It represents what was true when it was measured. You never update it. New measurement, new row. The date is part of the record's identity rather than metadata about it.

A mixed row with a date per field group. Sometimes you genuinely need one row carrying several things measured on different schedules. That is fine, but then one date column is not enough. Each group gets its own date, and the row stops making a claim it cannot support.

Most tables with this problem are trying to be the first and the second at the same time.

Where these writes come from

Nightly syncs. Backfills. Partial imports. Enrichment jobs that add a few fields to records someone else created. Anything that reads a source, maps a subset of fields, and writes them into an existing row.

It is worth being precise about where the bug is, because it is easy to blame the wrong layer. Merge and upsert semantics are not the problem. Preserving fields the payload did not mention is exactly what a merge is supposed to do, and if it did anything else it would be far more dangerous. The bug is in the payload: it carries a timestamp that describes only the part of the row being written, and that timestamp then gets applied to the whole thing.

An engineer reading a post that calls merge behaviour a bug will stop trusting the rest of it, and they would be right to.

What it costs, and the comparison goes first

Period over period comparison is usually the most valuable thing a dataset does, and it is the first casualty.

Ask how many records were measured in August and the answer over-counts, because rows measured in February are wearing an August date. Ask what changed since February and you get very little back, because February keeps moving forward. The records that should anchor the comparison have quietly joined the other side of it.

Year over year is worse, because the drift compounds and nobody has a reference point old enough to notice. The numbers stay plausible the whole time. That is what makes this expensive rather than merely annoying. There is no point at which the data looks wrong enough to investigate.

The audit you can run this week

Pick the table that the most decisions come out of. Find its date column. Then ask one question about it: was every field in this row measured on that date?

You do not need a specialised tool. Find the jobs that write to the table, and for each one, list which fields it sets. If any job sets a subset of the fields and also sets the row timestamp, you have found it. The shape of the query barely matters, because the answer usually comes from reading the write path rather than the data.

If you want to confirm it in the data, compare a field only the full refresh touches against a field the partial job touches, on the same rows, and look for rows where the two disagree about when they were last plausible. The specifics depend on your schema, which is why the write path is the faster route.

Knowing which job wrote which field on which day is the same provenance question we covered in Your Vendor Changed 200 Things On Your Site, and most teams find they cannot answer it for their own pipelines either.

A date column is a claim

Every timestamp in your database asserts that something was true at a moment. It is worth treating it that way, because everything downstream already does. Reports group by it. Comparisons anchor on it. Alerts fire off it. Freshness checks certify it.

Most teams have never checked whether theirs is true. It is cheap to check once and expensive to discover after a year of comparisons built on it, which is roughly when it usually surfaces. A frozen dashboard at least looks frozen, as we covered in The API Retired Today. A row with two ages looks perfect.