Skip to content

The tdfs4ds Enterprise Feature Store — Architecture

This document explains how tdfs4ds implements an Enterprise Feature Store (EFS) inside Teradata Vantage, and why it is built that way. It starts with the big picture and then goes into detail. It is split into two independent parts:

  • Part I: Core architecture. Storage, catalogs, processes, datasets, time, and operations. This is the feature store itself. It needs no LLM.
  • Part II: Agentic architecture. Documentation, the business dictionary, persisted lineage, the vector index, the consumer agent and the query optimizer. These are optional layers on top of Part I. They read what the core records, and a failure in them never affects the core.

0. The one-paragraph summary

A feature store answers one question: what was the value of feature F for entity E, as known at time T, and how was it computed? tdfs4ds answers it entirely inside the database. Feature logic is stored as a SQL view (a process). Running a process computes values and merges them into deep, temporal, typed tables: one row per (entity, feature, version), with a valid-time period. The layout is one table per (data domain, entity key, feature type). Metadata catalogs, also temporal, record every feature, process, dataset and run. A dataset is a view that pivots selected features back into a wide shape. Nothing is copied: a model reads the dataset view, and the view reads the feature tables.

   Source tables / views (the customer's data, untouched)
            │
            │  feature logic written once, as SQL
            ▼
   ┌───────────────────────┐        ┌──────────────────────────────┐
   │ PROCESS VIEW          │──────▶ │ PROCESS CATALOG  (what/how)  │
   │ (denormalized, wide)  │        │ DATA DISTRIBUTION (PI/part.) │
   └──────────┬────────────┘        │ FILTER / TIME MANAGER links  │
              │ run(process_id)     └──────────────────────────────┘
              ▼
   ┌───────────────────────┐        ┌──────────────────────────────┐
   │ VOLATILE staging      │        │ FOLLOW-UP LOG  (when/status) │
   │ dedup + NULL-key guard│──────▶ └──────────────────────────────┘
   └──────────┬────────────┘
              │ one MERGE per feature
              ▼
   ┌───────────────────────┐        ┌──────────────────────────────┐
   │ FEATURE TABLES FS_T_* │◀──────▶│ FEATURE CATALOG (name → id,  │
   │ deep, temporal, typed │        │ table, entity, domain)       │
   └──────────┬────────────┘        └──────────────────────────────┘
              │ pivot-by-view (zero copy)
              ▼
   ┌───────────────────────┐        ┌──────────────────────────────┐
   │ DATASET VIEW          │──────▶ │ DATASET CATALOG (+ entity,   │
   │ (wide, model-ready)   │        │ + features)                  │
   └───────────────────────┘        └──────────────────────────────┘

Part I — Core architecture

1. Scope: the entity-feature paradigm

tdfs4ds covers one data paradigm, and knowing its limits is the first design decision to understand.

A feature is a typed scalar value attached to an entity at a point in valid time, produced by a process.

  • Entity. The functional key of what is described: a customer, a contract, a (provider, month) pair, a claim. It is a set of one or more typed columns. You name the columns; tdfs4ds reads their SQL types from the source:
entity_id = ['CUSTOMER_ID', 'EVENT_DATE']

The entity is the certainty boundary of the whole system. Because the key is explicit, the relationship between a feature and what it describes is known, not inferred. A column that has no entity is not a feature, even if it sits in a documented table. - Feature. One value per entity, per valid time. A feature's identity is the triplet (DATA_DOMAIN, ENTITY, FEATURE_NAME). The same name under two entities is two different features. - Process. The SQL that computes a set of features for one entity. A process is the unit of lineage, execution, versioning and documentation.

What is out of scope, by design:

Not handled Why What to do instead
Raw events, logs, transactions No entity-level aggregation yet Aggregate them in the process view (the view is the feature logic)
Values without a key Nothing to join on, so no dataset Give them an entity (possibly a constant or a composite key)
CLOB / BLOB / JSON values Not scalar; rejected at ingestion (validate_feature_types) Extract scalar attributes in the view
Vectors, images, embeddings Not scalar typed values Keep them in dedicated stores
Multi-row outputs per entity (lists, sequences) One value per (entity, feature, time) Make the position part of the entity key, or aggregate

Time series fit the paradigm in two ways. A time grain can be a component of the entity ({'ACCOUNT_ID':…, 'MONTH':…}), which gives one value per period. Otherwise, time is carried by valid time (the feature's value as of a date, see §6). The choice is a modelling decision. The first gives a panel, the second gives an as-of history.

The restriction is deliberate. Every component that follows (deep tables, typed tables, the PI on the entity, generic MERGE, generic dataset pivot, catalog-driven lineage and documentation) is only possible because every value has the same shape: key + feature id + typed value + version + period.

2. Architectural principles

  1. Distance zero. Data never leaves the database. Features are computed by SQL in Vantage, stored in Vantage, and served by views in Vantage. Nothing is exported for the feature store to work.
  2. Logic is data. Feature logic is a persisted view, registered in a catalog with a UUID. It can be re-executed (run(process_id)), inspected (SHOW VIEW), traced (lineage) and documented.
  3. Everything is temporal. Feature values, the feature catalog, the process catalog, the dataset catalog, the business dictionary and the lineage graph are all VALIDTIME tables. "What did we know on date T" is a query, not a restore.
  4. Zero-copy consumption. Datasets are views. Building one moves no data.
  5. Generic, catalog-driven code. One uniform storage shape means one MERGE generator, one dataset builder and one stats routine for every feature ever registered.
  6. Additive and opt-in. Every advanced feature (FilterManager, TimeManager, OFS storage, table/view database split, cross-store federation, AI features) defaults to off and produces byte-identical SQL when unused.

3. The storage model

3.1 Deep tables rather than wide tables

A wide table has one row per entity and one column per feature. A deep (long) table has one row per (entity, feature). tdfs4ds stores features deep:

CREATE MULTISET TABLE <SCHEMA>.FS_T_<hash>, FALLBACK ...
(
    CUSTOMER_ID      BIGINT,          -- entity key column(s), native types
    FEATURE_ID       BIGINT,          -- which feature
    FEATURE_VALUE    FLOAT,           -- typed value (one type per table)
    FEATURE_VERSION  VARCHAR(255) NOT NULL,   -- = PROCESS_ID that produced it
    ValidStart       TIMESTAMP(0) WITH TIME ZONE NOT NULL,
    ValidEnd         TIMESTAMP(0) WITH TIME ZONE NOT NULL,
    PERIOD FOR ValidPeriod (ValidStart, ValidEnd) AS VALIDTIME
)
PRIMARY INDEX (CUSTOMER_ID)
PARTITION BY ( RANGE_N(FEATURE_ID BETWEEN 0 AND 20000 EACH 1) [, user levels] );

Why deep:

Concern Wide table Deep table (tdfs4ds)
Adding a feature ALTER TABLE ADD COLUMN: DDL, a table-level lock on a shared table, coordination between teams Insert rows. No DDL. The feature catalog assigns a FEATURE_ID
Temporal history One row per entity: a change to one feature closes and re-opens the period of all columns, and history grows with every feature's churn Each value has its own valid-time period. Refreshing one feature does not version the others
Independent refresh cadences All features share one row, so they must be refreshed together or rows get partially stale Each process refreshes its own rows, at its own cadence
Multiple versions of a feature Needs a new column per version (score_v1, score_v2…) FEATURE_VERSION column: versions coexist in the same table, and datasets pin one
Sparse features NULL cells for every entity lacking the feature No row, no storage
Concurrency Every writer touches the same rows Writers touch disjoint (FEATURE_ID) partitions
Generic code Column list differs per table, so code must be generated per table Identical schema: one MERGE / one pivot / one stats routine for everything
Governance Column metadata spread over DDL A feature is a catalog row, with lineage and documentation keyed on it

The cost of deep, and how it is paid:

  • Reads must pivot back to wide. A model wants one row per entity. The dataset view does this pivot (§8). Two properties make it cheap. (1) Every feature table of a given entity has the same primary index (the entity key), so the joins are AMP-local with no row redistribution. (2) Every sub-query filters on one FEATURE_ID, which is its own partition, so it reads only that partition (static partition elimination). A "grouped" mode (grouped=True) pivots all features of one table in a single MAX(CASE WHEN FEATURE_ID=…) scan instead of one join per feature.
  • Key and period columns are repeated per value. This is the accepted storage overhead. It is what buys per-value history and schema-free growth.

3.2 One table per (data domain, entity, feature type)

A feature value must have a SQL type. A deep table has only one FEATURE_VALUE column, so each table holds one type. The other components of the table's identity follow from the same logic:

table identity = DATA_DOMAIN + PRIMARY INDEX (entity keys) + partitioning keys
               + other entity keys + FEATURE TYPE
table name     = 'FS_T_' + uuid5(sha256(that string))      (view: 'FS_V_' + same)

(get_feature_store_table_name, feature_query_retrieval.py)

Why typed tables instead of one table with VARCHAR values:

  • Fidelity. A FLOAT stays a FLOAT, a DECIMAL(38,19) keeps its precision, and a TIMESTAMP keeps its semantics. Casting everything to text loses precision, breaks ordering and comparison ('10' < '9'), and pushes a cast into every consumer query.
  • Storage. Native numeric types are smaller than their text form.
  • The type is part of the contract. The dataset view exposes the true type, so the model and BI tools see the right column types.

The supported value types are FLOAT, BIGINT, DECIMAL, VARCHAR (LATIN or UNICODE), TIMESTAMP(0) with and without time zone, and PERIOD(TIMESTAMP). The exact SQL type of the source column is preserved, including length and character set.

Why one table per entity:

  • The entity key keeps its native columns and types. There is no concatenated string key. A dataset can join feature values back to source tables on real columns, with no cast.
  • Co-location. The primary index is the entity key, so all features of an entity hash to the same AMP. Joins between feature tables, and between a dataset and a source table keyed the same way, are AMP-local.
  • Shape follows meaning. An entity is a grain. Mixing grains in one table would force a nullable, heterogeneous key.

Why the data domain is in the identity: a DATA_DOMAIN is a project namespace. Putting it in the table identity isolates projects physically: one project's MERGE never locks another project's table, and a domain can be purged without touching the others.

Why a hashed name: the identity string can exceed Teradata's 128-character object-name limit and may contain arbitrary column names. A UUID derived from it is fixed-length and deterministic. The same entity + type + domain always gives the same table name, in any environment, which makes promotion between stores predictable. The readable metadata lives in the feature catalog (FEATURE_TABLE, FEATURE_DATABASE, ENTITY_NAME, DATA_DOMAIN) and in the table COMMENT.

Controlling table proliferation. Every distinct type string is a distinct table, so VARCHAR(17) and VARCHAR(23) would create two tables. force_varchar_length=N (a process setting, default 1024) rounds VARCHAR lengths up to a multiple of N, one type per character set for the whole result set, which groups text features into at most two tables per process. A stored type is widened later with plan_type_change.

A consequence for parallel runs. A MERGE locks its target table. Two processes may run in parallel only if they write different tables. The parallel planner (analyze_parallel_plan) serializes processes whose target tables overlap. It uses the (domain, entity) pair as a conservative key when the exact table is not known yet.

3.3 Primary index and partitioning

Primary index = entity key, by default. This gives even distribution when the key has high cardinality, and AMP-local joins (§3.1). It can be overridden (primary_index=). This is useful when one component of a composite key has low cardinality (a date, a region) and would skew the rows. The choice is recorded per process in FS_DATA_DISTRIBUTION (FOR_PRIMARY_INDEX, FOR_DATA_PARTITIONING) so run() rebuilds exactly the same layout.

Partitioning: one partition per feature. generate_partitioning_clause always prepends

RANGE_N(FEATURE_ID BETWEEN 0 AND 20000 EACH 1)

(FEATURE_PARTITION_N / FEATURE_PARTITION_EACH, configurable), and places any user partitioning after it as additional levels, e.g. a date entity column: partitioning="RANGE_N(EVENT_DATE BETWEEN DATE '2020-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' MONTH)".

Why a feature-level partition:

  • Reads. Every dataset sub-query has WHERE FEATURE_ID = <id>. The optimizer eliminates all other partitions statically, so reading one feature costs about the same as reading a table holding only that feature. This is what makes the deep layout competitive with a wide one.
  • Writes. Each ingestion MERGE targets one FEATURE_ID (§5.3), so it touches one partition. Different processes writing different features to the same table work on disjoint partitions.
  • Maintenance. Deleting or re-computing one feature is partition-scoped.
  • Second-level partitioning (e.g. by month) adds row-level elimination for time-sliced datasets and segmented ingestion.

3.4 Statistics collection

Deep, partitioned tables are hard for the optimizer to estimate without statistics. A dataset view is a chain of N joins on N filtered sub-queries. If the row count behind FEATURE_ID = x is wrong, the join order and join methods are wrong, and the error grows with every join. tdfs4ds therefore collects statistics as part of ingestion (apply_collect_stats, feature_data_processing.py):

  1. First, COLLECT STATS ON <table>. This refreshes whatever statistics already exist. It is cheap and respects any statistics a DBA added.
  2. If that fails (for example no statistics exist yet), explicit statements are generated:
COLLECT STATISTICS COLUMN(PARTITION), COLUMN(FEATURE_ID), COLUMN(<partition cols>),
                   COLUMN(FEATURE_VERSION), COLUMN(ValidStart), COLUMN(ValidEnd)
ON <table>;

COLLECT STATISTICS USING SAMPLE 25 PERCENT AND THRESHOLD 15 PERCENT
                   COLUMN(<primary index>), COLUMN(<entity keys>)
ON <table>;

The column choice follows the query shapes:

Statistic Serves
PARTITION, FEATURE_ID Row estimates for the FEATURE_ID = x filter, and partition elimination
FEATURE_VERSION The version pin in every dataset sub-query
ValidStart, ValidEnd Temporal predicates (CURRENT VALIDTIME, AS OF)
PI / entity keys (sampled, with threshold) Join cardinality between feature tables and with source tables

The sample and threshold keep the expensive multi-column statistics cheap on very large tables. They are skipped when less than 15 % of the data has changed.

When: once per run(), after all batches, not once per batch. A FilterManager run may ingest 100 segments. Collecting after each one would multiply the cost by 100 for no benefit, because only the final state matters to the optimizer. The routine never fails ingestion: it logs a per-table summary (ok / retried / failed).

3.5 The NULL trick in entity keys

The problem. Entity keys are matched with plain equality, both in the ingestion MERGE ... ON NEW.k = EXISTING.k and in dataset joins. In SQL, NULL = NULL is unknown, not true. So a row whose key contains a NULL:

  • never matches its previous version in the MERGE. Every run inserts it again instead of updating it, so values accumulate and "one value per entity per time" breaks.
  • never joins in a dataset view, so its features silently disappear.
  • hashes all NULL keys to the same AMP, which creates skew.

With several NULL keys and several features, the MERGE behaves like a cartesian product: storage and runtime grow with every run.

NULLs in keys are common in real data. Typical cases are an optional sub-account in a composite key, or a "not applicable" category.

The solution: substitute, persist, enforce.

tdfs4ds.upload_features(df, entity_id=['CLIENT_ID', 'CLI_CD'],
                        feature_names=[...],
                        entity_null_substitute={'CLI_CD': '-'})
  1. Substitute at staging. When the volatile staging table is built, each listed key is written as COALESCE(<key>, <substitute>) AS <key>. The stored key is therefore never NULL, and equality works in the MERGE, in joins and in hashing. (The UPDATE_INSERT strategy also applies the COALESCE on both sides of its ON clause.)
  2. Persist with the process. The substitute map is stored in the process catalog (ENTITY_NULL_SUBSTITUTE JSON). run(process_id) re-applies exactly the same substitution on every later execution. If it did not, re-runs would store NULLs again and the MERGE would stop matching.
  3. Enforce. After staging, tdfs4ds counts NULLs in every key column. Any key that still has NULLs (because it was not listed in the substitute map) makes ingestion fail with a ValueError naming the columns and counts. The same guard rejects duplicate keys. A process that produces two values for one entity is invalid, and it fails loudly instead of storing ambiguity.

Choose a substitute outside the key's real domain ('-', -1, DATE '1900-01-01'). Consumers see the substituted value in datasets, not NULL. This is a known, documented value rather than a silent loss of rows.

3.6 Catalog tables and public views

Each catalog is a physical temporal table plus a public view defined as LOCK ROW FOR ACCESS SELECT … FROM <table>. The view reads with an access lock, so catalog reads (the agent, dashboards, dataset resolution) never block, and are never blocked by, a writer. Feature tables are paired the same way with an FS_V_<hash> view that joins the catalog to show FEATURE_NAME next to FEATURE_ID.

With the optional table/view database split (database_view=), every table stays in SCHEMA and every view is created in SCHEMA_VIEW. This lets an organisation grant consumers access to views only.

4. Data and metadata managed by the EFS

All objects below are created by tdfs4ds.setup() (or self-healed by connect(create_if_missing=True)). "T" means a VALIDTIME temporal table.

4.1 Data

Object T Grain Content
FS_T_<hash> feature tables ✓ entity × feature × version × period Feature values. PI = entity, partitioned by FEATURE_ID
FS_V_<hash> feature views same Feature values + FEATURE_NAME (joined from the catalog)
Process views (user-named) as defined The feature logic: a denormalized SQL view, one column per feature
Dataset views (user-named) entity Wide, model-ready pivot of selected feature versions. Zero copy
FilterManager tables/views segment The partition values that segment a process run (*_HIDDEN table + a view showing the active segment)
TimeManager tables/views time step The business-date timeline used to replay history (roll_out)
Volatile staging tables entity Session-scoped; exist only during ingestion

4.2 Core metadata

Object T Key What it records
FS_FEATURE_CATALOG / FS_V_FEATURE_CATALOG ✓ FEATURE_ID (+ NUSI on FEATURE_NAME) Every feature: name, SQL type, physical table + database, value view, entity, data domain
FS_PROCESS_CATALOG / FS_V_PROCESS_CATALOG ✓ PROCESS_ID (UUID) Every process: type (denormalized view, uaf art view, …), view name, entity + types, ENTITY_NULL_SUBSTITUTE, feature names, version, domain, METADATA JSON
FS_V_PROCESS_CATALOG_FEATURE_SPLIT process × feature One row per feature produced by a process
FS_DATA_DISTRIBUTION PROCESS_ID The PI and partitioning each process's features are stored with
FS_FILTER_MANAGER ✓ PROCESS_ID Which FilterManager a process iterates on
FS_TIME_MANAGER ✓ PROCESS_ID Which TimeManager last rolled a process out (filled by roll_out)
FS_FOLLOW_UP (RUN_ID, PROCESS_ID) The execution log (§7)
FS_FS_DATASET_CATALOG / _ENTITY / _FEATURES (+ FS_V_ views) ✓ DATASET_ID Every dataset: name, database, valid time, its grain columns, its exposed features with type and source

(The doubled FS_FS_ prefix of the dataset catalog is a naming convention, not a typo: DATASET_CATALOG_NAME='FS_DATASET' and the catalog class adds FS_.)

4.3 Knowledge metadata (used by Part II, stored like the rest)

Object T Key What it records
FS_BUSINESS_DICTIONARY_OBJECTS ✓ (DATABASE_NAME, OBJECT_NAME) Level 0: one business description per table (T), view (V) or dataset (D)
FS_BUSINESS_DICTIONARY_SECTIONS ✓ (…, SECTION_NAME) Level 1: structured sections (OVERVIEW, ENTITY, FEATURE_THEMES, BUSINESS_QUESTIONS, INTENDED_AUDIENCE)
FS_BUSINESS_DICTIONARY_COLUMNS ✓ (DATABASE_NAME, TABLE_NAME, COLUMN_NAME) Level 2: one business description per column (hence per feature)
FS_PROCESS_DOCUMENTATION_BUSINESS_LOGIC / _FEATURES / _EXPLAIN ✓ PROCESS_ID Generated process documentation and EXPLAIN quality metrics
FS_LINEAGE_GRAPH / FS_V_LINEAGE_GRAPH ✓ (PARENT_DATABASE_NAME, PARENT_OBJECT_NAME) Persisted object-to-object dependency edges
FS_V_BUSINESS_DICTIONARY_* Permanent views that feed the Teradata vector store 
DATA_DOMAIN ─┬─ ENTITY ──┬── FEATURE (FEATURE_ID) ── stored in ── FS_T_<hash>
             │           │        │ FEATURE_VERSION = PROCESS_ID
             │           │        ▼
             │           └── PROCESS (PROCESS_ID) ── VIEW_NAME ── source objects
             │                    │                                   (lineage)
             │                    ├── FS_DATA_DISTRIBUTION (layout)
             │                    ├── FS_FILTER_MANAGER / FS_TIME_MANAGER
             │                    └── FS_FOLLOW_UP (runs, status, segment, time)
             │
             └─ DATASET (DATASET_ID) ── exposes FEATURE_ID@version ── grain = ENTITY

The process catalog is the keystone. A feature row points at its process through FEATURE_VERSION, the process points at its view, and the view's DDL points at the source objects. Lineage, freshness, re-execution and documentation all follow that chain.

5. Processes and ingestion

5.1 A process is a view

upload_features() takes one of: a teradataml DataFrame, a qualified 'schema.view', or a sql_query plus a target_view_name. It persists the feature logic as a permanent view and registers it with a UUID. The view must be denormalized: one row per entity, one column per feature. That is the natural shape of SQL feature engineering. The deep layout is an internal storage concern that users never write.

Keeping the logic as a view (rather than as Python code, or a copied result) gives:

  • Re-execution. run(process_id) recomputes from the current sources. roll_out(processes, time_manager) replays history.
  • Lineage. SHOW VIEW gives the exact dependencies.
  • Inspectability. Anyone with SQL access can read the logic, EXPLAIN it or document it.
  • Portability. Cross-store migration rewrites and re-registers views.

A special process type, uaf art view, supports Teradata UAF features whose value lives in a secondary Analytical Result Table layer. The statements that build the ART are stored in the process METADATA and replayed before ingestion.

5.2 Feature identity and versions

  • FEATURE_ID is a global BIGINT assigned at registration. It never changes and is the partition key.
  • FEATURE_VERSION is the PROCESS_ID that produced the value. Two processes can publish a feature with the same name for the same entity, and both versions coexist in the same table. A dataset pins the version it reads (selected_features = {'feature': '<process uuid>'}), so a new process never silently changes an existing dataset.

5.3 The ingestion pipeline

run(process_id)
 ├─ read process catalog + data distribution (entity, substitutes, PI, partitioning)
 ├─ register_entity / register_features   (create FS_T_* tables if missing; assign FEATURE_IDs)
 ├─ for each FilterManager segment (or once):
 │    ├─ skip if FS_FOLLOW_UP says COMPLETED for this segment and valid time
 │    ├─ followup_open  → RUNNING
 │    ├─ prepare_feature_ingestion
 │    │    ├─ CREATE VOLATILE TABLE AS (SELECT COALESCE(keys…), features FROM view)
 │    │    │     PRIMARY INDEX (entity) — so the MERGE below is AMP-local
 │    │    ├─ duplicate-key check  → fail if any
 │    │    └─ NULL-key check       → fail if any unsubstituted
 │    ├─ store_feature: one MERGE per feature
 │    │     MERGE INTO FS_T_x USING (SELECT keys, <id> AS FEATURE_ID,
 │    │                                   <col> AS FEATURE_VALUE, '<pid>' AS FEATURE_VERSION
 │    │                            FROM volatile) …
 │    │     WHEN MATCHED THEN UPDATE SET FEATURE_VALUE
 │    │     WHEN NOT MATCHED THEN INSERT
 │    └─ followup_close → COMPLETED | 'COMPLETED,0 rows' | FAILED,<error>
 └─ apply_collect_stats (once)

Notes on the choices:

  • The unpivot happens in the MERGE, not in a separate step. Each MERGE projects one feature column as FEATURE_VALUE with a constant FEATURE_ID. It reads the (small, co-located) staging table and writes one partition.
  • The staging table is volatile. It is session-scoped, has no permanent space footprint, is dropped automatically, and is safe under concurrency.
  • MERGE is the default (STORE_FEATURE='MERGE'). It is a single atomic statement per feature. UPDATE_INSERT is available as an alternative.
  • Temporal semantics of the write. With FEATURE_STORE_TIME=None the statement runs under CURRENT VALIDTIME. For a matched row, Teradata closes the current period and opens a new one from now, so the old value remains readable as of any earlier date. With FEATURE_STORE_TIME='2024-01-01 00:00:00' it writes the period (FEATURE_STORE_TIME, END_PERIOD), which is how history is back-filled.

5.4 Segmentation (FilterManager) and time replay (TimeManager)

A FilterManager is a small table of segment values (for example one row per month or region) and a view that exposes the active segment. The process view filters on it. run() iterates update(i) → ingest. This bounds spool per step (a process too large for one pass runs in slices), makes a failure restartable at segment granularity, and records the segment in the log.

A TimeManager holds a timeline of business dates. roll_out sets FEATURE_STORE_TIME at each step and runs the processes, so the feature tables receive the history they would have had if the store had existed at the time.

Both accept storage='OFS' to place their backing table on Object File System storage (for processes scored on the Analytical Compute Cluster).

6. Time

  • Every feature value carries a valid-time period, the business time during which the value is true. This is not transaction time: the system does not record when a row was physically written.
  • Reads default to CURRENT VALIDTIME. build_dataset(..., as_of='<ts>') puts VALIDTIME AS OF TIMESTAMP '<ts>' in each feature sub-query. This gives a point-in-time-correct training set without leakage: each entity gets the values that were valid at that date. With time_manager= the dataset instead follows a TimeManager's current business date, so moving the manager moves the dataset. Setting tdfs4ds.FEATURE_STORE_TIME alone does not time-travel a dataset.
  • END_PERIOD='UNTIL_CHANGED' leaves periods open until a newer value closes them.
  • The catalogs are temporal too, so the definition of a feature, process or dataset can also be read as of a date.

7. The execution log (FS_FOLLOW_UP)

RUN_ID, PROCESS_TYPE, PROCESS_ID, START_DATETIME, END_DATETIME,
VALIDTIME_DATE,            -- the valid time the run wrote
APPLIED_FILTER JSON(2000), -- the FilterManager segment
STATUS VARCHAR(2048)       -- RUNNING | COMPLETED | COMPLETED,0 rows | FAILED,<error>
PRIMARY INDEX (RUN_ID, PROCESS_ID)

Why a dedicated log table, separate from the catalogs. The catalogs are declarative: they say what exists and how it is computed. The log is operational: it says what happened, when, for which segment and time, and with what outcome. It is append-only and is itself the history, so it is not a temporal table.

What it enables:

  • Idempotent, restartable runs. A segment already COMPLETED at the same VALIDTIME_DATE is skipped unless force_compute=True. A failed 100-segment run resumes where it stopped.
  • Freshness, per segment. "When was this feature last computed?" is a query on the log. A segmented process can be fresh for one segment and stale for another, and freshness is reported for the stalest one.
  • Honest failure reporting. A failure writes FAILED,<message>. An empty ingestion writes COMPLETED,0 rows. That status is deliberately different from COMPLETED, so the skip logic recomputes it next time and never treats hollow values as final. on_failure= (continue / stop / raise) controls whether a failure propagates to the caller.
  • Upstream safety. run(..., check_upstream=True) refuses to run a process if a process it depends on (from lineage) has FAILED at this valid time.
  • Audit and operations. The run timeline, status calendar and dashboard report are built from this table.

8. Datasets: zero-copy consumption

build_dataset(entity_id, selected_features, view_name) creates a view:

REPLACE VIEW <db>.<dataset> AS LOCK ROW FOR ACCESS
CURRENT VALIDTIME
SELECT A1.CUSTOMER_ID, A1.feat_a, A2.feat_b, …
FROM ( SEQUENCED VALIDTIME
       SELECT B1.CUSTOMER_ID, B1.FEATURE_VALUE AS feat_a
       FROM <db>.FS_T_x B1
       WHERE FEATURE_ID = 12 AND FEATURE_VERSION = '<pid>' ) A1
INNER JOIN ( … FEATURE_ID = 57 … ) A2 ON A1.CUSTOMER_ID = A2.CUSTOMER_ID
…
  • No copy. The data stays in the feature tables. A dataset is refreshed by refreshing the features, and its cost is a view definition.
  • Performance relies on §3: partition elimination per sub-query, co-located joins on the entity PI, statistics for the join plan. grouped=True merges the features that share a storage table and process version into one MAX(CASE …) pivot. The rows are the same, but the view's SQL is shorter, so it is cheaper to parse on every query. join_type='FULL OUTER' keeps entities missing some features.
  • Registration. The dataset catalog records the dataset, its grain (_ENTITY) and its features (_FEATURES). This split is taken from what the build knew, not re-derived from DDL. A build record (_BUILD) stores the strategy, join type and time mode (current, a date, or a TimeManager), and restore_dataset_catalog() can rebuild all three from the views themselves. retrieve_batch() serves key lookups efficiently: an IN list for small batches, a PI-aligned volatile table join for large ones.

9. Enterprise extensions (core, opt-in)

Extension What it does
Table/view split (database_view) Views in a separate database, e.g. to grant consumers view-only access
Read federation (databases_readonly) Reads other stores read-only. Catalog reads become a UNION ALL tagged with FEATURE_STORE. Writes stay on the primary
Migration (plan_migration / migrate_objects) Promotes processes/datasets from a lab store to production. Plan is read-only, execution requires approval, and dropping a feature a downstream consumer needs is blocked
Parallel planner (analyze_parallel_plan) Computes dependency waves from lineage and generates a process-pool execution script. Processes writing the same table are serialized
Quoted identifiers Database names with @, -, . (LDAP logins) are quoted correctly

Part II — Agentic architecture

Part II adds a knowledge layer and AI consumers on top of the core. The principles are:

  • Knowledge lives next to the data, in the same database, and is temporal. Documentation and lineage are rows in Vantage tables, not files or a separate service.
  • Structured, not blobs. Documentation is stored as rows at three levels (object, section, column). Retrieval is exact and needs no chunking.
  • Grounded in the catalogs. The LLM proposes, and the catalogs verify. Names that do not exist are dropped. Facts about features come from the catalogs, not from the model.
  • Deterministic where possible. Scores, routing guards, lineage and dashboard SQL are computed in code. The LLM formulates and explains.
  • Opt-in and non-fatal. Every AI step (document=, optimize_query=, vector_indexing=) defaults to off. A failure is a warning and never affects ingestion.
            ┌──────────────────── Part I (catalogs, views, log) ────────────────────┐
            │                                                                       │
 SHOW VIEW  ▼             documentation (LLM)            lineage (deterministic)    │
   ┌──────────────────┐   ┌──────────────────────┐   ┌────────────────────────┐    │
   │ process / dataset│──▶│ BUSINESS DICTIONARY  │   │ FS_LINEAGE_GRAPH       │◀───┘
   │ DDL + EXPLAIN    │   │ objects/sections/cols│   │ parent → child edges   │
   └──────────────────┘   └─────────┬────────────┘   └──────────┬─────────────┘
                                    │ embed                     │ networkx traversal
                                    ▼                           │
                          ┌──────────────────────┐              │
                          │ VECTOR INDEX         │              │
                          │ Chroma | Teradata EVS│              │
                          └─────────┬────────────┘              │
                                    ▼                           ▼
                 ┌──────────────────────────────────────────────────────────┐
                 │ CONSUMER AGENT (LangGraph)                               │
                 │ classify → detect domain → skill → synthesize            │
                 └──────────────────────────────────────────────────────────┘
                 ┌──────────────────────────────────────────────────────────┐
                 │ QUERY OPTIMIZER   simplify → candidates → gates → pick   │
                 └──────────────────────────────────────────────────────────┘

10. The business dictionary

Level Table Holds
0 FS_BUSINESS_DICTIONARY_OBJECTS One description per table, view or dataset
1 FS_BUSINESS_DICTIONARY_SECTIONS Named sections per object (dataset OVERVIEW, ENTITY, FEATURE_THEMES, BUSINESS_QUESTIONS, INTENDED_AUDIENCE)
2 FS_BUSINESS_DICTIONARY_COLUMNS One description per column

A feature's business description is a level-2 row on its process view (TABLE_NAME = VIEW_NAME, COLUMN_NAME = FEATURE_NAME). The documentation of a feature therefore follows the chain feature → process → view → column. It is the same keystone as lineage.

Rows can be supplied by users (upload_business_dictionary_*) or generated:

  • document_process_incremental(process_id) reads the view DDL and its EXPLAIN plan. It writes the business logic, one description per feature and a plan quality assessment (USER_SCORE, OPTIMIZATION_SCORE, N_STEPS, N_SPOOL_OBJECTS, the last two parsed deterministically from the plan).
  • document_dataset_incremental(dataset_id) walks the lineage bottom-up. It documents undocumented upstream views first, propagates feature descriptions from the processes (no LLM), then generates the dataset sections in one JSON-constrained call.
  • rebuild_documentation() walks the persisted lineage graph from its roots so that every object is documented exactly once.

11. Persisted lineage

FS_LINEAGE_GRAPH stores one row per directed edge between database objects (views, tables, datasets). It is built by parsing SHOW VIEW / SHOW TABLE DDL recursively (build_teradata_dependency_graph). A dataset is expanded through the process catalog to the process views it reads, not to the raw FS_T_* tables.

  • Written incrementally by upload_features. Writes are diff-based: an unchanged edge is not rewritten, so valid-time history records only real changes.
  • Read in one pass into a networkx graph for traversal: get_ancestors (upstream impact), get_descendants (downstream impact), check_downstream_processes (do consumers still compile after a change?).
  • Used by: the LINEAGE skill, cascade re-documentation, the parallel planner, migration closure computation and the upstream-failure guard.

Lineage is deterministic. It uses no LLM.

12. The vector index

The business dictionary is embedded for semantic search. There are two backends:

  • Chroma (default): a local or server index. It is a copy of the dictionary, rebuilt with rebuild_vector_indexing().
  • Teradata Enterprise Vector Store: three collections (objects, sections, columns) that read the permanent FS_V_BUSINESS_DICTIONARY_* views directly. The embeddings stay in the database. Optional in-database embedding is available (TERADATA_AI_API_TYPE, e.g. td_hosted).

Results are filtered by a distance threshold, and names returned by the index are checked against the catalogs before use.

13. The consumer agent

A conversational agent for business users, built as a LangGraph state graph:

classify ─▶ detect_domain ─▶ route ─▶ skill_* ─▶ synthesize ─▶ answer
   │            │                        │
   │            └ which DATA_DOMAIN owns the object (sticky across turns)
   └ intent + object + entity + confidence (structured output)

Skills (one per intent, each described by a SKILL.md):

Intent Answers
SEARCH Which features/datasets match a business need
DEFINITION What a feature, column or dataset means
EXPLAIN How a feature is computed (SQL formula → business language)
LINEAGE Where it comes from, and what depends on it
FRESHNESS Is it up to date (from FS_FOLLOW_UP, per segment)
SUMMARY / DATA_DOMAINS What exists in a domain or dataset
USAGE How to use it, with paste-ready SQL per dataset
DATASET / BUILD_DATASET Which datasets expose it / create one conversationally
FEASIBILITY Which existing assets cover a use case, and what is missing
ALGORITHM Which ML model is behind a feature (via tdstone2 metadata, MCP-gated)
DATA_QUERY Sample rows / aggregates (via a Teradata MCP server, optional)
HELP / RECAP / OUT_OF_SCOPE Concepts, conversation recap, refusal

Design choices:

  • The entity is the certainty boundary. For a feature, the agent states facts (entity, domain, process, lineage, freshness). For a non-feature column it states only its owning object and lineage position. Anything else is labelled as an interpretation.
  • Grounding. Every candidate name is intersected with the catalogs. Score direction and formula meaning are derived from the DDL and injected as authoritative context, with a deterministic post-check.
  • Most skills answer directly. DEFINITION, EXPLAIN, LINEAGE, FRESHNESS and others return their final answer. Only SEARCH uses a second LLM synthesis pass.
  • Memory is structured. Each conversation keeps an accumulated context graph (features, entities, processes, datasets and the links between them). It drives follow-up questions ("are these fresh?") and cache fast-paths, and it is rendered as a knowledge graph in the UI.
  • Efficient database access. Queries are batched with IN lists and cached per turn. The agent never loops one query per feature.
  • Extensible. Plugin skills are a folder with a SKILL.md and an optional skill.py.

14. The query optimizer

skill_optimize_query (or upload_features(optimize_query=True)) rewrites a process view to improve its plan:

  1. Simplify. Columns are qualified deterministically first, so a later rewrite cannot silently bind a column to another table.
  2. Generate candidate rewrites with different technique strategies.
  3. Gate each candidate. It must compile (EXPLAIN), and it must keep the structural invariants of the original (tables, joins, predicates, DISTINCT, grain, window partitions). A candidate that drops work is rejected, even if it scores better.
  4. Score deterministically from the EXPLAIN findings. The LLM never assigns the score.
  5. Report the result as Markdown. update_view=True applies the best rewrite with REPLACE VIEW.

15. Serving and deployment

  • python -m tdfs4ds.serve runs a FastAPI server. It exposes an OpenAI-compatible API (tdfs4ds-consumer, tdfs4ds-query-optimizer) and an admin web shell (chatbot, report dashboard, lineage, execution plan, migration, evaluation, benchmark, maintenance, parameters).
  • deploy_to_agentops() packages and deploys the consumer agent to Teradata AI Studio AgentOps.
  • check_configuration() validates the LLM, embedding, vector store and MCP configuration and names the setting to fix.
  • evaluate_agent() scores the agent against catalog-grounded scenarios with an LLM judge.

Appendix — Design decisions at a glance

Decision Chosen Main reason Cost accepted
Paradigm Entity-feature only Uniform shape makes everything generic and governable Raw events and non-scalar data must be aggregated or kept elsewhere
Layout Deep (long) Schema-free growth, per-value history, coexisting versions, generic code Pivot at read time; repeated keys
Table split Domain × entity × SQL type Type fidelity, native keys, co-location, project isolation More tables (limited by VARCHAR bucketing)
Table name Deterministic hash Length-safe, reproducible across stores Not human-readable (the catalog is)
Primary index Entity key (overridable) AMP-local joins between features and datasets Skew if the key has low cardinality, so override it
Partitioning RANGE_N(FEATURE_ID … EACH 1) + user levels Per-feature partition elimination for reads, writes and deletes Feature-count ceiling (configurable)
Statistics Refresh, else explicit + sampled, once per run Correct plans for many-join dataset views Collection time per run
NULL keys COALESCE substitute, persisted, enforced Equality-based MERGE and joins stay correct; no skew Consumers see the sentinel value
Duplicates Hard failure One value per entity per time is the contract A bad process fails instead of being silently wrong
Staging Volatile table, PI = entity Session-safe, no permanent space, co-located MERGE Recomputed per run
Write One MERGE per feature Atomic, partition-scoped, temporal-aware N statements per process
Time VALIDTIME everywhere Point-in-time correctness and definition history Temporal SQL rules must be respected
Execution log Separate append-only table Restartability, freshness, failure propagation, audit —
Consumption Views (zero copy) No duplication, always consistent Query-time cost, reduced by the layout choices
Knowledge Structured, temporal rows in-DB Exact retrieval, no chunking, same governance as data —
AI Opt-in, non-fatal, catalog-grounded The store never depends on an LLM —