An old event timestamp can still leak future information into training data

A payment happened at 11:45. Your system received it at 12:10. Could a decision made at noon have used it?

Filtering on the event timestamp alone says yes. Reconstructing what the system knew at noon says no.

That difference matters when building historical features. A backfilled event can be old in business time and new information to the system.

Two rows are enough to expose it

Assume one entity, one currency, unique immutable event IDs, and a decision at 12:00 UTC. The feature is the sum of amounts from the preceding hour.

ID	Amount, minor units	Event time	Available to the decision system
a	1,000	11:30	11:31
b	2,500	11:45	12:10

An event-time filter includes both rows and returns 3,500. The historically available sum is 1,000, contributed by a alone.

The second amount is valid data. It is simply unavailable for this decision. Cleaning duplicates or fixing its timestamp would not address the problem.

Make the cutoff explicit

This example uses a half-open event window: [11:00, 12:00). Event time exactly at the decision is excluded. Availability exactly at the decision is included. These are deliberate exercise rules; a real system needs a cutoff that matches its actual read semantics.

The additional predicate is:

WHERE event_time >= :window_start
  AND event_time < :decision_at
  AND available_at <= :decision_at

Here is the complete experiment. Save it as point_in_time_demo.py and run it with Python. It uses only the standard-library sqlite3 module and an in-memory database.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE events(id TEXT, amount_minor INTEGER, event_time TEXT, available_at TEXT)")
db.executemany("INSERT INTO events VALUES (?, ?, ?, ?)", [
    ("a", 1000, "2026-01-02T11:30:00Z", "2026-01-02T11:31:00Z"),
    ("b", 2500, "2026-01-02T11:45:00Z", "2026-01-02T12:10:00Z"),
])

start = "2026-01-02T11:00:00Z"
decision = "2026-01-02T12:00:00Z"


def feature_rows():
    return db.execute("""
        SELECT id, amount_minor FROM events
        WHERE event_time >= ? AND event_time < ? AND available_at <= ?
        ORDER BY id
    """, (start, decision, decision)).fetchall()


event_time_total = db.execute("""
    SELECT COALESCE(SUM(amount_minor), 0) FROM events
    WHERE event_time >= ? AND event_time < ?
""", (start, decision)).fetchone()[0]
rows = feature_rows()
available_total = sum(amount for _, amount in rows)
assert (event_time_total, available_total, rows) == (3500, 1000, [("a", 1000)])
print(f"event-time-only={event_time_total}; available-at-decision={available_total}")
print("contributing events:", rows)

# Boundaries are part of this teaching contract, not inferred from timestamp names.
db.executemany("INSERT INTO events VALUES (?, ?, ?, ?)", [
    ("start", 25, start, "2026-01-02T11:01:00Z"),
    ("known-boundary", 5, "2026-01-02T11:59:00Z", decision),
    ("at-decision", 99, decision, decision),
    ("too-old", 50, "2026-01-02T10:59:59Z", "2026-01-02T11:01:00Z"),
])
assert feature_rows() == [("a", 1000), ("known-boundary", 5), ("start", 25)]
assert sum(amount for _, amount in feature_rows()) == 1030
print("window and availability boundary checks: passed")

The output from the local run was:

event-time-only=3500; available-at-decision=1000
contributing events: [('a', 1000)]
window and availability boundary checks: passed

The extra assertions test the start of the window, a row becoming available exactly at the decision, an event exactly at the decision, and an event just before the window. They check contributing IDs as well as the total.

Define what “available” means before trusting the query

In this fixture, available_at means the information was usable by the component making the decision. A warehouse ingestion timestamp may be too early or too late to represent that. A message can arrive before enrichment finishes, before a materialization commits, or before a serving system can read it.

The query only enforces the meaning you give that column. It cannot recover historical availability from a table that overwrites it during a backfill.

For corrected records, the problem gets another dimension: which version was known then? This immutable-event example deliberately does not implement version history.

The text comparisons are also intentionally narrow. Every timestamp here uses the same fixed-width UTC format ending in Z. Lexicographic comparisons are not a general solution for mixed offsets or timestamp formats. Normalize instants before comparing them, and use appropriate timestamp types in a production database.

For multiple entities and currencies, filter or join on those keys too. Adding EUR and USD minor units would produce an invalid feature even if every time predicate were correct.

Labels have a separate availability cutoff

Features answer: what could be used at decision time?

Training labels answer: what was known when this dataset was assembled?

For example, a decision on January 2 may have a label that becomes known on January 25. A training snapshot as of January 20 cannot use that label. A label maturity requirement is separate again: an observation period may still be incomplete even when an early label exists.

Passing the feature-time check does not establish either label condition, and it does not replace a time-based train/validation/test split.

Turn the result into a reproducible exercise

I maintain a free fintech data engineering roadmap. C08: leakage-safe training data with delayed labels extends this example with entity/currency matching, capture-only events, normalized timestamp offsets, label maturity, label knowledge time, and temporal splits.

It returns contributing event IDs alongside each training row. Keep that provenance when testing: a plausible sum can still come from the wrong events.

The exercise builds a synthetic dataset, not a classifier or a production feature store. The repository runs its core Python and SQL exercises locally without paid cloud services.

A useful next counterexample is a correction received after the decision that replaces an earlier value. Write down which version should be visible before changing the query.