Lags and leads — `feature_offsets`¶

A dataset row describes an entity at one instant t:

Dataset	t
`build_dataset()`	now (when the view is queried)
`build_dataset(as_of=...)`	that date
`build_dataset(time_manager=tm)`	the TimeManager's business date (it moves with `tm.update()`)
`build_historical_dataset(...)`	every instant of the window (one row per period of constant values)

feature_offsets reads a feature at another instant than t, and can read the same feature several times:

a lag -L is the value at t − L: yesterday's amount, last week's, last month's. These are the inputs of autoregressive and seasonal models;
a lead +L is the value at t + L: next month's churn flag. This is the target of a forecast;
0 is the instant itself.

tdfs4ds.build_dataset(
    entity_id=['CustomerID'],
    selected_features={'amount': pid_amount, 'churn': pid_churn},
    feature_offsets={'amount': ['0', '-1 day', '-7 days', '-1 month'],
                     'churn':  ['+1 month']},
    time_manager=tm,
    view_name='DS_CHURN_TRAIN')
# CustomerID, amount, amount_LAG_1D, amount_LAG_7D, amount_LAG_1MO, churn_LEAD_1MO

Each offset is a column. A listed feature keeps its plain column only when '0' is listed. A feature not listed in feature_offsets is read as before. A version given its own column name ({'process_id': pid, 'alias': 'spend'}, see Datasets) carries its offsets under that name: spend_LAG_1D.

What an offset means¶

A value valid over [s, e) is, as a lag of L, valid over [s + L, e + L): its validity period is shifted, then the usual temporal rules apply. At instant t, amount_LAG_1D is therefore the value that was valid at t − 1 day.

