Datasets — `build_dataset`¶

A dataset is a denormalised Teradata view that joins selected feature versions for a given entity. It is always a view, never a materialised table.

build_dataset returns a snapshot — each entity's values at one valid instant. For how the values evolved over a window (one row per entity per period of constant values), see Historical datasets.

Choose the features¶

selected_features = ['nb_transactions', 'total_amount', 'avg_amount']

When each feature is computed by one process, the names are enough: build_dataset finds each feature's version, with one catalog query for all of them.

A feature computed by several processes has several versions, and you say which one to read. Write it only for that feature, None for the others:

selected_features = {'total_amount': '<process uuid>', 'nb_transactions': None, 'avg_amount': None}

Named without a version, such a feature raises tdfs4ds.FeatureSelectionError (a ValueError). The message lists its processes and gives the dict to paste, for one version or for all of them side by side. A name not registered for the entity in the current data domain raises the same error. get_feature_versions still lists the versions of a set of features.

Several versions of a feature, and column names (aliases)¶

A feature computed by several processes has several versions (its FEATURE_VERSION is the producing process). A dataset can put them side by side, one column each, and any version can be given its own column name:

Value in `selected_features`	Columns
`'<pid>'`	the feature's name
`{'process_id': '<pid>', 'alias': 'spend'}`	`spend`
`[{'process_id': p1, 'process_view_name': 'FEAT_V1'}, {'process_id': p2, 'process_view_name': 'FEAT_V2'}]`	`amount_FEAT_V1`, `amount_FEAT_V2`
`[{'process_id': p1, 'alias': 'amount_old'}, {'process_id': p2, 'alias': 'amount_new'}]`	`amount_old`, `amount_new`

tdfs4ds.build_dataset(
    entity_id=['CUSTOMER_ID'],
    selected_features={'amount': [{'process_id': pid_v1, 'alias': 'amount_old'},
                                  {'process_id': pid_v2, 'alias': 'amount_new'}],
                       'nb_transactions': pid_tx},
    view_name='DS_COMPARE_VERSIONS')
# CUSTOMER_ID, amount_old, amount_new, nb_transactions

