Bug (pre-existing, should fix in this PR): This still uses pd.date_range(start=start_date, ...)[:1] which takes start_date as the entity timestamp, not end_date. This was identified in the PR #6066 review — the ClickHouse implementation correctly uses [end_date].

Since this PR is already modifying the non-entity retrieval path, it's a good opportunity to fix this:

entity_df = pd.DataFrame({"event_timestamp": [end_date]})

In the current code, the entity_df isn't used by the LOCF query path (the spine replaces it), but it's still uploaded to a temp table via _upload_entity_df — so at minimum it wastes a round-trip to Postgres. Even better: skip creating/uploading the entity_df entirely when start_date and end_date are set, since the LOCF path never references left_table_query_string.

Duplicate computation: When start_date is None, max_ttl_seconds is already computed in the block above (lines 147-151) to derive start_date. This block recomputes the exact same value. Consider hoisting max_ttl_seconds to a single computation before both uses:

max_ttl_seconds = max(
    (int(fv.ttl.total_seconds()) for fv in feature_views
     if fv.ttl and isinstance(fv.ttl, timedelta)),
    default=0,
)

Dead code: all_feature_cols is computed here but never referenced in the rest of the template. The final SELECT iterates over featureview.features directly and computes col_name inline. This entire block (lines 569-579) can be removed.

Entityless FV edge case: PARTITION BY 1 works in Postgres but is non-standard and some query planners may not optimize it well. An alternative is PARTITION BY (SELECT NULL) or simply omitting the PARTITION clause (which gives one partition over the whole result set).

Also worth adding a test case for entityless feature views to verify the PARTITION BY 1 and {% else %}1{% endif %} paths generate valid SQL.

Good use of IS NOT DISTINCT FROM here. This fixes a bug in the old LATERAL join path where NULL = NULL would fail the join for FVs with different entity sets. Worth calling this out in the PR description as a bug fix.

The TTL enforcement via CASE is correct — when filled_ts is NULL (no prior observation), the subtraction yields NULL, the comparison is UNKNOWN, and CASE falls through to ELSE NULL.

One edge case: if featureview.ttl is a very large number, {{ featureview.ttl }} * interval '1' second could overflow on some Postgres versions. Consider using make_interval(secs => {{ featureview.ttl }}) for safety, though in practice TTLs are unlikely to overflow.

nit: When there's no created_timestamp_column, this CTE is a no-op passthrough (SELECT * FROM __data_raw). Postgres will likely optimize it away, but you could simplify by using __data_raw as the alias directly:

{% if featureview.created_timestamp_column %}
"{{ featureview.name }}__data" AS (
    SELECT * FROM (
        ... dedup logic ...
    ) __dedup WHERE __rn = 1
),
{% endif %}

Then reference {{ featureview.name }}__data{% if not featureview.created_timestamp_column %}_raw{% endif %} downstream. Though I understand the current approach is simpler to maintain.

Good tests for the happy path and TTL=0 case. Consider adding coverage for:

1. Different entity sets across FVs — e.g., fv1 has driver_id, fv2 has customer_id. This exercises the NULL AS "{{ entity }}" and IS NOT DISTINCT FROM paths which are the trickiest part of the LOCF approach.
2. Entityless feature view — verifies the PARTITION BY 1 fallback generates valid SQL.
3. created_timestamp_column set — verifies the dedup CTE (__data_raw → __data via ROW_NUMBER) is generated correctly.
4. full_feature_names=True — verifies the featureview.name ~ '__' ~ feature column naming.

These are the main code paths that differ from the happy path and are most likely to produce malformed SQL.