02 · Temporal (As-Of) Joins

This notebook puts featurizer’s two relationship directions side by side, using a healthcare scenario:

Patients (target entity) with admission dates
Care Plans — many per patient, aggregated onto the patient
Risk Assessments — scores recorded over time, looked up as-of each patient’s admission

The key concept is the as-of join: for each patient row, we want the most recent risk assessment as of that patient’s admission date — and nothing recorded after it.

The second concept is the one people get wrong: which side is parent. It is the opposite for the two kinds of relationship, and a temporal: block on the wrong kind does nothing.

1. Setup

import sys
from pathlib import Path

# This tutorial is database-free: it loads the config and inspects the
# synthesized features and the generated SQL — none of which touch a
# database. To actually *execute* the features against PostgreSQL, run the
# example script instead (`just example <NN>`, or create_data.py +
# run_example.py with DATABASE_URL / PG* set). See the example README.
sys.path.insert(0, str(Path.cwd().parent.parent))

2. Understanding Temporal Relationships

The configuration declares two relationships. Only the second carries a temporal: block:

with open("config.yaml") as f:
    print(f.read())

# Healthcare feature generation: two relationships, two directions.
#
#   patients <- care_plans          aggregation  (care_plans is the CHILD)
#   risk_assessments -> patients    as-of lookup (risk_assessments is the PARENT)
#
# The `temporal:` block is read only on the second kind. Declared on the first
# it is discarded, and validation says so.

target: patients
max_depth: 2

intervals:
  - P30D  # Last 30 days
  - P90D  # Last 90 days

# A focused primitive set (the full default set would exceed PostgreSQL's 1664
# columns-per-row limit on this config). Includes the rolling ordered-set stats
# — rolling_median / rolling_iqr render as correlated subqueries (PostgreSQL
# forbids OVER on percentile_cont), exercised end to end here.
aggregations:
  - count
  - sum
  - mean
  - min
  - max
  - stddev
  - median
transformations:
  - identity
  - abs
  - lag_1
  - rolling_mean_7
  - rolling_median_7
  - rolling_iqr_7

entities:
  - alias: patients
    id: patient_id
    table: patients
    temporal_ix: admission_date
    variables:
      age:
        type: numeric
      severity_level:
        type: categorical

  - alias: care_plans
    id: plan_id
    table: care_plans
    temporal_ix: plan_date
    variables:
      treatment_type:
        type: categorical
      cost:
        type: numeric

  - alias: risk_assessments
    id: assessment_id
    table: risk_assessments
    temporal_ix: assessed_on
    variables:
      risk_score:
        type: numeric

relationships:
  # Aggregation. patients is the parent and the target, so its care plans roll
  # up onto it. Every read of care_plans is already bounded by the as-of date;
  # no `temporal:` block belongs here.
  - parent:
      entity: patients
      key: patient_id
    child:
      entity: care_plans
      key: patient_id

  # As-of lookup. The timestamped lookup table is the PARENT and the entity
  # receiving the value is the CHILD: each patient row pulls the most recent
  # assessment dated at or before its own admission_date.
  - parent:
      entity: risk_assessments
      key: patient_id
    child:
      entity: patients
      key: patient_id
    temporal:
      mode: as_of
      grace: P30D  # Lookback cap: ignore assessments older than 30 days

Two relationships, two directions

	`parent`	`child`	Result
Aggregation	`patients` (the target)	`care_plans`	many care plans rolled up per patient
As-of lookup	`risk_assessments` (the lookup)	`patients`	one assessment attached per patient row

temporal:
  mode: as_of   # Enable as-of join semantics
  grace: P30D   # Lookback cap: ignore assessments older than 30 days

This means: for each patient, find the most recent assessment where admission_date - 30 days <= assessed_on <= admission_date.

grace only ever limits how far back the join looks. It never lets the join read a row dated after the patient’s admission.

3. Load Configuration

from featurizer import Featurizer

featurizer = Featurizer("config.yaml")

print(f"Target: {featurizer.target.alias}")
print(f"Intervals: {featurizer.intervals}")

