TD_SimpleImputeTransform

TD_SimpleImputeTransform is a Teradata analytic function that prepares data by replacing missing values with practical substitutes. Depending on the column type and configuration, it can use values such as the mean, median, mode, or a user-defined constant. This helps machine-learning models work with complete datasets without removing rows that contain missing information. Because the transformation runs inside Teradata Vantage, data can be cleaned efficiently without first moving it to an external Python environment.

Running TD_SimpleImputeTransform in Teradata provides several benefits beyond simply filling missing values. The transformation runs close to the data, reducing the need to move large datasets between Teradata and external Python or analytics systems. This can improve performance, lower network usage, and help maintain data governance by keeping sensitive information inside the database. Teradata can also apply the transformation using its massively parallel processing architecture, making it suitable for large enterprise tables. In addition, performing imputation in the database creates a consistent, repeatable preprocessing step that can be reused across development, testing, and production workflows.

The syntax for TD_SimpleImputeTransform depends on whether a partition column was specified during the TD_SimpleImputeFit step. If no partitioning was used, the fit table is referenced as a dimension table. If partitioning was used, both the input and fit tables must be partitioned using the same column.

Without a Partition Column

TD_SimpleImputeTransform (
    ON { table | view | (query) }
        AS InputTable
        PARTITION BY ANY

    ON { table | view | (query) }
        AS FitTable
        DIMENSION
)

With a Partition Column

TD_SimpleImputeTransform (
    ON { table | view | (query) }
        AS InputTable
        PARTITION BY partition_column

    ON { table | view | (query) }
        AS FitTable
        PARTITION BY partition_column
)

TD_SimpleImputeTransform Syntax • Database Analytic Functions • Reader • Teradata Documentation

This example uses the first syntax because replacement values are calculated across the entire dataset rather than separately for individual groups.

1. Create Sample Data

To demonstrate the imputation process, the example begins by creating a simple banking customer table containing missing values. Several records have null values for account type, credit score, and annual income, providing data that can be cleaned using the Teradata analytic functions.

CREATE TABLE simpleimpute_input (
    customer_id INTEGER,
    account_type VARCHAR(12),
    credit_score INTEGER,
    annual_income DECIMAL(10,2),
    defaulted INTEGER
);

INSERT INTO simpleimpute_input
VALUES (3, NULL, NULL, NULL, 1);

2. Generate Replacement Values with TD_SimpleImputeFit

The TD_SimpleImputeFit function analyses the data and determines the values that should be used to replace missing entries. In this example, missing account types are replaced with 'checking', while missing credit scores and annual income values are replaced using the mean and median respectively.

CREATE TABLE fit_table AS (

    SELECT *

    FROM TD_SimpleImputeFit (

        ON simpleimpute_input AS InputTable

        USING

        ColsForLiterals (
            'account_type'
        )

        Literals (
            'checking'
        )

        ColsForStats (
            'credit_score',
            'annual_income'
        )

        Stats (
            'mean',
            'median'
        )

    ) AS dt

) WITH DATA;

3. Apply the Replacement Values

Once the fit table has been created, TD_SimpleImputeTransform applies the generated replacement values to the source data. Because no partition column was specified during the fit step, the transform uses PARTITION BY ANY and references the fit table as a dimension table.

SELECT *

FROM TD_SimpleImputeTransform (

    ON simpleimpute_input
        AS InputTable
        PARTITION BY ANY

    ON fit_table
        AS FitTable
        DIMENSION

) AS dt

ORDER BY customer_id;

After the transformation completes, all null values are replaced using the rules defined in the fit step, producing a dataset that is ready for downstream analytics and machine learning workloads.

TD_SimpleImputeTransform.tdsql