← Training overview

9. NULLs in entities

A subtle production trap: if any entity-ID column in your source DataFrame can be NULL, the rows with NULL keys are silently dropped during ingestion unless you tell tdfs4ds what sentinel value to use instead. This module explains why, and how to declare the substitution.

The problem

In SQL, NULL = NULL is unknown (not true), so an equi-join on an entity-ID column never matches when either side is NULL. The feature-store ingestion MERGE joins the incoming DataFrame to the existing feature table on the entity-ID columns to decide insert-vs-update; rows whose entity ID is NULL therefore neither update an existing row nor insert a new one — they just disappear, without an error.

The same issue surfaces on the SELECT that unpivots the source into the volatile staging table, because the entity columns also serve as a primary index there.

The solution

upload_features accepts an entity_null_substitute dict that maps each entity-ID column to a sentinel:

tdfs4ds.upload_features(
    df=df,
    entity_id=['customer_id', 'merchant_code'],
    feature_names=['amount'],
    entity_null_substitute={
        'customer_id':   -1,         # numeric sentinel
        'merchant_code': 'UNKNOWN',  # string sentinel
    },
)

Under the hood, tdfs4ds rewrites every comparison on those columns as COALESCE(col, sentinel) on both sides — the staging SELECT and the join predicate of the MERGE. NULL rows are now joined and stored under the sentinel value, and downstream queries can find them.

Choosing a sentinel

The sentinel must be a value that cannot collide with a legitimate entity ID. Common safe choices:

Entity-ID type	Sentinel
BIGINT / INTEGER	-1 (or another large negative reserved value)
VARCHAR	'UNKNOWN', '__NULL__', or a magic UUID
DATE	DATE '1900-01-01'
TIMESTAMP	TIMESTAMP '1900-01-01 00:00:00'

If a sentinel can collide with real data, you get a worse bug: legitimate rows merge with the NULL bucket.

What if you forget

There is no error and no warning. Inspect the row counts after a load:

import teradataml as tdml
tdml.execute_sql(
    "SELECT COUNT(*) FROM SRC_TABLE WHERE customer_id IS NULL"
).fetchall()

If the count is non-zero and you didn't pass entity_null_substitute, those rows are not in the feature store.

Composability

entity_null_substitute works alongside FilterManager and TimeManager: the substitution is applied at SELECT and MERGE time for every iteration, so segmented and replayed ingestion behave consistently.

Learn more

• upload_features — the parameter is part of the public signature.
• Data model — entity-ID semantics and the hidden-tables pattern.