Evidlake
Menu

Backfill

Also known as: historical load, back-population.

Loading or recomputing data for past periods after the fact — and recording when each value was really loaded, so history is not made to look earlier-known than it was.

Updated Last reviewed

Synthetic dataExamples on this page use synthetic data created for illustration.

Not adviceThis is educational material. It is not investment, business or other professional advice.

Definition

A backfill loads or recomputes data for past periods because a source became available late, a job was missed, or a calculation changed. A careful backfill records when each value was actually loaded, so that history is not made to look as if it had been known earlier.

In plain terms

Backfills are routine and useful: they fill gaps and fix old numbers. The danger is that a backfilled table looks like a clean, continuous history, and a study run on it implicitly assumes every value was available at the time it describes. That assumption is false for the backfilled part.

The remedy is to keep two timestamps: effective time (the period the value describes) and available time (when it was loaded). Backfilled rows get an effective time in the past and an available time of today.

Example

A supplier delivers 12 months of price history on 1 October 2026.

ItemEffective timeAvailable time
Price for March 2026 (backfilled)2026-032026-10-01
Price for September 2026 (collected live)2026-092026-09-30

A test that replays the system “as of 15 March 2026” must not see the March price: nobody had it then. If only the effective time were stored, the test would see it and look better than any real run could have.

Common misunderstandings

  • “Backfilled history is the history of what we knew.” It is the history of what happened, loaded later; what was known then is a different thing.
  • “A backfill is a one-off script.” Make it repeatable and idempotent; it will be run again.
  • “Filling gaps by interpolation is the same as backfilling.” Interpolated values are estimates and should be marked as such, not stored as observations.
  • Late-arriving Data

    Records that reach a pipeline after the period they describe has already been processed or reported. They are normal, so a pipeline needs a rule for them.

  • Bitemporal Data

    Data that records two times for every fact: when it was true in the world, and when the system learned it.

  • Look-ahead Bias

    A study error where information that was not yet available at the time of a decision is used to evaluate that decision.

  • Point-in-Time

    Reading data as it was known at a past moment — not as it looks today after later corrections and additions.

References

  1. Martin Fowler. Bitemporal History . martinfowler.com, 2021.
  2. Martin Kleppmann. Designing Data-Intensive Applications . O'Reilly Media, 2017. ISBN 978-1449373320.