[32m2026-09-19 00:57:53.775[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36mplan[0m:[36m253[0m - [34m[1mStarting feature build for target patients[0m


[32m2026-09-19 00:57:53.776[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m279[0m - [34m[1mbuild_features(patients) depth=0[0m


[32m2026-09-19 00:57:53.777[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m279[0m - [34m[1mbuild_features(risk_assessments) depth=1[0m


[32m2026-09-19 00:57:53.777[0m | [1mINFO    [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m299[0m - [1mMaximum recursion depth reached at depth 2; materializing risk_assessments without traversing further.[0m


[32m2026-09-19 00:57:53.777[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_direct[0m:[36m1359[0m - [34m[1mProcessing forward relationship Entity(risk_assessments).patient_id -> Entity(patients).patient_id[0m


[32m2026-09-19 00:57:53.778[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m279[0m - [34m[1mbuild_features(care_plans) depth=1[0m


[32m2026-09-19 00:57:53.778[0m | [1mINFO    [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m299[0m - [1mMaximum recursion depth reached at depth 2; materializing care_plans without traversing further.[0m


[32m2026-09-19 00:57:53.780[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1270[0m - [34m[1mProcessing backward relationship Entity(patients).patient_id -> Entity(care_plans).patient_id[0m


[32m2026-09-19 00:57:53.781[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_apply_direct_roles[0m:[36m1463[0m - [33m[1mDirect variable 'patients.severity_level' (type: categorical) will pass through as a raw string column and is likely to crash a downstream encoder. Set role: categorical (with a declared vocabulary or a PostgreSQL ENUM) to one-hot encode it, or role: identifier to exclude it.[0m


Target: patients
Intervals: ['P30D', 'P90D']

# Examine both relationships: only the lookup carries temporal settings
for rel in featurizer.relationships:
    print(f"Relationship: parent={rel.parent.alias} child={rel.child.alias}")
    print(f"  Temporal mode: {rel.temporal_mode}")
    print(f"  Grace period: {rel.temporal_grace}")

Relationship: parent=patients child=care_plans
  Temporal mode: None
  Grace period: None
Relationship: parent=risk_assessments child=patients
  Temporal mode: as_of
  Grace period: P30D

4. Examining the Generated SQL

An as-of relationship renders as a left join lateral named <parent>_asof_for_<child>. Searching for lateral alone is not enough — every featurizer query has an outer cross join lateral over as_of_dates — so look for the _asof_for_ name.

sql = featurizer.query

print(f"as-of laterals in the query: {sql.count('_asof_for_')}")
print("=" * 60)

# Print the lateral subquery that ends in the _asof_for_ alias
end = sql.index("_asof_for_")
start = sql.rfind("left join", 0, end)
stop = sql.index("\n", end)
for line in sql[start:stop].splitlines():
    if line.strip():
        print(line)

[32m2026-09-19 00:57:53.852[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.sql[0m:[36mrender[0m:[36m40[0m - [34m[1mRendered SQL for target 'patients': 7 CTEs, 254819 chars[0m


as-of laterals in the query: 1
============================================================
left join
         lateral (
        select
        risk_assessments_transform."ABS(risk_assessments.risk_score)" as "ABS(risk_assessments.risk_score)",
        risk_assessments_transform."LAG_1(risk_assessments.risk_score)" as "LAG_1(risk_assessments.risk_score)",
        risk_assessments_transform."ROLLING_IQR_7(risk_assessments.risk_score)" as "ROLLING_IQR_7(risk_assessments.risk_score)",
        risk_assessments_transform."ROLLING_MEAN_7(risk_assessments.risk_score)" as "ROLLING_MEAN_7(risk_assessments.risk_score)",
        risk_assessments_transform."ROLLING_MEDIAN_7(risk_assessments.risk_score)" as "ROLLING_MEDIAN_7(risk_assessments.risk_score)",
        risk_assessments_transform.risk_score as risk_score
        from risk_assessments_transform
        where risk_assessments_transform.patient_id = patients.patient_id and risk_assessments_transform.assessed_on <= patients.admission_date and risk_assessments_transform.assessed_on >= patients.admission_date - interval 'P30D'
        order by risk_assessments_transform.assessed_on desc
        limit 1
    ) as risk_assessments_asof_for_patients on true  left join  care_plans_aggs_for_patients on care_plans_aggs_for_patients.patient_id = patients.patient_id

Understanding the LATERAL Join

The where clause above carries the three conditions that make the join point-in-time correct:

risk_assessments_transform.patient_id = patients.patient_id — only this patient’s assessments
assessed_on <= patients.admission_date — nothing recorded after the admission
assessed_on >= patients.admission_date - interval 'P30D' — the grace period, a lookback cap

and order by assessed_on desc limit 1 keeps the most recent match. A patient with no assessment inside the window gets NULL — it is a left join, so the patient row survives.

5. The Orientation Trap

The planner reads a temporal: block only on a forward (parent → child) transfer. Put the same block on the aggregation relationship — the target as parent of the timestamped table — and it is discarded: no lateral renders and grace is never applied. Validation warns about it:

import copy

import yaml

from featurizer.validation import ConfigValidator

with open("config.yaml") as f:
    trap = yaml.safe_load(f)

# Move the temporal block onto the aggregation relationship
block = trap["relationships"][1].pop("temporal")
trap["relationships"][0]["temporal"] = copy.deepcopy(block)

result = ConfigValidator().validate(trap)
print(f"is_valid: {result.is_valid}")
for warning in result.warnings:
    if "has no effect" in warning.message:
        print(f"\n[{warning.location}]")
        print(warning.message)

is_valid: True

[relationships[0].temporal]
The 'temporal' block on relationship 'patients -> care_plans' has no effect. 'patients' is nearer the target than 'care_plans', so 'care_plans' is aggregated onto 'patients', and the block is read only on a forward (parent -> child) transfer. The child stream is still bounded by as_of_date, so no data after the as-of date is read — but 'grace' is not applied. For an as-of join, declare the timestamped lookup entity as 'parent' and the entity that receives its value as 'child'; otherwise delete the block.

The config is still valid, and nothing leaks: an aggregation bounds its child rows on as_of_date with or without the block. The warning exists because the author believes they declared something they did not get.

6. Generated Features

target_features = featurizer.features[featurizer.target.alias]

print(f"Total features: {len(target_features)}")

print("\nFrom risk_assessments (via the as-of lookup):")
lookup = [f for f in target_features if "risk" in f.name.lower()]
for f in sorted(lookup, key=lambda x: len(x.name))[:6]:
    print(f"  {f.name}")

print("\nFrom care_plans (via aggregation):")
aggregated = [f for f in target_features if "care_plans" in f.name.lower()]
for f in sorted(aggregated, key=lambda x: len(x.name))[:6]:
    print(f"  {f.name}")

Total features: 763

From risk_assessments (via the as-of lookup):
  risk_score
  "ABS(risk_assessments.risk_score)"
  "LAG_1(risk_assessments.risk_score)"
  "ROLLING_IQR_7(risk_assessments.risk_score)"
  "ROLLING_MEAN_7(risk_assessments.risk_score)"
  "ROLLING_MEDIAN_7(risk_assessments.risk_score)"

From care_plans (via aggregation):
  "MAX(care_plans.cost)"
  "SUM(care_plans.cost)"
  "MIN(care_plans.cost)"
  "MEAN(care_plans.cost)"
  "MEDIAN(care_plans.cost)"
  "STDDEV(care_plans.cost)"

7. When to Use Temporal Joins

Use temporal (as-of) joins when:

Point-in-time correctness matters: You need a value as it stood at a specific moment
Preventing data leakage: In ML, you can’t use future data to predict past events
Time-varying lookups: The “current” related record changes over time

Examples:

Customer’s current address at time of order
Patient’s latest risk assessment at time of admission
Product’s price at time of purchase
Employee’s department at time of performance review

In each case the timestamped lookup is the parent, and the row that receives the value is the child.

print("Summary")
print("=" * 40)
print(f"Target: {featurizer.target.alias}")
print("Aggregation: patients <- care_plans")
print("As-of lookup: risk_assessments -> patients (grace P30D)")
print(f"Total features: {len(target_features)}")

Summary
========================================
Target: patients
Aggregation: patients <- care_plans
As-of lookup: risk_assessments -> patients (grace P30D)
Total features: 763