Point-in-Time Feature Join Interview Questions: Event Time, Availability, and Leakage

A feature recorded at 09:50 can still leak into a 10:00 prediction if the serving system did not receive it until 10:05. Filtering only on event time reproduces a completed history, not necessarily the information available to the model.

For machine learning engineers, point-in-time feature join interview questions ask you to define both clocks, filter eligible records, and only then choose the correct event and revision. Practise the full reconstruction with Design and Debug a Point-in-Time Backtest Data Pipeline.

Evidence boundary: Feast's current official documentation explains event-time joins, TTL, and an optional created-timestamp filter. PostgreSQL and pandas documentation support the query mechanisms discussed below. The dataset, feature contract, and audit rules are original exercises; no employer process or candidate interview outcome is claimed.

Define the prediction contract and its clocks

For every prediction row, identify entity, prediction timestamp, feature definition, acceptable age, and serving path. An entity can have many predictions, so a unique prediction ID matters as much as the entity key.

Event time says when the underlying observation occurred or became effective. Availability time says when the feature value could actually be retrieved by the intended model path. Warehouse ingestion time, transformation completion time, and online materialization time may all differ. Choose the clock that represents the claim you are testing.

For this exercise, use a state-valued feature called risk_score. At prediction time t, choose the newest event whose event_time<=t and available_at<=t. Within that event timestamp, choose the latest eligible revision. Reject equal-timestamp conflicting revisions unless a deterministic source sequence resolves them. This is a feature contract, not a universal rule for every feature.

An aggregate feature has additional boundaries. “Transactions in the previous hour” must define event inclusion, late arrivals, corrections, and whether the serving value was materialized before t. A timestamp on the output row cannot repair an aggregate that included future events.

Work through delayed arrival and revision history

Assume customer U1 has a prediction P1 at 10:00. All times in this original exercise use the same date and timezone. The available timestamp denotes successful availability to the model's serving path, not merely a date entered by an upstream analyst.

Feature record	Event and availability
F1: value 10	Event 09:40; available 09:41; revision 1.
F2: value 20	Event 09:50; available 10:05; revision 1.
F3: value 12	Event 09:40; available 10:10; revision 2.
F4: value 30	Event 10:02; available 10:03; revision 1.

At 10:00, only F1 is eligible, so the value is 10. An event-time-only join might choose F2 and return 20, even though that value was unavailable. A current corrected table might replace F1 with F3 and return 12, introducing another kind of future knowledge.

At 10:06, F1, F2, and F4 are eligible. F4 has the newest event, so the selected value is 30. At 10:11, F3 becomes eligible but does not displace F4, because its event belongs to the older 09:40 state. Latest availability is not the same as latest event under this contract.

If the interviewer instead defines the feature as “latest published measurement, regardless of observation period,” the ordering changes. Ask before writing SQL. Many apparent join bugs are disagreements about feature meaning, not failures of the window function.

Filter eligibility before ranking candidates

A practical SQL shape is to left join prediction rows to eligible feature records and rank candidates within each prediction. Keep the temporal predicates in the join so a prediction with no eligible record remains present. Partitioning only by entity would merge multiple prediction decisions into one.

The following is PostgreSQL-style SQL for the stated state feature. Assume revision_id supplies a documented total order when timestamps tie, feature history contains only this feature, and the event/availability columns are non-null:

WITH candidates AS (
  SELECT p.prediction_id, p.entity_id, p.prediction_at,
         f.value, f.event_time, f.available_at, f.revision_id,
         ROW_NUMBER() OVER (
           PARTITION BY p.prediction_id
           ORDER BY f.event_time DESC NULLS LAST,
                    f.available_at DESC NULLS LAST,
                    f.revision_id DESC NULLS LAST
         ) AS rn
  FROM predictions p
  LEFT JOIN feature_history f
    ON f.entity_id = p.entity_id
   AND f.event_time <= p.prediction_at
   AND f.available_at <= p.prediction_at
)
SELECT prediction_id, entity_id, prediction_at,
       value, event_time, available_at, revision_id
FROM candidates
WHERE rn = 1;

Do not rank the complete history first and then remove unavailable records. That can select a future revision, discard it, and lose an older eligible revision that should have survived. Eligibility reduces the candidate set before ordering decides the winner.

The explicit null ordering makes the unmatched left-join row harmless in this example. It does not define a policy for null timestamps inside real feature history; those were excluded by the exercise assumptions. Validate them upstream or define a deliberate quarantine rule.

If the table contains several features, partition and filter by feature identity too, or retrieve each view under its own contract. Returning one winning row from a heterogeneous feature table does not create a complete feature vector. PostgreSQL window functions.

What does Feast enforce by default?

Feast documents historical retrieval that searches backward from each entity-row timestamp within the feature view's TTL. TTL is relative to that historical prediction timestamp, not to the wall clock when the training query runs.

