SSC-FDM-TD0025

Teradata Database Temporal Table is not supported in Snowflake

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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]