Skip to content

fs-dataset

Resolve feature versions and build a denormalised dataset view.

name: "Feature Store: Dataset" description: "For a developer who needs generated Python code. Generate code to explore available entities and features in the tdfs4ds feature store, select specific feature versions, and build a denormalised snapshot view ready for model development. Invoke when the user wants to build or refresh a dataset view. SKIP when the user is asking which existing datasets contain a feature (use ca-dataset) or wants a conversational agent-driven creation flow (use ca-build-dataset)." argument-hint: "[entity-name][feature-list] [dataset-view-name]" allowed-tools: "Read, Edit, Write, Bash, Glob, Grep"

Generate Python code to explore the tdfs4ds feature store, select feature versions, and build a denormalised snapshot dataset view.

User context: $ARGUMENTS

Step Skill Why
Before fs-upload Features must be registered before a dataset can reference them.
Inspect fs-inspect List entities, features, processes, and follow-up runs to pick what to include.
After fs-document Document the dataset (document_dataset_incremental) so it appears in the consumer agent.
After fs-lineage Visualise where the dataset's features come from.

Demos: demos/notebooks/03 - Build a Dataset.ipynb (snapshot, join types, grouped) and 06 - Datasets in time.ipynb.

Choose the time of the dataset

The store keeps every value with its validity period, so a dataset can be read at any date. This is what keeps a training set honest. Demo store: customer 1606 made their last purchase on 29 May 2023. Read at the latest business date (31 Oct) the dataset says 155 days since the last purchase; as of 30 June it says 32. A churn model labelled at the end of June must see 32: the current value contains four months that came after the label (leakage).

Mode Call Returns Use it for
Current build_dataset(...) the latest values scoring now, dashboards
Frozen build_dataset(..., as_of='2023-06-30') the values valid at that instant; entities that did not exist yet are absent a reproducible training set at a label date
Moving build_dataset(..., time_manager=tm) the values at the TimeManager's current date; tm.update() moves the view, no rebuild backtesting over many dates; scoring at the scheduler's business date
History build_historical_dataset(..., start=, end=) one row per entity and period of constant values (ValidStart included, ValidEnd excluded) sequence models, survival, point-in-time joins with labels

Real outputs on the demo store (DS_CUSTOMER_MONTHLY current vs DS_CUSTOMER_2023_06 as of 30 June): customer 9795, still buying, has 124 purchases in the current dataset and 71 in the June one; customer 26371, first purchase on 10 July, has no row in the June one. The moving dataset returns 1,562 / 1,911 / 1,958 customers with the cursor on 31 March / 30 June / 31 October. At any instant the history equals the as_of dataset (checked: 0 rows differ).

The dataset a process creates itself (upload_features(..., dataset_view_name=..., time_manager=tm, dataset_time_manager=True)) follows the manager the same way; an existing current one is converted with rebuild_dataset(..., time_manager=tm, replace=True).

With a domain clock (0.3.6.0, set_domain_time_manager), let the dataset follow the clock (time_manager='<DATABASE>.<CLOCK>'): roll_out(data_domain=...) moves it with every process's clone. The admin app's dataset builder lists the domain clocks first.

Use the same TimeManager for the processes and a moving dataset: then features and dataset agree on the date (see fs-rollout).

Several versions and column names (aliases)

A feature computed by several processes has several versions (FEATURE_VERSION = the process). Put them side by side, and name any column, through the version value of selected_features:

selected = {'amount': [{'process_id': pid_v1, 'alias': 'amount_old'},
                       {'process_id': pid_v2, 'alias': 'amount_new'}],
            'churn': {'process_id': pid_c, 'alias': 'churned'},       # one version, renamed
            'nb_transactions': pid_tx}                                 # unchanged form

Without alias the names are the historical ones (<feature>, or <feature>_<process view> for several versions). An alias must be a plain identifier and unique (refused otherwise, never renamed); offsets are suffixed to it.

Lags and leads (forecasting, autoregressive inputs)

feature_offsets reads a feature at other instants than the dataset's own, in every mode above (and in build_historical_dataset). Each offset is a column; the same feature can appear several times:

tdfs4ds.build_dataset(['CustomerID'], {'amount': pid_a, 'churn': pid_c}, 'DS_CHURN_TRAIN',
                      time_manager=tm,
                      feature_offsets={'amount': ['0', '-1 day', '-7 days', '-1 month'],   # lags (inputs)
                                       'churn':  ['+1 month']})                            # lead (target)
