Unit 12.03: Freshness, versioning, and stale-answer risk

Unit ID: SQL-M12-U04 - Estimated active time: 13-16 minutes Objective: make currency visible so an assistant cannot quote outdated facts as current.

Staleness becomes a correctness problem once data is quoted

A stale row in an analytics table produces a slightly old chart. A stale row in a retrieval table produces an assistant stating an outdated fact as current, with a citation that makes it look verified.

SELECT COUNT(*) FROM order_facts WHERE source_updated_at < '2026-06-15';
-- 472

Nearly half our rows predate mid-June. Whether that matters depends on the question - but the system must be able to tell, which means the field has to exist.

Surface age, do not just store it

SELECT doc_id,
       source_updated_at,
       DATE_DIFF('day', CAST(source_updated_at AS DATE), DATE '2026-07-01') AS days_old
FROM order_facts
WHERE order_id = 501;
-- order-501 | 2026-06-21 09:00:00 | 10

An answer that says "as of 21 June (10 days ago)" lets a reader judge it. An answer with no date invites them to assume it is current.

Deletion must propagate

The rule from Module 5's orphan check applies with higher stakes: if a source row is deleted and its retrieval row is not, the assistant keeps answering from data that no longer exists.

-- Retrieval rows with no surviving source order
SELECT COUNT(*)
FROM order_facts f
LEFT JOIN orders o ON o.order_id = f.order_id
WHERE o.order_id IS NULL;
-- 0

Zero orphans today. This query belongs in a scheduled check, not a one-off - because the failure appears later, quietly, and only when someone asks the wrong question.

Rebuild strategy

Full rebuilds are simplest and safest when the table is small: drop, recreate, done - no drift possible. Incremental updates scale better but must handle deletes explicitly, which is exactly the case people forget. If you choose incremental, the orphan query above is not optional.

Practice

Your assistant cites a policy that was superseded last month. Name the two preparation failures.

Check your answer

The superseded row was never removed or marked - deletion or supersession did not propagate to the

retrieval table.

No freshness field was surfaced, so neither the assistant nor the reader could see the fact was

old.

Either alone causes the incident; together they make it invisible until someone acts on the wrong policy.

Takeaway

Store and surface source_updated_at, and check for orphaned rows on a schedule. A retrieval table that cannot express its own age will quote the past as the present.

---