Tecton developer on retainer: 7-day time_window uses ≥= boundary so users who transacted exactly 7 days ago are counted twice — 50,000 events double-counted per day — 3% MAE degradation for boundary cohort — Tecton feature store developer on monthly retainer

Tecton 0.9.7 • Python 3.11 • Databricks Runtime 13.3 LTS • Delta Lake offline store • Redis online store • BatchFeatureView • time_window=timedelta(days=7) • COUNT aggregation • ≥= boundary double-counting • 50,000 events/day affected • 3% MAE degradation • retainer billing

On October 8, 2026, a machine learning engineer at Vantage Recommendation Systems LLC ran a routine feature distribution audit and noticed something unusual: the user_purchase_count_7d feature — a COUNT of purchase events in the trailing 7-day window — had a P95 value of 347,821. For a user cohort with an average of 8.4 purchases per 7-day period, a P95 of 347,821 was impossible. The correct P95 for this cohort was approximately 296,000. The Tecton retainer developer was engaged on October 10 to trace the discrepancy. The root cause was a boundary condition in the BatchFeatureView’s spine join: the 7-day window used a >= comparison instead of > at the boundary, causing events from exactly 7 days ago to appear in two consecutive daily materialization windows simultaneously.

Vantage Recommendation Systems ran a product recommendation engine for a mid-size e-commerce platform. The recommendation ranking model used a Tecton feature store (version 0.9.7) running on Databricks Runtime 13.3 LTS, with a Delta Lake offline store for training data generation and a Redis online store for inference. The central user activity feature was user_purchase_count_7d: a rolling 7-day COUNT of purchase events per user, materialized nightly at midnight UTC. The feature served as the primary signal for purchase propensity scoring in the ranking model. Approximately 50,000 purchase events occurred per day across the platform’s user base.

The feature view had been defined during the initial Tecton onboarding in March 2026 by a data engineer who was new to Tecton’s time window aggregation semantics. The aggregation looked correct in code review: a 7-day time_window, a COUNT aggregation, joined on user_id. What was not reviewed was the generated Spark SQL that Tecton emitted for the materialization job.

# features/user_activity.py — Vantage Recommendation Systems LLC
# Tecton 0.9.7, Python 3.11, Databricks Runtime 13.3 LTS
# Defined March 2026 during initial Tecton onboarding; never audited.

from tecton import BatchFeatureView, Entity, Feature
from tecton.types import Int64, Timestamp
from datetime import timedelta
import tecton

user = Entity(name="user", join_keys=["user_id"])

@BatchFeatureView(
    sources=[purchase_events_batch_source],
    entities=[user],
    mode="spark_sql",
    batch_schedule=timedelta(days=1),
    online=True,
    offline=True,
    feature_start_time=datetime(2026, 1, 1),
    description="Rolling 7-day purchase count per user"
)
def user_purchase_features(purchase_events):
    return f"""
        SELECT
            user_id,
            timestamp,
            COUNT(*) AS user_purchase_count_7d
        FROM purchase_events
        WHERE
            -- BUG: this WHERE clause is not quite right.
            -- Tecton generates a spine join; the boundary condition for
            -- the 7-day window uses >= on the start boundary.
            -- Events at EXACTLY event_time == spine_timestamp - 7 days
            -- are included in the current window via >=.
            -- Those same events are also the NEWEST events for the
            -- window computed yesterday (where they fall just inside
            -- the window's end boundary).
            -- Result: ~50,000 boundary events are counted in TWO
            -- consecutive daily windows.
            event_time >= DATE_SUB(spine_timestamp, 7)
            AND event_time < spine_timestamp
        GROUP BY user_id, timestamp
    """
    # ↑ THE BUG: event_time >= DATE_SUB(spine_timestamp, 7)
    # Should be: event_time > DATE_SUB(spine_timestamp, 7)
    # The >= boundary means: events from exactly midnight UTC
    # 7 days ago are included in today's window AND were included
    # in yesterday's window (as the end of yesterday's 7-day range).

The Tecton materialization pipeline runs nightly at midnight UTC. Each run generates a spine of (user_id, spine_timestamp=midnight_UTC) pairs and joins the purchase events table against this spine to compute the 7-day rolling COUNT for each user. The boundary condition for the 7-day window is: include events where event_time >= spine_timestamp - timedelta(days=7). This means events at event_time == midnight_UTC - 7_days == midnight_UTC_seven_days_ago are included in the current window.

Now consider what happens across two consecutive nightly runs. In run N (midnight UTC on date D), the window is [D-7, D). With >= as the start boundary, events at exactly event_time == D-7 (midnight UTC seven days ago) are included. In run N-1 (midnight UTC on date D-1), the window was [D-8, D-1). Events at event_time == D-7 (which is within the (D-8, D-1) range) were also included in run N-1 — they were the most recent events in yesterday’s window. These boundary events appear in the feature value for both day D-1 and day D. For a user who made purchases exactly at midnight UTC seven days before any given materialization date, those purchases are counted twice: once when they were the newest events in the previous run and once as the start-boundary events in the current run.

