SSC-FDM-TD0025

Teradata Database Temporal Table is not supported in Snowflake

Description

The Teradata Database Temporal Support involves the creation of temporal tables and temporal DDL and DML objects. The support for temporal (time-aware) tables and data are not supported in Snowflake since there is not an absolute equivalent.

All these statements are recognized (parsed) by SnowConvert, but to execute the queries in Snowflake, these elements are removed in the translation process.

It is worth noting that in cases where an abort statement is encountered, it will be transformed into a Delete command to keep the equivalence functionality allows you to undo operations performed during a transaction and restore the database to the state it had at the beginning.

Example code

The following example shows a Temporal-form Select being translated to a usual Select.

Input code:

IN -> Teradata_01.sql

SEQUENCED VALIDTIME  
   SELECT
   Policy_ID,
   Customer_ID
   FROM Policy
      WHERE Policy_Type = 'AU';

Output code:

OUT -> Teradata_01.sql

----** SSC-FDM-TD0025 - TEMPORAL FORMS ARE NOT SUPPORTED IN SNOWFLAKE **
--SEQUENCED VALIDTIME
SELECT
   Policy_ID,
   Customer_ID
   FROM
   Policy
      WHERE
   UPPER(RTRIM( Policy_Type)) = UPPER(RTRIM('AU'));

Case where the Abort command is used in the context of a transaction.

Input code:

IN -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE TEST.ABORT_STATS()
BEGIN
    CURRENT VALIDTIME AND NONSEQUENCED TRANSACTIONTIME ABORT 
     FROM table_1 
     WHERE table_1.x1 = 1;
END;

Output code:

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE TEST.ABORT_STATS ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        --    CURRENT VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
        --** SSC-FDM-TD0025 - TEMPORAL FORMS ARE NOT SUPPORTED IN SNOWFLAKE **
        LET _ROW_COUNT FLOAT;
        SELECT
            COUNT(*)
        INTO
            _ROW_COUNT
            FROM
            table_1
                 WHERE table_1.x1 = 1;
            IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
            END IF;
    END;
$$;

Recommendations

• No additional user actions are required.

• If you need more support, you can email us at [email protected]