Historical datasets — `build_historical_dataset`¶

A dataset built with build_dataset is a snapshot: one row per entity, holding each feature's value at one valid instant (now, a fixed date, or a TimeManager's business date).

A historical dataset returns how those values evolved: one row per entity per period during which its feature values did not change.

<entity columns>, ValidPeriod, ValidStart, ValidEnd, <feature columns>

Each row reads: from ValidStart (included) to ValidEnd (excluded), this entity had exactly these values. ValidPeriod is the same interval as a Teradata PERIOD(TIMESTAMP(0) WITH TIME ZONE), for CONTAINS, OVERLAPS and P_INTERSECT.

It is still a plain view: nothing is materialised, new ingestions show up at once, and a relative window moves by itself.

Build¶

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(
    entity_id=['Category'],
    selected_features=selection,
    view_name='HIST_CATEGORY',
)

The arguments are those of build_dataset (entity_id, selected_features, view_name, schema_name, comment, return_query, join_type, record_lineage, document, vector_indexing) plus the window below. There is no grouped option for a historical dataset.

A real result¶

On the demo feature store (demos/, notebooks 00–06), the daily category features were rolled out over business dates. The view above returns, for the Clothing category (executed on Vantage 20):

Category	ValidStart	ValidEnd	sum	max	mean	median
Clothing	2023-01-01	2023-05-11	4877.36	298.05	168.18	144.81
Clothing	2023-05-11	2023-05-12	4334.25	299.90	154.79	133.42
Clothing	2023-05-12	2023-05-13	4306.97	280.23	148.52	137.25
Clothing	2023-05-13	2023-05-14	6352.94	294.98	158.82	147.62
Clothing	2023-05-14	2023-05-15	5584.32	299.44	150.93	162.76
Clothing	2023-05-15	now	4948.17	278.53	154.63	173.42

The last period is still open in the store; it ends at the window's end, here the moment the view was queried.

The window¶

The window is half-open, [start, end). now is the current valid time when the view is queried (CURRENT_TIMESTAMP), so a window without a fixed end slides with time. The end always exists: a period still open in the store ends at the window's end, never at 9999-12-31.

Arguments	Window	Slides?
none	`[earliest, now)` — the full history	yes
`delay`	`[earliest, now − delay)`	yes
`duration`	`[now − duration, now)`	yes
`duration` + `delay`	`[now − delay − duration, now − delay)`	yes
`start`	`[start, now)`	end only
`end`	`[earliest, end)`	no
`start` + `end`	`[start, end)`	no
`start` + `duration`	`[start, start + duration)`	no
`end` + `duration`	`[end − duration, end)`	no

build_historical_dataset(..., start='2024-01-01', end='2024-04-01')   # Q1, end excluded
build_historical_dataset(..., duration='12 months')                    # last 12 months
build_historical_dataset(..., duration='12 months', delay='1 day')     # ... up to yesterday
build_historical_dataset(..., end='2025-01-01', duration='90 days')

start / end accept ISO strings, date, datetime, pandas.Timestamp. A bare date is midnight, so end='2024-04-01' covers all of March.
duration / delay accept '3 months', '90 days', '12 hours', '1 year 2 months', '2 days 6 hours', ISO 8601 ('P1Y2M', 'PT12H'), (3, 'months') or a datetime.timedelta. 'm' alone is refused (minute or month?).
Months are applied with ADD_MONTHS, which clamps month-ends: 31 May minus 3 months is 29 February. The rest uses a DAY TO SECOND interval, so a duration is limited to 9999 days plus months.
Refused with a message naming the rule: delay with start or end; start, end and duration together; a window that ends before it starts.

INNER or FULL OUTER¶

NORMALIZE merges adjacent periods with equal values; it never decides which periods exist. The join type does:

INNER keeps a period only where every feature has a value. An entity's history starts when its last feature appears; an entity whose features never overlap is absent.
FULL OUTER keeps every period where at least one feature has a value, with NULLs elsewhere.

A real case from the demo store (domain Customer Transaction Analytics Time and Category Management, notebooks 09–16): sum_Transaction_Amount exists under three processes. Two were rolled out over 2023 business dates (FILTERED, WITH_CLONE); the third (HYBRID) ran in real time today. Selecting all three versions (get_feature_versions returns them as a list, and each becomes its own column) gives, for the Restaurant category:

ValidStart	ValidEnd	sum · HYBRID	sum · FILTERED	sum · WITH_CLONE	FULL OUTER	INNER
2023-01-01	2023-05-21	NULL	1514.00	1514.00	✔	✘
2023-05-21	2023-05-22	NULL	2086.89	1514.00	✔	✘
… 4 more daily periods …		NULL	…	1514.00	✔	✘
2026-10-06 08:03:13	2026-10-06 08:03:46	1328.46	1872.26	1514.00	✔	✔
2026-10-06 08:03:46	2026-10-06 08:03:47	1111.84	1872.26	1514.00	✔	✔
2026-10-06 08:03:47	2026-10-06 08:04:20	1111.84	1872.26	1514.00	✔	✔
…					✔	✔

(The three max_Transaction_Amount columns are omitted for width; the one-second row exists because the HYBRID run changed sum at 08:03:46 and max at 08:03:47 — see Short periods from real-time ingestion below.)

Over all six categories the FULL OUTER view returns 112 rows and the INNER one 75: INNER starts each history only when the HYBRID process first wrote a value, today; FULL OUTER keeps the 2023 history with NULL in that column. The rows both keep are identical.

The rule matches the snapshots: at any instant t of the window, the history equals build_dataset(..., as_of=t, join_type=...). This was checked live on the demo store, at three instants and for both join types.

When every feature of an entity is written by the same process at the same times — as in the category example above — both join types return the same rows. A stored NULL and an absent value both show as NULL and merge into one period under FULL OUTER, as they look identical in a snapshot.

Short periods from real-time ingestion¶

Before 0.3.1.227, a process run without tdfs4ds.FEATURE_STORE_TIME (real time) merged each feature with its own statement, stamped with its own current time, so two features of the same run could start a second apart. Seen on the demo store: one run changed sum_Transaction_Amount at 08:03:24 and max_Transaction_Amount at 08:03:25. A history that combines them shows that second as a period of its own, with the new sum and the previous max.

Since 0.3.1.227 a real-time run reads the database time once and uses it for every feature and every partition (checked live: one run over a FilterManager, 48 rows across 8 features and 6 categories, a single valid start). Data written by earlier versions keeps its one-second periods, and the history reports them because the store holds them. Runs driven by a TimeManager or with FEATURE_STORE_TIME set have always shared one valid start, and are the recommended way to run processes.

A related effect concerns floating-point features. Re-running a process on unchanged data leaves a stored period intact only when the new value is bit-identical. An aggregate recomputed in a different order may differ in its last bits: on the demo store, a re-run turned 16038.699999999999 into 16038.7 and the store opened a new period. The history shows two rows whose values print alike but differ. Round such features in the process view if these periods are unwanted.

The window in the catalog¶

Each historical dataset has one row in FS_V_<catalog>_WINDOW (default FS_V_FS_DATASET_WINDOW), read by tdfs4ds.get_dataset_window(). A snapshot dataset has none, so join it to the catalog with a LEFT OUTER JOIN:

SELECT c.DATASET_NAME, w.START_KIND, w.END_KIND, w.WINDOW_START, w.WINDOW_END, w.WINDOW_TEXT
FROM FS_V_FS_DATASET_CATALOG c
LEFT OUTER JOIN FS_V_FS_DATASET_WINDOW w ON w.DATASET_ID = c.DATASET_ID;

Column	Content
`WINDOW_START`, `WINDOW_END`	The bounds when the view is queried: a sliding window moves exactly as the dataset does. `WINDOW_START` is NULL when the window starts at the earliest stored value
`START_KIND`	`FIXED` (`start` given), `END_MINUS_DURATION` (`duration`) or `EARLIEST`
`END_KIND`	`FIXED` (`end` given), `START_PLUS_DURATION` (`start` + `duration`) or `NOW` (query time, minus `delay`)
`START_TS`, `END_TS`	The `start` / `end` given, as timestamps
`DURATION_ISO`, `DELAY_ISO`	`duration` / `delay` in ISO 8601 (`P3M`, `P2DT6H`); also as `_MONTHS` + `_SECONDS`
`SLIDING`	`Y` when a bound depends on the query time
`WINDOW_TEXT`	The window as written in this page: `[now - 2 DAY 06:00:00 - 3 MONTH, now - 2 DAY 06:00:00)`

Every window has a row, the full history included (EARLIEST / NOW), so a missing row only means "not recorded". On the demo store (queried 2026-10-06 10:29 UTC):

Dataset	Window	`WINDOW_START`	`WINDOW_END`
full history	`[earliest, now)`	NULL	2026-10-06 10:29:07
`duration='3 months', delay='2 days 6 hours'`	`[now - 2 DAY 06:00:00 - 3 MONTH, now - 2 DAY 06:00:00)`	2026-07-04 04:29:08	2026-10-04 04:29:08
`start='2023-02-28', duration='1 month 1 day'`	`[2023-02-28 00:00:00, … + 1 MONTH 1 DAY)`	2023-02-28 00:00:00	2023-03-29 00:00:00
`end='2023-05-31', duration='3 months'`	`[2023-05-31 00:00:00 - 3 MONTH, 2023-05-31 00:00:00)`	2023-02-28 00:00:00	2023-05-31 00:00:00