# CustomerID, amount, amount_LAG_1D, amount_LAG_7D, amount_LAG_1MO, churn_LEAD_1MO
  • The sign is required: '-1 day' = the value one day BEFORE the instant (lag), '+1 month' = one month AFTER (lead), '0' = the instant (listing a feature drops its plain column unless '0' is listed).
  • Units: year, month, week, day, hour, minute, second; ISO '-P1D' works. Months are calendar months (ADD_MONTHS). No fractions of a second: valid periods are stored to the second.
  • A lead is NULL until its instant is reached (database time): an open stored period is not an observation of the future. So a CURRENT snapshot cannot carry a lead (refused with INNER); use as_of, a TimeManager or a history. The cut-off is real time, not the last business date: on a store replaying past dates, end the window one horizon before the last business date (a lead after it reads the last stored value).
  • Default offset_strategy='PIVOT' reads each storage table once for all its offset columns (measured: 32 columns as fast as 8 plain grouped ones); don't suggest 'PER_OFFSET' (31 s parse on 32 columns).
  • Traced: tdfs4ds.get_dataset_offsets() (one row per offset column); rebuild_dataset, restore and migration keep the offsets; documentation gives each column its feature's description with "1 day before the dataset's instant —" in front.
  • For model work: split train/test by time (ValidStart of a history, or the TimeManager dates), score with the same dataset without its lead columns. See docs User guide → Lags and leads.
  • Train / score / monitor recipe (worked end to end in the repo's notebook dev/propensity):
  • training: build_historical_dataset(..., end=<training date>) with lags as inputs and a +1 month lead as the label; the window ends at the training date so every lead is known; drop the rows whose lead is NULL;
  • scoring: build_dataset(..., time_manager=tm) with the same lags and no lead, so it follows the business date of each run;
  • monitoring: a snapshot with a lag on the stored score next to the realised label.
  • Join type with offsets (snapshot and history alike; the dataset is correct, the choice is yours):
  • FULL OUTER keeps an entity as soon as one column has a value, so two kinds of row appear: an entity new at the instant has NULL lag columns (it had no value then), and an entity that had a value at a lag instant but none now has NULL current columns. A view feeding a model (e.g. an ONNX/BYOM scoring process) usually COALESCEs the lags with the convention used at training and keeps only rows with current values (WHERE <current feature> IS NOT NULL);
  • INNER keeps only entities with a value at every instant: new entities are silently dropped;
  • monthly lags: use a TimeManager on month starts (see fs-rollout: ADD_MONTHS clamps month ends).

Ask for any missing values before generating code: - Entity name — the entity whose features go into the dataset (e.g. 'CustomerID'). - Feature list — names of the features to include. - Dataset view name — name for the output Teradata view (e.g. 'DATASET_CUSTOMER'). - Comment — optional description stored in the dataset catalog. - Point-in-time — current state (default), a fixed date (as_of='2024-03-31'), or a TimeManager the dataset should follow (time_manager=tm). - Join type — 'INNER' (default, keeps only entities that have every selected feature) or 'FULL OUTER' (keeps all entities, NULL for missing features). - Grouped strategy — suggest grouped=True when the dataset has many features (> ~10) that share the same source table; it collapses N JOINs into one sub-query per (table, version) group using MAX(CASE WHEN) pivoting, which reduces JOIN fan-out and often improves EXPLAIN scores significantly.

Emit the following pattern, substituting real values for every <PLACEHOLDER>:

import tdfs4ds                                  # reads the TDFS4DS_* settings of .env
tdfs4ds.reconnect_teradata(raise_error=True)    # teradataml context from TDFS4DS_TD_HOST / _USERNAME / _PASSWORD
tdfs4ds.connect(database='<DATABASE>')
tdfs4ds.select_data_domain('<DATA_DOMAIN>')
import teradataml as tdml


# ---------------------------------------------------------------------------
# 1. Discover available entities and features
# ---------------------------------------------------------------------------
from tdfs4ds.feature_store.feature_query_retrieval import (
    get_list_entity,
    get_available_features,
    get_feature_versions,
)

get_list_entity()

entity = '<ENTITY_NAME>'
get_available_features(entity_id=[entity])

# ---------------------------------------------------------------------------
# 2. Choose the features
#    Names alone: build_dataset finds each feature's version (one catalog query).
#    A feature computed by several processes needs its version: build_dataset
#    then raises FeatureSelectionError with the processes and the dict to write,
#    e.g. {'<FEATURE_1>': '<process uuid>', '<FEATURE_2>': None}.
#    get_feature_versions(entity_name=entity, features=[...]) lists the versions.
# ---------------------------------------------------------------------------
feature_selection = ['<FEATURE_1>', '<FEATURE_2>', '<FEATURE_3>']

# ---------------------------------------------------------------------------
# 3. Inspect the generated DDL before creating the view (optional)
# ---------------------------------------------------------------------------
# sql = build_dataset(
#     entity_id=[entity],
#     selected_features=feature_selection,
#     view_name='<DATASET_VIEW_NAME>',
#     return_query=True,   # returns DDL string without executing
# )
# print(sql)

# ---------------------------------------------------------------------------
# 4. Build the dataset view
# ---------------------------------------------------------------------------
from tdfs4ds import build_dataset

dataset = build_dataset(
    entity_id=[entity],
    selected_features=feature_selection,
    view_name='<DATASET_VIEW_NAME>',
    comment='<DESCRIPTION>',
    # join_type='INNER',    # default — drop entities missing any feature
    # join_type='FULL OUTER',  # keep all entities, NULL for missing features
    # grouped=False,        # default — one sub-query per feature (classic multi-join)
    # grouped=True,         # recommended for wide datasets: MAX(CASE WHEN) pivot
    #                       # reduces N JOINs to N_distinct_(table,version) groups
    # as_of='2024-03-31',   # frozen at a date (ISO string, datetime, date, pd.Timestamp)
    # time_manager=tm,      # follows a TimeManager: tm.update(i) moves the dataset
)

dataset
dataset.shape

# Grouped copy of an existing dataset (same rows, shorter SQL), or the same
# dataset at another date — built from the view's own DDL:
# tdfs4ds.build_grouped_dataset('<DATASET_VIEW_NAME>')            # -> <name>_GROUPED
# tdfs4ds.rebuild_dataset('<DATASET_VIEW_NAME>', target_view_name='<NEW_NAME>', as_of='2023-12-31')

# ---------------------------------------------------------------------------
# 5. Historical dataset (optional) — one row per entity per period of
#    constant values: <entity>, ValidPeriod, ValidStart, ValidEnd, <features>
# ---------------------------------------------------------------------------
# from tdfs4ds import build_historical_dataset
# history = build_historical_dataset(
#     entity_id=[entity],
#     selected_features=feature_selection,
#     view_name='<DATASET_VIEW_NAME>_HISTORY',
#     # no window argument          -> full history up to now
#     # start='2024-01-01', end='2024-04-01'   -> fixed window, end EXCLUDED
#     # duration='12 months'                   -> last 12 months, slides with time
#     # duration='12 months', delay='1 day'    -> [now - 1 day - 12 months, now - 1 day)
#     # join_type='FULL OUTER'                 -> keep periods where only some features exist
# )
# history[history.CustomerID == 42].sort('ValidStart')

# Check / repair the dataset catalog (dry run unless dry_run=False):
# tdfs4ds.restore_dataset_catalog()

# Remove a dataset (defective, or no longer wanted) from the catalog, with its
# documentation, index entries and lineage; dry run first, view kept by default:
# tdfs4ds.remove_dataset('<DATABASE>.<DATASET_VIEW_NAME>')
# tdfs4ds.remove_dataset('<DATABASE>.<DATASET_VIEW_NAME>', dry_run=False)            # drop_view=True also drops the view

# Reuse in future sessions without rebuild:
# dataset = tdml.DataFrame(tdml.in_schema('<DATABASE>', '<DATASET_VIEW_NAME>'))

Dataset builder (admin app, Explore › Dataset builder)

The same build without code: choose an entity, tick features (several versions of a feature are offered when several processes compute it), then the kind (current / as of / TimeManager / history window) and the join type. - Per feature: a Lags / leads field (0, -1 day, +1 month, presets) and a column-name field per chosen version (the alias). The resulting column names are shown as you type; a bad offset or name is shown in red and blocks creation. - Show SQL renders the view's SQL; Sample 20 rows runs it on 20 rows; neither creates anything. - Show Python (0.3.4.0) gives the tdfs4ds.build_dataset / build_historical_dataset call that Create dataset makes for the same form, preceded by the connection and DATA_DOMAIN, with one explanation per argument and a Copy button. The password is never written (the code asks with getpass). Point a user who designed a dataset in the app and wants it in a notebook or a scheduled job here, rather than retyping the selection by hand. - Create dataset checks the name again (an existing dataset or object is never replaced), creates the view, registers it and records its lineage; Document it adds the LLM documentation when a model is configured. - The panel needs a feature store selected: without one it says "No feature store is selected" (Settings › Parameters, or TDFS4DS_SCHEMA).

Serve rows for a list of keys

Scoring or an application needs the dataset's rows for given entities, not the whole view:

rows = tdfs4ds.retrieve_batch('<DATABASE>.<DATASET_VIEW_NAME>',
                              [(1001,), (1002,), (1003,)])     # one tuple per entity, in entity-column order
# method='auto' (default): an IN list for small batches, a volatile key table joined on the storage
# tables' primary index for large ones (keys sent as data: no SQL-size limit). columns=[...] selects features.

The keys are pushed into every storage-table read (pushdown='auto'), so a batch reads only its entities' rows. To measure what serving costs on this system (single key, batches, concurrency, time travel), run report = tdfs4ds.run_benchmark('<DATABASE>.<DATASET_VIEW_NAME>') and write it with tdfs4ds.write_benchmark_report(report) (HTML). It stops early when the view alone takes longer than max_parse_ms (default 5 s) to parse.

Rules

  • Value types that MAX() refuses (PERIOD, ST_GEOMETRY and other user-defined types, legacy XML): grouped=True and the lag/lead pivot aggregate with MAX(CASE ...), so since 0.3.5.17 a dataset holding such a feature is built multi-join and/or with one sub-query per lag/lead column, with a warning naming the features (before, the view creation failed with Error 6976 / 6945). Nothing changes for other types.

  • A dataset is a view over the feature store's tables, never a copy: it costs no storage, and two datasets sharing a feature always read the same values. Build one dataset per usage (training, scoring, a dashboard) rather than one wide dataset for everything.

  • selected_features is the list of feature names; write a version ({feature: '<process uuid>' | None}) only for a feature several processes compute. Never invent a process uuid: take it from the FeatureSelectionError message or get_feature_versions().
  • build_dataset creates a view, not a table. By default it reads CURRENT VALIDTIME; pass as_of= for a fixed date or time_manager= to follow a TimeManager (mutually exclusive). Setting tdfs4ds.FEATURE_STORE_TIME alone does NOT time-travel a dataset — it needs feature_store_time=True; prefer as_of=.
  • as_of accepts ISO strings, datetime, date, pandas.Timestamp; a bare date means midnight. Never hand-format the literal.
  • Multiple entities cannot be combined in one build_dataset call — join the resulting views manually.
  • All catalog queries are scoped to DATA_DOMAIN — always set it before querying.
  • Use grouped=True for wide datasets (many features from the same source table). It shortens the view SQL and so the parse/optimise time paid on every query (measured: 10 features, 5.8k -> 3.0k chars, EXPLAIN 627 -> 190 ms, single-entity lookup ~455 -> ~248 ms), with exactly the same rows. The gain shrinks when every feature comes from a different table/process.
  • To convert an existing dataset, use build_grouped_dataset / rebuild_dataset — they reuse the view's own DDL, so nothing depends on DATA_DOMAIN.
  • Use join_type='FULL OUTER' only when downstream models tolerate NULL feature values. Default 'INNER' is safer — it drops entities that lack any selected feature, keeping the dataset clean.
  • Use return_query=True to review the generated DDL before running it — useful for performance review or when working with fs-analyze.
  • Use build_historical_dataset when the model needs how features evolved (sequences, survival, point-in-time training rows over many dates), not just their value at one instant. It is a view too; now is evaluated at query time, so a duration/delay window slides by itself. INNER keeps a period only where every feature has a value, FULL OUTER every period where at least one has — at any instant the history equals build_dataset(as_of=<instant>) with the same join type. FULL OUTER costs about twice the time of INNER. No grouped option for histories. Its window is in the catalog: tdfs4ds.get_dataset_window() (FS_V_FS_DATASET_WINDOW, LEFT OUTER JOIN on DATASET_ID; snapshots have no row) gives the definition and WINDOW_START/WINDOW_END, the bounds at query time (WINDOW_START NULL = from the earliest value).
  • A dataset can stay in the catalog after its view was dropped, or after an object it reads went away. In the admin app (Dataset builder › Existing datasets) a failed sample or Check all marks it Defective with the cause; Remove from catalog (admin view) previews then removes it. In Python: tdfs4ds.remove_dataset(name) — drop_dataset(dataset_id) removes only the catalog rows and leaves its documentation and index entries behind. Since 0.3.3.27 remove_dataset(..., dry_run=False) refuses when process views read the dataset (DependentObjectsError, dependents in removal order); tdfs4ds.write_removal_script(tdfs4ds.plan_removal(['<db>.<dataset>'])) removes them in order.
  • For hyper-segmented ML: to make a partition column (e.g. region) available in the dataset, it must have been registered as a feature at upload time — SELECTed in the view SQL and listed in feature_names in upload_features(). Once registered, include it in get_feature_versions() like any other feature; no manual join to the source table is needed.