tdfs4ds.dataset.history¶
Historical datasets: one row per entity per period of constant feature values.
Pure module (no database access), so every rule here is testable offline.
A snapshot dataset (build_dataset) reads the feature store at ONE valid
instant. A historical dataset reads it over a WINDOW and returns, for each
entity, the successive periods during which the combination of its feature
values did not change::
entity..., ValidPeriod, ValidStart, ValidEnd, feature_1, feature_2, ...
What makes that more than a NONSEQUENCED read: features change at
different instants, so their valid periods must be intersected, and each
entity's result must be one row per period of constant values.
The SQL shapes below were chosen by reading EXPLAIN plans on Vantage 20 (see CLAUDE.md, "Historical datasets"):
INNER
The native SEQUENCED VALIDTIME join: Teradata intersects the periods
itself, each storage table is read once ("from a single partition"), the
joins are rowkey-based merge joins, and the window (even a sliding one built
on CURRENT_TIMESTAMP) is folded into the join step as constants.
FULL OUTER
A sequenced outer join is refused (Error 9347), so each entity's timeline
is cut at every change point ("elementary intervals") and each feature is
attached with a LEFT JOIN on containment.
Both end with SELECT NORMALIZE ON MEETS OR OVERLAPS, which merges adjacent
periods carrying equal values (NULLs compare equal), so consecutive rows of an
entity always differ. tdfs4ds' own ingestion does not create such neighbours:
re-writing an unchanged value leaves the stored period intact (verified live,
real-time and explicit-valid-time runs alike), and on the demo store NORMALIZE
merged nothing. It is a guarantee for histories that do hold adjacent equal
rows — written outside the MERGE path, copied, corrected — at the cost of one
aggregation step. It was preferred to the
TD_NORMALIZE_OVERLAP_MEET table function, which needs exact RETURNS
types, only works behind a WITH clause, and silently multiplies rows when
fed by a derived table (the input becomes a second FROM item).
What the join type changes, and NORMALIZE does not: INNER keeps a period only
where EVERY feature has a value, FULL OUTER every period where AT LEAST ONE
has. At any instant t of the window, the history equals
build_dataset(as_of=t) with the same join type.
module-attribute
¶
_UNITS = {'year': ('months', 12), 'month': ('months', 1), 'week': ('seconds', 7 * 86400), 'day': ('seconds', 86400), 'hour': ('seconds', 3600), 'minute': ('seconds', 60), 'second': ('seconds', 1)}
module-attribute
¶
_UNIT_ALIASES = {'y': 'year', 'yr': 'year', 'yrs': 'year', 'year': 'year', 'years': 'year', 'mo': 'month', 'mon': 'month', 'month': 'month', 'months': 'month', 'w': 'week', 'wk': 'week', 'wks': 'week', 'week': 'week', 'weeks': 'week', 'd': 'day', 'day': 'day', 'days': 'day', 'h': 'hour', 'hr': 'hour', 'hrs': 'hour', 'hour': 'hour', 'hours': 'hour', 'min': 'minute', 'mins': 'minute', 'minute': 'minute', 'minutes': 'minute', 's': 'second', 'sec': 'second', 'secs': 'second', 'second': 'second', 'seconds': 'second'}
module-attribute
¶
_DURATION_HINT = "Use a string such as '3 months', '90 days', '12 hours', '1 year 2 months', an ISO 8601 duration ('P1Y2M', 'PT12H'), a (number, unit) tuple, or a datetime.timedelta. Units: year, month, week, day, hour, minute, second ('m' is refused: minute or month?)."
module-attribute
¶
_ISO_RE = compile('^P(?:(?P<y>\\d+)Y)?(?:(?P<mo>\\d+)M)?(?:(?P<w>\\d+)W)?(?:(?P<d>\\d+)D)?(?:T(?:(?P<h>\\d+)H)?(?:(?P<mi>\\d+)M)?(?:(?P<s>\\d+)S)?)?$', IGNORECASE)
module-attribute
¶
module-attribute
¶
_TABLE_ALIAS_RE = compile(f'\b(?:FROM|JOIN)\s+(?P<table>{_IDENT}\s*\.\s*{_IDENT})\s+B(?P<n>\d+)\b', IGNORECASE)
module-attribute
¶
_PRED_RE = compile("\\bB(?P<n>\\d+)\\.FEATURE_ID\\s*=\\s*(?P<id>\\d+)\\s+AND\\s+B(?P=n)\\.FEATURE_VERSION\\s*=\\s*'(?P<v>[^']*)'", IGNORECASE)
module-attribute
¶
module-attribute
¶
module-attribute
¶
_TS_LIT_RE = compile("^CAST\\s*\\(\\s*TIMESTAMP\\s*'(?P<ts>[^']*)'\\s+AS\\s+TIMESTAMP\\s*\\(\\s*0\\s*\\)\\s+WITH\\s+TIME\\s+ZONE\\s*\\)$", IGNORECASE)
module-attribute
¶
_NOW_RE = compile('^CAST\\s*\\(\\s*CURRENT_TIMESTAMP\\s*\\(\\s*0\\s*\\)\\s+AS\\s+TIMESTAMP\\s*\\(\\s*0\\s*\\)\\s+WITH\\s+TIME\\s+ZONE\\s*\\)$', IGNORECASE)
module-attribute
¶
module-attribute
¶
_INTERVAL_RE = compile("^\\((?P<expr>.*)\\s(?P<sign>[+-])\\s+INTERVAL\\s*'(?P<d>\\d+)\\s+(?P<h>\\d{2}):(?P<mi>\\d{2}):(?P<s>\\d{2})'\\s+DAY\\s+TO\\s+SECOND\\s*\\)$", IGNORECASE | DOTALL)
module-attribute
¶
module-attribute
¶
_ALL_REQUIRED_RE = compile(f'\bWHERE\s+I\.{_VE}\s+IS\s+NOT\s+NULL\s+AND\s+F1\.{_VS}\s+IS\s+NOT\s+NULL\b', IGNORECASE)
module-attribute
¶
_SHIFTED_VS_RE = compile(f'\bB(?P<n>\d+)\.FEATURE_VALUE\s+AS\s+\w+\s*,\s*(?P<vs>.+?)\s+AS\s+{_VS}\s*,\s*(?P<ve>.+?)\s+AS\s+{_VE}\s+FROM\b', IGNORECASE | DOTALL)
module-attribute
¶
module-attribute
¶
A non-negative length of time: a calendar part (months) and an exact part (seconds).
The two are kept apart because a month has no fixed length: months are
applied with ADD_MONTHS (which clamps month-ends — 31 May minus three
months is 29 Feb, verified live; - INTERVAL 'n' MONTH would raise
Error 2665 instead), the rest with a DAY TO SECOND interval.
INTERVAL 'd hh:mi:ss' DAY TO SECOND for the exact part.
No precision on DAY: DAY(4) TO SECOND inside a literal is a
syntax error (Error 3706, verified live); the precision is taken from
the literal itself, and 9999 days is accepted.
SQL for expr ± self; sign is +1 or -1.
Months first (innermost), then the exact part — "back one month, then back two days". The order is fixed so the text round-trips.
The valid-time window of a historical dataset, half-open [start, end).
====================== ========================================== =========
Given Window Slides?
====================== ========================================== =========
nothing [earliest, now) 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
====================== ========================================== =========
now is the current valid time WHEN THE VIEW IS QUERIED
(CURRENT_TIMESTAMP), so a relative window moves by itself. The end is
therefore always defined: a period still open in the store ends at the
window's end, never at 9999-12-31.
property
¶
How the lower bound is defined: FIXED, END_MINUS_DURATION or EARLIEST.
property
¶
How the upper bound is defined: FIXED, START_PLUS_DURATION or NOW
(the query time, minus delay when given).
The window as the typed columns of the dataset catalog's window table.
Every window is recorded explicitly, the full history included
(EARLIEST / NOW), so "no row" only ever means "not recorded".
Normalise value to the body of a Teradata TIMESTAMP '...' literal.
Returns 'YYYY-MM-DD HH:MI:SS[.ffffff][+HH:MM]'. A time part is always
present: Teradata rejects TIMESTAMP '2024-03-31' (Error 3706, verified
live), so a bare date becomes midnight. Fractional seconds and a time-zone
offset are kept when given (both verified accepted in VALIDTIME AS OF);
without an offset the literal is read in the session time zone, exactly as
tdfs4ds.FEATURE_STORE_TIME is when features are ingested.
Accepted inputs: ISO strings ('2024-03-31', '2024-03-31 23:59',
'2024-03-31T23:59:59.5Z', '2024-03-31 23:59:59+0200'),
datetime.datetime, datetime.date, pandas.Timestamp and
numpy.datetime64. Anything else — including ambiguous forms such as
'03/31/2024' — raises ValueError naming the accepted formats, rather
than being guessed.
Read value into a Duration. None stays None.
Accepted: '3 months', '90 days', '1 year 2 months', '2 days 6
hours', ISO 8601 ('P1Y2M', 'PT12H'), (3, 'months'), a
datetime.timedelta (whole seconds) or a Duration.
Refuse feature aliases that would collide with the period columns.
INNER: native SEQUENCED VALIDTIME join, then window clip, then NORMALIZE.
(start_sql, end_sql, filters_sql) of f's periods moved by its offset, read from alias x.
A lag of L moves a period [s, e) to [s + L, e + L); a lead moves it back and
stops at the database's current time (what is valid after now is not known
yet). Filters compare the COLUMNS with shifted CONSTANTS, so the storage read
keeps its access path; the END expression only adds to ValidEnd below
the window end, so the 9999-12-31 sentinel never overflows.
One feature's valid periods, restricted and clipped to the window (FULL OUTER).
The clip of a feature read with an offset: shift first, then clip like any other.
FULL OUTER: elementary intervals, LEFT JOIN each feature, then NORMALIZE.
all_required keeps only the intervals where EVERY feature has a value: the INNER semantics, for a history with offsets (a shifted period cannot go through the native sequenced join).
The REPLACE VIEW statement of a historical dataset. None without features.
(base, literal, ops): base 'lit'|'now'; ops innermost-first, each ('m', n) or ('s', ±n).
Read a historical dataset's DatasetSpec back from its DDL, or None.
Recognised by its NORMALIZE clause and the ValidPeriod projection, so a
snapshot view (which carries similar per-feature sub-queries) is never
mistaken for one, nor the reverse.
{alias number: Offset} of the features read with an offset (their end is a CASE).
The bound expression following CASE WHEN K1.Valid... <op> up to THEN.