MSCEWI5003

PERIOD DEFINITION IS NOT SUPPORTED IN SNOWFLAKE.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

DB2 temporal tables do not have a functional equivalent in Snowflake. When an application-period or system-period temporal table declaration is found in the CREATE TABLE columns, that column is commented out from the resulting script. The behavior of the SELECT statement will differ from Snowflake due that temporal tables are not part of the Snowflake solution and this causes the result to be different if the Select statement is migrated partially, see the example below for more information about this.

Select Query

SELECT 
  ID,
  Start,
  END
FROM 
  timetable 
FOR system_time as of '2022-05-09-16.20.17.0';

Result

If the Select statement is migrated partially we get a very different result as shown below. For this reason, the complete Select statement will be commented out with this EWI

Select Query

SELECT 
  ID,
  Start,
  END
FROM 
  timetable 
-- FOR system_time as of '2022-05-09-16.20.17.0';

Result

Code example

DB2

Create table

CREATE TABLE T1(
COL1 DATE,
COL2 DATE,
PERIOD SYSTEM_TIME (COL1, COL2)
)

Select Query

Snowflake

Create Table

CREATE TABLE PUBLIC.T1(
COL1 DATE,
COL2 DATE
-- MSC-WARNING - MSCEWI5003 - PERIOD DEFINITION IS NOT SUPPORTED IN SNOWFLAKE.
-- PERIOD SYSTEM_TIME (COL1, COL2)
)

Select Query

Recommendations

• Snowflake allows the storage of historical table data for up to 90 days, to know more about this see Understanding & Using Time Travel.

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