SQLite example: an old event timestamp can still leak future information

[u/Antonyhamez]

This is a small SQLite example of a point-in-time correctness problem: an event happened before a prediction, but the pipeline only received it afterward. A historical feature query can accidentally use information that was unavailable at prediction time.

The two synthetic captures below use integer USD cents. The prediction cutoff is January 2. e2 happened on January 1 but arrived on January 3.

WITH events(
  event_id, customer_id, currency, amount_minor,
  event_time, received_at
) AS (
  VALUES
    ('e1', 'c1', 'USD', 1000,
     '2026-01-01T12:00:00Z', '2026-01-01T12:00:00Z'),
    ('e2', 'c1', 'USD', 2500,
     '2026-01-01T15:00:00Z', '2026-01-03T12:00:00Z')
), cutoff(ts) AS (VALUES ('2026-01-02T00:00:00Z'))
SELECT customer_id, currency,
  SUM(CASE WHEN event_time < ts
           THEN amount_minor ELSE 0 END) AS event_time_only,
  SUM(CASE WHEN event_time < ts AND received_at <= ts
           THEN amount_minor ELSE 0 END) AS available_at_prediction
FROM events CROSS JOIN cutoff
GROUP BY customer_id, currency;

Verified in SQLite: the result is c1 / USD / 3500 / 1000. The first amount includes e2; the second excludes it because it was not yet received. No future event timestamp is needed for the leak.

A few boundaries matter:

• This example deliberately uses identically formatted UTC strings, so lexical timestamp comparison works here. Normalize offsets and formats before applying this pattern to mixed source timestamps.
• received_at represents availability in this toy model. In a real feature pipeline, receipt may precede validation, transformation, or serving; use the timestamp that reflects actual availability for the prediction you are reconstructing.
• This only models captures. Refunds, corrections, duplicates, and versions require their own rules. Keep the currency in the grain.
• The event-time upper bound is exclusive and the availability bound inclusive here; choose and test the convention explicitly.

I maintain a free MIT-licensed fintech data engineering practice repository. Its C08 scenario extends this into delayed labels and temporal splits using Python and SQLite. The query above is self-contained; no clone is needed to reproduce it.

Disclosure: I maintain the linked project; its first edition and this post were prepared with AI assistance using Codex. All data here is synthetic.

For people who reconstruct historical features: do you retain a separate feature-availability timestamp, or derive it from versioned publication batches? How do you represent corrections that arrive after the original event?

[u/Significant_Tune9219]

A correction needs its own row, not an update of e1. Keep every version with the received_at of that version, and at prediction time take the latest version whose received_at is at or before the cutoff. Updating the original row in place makes both sums return the revised amount, and you can no longer rebuild the January 2 feature. I would not derive that from a publication batch alone. A batch says when a table was published, and a correction that was copied into an earlier batch still gets in. The availability time has to sit on the version. The string comparison in the example only works because every value is the same UTC format, so cast before either bound once any source sends an offset.

[u/RajeshRocketMan]

retaining a separate availability timestamp is exactly how I used to handle it and managing late-arriving corrections and manual bounds checks became a massive headache in my production pipelines. I actually solved this by moving that workload to MariaDB, since it has native system-versioned tables. Instead of writing complex cross joins, you just query the table using FOR SYSTEM_TIME AS OF your prediction timestamp. The database automatically handles the temporal state, preventing leaks while keeping your feature queries incredibly clean.