The current documentation also states that default joins constrain event timestamps. A configured created timestamp deduplicates same-event rows, but is not by default an availability cutoff. The documented filter_by_created_timestamp=True option adds that cutoff; null created timestamps are excluded, and unsupported offline stores raise an error. Feast point-in-time joins.

Those are current product facts, checked September 30, 2026. Verify your installed Feast version, offline store, and timestamp semantics before applying the option. A column called created_at is useful only if it represents the availability your experiment needs.

If online ingestion occurs after created_at, filtering on created_at can still admit a value the online path could not serve. The documentation's availability interpretation depends on that correspondence. Preserve serving publication evidence when you need to reconstruct the actual production state.

This distinction narrows a common claim: point-in-time retrieval is a mechanism, while leakage prevention is a contract covering timestamp semantics, feature computation, and data history. Do not advertise a platform flag as proof that the complete training pipeline is leakage-free.

Add TTL without changing the meaning accidentally

Suppose the feature has a fifteen-minute maximum age. Add event_time>=prediction_at-interval '15 minutes', with the lower boundary chosen explicitly. At 10:00, F1's 09:40 event is too old, so the result is missing even though it was available. F2 remains unavailable until 10:05.

Age by event time and age by availability time answer different questions. A freshly arrived report about a very old observation is not a fresh observation. Conversely, a stable account attribute may remain useful long after publication. Put the age rule in the feature definition rather than applying one universal TTL to every column.

Do not replace missing values with zero in the join without justification. No eligible history, expired history, unavailable data, and an actual measured zero are different states. Preserve a missingness reason where monitoring or model behavior depends on it.

When imputing, fit any learned imputation statistics on training data under the appropriate split and cutoff. The temporal join cannot protect you from later preprocessing that uses future or holdout information. Keep the join audit and model-pipeline audit connected.

Is a backward as-of join enough?

pandas documents merge_asof with sorted keys, grouping, direction, and optional tolerance. A backward match selects an earlier-or-equal key under its configured semantics. That is a useful mechanism, but it does not decide which business clock or revision policy is correct. pandas merge_asof.

An as-of join on event_time alone does not enforce available_at<=prediction_at. An as-of join on available_at alone chooses the newest publication, which can be a correction to an older event. Neither automatically implements this article's two-stage latest-event/latest-revision contract.

You can precompute an availability-ordered state table where each publication records the current correct feature state. A backward join over that table may then reproduce the contract efficiently. The state materialization must itself apply event ordering and revision rules, and retain enough history for replay.

For a coding exercise, start with the explicit candidate-filtering reference, then optimize. Explain the cost of joining many prediction rows to many historical candidates and the indexes, partitioning, or interval representation that can reduce it. Do not trade away correctness for a conveniently named API.

Preserve history across corrections and backfills

An overwrite-only table cannot reconstruct a previous value after correction. Store immutable revisions or bitemporal intervals with a clear effective time and knowledge time. The exact representation is a design choice; the essential requirement is retaining what was knowable at each simulated decision.

Backfills need a replay mode. “As originally known” excludes later publications. “As currently corrected” may intentionally include them for a different analysis. Label the output mode and dataset revision so a corrected-history experiment is not mistaken for a deployable backtest.

Keep feature-code versions and source snapshots alongside selected record IDs. Two runs can use identical timestamps but compute different aggregate definitions after a code change. Reproducibility needs the transformation contract, not only row dates.

Labels have their own availability clock. A fraud confirmation received days later may be valid for eventual evaluation but unavailable for training at an earlier simulated run. Correct features do not make prematurely available labels acceptable.

Test the join with a small independent oracle

For each prediction, an independent reference can scan history, filter both clocks, apply TTL, sort the eligible candidates by the documented ordering, and select one. Compare the optimized query against that reference before trusting a large training metric.

Test the 10:00 prediction above, equality at both cutoffs, a later revision of an older event, another entity, repeated predictions for one entity, no history, expired history, and out-of-order ingestion. Add conflicting same-key rows and null availability timestamps to test the rejection policy rather than letting input order choose.

Assert one output row per prediction ID, entity isolation, event_time<=prediction_at, and available_at<=prediction_at for every matched row. Save the chosen source record and both timestamps. A model reviewer should be able to explain one row without reverse-engineering a warehouse job.

Monitor missingness and publication lag by feature and serving path. Offline/online parity checks should compare values at recorded production cutoffs, including failures and stale reads. Matching a freshly rebuilt offline table to today's online value is a weaker claim.

Sources and Further Reading

• Feast: Point-in-time joins
• PostgreSQL: Window functions
• pandas: merge_asof

Primary references checked September 30, 2026. Product details and supported versions can change; the worked examples are original practice material.