SQL, data modeling, data quality, EDA, visualization · insight-1058
Keep two clocks for corrected facts, when it was true and when you learned it
- Evidence type
- practitioner heuristic
- Difficulty
- hard
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- finance, aviation, cross-industry
Summary: situation, action, reason, boundary
Situation
Finance restates last month's revenue after a refund batch is posted, and the dashboard owner overwrites the monthly figures; a model trained on last month's numbers can no longer be reproduced.
Action
Store a business-effective time and a record (load or knowledge) time on corrected tables, append corrections rather than updating rows, and query "as of" a record time when reproducing a past report or a training set.
Reason
Corrections change what was true in the world and what the system knew, and these are two independent time axes.
Boundary
Restated financials, revised forecasts, late refunds, corrected sensor data and training data for models that must be point-in-time correct.
Symptom or risk
Overwriting loses what was believed at each earlier date, so audits, backtests and "why did the number change" questions cannot be answered.
Exceptions
Data where corrections take effect immediately and history is not needed.
Trade-offs
Roughly doubles the logic in every query and storage for rewritten history.
Pitfalls
Adding only an updated_at column, which records when a row changed but not which period it describes.
Lessons and concepts
Claims and their sources
Fowler's bitemporal history separates actual time from record time so that retroactive corrections can be reconstructed, keeps record history append-only, and notes the added complexity.