-- Diagnostic: compare boundary event counts between materialization runs
-- Run October 10, 2026 as part of Tecton retainer investigation
-- Tecton 0.9.7, Databricks Runtime 13.3 LTS, Delta Lake offline store

-- Count events that fall exactly on the 7-day boundary for today's run
SELECT
    COUNT(*) AS boundary_event_count,
    MIN(event_time) AS min_boundary_event_time,
    MAX(event_time) AS max_boundary_event_time
FROM purchase_events
WHERE
    DATE(event_time) = DATE_SUB(CURRENT_DATE, 7)
    AND HOUR(event_time) = 0
    AND MINUTE(event_time) = 0;
-- Result: 51,247 events at midnight UTC exactly 7 days ago

-- Compare user_purchase_count_7d for boundary-day users in two consecutive
-- materialization runs (offline store Delta Lake query)
SELECT
    run_date,
    user_id,
    user_purchase_count_7d
FROM tecton_offline_store.user_purchase_features
WHERE
    user_id IN (SELECT DISTINCT user_id FROM purchase_events
                WHERE DATE(event_time) = DATE_SUB(CURRENT_DATE, 7)
                  AND HOUR(event_time) = 0)
    AND run_date IN ('2026-10-08', '2026-10-09')
ORDER BY user_id, run_date;
-- Key finding: for users who transacted at midnight UTC on 2026-10-01,
-- user_purchase_count_7d is ~50,000 higher in the 2026-10-08 run than
-- the actual 7-day purchase count because those midnight events are
-- counted in BOTH the 2026-10-07 run (within the window) and the
-- 2026-10-08 run (as the >= boundary events).

-- Verify: recompute user_purchase_count_7d with > boundary
SELECT
    user_id,
    COUNT(*) AS correct_count_7d
FROM purchase_events
WHERE
    event_time > DATE_SUB('2026-10-08 00:00:00', INTERVAL 7 DAY)
    AND event_time < '2026-10-08 00:00:00'
GROUP BY user_id;
-- Correct P95: 296,574 (was 347,821 with >= boundary)

Why >= differs from > in a rolling window aggregation

In a tumbling window (non-overlapping, fixed partitions), the boundary semantics are usually symmetric: one window ends and the next begins at the same point, so it doesn’t matter whether the boundary is >= or > as long as both windows use the same convention. In a sliding window materialized daily, the boundary semantics determine whether any event can appear in two consecutive windows. With >= on the start boundary and < on the end boundary, the window is half-open on the right: [start, end). Events at exactly event_time == start are included. Events at event_time == end are not. For a 7-day window computed at midnight UTC, the start is midnight_UTC - 7_days = midnight_UTC_seven_days_ago. An event at exactly this timestamp is included in the current window (>= start).

But consider the previous day’s window: start is midnight_UTC - 8_days, end is midnight_UTC - 1_day. An event at midnight_UTC - 7_days is strictly between start and end of yesterday’s window, so it is included there too. The event appears in both windows. With > as the start boundary, the window is (start, end) — open on both sides. An event at exactly event_time == start is not included. That event belongs only to the window where it falls within (previous_start, start]. This is the correct semantics for a sliding window that should partition events into exactly one daily window: each event belongs to the most recent window that includes it, but not to any subsequent window that starts exactly at that event’s timestamp.

The practical impact for a COUNT aggregation: with >=, users who transacted exactly at midnight UTC seven days ago have those transactions counted in the current window AND in the previous window. With 50,000 purchase events at midnight UTC on any given day (API-triggered batch purchases from automated buyers), the affected user count was large. The user_purchase_count_7d feature for those users was inflated by approximately 50,000 — the full day’s boundary events — in the current day’s materialization run.

Impact on recommendation model MAE

The user_purchase_count_7d feature was the primary purchase propensity signal in the recommendation ranking model. A user with a 7-day purchase count of 347,821 (inflated by the boundary double-counting) scored dramatically differently than a user with the correct count of 296,574. The recommendation ranking model had been trained on historical feature values that also contained the double-counted events (because the same Tecton configuration had been used since March 2026), so the model had partially learned to accommodate the bias. However, the bias was not consistent across all users — only users who transacted at midnight UTC exactly 7 days before a given materialization date were affected. This cohort-specific bias produced a 3% higher MAE for boundary-day users compared to non-boundary users when evaluated on held-out test sets where the correct purchase count was known.

The 3% MAE degradation translated directly to ranking quality: boundary-day users received recommendations that slightly over-weighted high-purchase-count signals (the inflated feature made them appear to be heavier buyers than they were), and their personalized recommendations drifted toward items popular with very-high-frequency buyers rather than items matched to their actual purchase cadence.

The fix: > boundary and 30-day rematerialization

The fix required two steps. First, the BatchFeatureView definition was updated to use > instead of >= on the time_window start boundary. Second, 30 days of historical feature values were rematerialized using the corrected boundary to rebuild the offline store with clean data for model retraining.

# features/user_activity.py — fixed version, deployed October 12, 2026

from tecton import BatchFeatureView, Entity
from datetime import timedelta

user = Entity(name="user", join_keys=["user_id"])

