What Could the Model Have Known at That Moment?

Botie is preparing training rows for a model that will help prioritise pump inspections. Each row represents a decision made at noon. A temperature was measured at 11:00, but the device did not upload it until 13:00. Today’s database contains that measurement. The person making the noon decision did not have it.

If we include the late upload in the noon feature, the training example describes a better-informed system than the one we can actually deploy. We need to reconstruct the information available at each decision, not simply gather every record whose event time looks old enough.

Match events to decisions by asset, enforce both time boundaries, then summarise and reattach the results to every decision.

[
  {
    "decision_id": "D1",
    "feature_mean": 20,
    "n": 2
  },
  {
    "decision_id": "D2",
    "feature_mean": null,
    "n": 0
  }
]

A training row needs a stable identity and a stated prediction time. Here decision_id identifies the row, asset_id identifies the pump and cutoff is the instant at which we imagine making the prediction. The same pump can have several decision rows at different times.

Do not group only by asset_id when the task has repeated decisions. That would merge information boundaries that must remain separate.

One row per decision is the grain of the feature table, even when many events contribute to it.

event_at describes when the measurement occurred. available_at describes when that value became usable by the decision system. Ingestion, validation or publication can make availability later than the physical measurement.

These timestamps require trustworthy pipeline semantics. A warehouse load time is not automatically the moment a production service could read the value. If availability was never recorded, a query cannot reliably recover it from wishful assumptions.

The feature contract needs the availability of the actual value used, including later corrections.

The lookback includes events at the start of the preceding 24 hours and excludes events at the cutoff itself. The availability condition allows values usable no later than the cutoff. These inclusivity choices are part of this lesson’s contract.

All event conditions belong in the LEFT JOIN matching rule. That preserves decisions with no eligible event. AVG then remains NULL for those decisions, while COUNT of a real event ID is zero.

Event time prevents future observations. Availability time prevents later-arriving past observations.

CREATE TABLE decisions(decision_id TEXT PRIMARY KEY NOT NULL, asset_id TEXT NOT NULL, cutoff TEXT NOT NULL);
CREATE TABLE measurements(event_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, event_at TEXT NOT NULL, available_at TEXT NOT NULL, value REAL NOT NULL);
INSERT INTO decisions VALUES ('D1','P7','2026-01-02 12:00:00'),('D2','P8','2026-01-02 12:00:00');
INSERT INTO measurements VALUES
(1,'P7','2026-01-01 12:00:00','2026-01-01 12:05:00',10),
(2,'P7','2026-01-02 10:00:00','2026-01-02 10:05:00',30),
(3,'P7','2026-01-02 11:00:00','2026-01-02 13:00:00',90),
(4,'P7','2026-01-02 12:00:00','2026-01-02 12:00:00',100);

SELECT d.decision_id, AVG(m.value) AS feature_mean
FROM decisions AS d LEFT JOIN measurements AS m
  ON m.asset_id=d.asset_id
 AND m.event_at >= datetime(d.cutoff, '-24 hours')
 AND m.event_at < d.cutoff
 AND m.available_at <= d.cutoff
GROUP BY d.decision_id ORDER BY d.decision_id;

When a feature value looks surprising, select the joined event IDs and timestamps before aggregating them. That reveals a late arrival or a boundary event directly. An aggregate alone hides which records were admitted.

The workshop exposes this relation as a separate component. Its summaries are then left-joined back to the decision list, because filtering joined event rows first can otherwise remove decisions with no remaining matches.

Preserve the intended decision population separately from the population of eligible events.

CREATE TABLE decisions(decision_id TEXT PRIMARY KEY NOT NULL, asset_id TEXT NOT NULL, cutoff TEXT NOT NULL);
CREATE TABLE measurements(event_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, event_at TEXT NOT NULL, available_at TEXT NOT NULL, value REAL NOT NULL);
INSERT INTO decisions VALUES ('D1','P7','2026-01-02 12:00:00'),('D2','P8','2026-01-02 12:00:00');
INSERT INTO measurements VALUES
(1,'P7','2026-01-01 12:00:00','2026-01-01 12:05:00',10),
(2,'P7','2026-01-02 10:00:00','2026-01-02 10:05:00',30),
(3,'P7','2026-01-02 11:00:00','2026-01-02 13:00:00',90),
(4,'P7','2026-01-02 12:00:00','2026-01-02 12:00:00',100);

SELECT d.decision_id, m.event_id, m.value
FROM decisions AS d JOIN measurements AS m ON m.asset_id=d.asset_id
 AND m.event_at >= datetime(d.cutoff, '-24 hours')
 AND m.event_at < d.cutoff AND m.available_at <= d.cutoff
ORDER BY d.decision_id, m.event_id;

A failure label might describe whether the pump failed in the next seven days. That future information is the target being learned, not an input available at noon. Its source records and join conditions should remain separate from the feature calculation.

A decision made two days before the dataset ends has not necessarily completed a seven-day outcome window. Treating its absent failure record as a negative label can introduce censoring errors. Define when labels have matured, how failures are observed and how incomplete follow-up is handled.

A clear label definition includes its horizon and observation coverage as well as the outcome event.

Suppose an old value is corrected after the decision. If the original row was overwritten, filtering its old event time does not reconstruct what the model originally saw. Preserve versions or an as-of history with availability for each value revision.

Use a documented revision-selection rule when several versions of one logical event are eligible. Do not count those versions as independent physical measurements. Our small table deliberately contains one immutable row per event so that the timing rule can be studied first.

Point-in-time reconstruction depends on data history that the storage design actually retains.

A measurement joined to several maintenance notes can appear several times before averaging. Some averages may accidentally remain the same while counts and weighted summaries change. Aggregate each relation to the intended grain before combining independent one-to-many histories.

Check uniqueness of decision_id in the final result. Compare counts of eligible event identities as well as numerical summaries, and retain a versioned feature definition so a changed rule is not silently mixed into old training sets.

A time-correct join can still be wrong because of record multiplication.

The live service and historical query need compatible units, windows, missing-value handling and availability rules. A notebook calculation that uses a different cutoff or quietly fills missing means with zero will train a different input distribution.

Feast’s documented created-timestamp filter is one available mechanism when supported by the selected offline store and backed by a meaningful timestamp. Framework support does not decide the correct timestamp semantics or repair missing version history for us.

Verify the deployed feature values against independently reconstructed historical examples.

This lesson prevents one source of leakage within each row. Training and evaluation still need a split suited to the deployment question, which may involve later time periods, held-out equipment or both. Reusing related outcomes across the split can still make results look better than they will be on new work.

Keep preprocessing fitted only on the appropriate training data. The SQL table is an input to that learning process, not evidence that the entire modelling workflow is free of leakage.

The database boundary and the model-evaluation boundary solve different problems.

Move availability from 13:00 to exactly noon and predict that the 11:00 event becomes eligible. Move an event to exactly the decision cutoff and predict that it stays excluded. Add a second decision for the same pump and calculate its boundaries independently.

These changes test the information the model was allowed to have. They are more revealing than checking only whether the base mean happens to equal twenty.

A useful historical feature test states both what entered and why other plausible-looking evidence stayed out.