For all eight shapes of the window table above, WINDOW_START and WINDOW_END equalled the bounds the dataset view itself computes, evaluated in the same statement (months first with ADD_MONTHS, so 31 May minus 3 months is 28 February in 2023).

The window table is a separate table, like the build table: an existing catalog needs no ALTER, and versions older than 0.3.1.226 ignore it. It is created the next time the catalog is opened; a user without CREATE TABLE rights gets a warning and the window is then held only by the view's DDL. restore_dataset_catalog reports a historical dataset without a window row as REPAIR ("history window not recorded") and writes it when applied. When a history is rebuilt in place as a snapshot, its window row is closed.

Lags and leads¶

feature_offsets works on histories too: at each instant of the window, amount_LAG_1D reads the value one day earlier and churn_LEAD_1MO one month later. See Lags and leads.

Rebuild, restore, migrate¶

The view is registered in the dataset catalog with TIME_MODE='HISTORY' and its window, and parse_dataset_ddl reads it back, so:

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

restore_dataset_catalog and cross-store migration (migrate_objects) handle historical datasets like the others.

Process versions in the window¶

Since 0.3.3.12. The values of a feature all carry its process's PROCESS_ID, but the SQL beneath that process may have changed inside the window (see Process versions). build_historical_dataset logs a warning per feature whose window spans several versions, and:

tdfs4ds.history_versions('DB.HIST_CATEGORY')
# {'sum_amount': {'process_id': '...', 'versions': [1, 2],
#                 'segments': [{'version': 1, 'from': ..., 'to': ...}, {'version': 2, ...}]}}

tdfs4ds.build_historical_dataset(entity_id, selected_features, 'DB.HIST_CATEGORY',
                                 start='2024-01-01', process_version=True)
# also creates DB.HIST_CATEGORY_PV: the history + sum_amount_PROCESS_VERSION per period

A period gets the version in force at its ValidStart (NULL before versioning). The history view itself is unchanged, with or without the option. Values written by different processes are not stitched together.

Why this SQL¶

Inspect it with return_query=True. The shapes were chosen by reading EXPLAIN plans on Vantage 20:

INNER uses Teradata's native SEQUENCED VALIDTIME join, which intersects the valid periods itself. Every storage table is read once, from a single partition (the FEATURE_ID partition elimination is kept), the joins are rowkey-based merge joins without redistribution, and the window — even one built on CURRENT_TIMESTAMP — is folded into the join step as constants.

FULL OUTER cannot use it: a sequenced outer join is refused (Error 9347). Each entity's timeline is cut at every change point and each feature is joined on containment. The change points come from one read per feature that emits both bounds; reading starts and ends separately returned the same rows with 8 storage reads instead of 3 and 10 redistributions instead of 2. FULL OUTER remains about twice as slow as INNER on the tested data.

Normalization uses SELECT NORMALIZE ON MEETS OR OVERLAPS, so that consecutive rows of an entity always differ. tdfs4ds' own ingestion does not create equal neighbours — re-running a process on unchanged data, in real time or at an explicit valid time, left the stored periods intact — and on the demo store the merge found nothing to do. It keeps the property true for histories that do hold equal neighbours, for example rows written outside tdfs4ds. The TD_NORMALIZE_OVERLAP_MEET table function was tested and set aside: it needs RETURNS types matching the input exactly, works only behind a WITH clause, and fed by a derived table it silently multiplied the rows (3 rows in, 6 out), because the input becomes a second FROM item joined to the output.

Measured on the demo store with the shipped SQL (EXPLAIN read step by step; elapsed = warm runs; small data, so read the plan columns, not the times, as the scaling indicator). Every view was checked row for row against an independent reference computed from the raw storage rows:

View	Rows	Steps	Single-partition reads	Redistributions	Elapsed
Customer, 4 features, INNER	1,079	9	4	0	0.13–0.17 s
Customer, 4 features, FULL OUTER	1,079	22	8	1	0.22–0.25 s
Category, 4 features, INNER	39	8	4	0	0.07 s
Category, 4 features, FULL OUTER	39	20	5	2	0.09–0.11 s
Category, 2 features × 3 versions, INNER	75	9	6	0	0.07 s
Category, 2 features × 3 versions, FULL OUTER	112	24	9	2	0.14–0.28 s

With several features from the same storage table and very few entities (6 categories), the optimizer joins two small spools with a local product join whose condition still carries the entity equality; with 1,079 customers the same shape uses merge joins only.

Historical datasets — build_historical_dataset¶