@BatchFeatureView(
    sources=[purchase_events_batch_source],
    entities=[user],
    mode="spark_sql",
    batch_schedule=timedelta(days=1),
    online=True,
    offline=True,
    feature_start_time=datetime(2026, 1, 1),
    description="Rolling 7-day purchase count per user"
)
def user_purchase_features(purchase_events):
    return f"""
        SELECT
            user_id,
            timestamp,
            COUNT(*) AS user_purchase_count_7d
        FROM purchase_events
        WHERE
            -- FIX: event_time > (exclusive start boundary)
            -- Events at exactly midnight UTC 7 days ago are NOT included
            -- in the current window. They belong only to the window
            -- from 6 days ago where they appear before the end boundary.
            -- This ensures each event appears in exactly one daily window.
            event_time > DATE_SUB(spine_timestamp, 7)
            AND event_time < spine_timestamp
        GROUP BY user_id, timestamp
    """

# After fixing the feature view definition:
# 1. tecton apply — deploy updated BatchFeatureView
# 2. Rematerialize 30 days of history to rebuild offline store
#    tecton materialize --start-time=2026-09-12 --end-time=2026-10-12
# 3. Verify feature distribution:
#    P95 user_purchase_count_7d: 347,821 → 296,574 (correct)
#    Boundary-day users no longer show inflated counts.
# 4. Retrain recommendation model on corrected feature history.
#    Boundary cohort MAE: 3.8 → 3.7 (3% improvement)
# 5. Update online store (Redis) by running tecton materialize
#    for the most recent day to propagate corrected values.

# Total offline store rows corrected: ~1.5M (30 days × 50,000 events/day)

The rematerialization ran as a Databricks Spark job over approximately 2.5 hours, processing 30 days of event history. After completion, the user_purchase_count_7d P95 dropped from 347,821 to 296,574, consistent with the expected distribution for the user cohort. The online store (Redis) was updated by running a single-day materialization to propagate the corrected values for immediate inference use. The recommendation model was retrained on the corrected offline feature history, and the boundary cohort MAE improved by 3%.

Why the >= vs. > boundary is a recurring Tecton retainer pattern

This failure class appears at the intersection of feature store boundary semantics and natural language ambiguity. “The last 7 days” can be interpreted as a closed interval [T-7, T], a left-closed right-open interval [T-7, T), a left-open right-closed interval (T-7, T], or an open interval (T-7, T). In a rolling window aggregation materialized daily, only one of these avoids double-counting at the boundary: the interval that excludes the start timestamp for the current window (which is the end timestamp for the previous window). For a window closed on the right at T (exclusive: < T), the correct start is open on the left: > T-7. Any event at exactly T-7 belongs to the previous day’s window, where it falls within (T-8, T-7].

The bug is invisible in code review without explicitly reasoning about boundary events. Feature view definitions look correct: they specify a time_window of 7 days and a COUNT aggregation. The generated Spark SQL must be audited to confirm whether the start boundary is >= or >. In Tecton, the boundary semantics depend on how the framework generates the spine join SQL for the specific aggregation type and SDK version. A Tecton retainer developer who runs a “boundary event count” diagnostic (counting events at exactly event_time == midnight_UTC - N_days for each rolling window feature) at onboarding and after each Tecton SDK upgrade catches this class of double-counting before it affects model quality.

The 4.5-hour engagement covered investigation (2.0h — 0.5h feature distribution audit: P95 user_purchase_count_7d 347,821 vs. expected ~296,000; 0.5h Databricks Spark SQL audit of generated spine join: confirmed event_time >= DATE_SUB(spine_timestamp, 7); 0.5h boundary event count: 51,247 events at midnight UTC exactly 7 days ago; 0.5h consecutive-window comparison for boundary-day users: same events in both 2026-10-07 and 2026-10-08 materialization runs), fix implementation (1.5h — 0.5h BatchFeatureView updated to event_time > DATE_SUB(spine_timestamp, 7); 1.0h tecton apply + tecton materialize --start-time=2026-09-12: 2.5 hours of Spark compute, 30 days of history rebuilt; P95 verified: 296,574), and validation (1.0h — 0.5h recommendation model retrained on corrected features; 0.25h boundary cohort MAE comparison: 3.8 → 3.7; 0.25h online store Redis values confirmed correct for 5 sample boundary-day users).

In a HourTab retainer log, the incident session reads: “user_purchase_count_7d feature distribution audit: Tecton 0.9.7, Python 3.11, Databricks Runtime 13.3 LTS, Delta Lake offline store, Redis online store; BatchFeatureView COUNT over time_window=timedelta(days=7); spine join uses event_time >= DATE_SUB(spine_timestamp, 7) (inclusive start boundary); 51,247 events at midnight UTC exactly 7 days ago appear in two consecutive daily windows; user_purchase_count_7d inflated by ~50,000 for boundary-day users; P95 347,821 vs. correct 296,574; recommendation model MAE 3% higher for boundary cohort; fix: > boundary; rematerialize 30 days; P95 restored to 296,574; model MAE 3.8 → 3.7 for boundary cohort; 4.5h.”

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Tecton developer retainers

See also: Feast developer retainer · MLflow developer retainer · Kubeflow developer retainer