time ───────────────────────────────────────────────►
amount            [ 10 ......... )[ 20 ................
amount_LAG_1D        [ 10 ......... )[ 20 .............   (one day later)
churn                        [ 0 ......... )[ 1 ........
churn_LEAD_1MO     [ 0 ......... )[ 1 ........            (one month earlier)

Writing an offset¶

Written	Meaning	Column suffix
`'0'`	the instant itself	none
`'-1 day'`, `'-P1D'`	one day before (lag)	`_LAG_1D`
`'-36 hours'`	36 hours before	`_LAG_1D12H`
`'-1 week'`	7 days before	`_LAG_7D`
`'-1 month 2 days'`	one calendar month, then 2 days, before	`_LAG_1MO2D`
`'+1 month'`, `'+P1M'`	one month after (lead)	`_LEAD_1MO`
`'+1 year'`	one year after	`_LEAD_1Y`

The sign is required. '1 day' is refused: lag or lead has to be explicit.
Units: year, month, week, day, hour, minute, second, freely combined. ISO 8601 durations are accepted with a sign ('-P1D', '+PT6H'), and so are a signed (n, unit) tuple and a timedelta.
Months and years are calendar months, applied with ADD_MONTHS: 31 March minus one month is 28 or 29 February.
No fraction of a second. Every valid period is stored as TIMESTAMP(0) WITH TIME ZONE, whatever the source's own timestamp type, so milliseconds or microseconds could not change any result; they are refused. Days, hours, minutes and seconds together are limited to 9,999 days (a Teradata interval); write longer offsets in months or years.
An offset finer than the data's cadence reads the value in force. On a store fed daily by a TimeManager, '-6 hours' returns the previous day's value.

Leads stop at now¶

A lead reads the future of t. What is valid after the database's current time has not been observed yet: an open stored period ("until changed") says what the value is now, not what it will be. So a lead is NULL while t + L is later than the database's current time:

in a history, the last L of the window has no lead value (FULL OUTER: NULL; INNER: no row);
build_dataset(as_of=...) close to today gives NULL leads (a warning says so);
a current snapshot (build_dataset() with no date) cannot have a lead at all. With join_type='INNER' it is refused, since the dataset would be empty; with 'FULL OUTER' the lead columns are NULL and a warning says so. Use an as_of date, a TimeManager or a history instead.

A training set therefore never contains a target that has not happened yet in real time.

Business dates in the past (a TimeManager replaying history). The cut-off is the database's current time, not the last business date the processes ran for. On a store whose business dates are in 2023, a lead read after the last business date returns the last value stored (the open period), which is not an observation. Stop the window, or the TimeManager's dates, one horizon before the last business date: with dates up to 31 October and a +1 month lead, end the window on 1 October.

Histories¶

tdfs4ds.build_historical_dataset(
    entity_id=['Category'], selected_features=selection,
    feature_offsets={'sum_amount': ['0', '-1 day', '-7 days', '+1 month']},
    start='2023-01-01', join_type='FULL OUTER', view_name='HIST_CATEGORY_LAGS')

The rule of histories still holds: at any instant t of the window, the history equals build_dataset(as_of=t, feature_offsets=...) with the same join type. This was verified on Vantage 20 at sampled instants, period starts and one second before them included.

A history with offsets is computed with elementary intervals for both join types, because the native sequenced join cannot shift a period (see Latency below). process_version=True is not available with offsets yet.

In the admin app¶

Explore › Dataset builder: each chosen feature gets a Lags / leads field (0, -1 day, -2 days, +1 month) with presets (lags 1–3 days, lags 1–3 months, lead 1 month). The column names appear as you type, an offset that cannot be read is shown in red, and a lead on a current snapshot is flagged before creation. Show SQL, Sample 20 rows and Create dataset work as for any other dataset.

In the dataset catalog¶

Feature list: each offset column is a row of the dataset's feature list (get_dataset_features), under its own name.
Offsets: tdfs4ds.get_dataset_offsets() returns one row per offset column, from the optional table FS_<catalog>_OFFSETS:

Column	What it holds
`FEATURE_NAME`	the column
`BASE_FEATURE_NAME`	the feature it reads
`FEATURE_ID`, `FEATURE_VERSION`	the feature's id and process
`OFFSET_DIRECTION`	`LAG`, `LEAD`, or `NONE` for `'0'`
`OFFSET_MONTHS`, `OFFSET_SECONDS`	the offset, signed
`OFFSET_ISO`	the offset as ISO 8601 (`-P1D`)
`OFFSET_TEXT`	the offset in words ("1 day before")
`OFFSET_STRATEGY`	`PIVOT` or `PER_OFFSET`
`LEAD_HORIZON`	`NOW` for a lead

A dataset without offsets has no row. The table is temporal, so rebuilding a dataset with other offsets keeps the earlier ones in its history. - The view's SQL is the reference: - rebuild_dataset (grouped, another date, a history) keeps the offsets; - restore_dataset_catalog reports a missing or different offsets row as REPAIR; - a cross-store migration rebuilds the dataset with its offsets. - Documentation: each offset column gets its feature's description with a fixed sentence in front, e.g. "amount, 1 day before the dataset's instant — …". No LLM is involved. The overview the instruct model writes sees those descriptions, so it can present the dataset as a training set.

Using it for forecasting and prediction¶

Supervised prediction. Put the inputs at t and the target at t + h in one row: churn_LEAD_1MO next to amount, amount_LAG_7D, … Each row holds only what was known at t, next to what happened h later, so there is no leakage.

A training set over time. A history with offsets is a supervised table over many instants: one row per entity and period, each with its lags and its lead. Split train and test on ValidStart (train on the past, test on the most recent periods), never at random. The same applies when a TimeManager is rolled over business dates.

Autoregressive and seasonal terms. - -1 day … -k days are the AR terms. - -7 days and -1 year add weekly and yearly seasonality. - Lags of several features let a model learn cross-effects (a lagged price on volume, for instance).

Direct multi-horizon forecasting. Leads +1 day, +7 days, +1 month in one dataset train one model per horizon on exactly the same inputs.

Scoring with the same definition. - The dataset without its lead columns, read now or at the TimeManager's date, is the input at scoring time. - Training and scoring then share one SQL definition, so their features cannot drift apart. - With a TimeManager, tm.update() moves the whole dataset, lags included, to the next business date.

Differences and ratios. amount - amount_LAG_1D or amount / amount_LAG_1MO are one expression away in the model or in a view on top of the dataset.

Latency and SQL¶

Offsets are written so that they keep the storage access paths. Measured on Vantage 20 by reading the EXPLAIN steps, not by counting them:

The shift is on the constant side.
The view reads ValidStart <= t - L, never ValidStart + L <= t.
Teradata folds t - L into a literal (TIMESTAMP '2023-04-14 00:00:00+00:00' in the plan).
Each storage table is still read from its FEATURE_ID partition(s), and a single-entity lookup stays single-AMP.
With a TimeManager, the shift is inside the business-date sub-query, so the manager is still read once per distinct instant, by its primary index.
PIVOT (default) reads each storage table once for all its offset columns: one aggregation step, local to the AMPs. offset_strategy='PER_OFFSET' gives each column its own sub-query and join. With 8 features × 4 offsets (32 columns):

	parse (EXPLAIN)	single-entity lookup, median
plain multi-join, 8 columns	0.26 s	43 ms
plain grouped, 8 columns	0.08 s	22 ms
`PIVOT`, 32 columns	0.35 s	23 ms
`PER_OFFSET`, 32 columns	31 s	— (stopped by the benchmark's parse gate)

Reading each feature once (rather than each table once) cost 1.1 s and 84 ms on the same columns. - Features without offsets keep their SQL: the same sub-query as without offsets, multi-join or grouped. A dataset without feature_offsets is unchanged. - Histories with offsets use elementary intervals for INNER too. This is slower than the native sequenced join that INNER uses without offsets: on the demo store, 23 plan steps against 7. A FULL OUTER history with one lag added 2 steps and 2 storage reads. Every storage read keeps partition elimination.

Compatibility¶

Without feature_offsets every statement is the one 0.3.3.27 wrote, and existing views read back without any offset.
A tdfs4ds version before 0.3.3.28 cannot read the offsets back from a view, so rebuilding or restoring such a dataset with it would lose them. Rebuild datasets with offsets using 0.3.3.28 or later.

Lags and leads — feature_offsets¶