Historical datasets

What this covers

A snapshot holds one instant. A history holds every period.

build_dataset answers "what were the values at time t?". build_historical_dataset answers "how did they change?".

The daily category features of the demo store, rolled out over business dates, for Clothing:

A snapshot at 2023-05-13 12:00 is the fourth row. The last period is still open in the store; it ends at the window's end, here the moment of the query.

Same arguments as build_dataset, plus a window

from tdfs4ds import build_historical_dataset
from tdfs4ds.feature_store.feature_query_retrieval import get_feature_versions

tdfs4ds.DATA_DOMAIN = "Customer Transaction Analytics Time Management"
selection = get_feature_versions(
    entity_name='Category',
    features=['sum_Transaction_Amount', 'mean_Transaction_Amount',
              'max_Transaction_Amount', 'median_Transaction_Amount'])

history = build_historical_dataset('Category', selection, 'HIST_CATEGORY')

Output columns, in order

entity

the grain, as in a snapshot (Category)

ValidPeriod

PERIOD(TIMESTAMP(0) WITH TIME ZONE), for CONTAINS, OVERLAPS, P_INTERSECT

ValidStart

included

ValidEnd

excluded

features

one column per selected feature

Fixed bounds, or relative to now

Half-open [start, end). now is the current valid time when the view is queried, so a window without a fixed end slides on its own.

The end always exists: an open period ends at the window's end, never at 9999-12-31.

Durations become Teradata date arithmetic

Accepted: '3 months', '90 days', '1 year 2 months', '2 days 6 hours', ISO 8601 'P1Y2M', (3, 'months'), a timedelta.

build_historical_dataset(..., duration='12 months', delay='1 day')

-- end and start, evaluated each time the view is queried
(CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0) WITH TIME ZONE) - INTERVAL '1 00:00:00' DAY TO SECOND)
ADD_MONTHS(<end>, -12)

Measured on real data: duration='40 years', delay='3 hours' ended the window three hours before the query and excluded a run written that morning.

NORMALIZE merges equal neighbours. The join type decides which periods exist.

Real case: sum_Transaction_Amount under three processes. Two were rolled out over 2023 (FILTERED, WITH_CLONE), one ran in real time today (HYBRID). Restaurant:

All six categories: FULL OUTER 112 rows, INNER 75. The rows both keep are identical.

At any instant, the history equals the snapshot

For every t in the window, the rows whose ValidPeriod contains t are exactly build_dataset(..., as_of=t) with the same join type.

SELECT ... FROM HIST_CUSTOMER WHERE ValidPeriod CONTAINS TIMESTAMP '2023-05-14 00:00:00'
-- compared with
build_dataset(..., as_of='2023-05-14 00:00:00', join_type=...)

Customer features of the demo store, compared value by value.

INNER uses Teradata's own sequenced join

SEQUENCED VALIDTIME intersects the valid periods itself. The window is a predicate and a P_INTERSECT, then NORMALIZE, then the final columns.

SELECT z.Category, z.TDFS_VP AS ValidPeriod, BEGIN(z.TDFS_VP) AS ValidStart, ...
FROM ( SELECT NORMALIZE ON MEETS OR OVERLAPS ...
       FROM ( SELECT x.*, x.TDFS_VT P_INTERSECT PERIOD(<start>, <end>) AS TDFS_VP
              FROM ( SEQUENCED VALIDTIME
                     SELECT B1.Category, B1.FEATURE_VALUE AS sum_Transaction_Amount, ...
                     FROM FS_T_... B1 INNER JOIN FS_T_... B2 ON B1.Category = B2.Category ...
                     WHERE (B1.FEATURE_ID = 18 AND B1.FEATURE_VERSION = '...') ...
                   ) x (..., TDFS_VT)
              WHERE x.TDFS_VT OVERLAPS PERIOD(<start>, <end>) ) y ) z

What EXPLAIN shows (customer history, 4 features)

• 9 steps; each storage table read once, from a single partition
• rowkey-based merge joins, built locally, no redistribution
• the sliding window folded into the join step as constants: TIMESTAMP '2026-10-06 07:50:41+00:00'

FULL OUTER cuts each timeline at every change

A sequenced outer join is refused: Error 9347, in a derived table and at the top level.

Change points first read each feature twice (starts, ends). One read now emits both bounds through a two-row expansion. Same rows, measured on the real category history:

FULL OUTER stays about twice as slow as INNER: each table is still read twice.

Why NORMALIZE, not TD_NORMALIZE_OVERLAP_MEET

TD_NORMALIZE_OVERLAP_MEET was tested first. Fed by a derived table, the input became a second FROM item and was product-joined with the output:

SELECT n.* FROM (SELECT ... 3 rows ...) d,
     TABLE (TD_SYSFNLIB.TD_NORMALIZE_OVERLAP_MEET(NEW VARIANT_TYPE(d.e, d.v), d.p)
            RETURNS (e INTEGER, v INTEGER, p PERIOD(...), nrm_count INTEGER)
            HASH BY e LOCAL ORDER BY e, v, p) AS n
-- 3 rows in, 6 rows out: every result row duplicated

Three behaviours of the store that a history makes visible

Registered, rebuilt and migrated like any dataset

The catalog records TIME_MODE='HISTORY' and the window. The view's own DDL is read back, so nothing is re-resolved against the feature catalog.

# a snapshot rebuilt as a history
tdfs4ds.rebuild_dataset('DATASET_CUSTOMER', target_view_name='DATASET_CUSTOMER_HIST', history=True)
# a history's window moved
tdfs4ds.rebuild_dataset('HIST_CATEGORY', duration='6 months', replace=True)
# back to a snapshot
tdfs4ds.rebuild_dataset('HIST_CATEGORY', target_view_name='CATEGORY_NOW', current=True)

How it was verified

The demo notebooks 00 and 02–18 were run against a fresh demo_user on Vantage 20, producing three data domains with rolled-out, filtered and real-time history. Every view was compared with a reference computed in Python from the raw storage rows, value by value.

Four more windows (fixed, start + duration, end − duration, sliding with delay) × both join types: 8 of 8 identical. Offline: 114 tests for the window, both shapes and the DDL round trip.

Small data: read the plan columns, not the times, as the scaling indicator.