Without alias, nothing changes: a version keeps the generated name (the feature's name, or <feature>_<process view> when several versions are side by side).
Rules for an alias:
a letter or _, then letters, digits or _ (128 characters at most);
it may not repeat another column (two equal aliases, or an entity column) — refused, never renamed.
With lags and leads, the offset suffix follows the alias: amount_new_LAG_1D.
The name is kept everywhere: the alias is the column name in the view and in the dataset catalog's feature list. rebuild_dataset, restore_dataset_catalog and a cross-store migration keep it.
Documentation: an aliased column gets its own version's description, with "amount, version computed by FEAT_V2 —" in front.

Build¶

dataset = tdfs4ds.build_dataset(
    entity_id         = ['CUSTOMER_ID'],
    selected_features = selected_features,
    view_name         = 'DS_CUSTOMER_SPEND',
    comment           = 'Customer spending features',
)

dataset.shape

Query strategy — `grouped` parameter¶

By default build_dataset emits one sub-query and one JOIN per feature:

SELECT A1.CUSTOMER_ID, A1.FEATURE_VALUE AS nb_transactions,
       A2.FEATURE_VALUE AS total_amount, ...
FROM (SEQUENCED VALIDTIME SELECT ... WHERE FEATURE_ID=1) A1
JOIN (SEQUENCED VALIDTIME SELECT ... WHERE FEATURE_ID=2) A2
  ON A1.CUSTOMER_ID = A2.CUSTOMER_ID
...

Pass grouped=True to activate the pivot strategy instead — features that share the same source table and process ID are collapsed into a single MAX(CASE WHEN FEATURE_ID=… THEN FEATURE_VALUE END) … GROUP BY sub-query:

dataset = tdfs4ds.build_dataset(
    entity_id         = ['CUSTOMER_ID'],
    selected_features = selected_features,
    view_name         = 'DS_CUSTOMER_SPEND',
    comment           = 'Customer spending features — grouped strategy',
    grouped           = True,
)

The generated SQL looks like:

SELECT A1.CUSTOMER_ID, A1.nb_transactions, A1.total_amount, ...
FROM (
    SEQUENCED VALIDTIME
    SELECT B1.CUSTOMER_ID
      ,MAX(CASE WHEN FEATURE_ID=1 THEN FEATURE_VALUE END) AS nb_transactions
      ,MAX(CASE WHEN FEATURE_ID=2 THEN FEATURE_VALUE END) AS total_amount
    FROM <storage table> B1
    WHERE FEATURE_VERSION='...' AND FEATURE_ID IN (1, 2)
    GROUP BY B1.CUSTOMER_ID
    HAVING COUNT(CASE WHEN FEATURE_ID=1 THEN 1 END) > 0
       AND COUNT(CASE WHEN FEATURE_ID=2 THEN 1 END) > 0
) A1
-- one such sub-query per (storage table, process version), joined on the entity

The HAVING keeps an entity only when it carries a row for every feature of the group: the same rows as the one-JOIN-per-feature form, including an entity whose stored value is NULL. Before 0.3.1.216 it tested MAX(…) IS NOT NULL, which dropped such an entity.

Single-feature groups are unaffected

When a group contains only one feature, build_dataset emits a plain FEATURE_ID = N scan with no GROUP BY — no performance penalty.

When to use grouped=True

The grouped form shortens the SQL Teradata has to parse and optimise each time the view is queried. That cost is paid on every query and grows with the number of features. Measured on Vantage 20 with a 10-feature dataset over 3 (table, version) groups:

	multi-join	grouped
view SQL	5,842 chars	2,972 chars
JOINs in the plan (full read)	9	2
`EXPLAIN` (parse + optimise)	627 ms	190 ms
`SELECT COUNT(*)`	531 ms	245 ms
single-entity lookup	~455 ms	~248 ms

Both return exactly the same rows. The more features share a storage table and process, the larger the gain.

Inspect the generated SQL¶

Use return_query=True to retrieve the DDL without executing it or touching the dataset catalog:

sql = tdfs4ds.build_dataset(
    entity_id         = ['CUSTOMER_ID'],
    selected_features = selected_features,
    view_name         = 'DS_CUSTOMER_SPEND',
    return_query      = True,
)
print(sql)

Point in time — current, a fixed date, or a TimeManager¶

A dataset reads temporal feature tables, so it reads them at some valid time. There are three modes; all work with both grouped strategies.

Mode	How to ask	Sub-query temporal qualifier	The view shows
Current (default)	nothing	`SEQUENCED VALIDTIME` (outer `CURRENT VALIDTIME`)	today's values
Fixed date	`as_of='2024-03-31'`	`VALIDTIME AS OF TIMESTAMP '2024-03-31 00:00:00'`	values as of that instant, forever
TimeManager	`time_manager=tm`	`NONSEQUENCED VALIDTIME` + `B1.ValidStart <= (SELECT … BUSINESS_DATE …) AND B1.ValidEnd > (…)`	values at the manager's current business date

A fixed date¶

dataset_q1 = tdfs4ds.build_dataset(
    entity_id         = ['CUSTOMER_ID'],
    selected_features = selected_features,
    view_name         = 'DS_CUSTOMER_SPEND_2024Q1',
    as_of             = '2024-03-31 23:59:59',
)

as_of takes an ISO date or timestamp, or a datetime, date or pandas.Timestamp, and normalises it for you:

You pass	The view reads
`'2024-03-31'`	`TIMESTAMP '2024-03-31 00:00:00'`
`'2024-03-31 23:59'`	`TIMESTAMP '2024-03-31 23:59:00'`
`'2024-03-31T23:59:59Z'`	`TIMESTAMP '2024-03-31 23:59:59+00:00'`
`'2024-03-31 23:59:59+0200'`	`TIMESTAMP '2024-03-31 23:59:59+02:00'`
`datetime.date(2024, 3, 31)`	`TIMESTAMP '2024-03-31 00:00:00'`

A bare date becomes midnight because Teradata rejects a date-only TIMESTAMP literal. Without an offset the value is read in the session time zone, like FEATURE_STORE_TIME at ingestion. Ambiguous input such as '03/31/2024' is refused rather than guessed.

The historical form still works: feature_store_time=True reads tdfs4ds.FEATURE_STORE_TIME, which is now normalised the same way.

Following a TimeManager¶

from tdfs4ds.utils.time_management import TimeManager

tm = TimeManager(table_name='TM_MONTH_END', schema_name=tdfs4ds.SCHEMA)

ds = tdfs4ds.build_dataset(
    entity_id         = ['CUSTOMER_ID'],
    selected_features = selected_features,
    view_name         = 'DS_CUSTOMER_SPEND_ROLLING',
    time_manager      = tm,             # or 'MY_DB.TM_MONTH_END'
)

tm.update(12)    # the dataset now shows the values at step 12 — no rebuild

The view reads the manager's public view at query time, so a roll_out that moves the manager moves the dataset too. This makes it the natural input for a back-test that scores each historical step.

as_of, time_manager and feature_store_time=True are mutually exclusive.

Lags and leads¶

The same feature can be read at other instants than the dataset's: feature_offsets={'amount': ['0', '-1 day', '-7 days'], 'churn': ['+1 month']} gives amount, amount_LAG_1D, amount_LAG_7D and churn_LEAD_1MO (a forecast target, empty until its date is reached). See Lags and leads.

Build a dataset in the admin app — Explore › Dataset builder¶

Use this panel to build a dataset without writing code, in the user view as well as the admin view.

Entity: the entities that features can be combined on, grouped by data domain. Each one shows its key columns and the datasets that already exist on it; hover a dataset to see its features.
Features: the features of that entity.
Hovering a feature shows its definition: description, the process that computes it, its last run and where the description comes from.
A feature computed by several processes shows one checkbox per process (its versions); by default the one that ran last. Ticking several puts them side by side, one column each.
Column name: each chosen version has a field to give its column a name of its own (an alias); empty keeps the default name. The resulting column names are shown as you type, and an invalid or repeated name is shown in red and blocks the creation.
Lags / leads: see Lags and leads.
When the selection matches the features of an existing dataset exactly, the panel says so.
Dataset: the view name and the kind of dataset.
Name: it is checked as you type. Create dataset stays disabled while the name is taken, either by a registered dataset or by any table or view of that name. The server checks again before creating, so an existing object is never replaced.
Snapshot: one row per entity, read at a valid time: current, as of a date, or following a TimeManager (the TimeManagers attached to processes are listed).
Historical: one row per entity and period of constant values, over a window: start / end / duration / delay, or the full history (see Historical datasets).
Join: inner or full outer.
Documentation: optional, and only when an instruct model is configured.

Existing datasets lists the registered datasets with their kind, number of features and entity. It shows the selected entity's datasets by default; untick the box to see them all.

Sample 20 rows reads 20 rows drawn at random (SAMPLE 20): - on an existing dataset, from its row in that list; - on the dataset you are defining, before it is created: its query runs as is and nothing is created.

Show SQL displays the view's SQL without creating it. Create dataset goes through the same path as datasets created from the chat: it creates the view in the view database, registers it in the dataset catalog and records its lineage. When the database refuses (for example, no right to create a view there), its message is shown whole.

Show Python gives the code that creates the same dataset from a notebook or a script, with a line on what each argument does. It is the call Create dataset makes, tdfs4ds.build_dataset or tdfs4ds.build_historical_dataset with the same arguments, preceded by the connection and the data domain. The host and user are filled in; the password is never written, the code asks for it with getpass. Use it to rebuild a dataset in a scheduled job, or to learn the API from a dataset you designed in the app.

Rebuild a dataset — grouped, or at another date¶

The DDL of an existing dataset view fully describes it: storage tables, feature ids, process versions and column names. rebuild_dataset reads it and writes a new view from it. Features are not re-resolved against the catalog, so the result does not depend on your DATA_DOMAIN or on which versions are registered today.

# The grouped version of an existing dataset, as a new view <name>_GROUPED
tdfs4ds.build_grouped_dataset('DS_CUSTOMER_SPEND')

# ... or converted in place
tdfs4ds.build_grouped_dataset('DS_CUSTOMER_SPEND',
                              target_view_name='DS_CUSTOMER_SPEND', replace=True)

# The same dataset frozen at a date, or following a TimeManager
tdfs4ds.rebuild_dataset('DS_CUSTOMER_SPEND', target_view_name='DS_CUSTOMER_SPEND_2023',
                        as_of='2023-12-31')
tdfs4ds.rebuild_dataset('DS_CUSTOMER_SPEND', target_view_name='DS_CUSTOMER_SPEND_BT',
                        time_manager=tm, grouped=True)

In the admin app, Maintenance → Datasets — grouped version does the same. Pick a dataset (its current strategy and time mode are shown), choose convert in place or a target name (default <dataset>_GROUPED), then Preview SQL or Convert to grouped. A refusal, such as an existing target without overwrite, is shown with its reason.

Anything not passed keeps the source's value (strategy, join type, temporal mode). A target that already exists is refused unless replace=True. The rebuilt dataset is registered; when it is a new view, its build record (SOURCE_DATASET_ID) points to the dataset it came from.

The dataset catalog¶

tdfs4ds.dataset_catalog()          # one row per dataset; DATASET_VALIDTIME = governing qualifier
tdfs4ds.get_dataset_entity()       # entity per dataset
tdfs4ds.get_dataset_features()     # feature versions per dataset
tdfs4ds.get_dataset_build()        # how each dataset was built (below)
tdfs4ds.get_dataset_window()       # window of each historical dataset

get_dataset_build() reads FS_V_<catalog>_BUILD, a table added in 0.3.1.216:

Column	Content
`BUILD_STRATEGY`	`MULTIJOIN` or `GROUPED`
`JOIN_TYPE`	`INNER` or `FULL OUTER`
`TIME_MODE`	`CURRENT`, `AS_OF`, `TIME_MANAGER` or `HISTORY`
`AS_OF_TIMESTAMP`	the literal, for `AS_OF`
`TIME_MANAGER_DATABASE` / `_VIEW` / `_TABLE`	the manager, for `TIME_MANAGER`
`SOURCE_DATASET_ID`	the dataset it was rebuilt from, if any

It is a separate table so that existing catalog tables need no ALTER. Older tdfs4ds versions keep reading the catalog unchanged. It is created the next time the catalog is opened (setup(), connect(), any dataset call). A user without CREATE TABLE rights gets a warning instead of an error, and datasets keep working. A dataset registered earlier has no build row until it is built again (build_dataset or rebuild_dataset) or the catalog is restored.

The window of a historical dataset has its own table, FS_V_<catalog>_WINDOW, read by get_dataset_window(); see Historical datasets.

Restore a damaged dataset catalog¶

Earlier versions wrote parts of the catalog wrongly:

DATASET_VALIDTIME held SELECT (or SELECT A1.<column>) instead of the temporal qualifier;
FEATURE_ID was stored space-padded (' 6');
before 0.3.1.79, a view could be registered with no feature at all;
a view registered twice blocks every later update.

restore_dataset_catalog lists every dataset, compares what the catalog says with what each view actually does, and repairs the difference. It changes nothing unless you pass dry_run=False:

report = tdfs4ds.restore_dataset_catalog()           # dry run: list + diagnosis
report[['DATASET_NAME', 'STATUS', 'ISSUES', 'ACTION']]

tdfs4ds.restore_dataset_catalog(dry_run=False)       # apply

`STATUS`	Meaning	Applied action
`OK`	the catalog matches the view	none
`REPAIR`	the catalog disagrees with the view	rows rewritten, `DATASET_ID` kept
`REGISTER`	a dataset view the catalog does not know	registered
`DUPLICATE`	the view is registered several times	one id kept, the others closed
`ORPHAN`	the catalog points to a view that cannot be read — dropped, or no `SELECT` right on it	closed only with `drop_orphans=True`
`UNPARSEABLE`	a catalogued view not built by `build_dataset`	left alone

In the admin app the same runs from Maintenance → Dataset catalog. Check is the dry run, and Restore applies it after a confirmation. You can also close unreadable entries and restrict the run to a list of datasets; progress streams into the live log.

Pass datasets=['DS_A', 'MYDB.DS_B'] to restrict it, discover=False to check catalogued datasets only, or databases=[...] to choose where to look for unregistered views. Repairs are temporal updates: the previous rows stay in the table's history.

Augment a source table with features¶

augment_source_with_features creates a view that keeps every row of an existing source table or view and adds the selected features as columns. The view is not registered in the dataset catalog, so it suits ad-hoc model exploration:

augmented = tdfs4ds.augment_source_with_features(
    source_schema      = 'MY_DB',
    source_name        = 'CUSTOMER_EVENTS',     # must carry the entity columns
    entity_id          = ['CUSTOMER_ID'],
    feature_selection  = selected_features,
    output_view_schema = 'MY_DB',
    output_view_name   = 'CUSTOMER_EVENTS_WITH_FEATURES',
    join_type          = 'LEFT JOIN',           # or 'INNER JOIN'
)

It always reads current values (CURRENT VALIDTIME).

Re-attach¶

# In any later session — no rebuild. Datasets live in the view database.
ds = tdml.DataFrame(tdml.in_schema(tdfs4ds.view_schema(), 'DS_CUSTOMER_SPEND'))

Datasets — build_dataset¶